云计算课程笔记第六册mysql数据库

三、主流数据库介 四、用户权限设置 操 五、企业数据库备份..................................................................................................... 一、引擎概念介 四、企业选择引擎的依 二、主从原理原 （Data， 用户可以通过DBMS操作数据库，也可以通过应用程序操作数据库20701970IBM研究员2080年代初，IBMDB2Oracle 中。例如，Oracle支持的“关系-对象”数据库模型。2080~90年代是关系数据库产品发展和竞争的时代，SQLServer、Oracle、DB2、MySQL等一批很有实力的关系数据库产品走到了主流商品数据库的位置。面向Windows操作系统DB2（IBM公司产品）关系”（ER）图来直接表示。E-R图中包含了实体（数据对象、关系和属性三个要素。 关系数据库的结构是二维表格，反映事物及其联系的数据是以表格形式保存的，在云计 第六 orientedsession共享：在负载均衡集群中，用户时，前一秒与后一秒提供服务的sessionMemcachedsession共享可实现同一个用户登录时能获得同一个session也是一个以key-value方式数据看，数据也是保存在内存中，但会定期将数据写入磁盘中，相对于Memcached有一下特点： MySQL社区版是由分散在世界各地的MySQL开发者、者一起开发与，可以免·商业版MySQL的地址 [root@mysql~]#rpm-qa|grep[root@mysql~]#yum-yinstallncurses-devel[root@mysql~]#tarxfcmake-2.8.6.tar.gz-C/usr/src/[root@mysql~]#cd/usr/src/cmake-2.8.6/增加程序用户[root@mysql~tarxfmysql-5.5.22.tar.gzCusr/src/cmake配置，编译及安装 -- - -- - //- - - fmy-finnodb引擎fffMySQL数据库引擎[root@mysqlmysql-5.5.22]#cpsupport-files/mysql.server/etc/init.d/mysqld[root@mysqlmysql-5.5.22]#od+x/etc/init.d/mysqld[root@mysql~]#chkconfigmysqld--add[root@mysql~]#chkconfigmysqld-- 0:关闭1:关闭2:启用3:启用4:启用5:启用6:[root@mysql~]#./etc/profile[root@mysql~]#echo Starting 0连接并登录到mysqlmysqlu-p-h-P[root@mysql~]#-[root@mysql~]#mysqladmin-urootpassword'123123';history-c[root@mysql~]#mysql-uroot-pEnterYourMySQLconnectionidisCopyright(c)2000,2011,Oracleand/oritsaffiliates.AllrightsOracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.OthernamesmaybetrademarksoftheirrespectiveType'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>^DByeShuttingdown [root@mysql~]#mysqld_safe--skip-grant-tables&[root@mysql~]#jobs [root@mysql~]#mysqletotheMySQLmonitor.Commandsendwith;orYourMySQLconnectionidisCopyright(c)2000,2011,Oracleand/oritsaffiliates.AllrightsOracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.OthernamesmaybetrademarksoftheirrespectiveType'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>usemysql; Changed: mysql>flushQueryOK,0rowsaffected(0.00 Starting etotheMySQLmonitor.Commandsendwith;orYourMySQLconnectionidisCopyright(c)2000,2011,Oracleand/oritsaffiliates.AllrightsOracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.OthernamesmaybetrademarksoftheirrespectiveType'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>quit[root@mysql~]# 2627 =28 =30ShuttingdownStarting [root@mysql~]#QueryOK,1rowaffected(0.05 Changed: mysql>flushQueryOK,0rowsaffected(0.10修 etotheMySQLmonitor.Commandsendwith;orYourMySQLconnectionidis 132134auto-Shuttingdown Starting [root@mysql~]#mysql-uroot-pEnterpassword:123123etotheMySQLmonitor.Commandsendwith;orYourMySQLconnectionidisCopyright(c)2000,2011,Oracleand/oritsaffiliates.AllrightsOracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.OthernamesmaybetrademarksoftheirrespectiveType'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatementmysql>showdatabases; | | | 4rowsinset(0.00mysql>use createdatabasecreatetable mysql>exitEntermysql>charsetutf8;Charsetchanged[root@mysql~]# [SQL格式：showdatabases; 0 Enter格式：use数据库名；

格式：describe[数据库.]表名格式：createdatabase数据库名;createtable(定义字段); QueryOK,1rowaffected(0.00mysqluse mysql>createtableusers(user_namechar(20)notnull,user_passwdchar(30)default'',primarykey(user_name)); 在auth库中，创建名为users的表，表内两个字段为（user_name20user_passwd30键字user_name）QueryOK,0rowsaffected(0.10格式：droptable数据库名.]表名;dropdatabase数据库名;mysqldroptable QueryOK,0rowsaffected(0.04mysqldropdatabaseauth;//authQueryOK,0rowsaffected(0.03mysql>useauth;Databasechangedmysql>descusers;2rowsinset(0.00//users表的（字段user_name，字段user_passwd）中添加（zhangsanQueryOK,1rowaffected,1warning(0.04>'));//QueryOK,1rowaffected,1warning(0.00mysql>mysql>select*from2rowsinset(0.00格式：updateset1=12=2where条件表达式;QueryOK,1rowaffected(0.04 Changed: mysql>select*from2rowsinset(0.00) QueryOK,3rowsaffected(0.00 Changed: mysqlflush QueryOK,0rowsaffected(0.00格式：deletefromwhere条件表达式;mysql>deletefromauth.userswhereuser_name='lisi'; 户名为lisi的记录QueryOK,1rowaffected(0.04sec)mysql>select*fromauth.users;1rowinset(0.00mysql>deletefrommysql.userwhereuser=''; //mysqluser表中，删除空的用户QueryOK,2rowsaffected(0.03select1,2,……fromwhere条件表达式建立IT_salary数据表，以保存IT运营部员工的工资信息，如下表所示：mysql>show | | | | 5rowsinset(0.00mysql>createdatabaseimployee_salary;QueryOK,1rowaffected(0.00sec)mysql>useimployee_salary;mysql>createtableIT_salary(岗位类别char(20)notnull,char(20)notnull,IDintnotnull,char(6),int,intnotnull,primarykey//int数字类型、char字符串类型、notnull不能为空、char()指定最多字节个数、primarykey()指定索引字段IT运营部的员工工资信息插入到,mysqlinsertintoIT_salary(岗位类别,,QueryOK,1rowaffected(0.01

,,,,员工)mysql>grantallon*.*to'root'@'08'IDENTIFIEDBYQueryOK,0rowsaffected(0.00QueryOK,0rowsaffected(0.00sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)[root@mysql~]#mysql-uamber-p123456etotheMySQLmonitor.Commandsendwith;orYourMySQLconnectionidisERROR1142(42000):DELETEcommanddeniedtouser'amber'@'localhost'fortablemysql>showgrants;mysql>showgrantsfor'amber'@'localhost';QueryOK,0rowsaffected(0.00sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.00MySQL通配符：mysql>grantallon*.*to'amber'@'%'identifiedbyQueryOK,0rowsaffected(0.00sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.00mysql>GRANTALLPRIVILEGESON*.*TO'amber'@'%'IDENTIFIEDBY'123123'WITHGRANTmysql>FLUSHhelpcreatedatabase；helpcreatetables；showerrors；mysql>selectmysql>selectmysql>selectmysql>selectmysql>select mysqldump-u用户名-p[][选项][数据库名][表名]>/备份路径/备份文件名--[root@www~]#mysqldump-uroot-pmysqluser>mysql-user.sqlEnterpassword:[root@www~]#mysqldump-uroot-p--databaseauth>auth.sqlEnterpassword:[root@www~]#mysqldump-uroot-p--opt--all-databases>all-data.sqlEnterpassword:mysqlurootp数据库名备份路径/备份文件名[root@www~]#mysqluroot-ptestmysql-user.sqlEnterpassword:MySQL3.23.23MySQL中，全文索引的类型为格式：CREATEINDEX索引的名字ON表名(字段);QueryOK,0rowsaffected(0.24 mysql>showindexfromIT_salary //showkeysfromIT_salary***************************1.row***************************Table:IT_salaryNon_unique:0Seq_in_index:1Column_nameIDCollation:ACardinality:2Sub_part:NULL***************************2.row***************************Table:IT_salaryKey_namesalary_indexSeq_in_index:1Column_name:薪资Collation:ACardinality:5Sub_part:NULL2rowsinset(0.00格式：CREATEUNIQUEINDEX索引的名字ON表名(字段);QueryOK,0rowsaffected(0.06sec)Records:0 Duplicates:0Warnings:0mysql>showkeysfromIT_salary\G;***************************1.row***************************Table:IT_salaryNon_unique:0Seq_in_index:1Column_nameIDCollation:ACardinality:5Sub_part:NULLIndex_type:BTREE***************************2.row***************************Table:IT_salarySeq_in_index:1Column_name:Collation:ACardinality:5Sub_part:***************************3.row***************************Table:IT_salarySeq_in_index:1Column_name薪资Collation:ACardinality:5Sub_part:NULLIndex_type:BTREE3rowsinset(0.00格式：CREATETABLE表名([…],PRIMARYKEY(字段));ALTERTABLEADDPRIMARYKEY(字段mysql>createtableIT_salary(岗位类别char(20)notnull,char(30)notnull,年龄mysql>altertableIT_salaryaddprimarykey(ID);QueryOK,0rowsaffected(0.07sec) 格式：SHOWINDEXFROM表名;SHOWKEYSFROM表名;mysql>showindexfromIT_salary***************************1.row***************************Table:IT_salaryNon_unique:0Seq_in_index:1Column_nameIDCollation:ACardinality:5Sub_part:NULLIndex_type:BTREENon_unique:0Seq_in_index:1Column_name:Collation:ACardinality:5Sub_part:Index_type:BTREE***************************3.row***************************Table:IT_salaryNon_unique:1Seq_in_index:1Column_name:薪资Collation:ACardinality:5Sub_part:NULL3rowsinset(0.00mysql>showkeysfromIT_salary***************************1.row***************************Table:IT_salaryNon_unique:0Seq_in_index:1Column_nameIDCollation:ACardinality:5Sub_part:NULL***************************2.row***************************Table:IT_salarySeq_in_index:1Column_name:Collation:ACardinality:5Sub_part:Index_type:BTREE***************************3.row***************************Table:IT_salaryNon_unique:1Seq_in_index:1Column_name:薪资Collation:ACardinality:5Sub_part:NULL3rowsinset(0.00格式：DROPINDEX索引的名称ON表名ALTERTABLEDROPINDEX索引的名称>;ALTERTABLE表名DROPPRIMARYKEY;mysql>dropindexsalary_indexonQueryOK,0rowsaffected(0.10 mysql>altertableIT_salarydropindexQueryOK,0rowsaffected(0.01 mysql>altertableIT_salarydropprimaryRecords:5Duplicates:0Warnings:0mysql>showkeysfromIT_salary;Emptyset(0.00sec) commit提交一个事rollback回滚一个事务（撤销）mysql>use QueryOK,0rowsaffected(0.00mysql>insertintousersQueryOK,1rowaffected,1warning(0.00mysql>insertintousersQueryOK,1rowaffected,1warning(0.00mysql> QueryOK,0rowsaffected(0.02mysql>select*from |user_name| | | | | 3rowsinset(0.00mysql>use QueryOK,0rowsaffected(0.00QueryOK,1rowaffected(0.00 Changed: mysql> QueryQueryOK,0rowsaffected(0.00mysql>select*from |user_name| | | | | 3rowsinset(0.00 mit=0自动提 mit=1开启自动提交mysql> mit= QueryOK,0rowsaffected(0.02mysql>usemysql>insertintousersQueryOK,1rowaffected,1warning(0.00mysql>insertintousersQueryOK,1rowaffected,1warning(0.00mysql> QueryOK,0rowsaffected(0.04mysql>select*from |user_name| | | | | | | | 5rowsinset(0.00mysql> mit QueryOK,0rowsaffected(0.00mysql>insertintousers QueryOK,1rowaffected,1warning(0.00mysql>insertintousers QueryOK,1rowaffected,1warning(0.04mysql>select*from |user_name| | | | | | | | | | | 7rowsinset(0.00mysql>createuser'admin'@'%'identifiedby‘mysql>dropuser格式：RENAMEUSER‘原用户名’@’原主机’to‘新用户名’@’新主机’; //可以只修改用户名QueryOK,0rowsaffected(0.00mysql>renameuseramber'@'192.168.2toadmin'@'192.168.2QueryOK,0rowsaffected(0.03mysql>renameuseradmin'@'192.168.2to QueryOK,0rowsaffected(0.00mysql>renameuseradmin'@to QueryOK,0rowsaffected(0.00 mysql>select | 1rowinset(0.00mysql>set QueryOK,0rowsaffected(0.00mysql>etotheMySQLmonitor.Commandsendwith;orYourMySQLconnectionidisQueryOK,0rowsaffected(0.00inet [root@mysql~]#mysql-uamber-p123456-etotheMySQLmonitor.Commandsendwith;orYourMySQLconnectionidismysql>select | 1rowinset(0.00[root@mysql~]#vim [root@mysql~]#etotheMySQLmonitor.Commandsendwith;orYourMySQLconnectionidisCopyright(c)2000,2011,Oracleand/oritsaffiliates.AllrightsOracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.OthernamesmaybetrademarksoftheirrespectiveType'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputQueryOK,4rowsaffected(0.01 Changed: mysql>flushQueryOK,0rowsaffected(0.00mysql>[root@mysql~]#vim [root@mysql~]#/etc/init.d/mysqldrestartShuttingdownMySQL.SUCCESS!StartingMySQL..etotheMySQLmonitor.Commandsendwith;orYourMySQLconnectionidisShuttingdownMySQL.[1][root@mysql~]#16042816:03:37mysqld_safeLoggingto'/usr/local/mysql/data/mysql.err'. [root@mysql~]#etotheMySQLmonitor.Commandsendwith;orYourMySQLconnectionidisCopyright(c)2000,2011,Oracleand/oritsaffiliates.AllrightsOracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.OthernamesmaybetrademarksoftheirrespectiveType'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputQueryOK,4rowsaffected(0.00 Changed: mysql>flushQueryOK,0rowsaffected(0.00mysql> StartingMySQL.. etotheMySQLmonitor.Commandsendwith;orYourMySQLconnectionidis格式：GRANT权限列表ON库名.表名TO‘用户名’@’来源地址’[IDENTIFIEDBY‘QueryOK,0rowsaffected(0.00mysql>grantallon*.*to'amber'@'%'identifiedbyQueryOK,0rowsaffected(0.00mysql>grantallon*.*to'admin'@'%'identifiedby'123123'withgrantQueryOK,0rowsaffected(0.00格式：SHOWSHOWGRANTSFOR用户名’@’来源地址mysql>show+ |++|GRANTALLPRIVILEGESON*.*TO'root'@'localhost'IDENTIFIEDBYPASSWORD EE4568DDA7DC67ED2CA2AD9'WITHGRANTOPTION| ON |+2rows2rowsinset(0.00mysql>showgrantsfor++ |++ '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1'| 1rowinset(0.00mysql>showgrantsfor+ |+ '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1'WITHGRANTOPTION|+1rowinset(0.00格式：REVOKEON库名.FROM用户名’@’来源地址mysql>showgrantsfor | 1rowinset(0.00' //amber的dropcreateQueryOK,0rowsaffected(0.00mysql>showgrantsfor++ |++|GRANTSELECT,INSERT,UPDATE,DELETE,RELOAD,SHUTDOWN,PROCESS,FILE,REFERENCES,INDEX,ALTER,SHOWDATABASES,SUPER,CREATETEMPORARYTABLES,LOCKTABLES,EXECUTE,REPLICATIONSLAVE,REPLICATION,CREATEVIEW,SHOWVIEW,CREATEROUTINE,ALTERROUTINE,CREATEUSER,EVENT,TRIGGER,CREATETABLESPACEON*.*TO'amber'@'%'IDENTIFIEDBYPASSWORD'*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1'|++1rowinset(0.00mysql>revokeallon*.*from QueryOK,0rowsaffected(0.00mysql>showgrantsfor | |GRANTUSAGEON*.*TO'amber'@'%'IDENTIFIEDBYPASSWORD'*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1'| 1rowinset(0.00ALTER：允许使用ALTERTABLE，修改表CREATEUSER：用户管理权限DELETE：允许使用DELETE，删除数据DROP：允许使用DROPTABLE，删除表MySQL启动和停止时，以及服务器在运行过程中发生的任何错误时的相关信息，默认在安装/usr/local/mysql/data/下的“主机名err可在登录时使用“--log-error=mysqld保存错误日志的位置；或者修改主配置文件f，在[mysqld]下方添加“log-error=文件路径及文件mysqld服务。[root@mysql~]# log-error ShuttingdownMySQL.SUCCESS!StartingMySQL..SUCCESS![root@mysql~]#cat 16042816:40:45InnoDB:Mutexesandrw_locksuseGCCatomicbuiltins16042816:40:45InnoDB:Compressedtablesusezlib1.2.316042816:40:45InnoDB:Initializingbufferpool,size=128.0M16042816:40:45InnoDB:Completedinitializationofbufferpool16042816:40:45InnoDB:highestsupportedfileformatisBarracuda.16042816:40:45 InnoDB:Waitingforthebackgroundthreadstostart16042816:40:46InnoDB:1.1.8started;logsequencenumber16042816:40:46[Note]EventScheduler:Loaded016042816:40:46[Note]/usr/local/mysql/bin/mysqld:readyforconnections.Version:'5.5.22-log'socket:'/tmp/mysql.sock'port:3306 SourcedistributionMySQL所有连接和语句都将会被记录。默认关闭此项日志记录，一般用作调试用，平可在登录时使用“--log=文件路径及文件名”或“-l文件路及文件名”选项指定；或修改主配置文件f，在[mysqld]下添加“logON”和“log=文件路径及文件名”，重启mysqld服务。[root@mysql~]# log=log=/usr/local/mysql/data/mysql_general.log //绝对路径可以省略，保证mysql对该 [root@mysql~]#tail-f/usr/local/mysql/bin/mysqld,Version:5.5.22-log(Sourcedistribution).startedwith:Tcpport:3306Unixsocket:/tmp/mysql.sock Id EnteretotheMySQLmonitor.Commandsendwith;orYourMySQLconnectionidismysql>showmysql>useauth;Databasechangedmysql>showtables; 要目的是在恢复时能够最大可能地恢复数据库。默认是开启的，默认路径在mysqlbinlog命令查看二进制日志文件。[root@mysql~]# [root@mysql~]#cd/usr/local/mysql/data/[root@mysqldata]#mysqlbinlogmysql-bin.000001径及文件会，会在/usr/local/mysql/data下生成“主机名-slow.log”；或修改主配置文件f，在[mysqld]下添加“long_query_time”和“log-slow-queries=文件路径及文件[root@mysql~]# long_query_time=5 =[root@mysql~]#/etc/init.d/mysqldrestartShuttingdownMySQL.SUCCESS!StartingMySQL..[root@mysql~]#ll-rw-rw1mysqlmysql1754月2816:59QueryOK,1rowaffected(0.05格式：CREATETABLE表名(DEFAULTmysql>useQueryOK,0rowsaffected(0.01格式：SETNAMES //或CHARSETmysql>setnamesQueryOK,0rowsaffected(0.00Charset[root@mysql~]# //注意，不是[mysqld]！！！否则启动错！！！（若为yum安装的不会报错ShuttingdownMySQL.SUCCESS!StartingMySQL..SUCCESS!·（如火灾、）11、直接打包数据库文件夹，如/usr/local/mysql/data/QueryOK,1rowaffected(0.00mysql>usemysql>createtableuser(namechar(10)notnull,IDQueryOK,0rowsaffected(0.04mysql>insertintouservalues('amber','123');QueryOK,1rowaffected(0.01sec)mysql>select*from | | |amber 123 1rowinset(0.02Shuttingdown [root@mysql~]#yum-yinstalltar:[root@mysql~]#tarxfmysql_all-2016-05-05.tar.xz[root@mysql~]#cdusr/local/mysql/data/[root@mysqldata]#mv*/usr/local/mysql/data/[root@mysqldata]#/etc/init.d/mysqldstartStarting[root@mysql~]#mysql-uroot-p123123mysql>select*fromauth.user; | | |amber 123 1rowinset(0.00库、表或全部的库导出为SQL，在需要恢复时可进行数据恢复。格式：mysqldump-u用户名-p[][选项][数据库名]>/备份路径/备份文件名[root@mysql~]#mysqldump-uroot-p123123auth>/backup/auth-$(date+%Y%m%d).sql[root@mysql~]#echo$?0 格式：mysqldump-u用户名-p[][选项]--databases库名1[库名2]…>/备份路径 - /backup/mysql+auth-$(date+%Y%m%d).sql[root@mysql~]#cat/backup/mysql+auth- 格式：mysqldump-u用户名-p[][选项]--all-databases>/备份路径/备份文件名[root@mysql~]#mysqldump-uroot-p123123--opt--all-databases //--opt[root@mysql~]#cat 格式：mysqldump-u用户名-p[][选项]数据库名表名>/备份路径/备份文件名[root@mysql~]#mysqldump-uroot-p123123authuser>/backup/auth_user- 格式：mysqldump-u用户名-p[]-d数据库名表名>/备份路径/备份文件名[root@mysql~]#cat mysql>show | | | | 5rowsinset(0.00mysql>dropdatabaseQueryOK,1rowaffected(0.05mysql>source/backup/mysql_all. mysql>showdatabases; | | | | 5rowsinset(0.00格式：mysql-u用户名-p[]<库备份的路mysql-u用户名-p[]库名<表备份的路[root@mysql~]#mysql-uroot-p123123-e'show | | | ||+|+[root@mysql~]#mysql-uroot-p123123-e'dropdatabase[root@mysql~]#mysql-uroot-p123123</backup/mysql_all. [root@mysql~]#mysql-uroot-p123123-e'showdatabases;' | | | | [root@mysql~]#mysql-uroot-p123123-e'droptable[root@mysql~]#mysql-uroot-p123123auth</backup/auth_user- [root@mysql~]#mysql-uroot-p123123-e'select*fromauth.user;' | | |amber 123 以对MySQL进行最大化还原）3、使用统一和易理解的备份名称，推荐使用库名或者表名加上时间名规则，如 user_info，表结构如[root@mysql~mysqlurootp123123mysql>createdatabase;QueryOK,1rowaffected(0.00mysql>usemysql>showvariableslike | | |character_set_| |character_set_connection| | | | | | | | | | 8rowsinset(0.00mysql>createtableuser_info(int(20),char(20),char(2),用户ID号QueryOK,0rowsaffected(0.01 QueryOK,1rowaffected(0.01 ','蓝凌','女QueryOK,1rowaffected(0.01','姜纹','女QueryOK,1rowaffected(0.00 ','关园','男QueryOK,1rowaffected(0.01 QueryOK,1rowaffected(0.01mysql>select*from ||||用户ID |资 1 | 11 100 2|蓝 | 12 98 3|姜 | 13 12 4|关 | 14 38 4|罗中 | 15 39 5rowsinset(0.00[root@mysql~]#mysql-uroot-p123123-e'droptable.user_info;'[root@mysql~]#mysql-uroot-p123123-e'use;showtables;'[root@mysql~]#mysql-uroot-p123123</backup/.user_info- [root@mysql~]#mysql-uroot-p123123-e'select*from.user_info;' ||||用户ID |资 1009812383910098123839 2|蓝 | 12 3|姜 | 13 4|关 | 14 4|罗中 | 15 [root@mysql~]#which#备 表/usr/local/mysql/bin/mysqldump-uroot-p123123[root@mysql~]#od+x/opt/bak_.sh[root@mysql~]#vim1.cron [root@mysql~]#crontab-l 实验要求：对mysql-server的auth库和 MySQL服务端，给予select和locktables权限，以备 QueryOK,0rowsaffected(0.00mysql>grantselect,locktableson.*to'admin'@'8'identifiedby QueryOK,0rowsaffected(0.00mysql>flushQueryOK,0rowsaffected(0.00[root@~]#rpm-qa|grepmysql[root@~]#yum-yinstallmysql[root@~]#mysql-uadmin-p123123-h08mysql>showdatabases; | | || 4rowsinset(0.00MySQL数据库备份#设置登录变量MySQL数据库备份#设置登录变量#设置备份的数据库 定义备份路径、工具、时间、文件名BF_TIME=$(date+%Y%m%d-%H%M)#备份为.sql [-d$BF_DIR]||mkdir-p$BF_DIRcd$BF_CMD$MY_CONN--databases$MY_DB1>$BF_CMD$MY_CONN--databases$MY_DB2>[root@~]#od+x/opt/bakmysql.sh[root@~]#/opt/bakmysql.sh[root@~]#ls - -[root@~]#tartvf --rw-r--r-- -[root@~]#tartvf/backup/ --rw-r--r-- 22502016-05-0518:05 -[root@~]#crontab- ~date //MMDDhhmmYY.SS月日小时分钟.2016050601:59:59[root@~]#ls - -[root@~]#date2016050701:59:59[root@~]#date2016050801:59:59[root@~]#ls #MySQL数据库数据#设置变量#MySQL数据库数据#设置变量mkdir.aaals$BF_DIR|column-t>awk-F'-''{print$2}'.aaa/db_list>if[$dt-ge ]&&[$dt-le grep"$dt".aaa/dt.txt&>/dev/nullif[$?-ne0];then"echo"搜索到的可恢复数据库如下:"readp请选择您要恢复数据库的编号nm=$(awkF$dt/'{printNR,$1aaa/db_list|awk$nb/'{print$2}')echo"现在开始恢复数据库:$nm到$dt"cdmkdir.bbbtarxf$onm-Cmysqlu$MY_USERp$MY_PASSh$MY_HOST$nmbbb/*echo"$nm已经恢复到$dt"cd-&>/dev/null[root@~]#od+xmysql>grantallonauth.*toQueryOK,0rowsaffected(0.00mysql>grantallon.*toQueryOK,0rowsaffected(0.00mysql>flushQueryOK,0rowsaffected(0.00[root@~]#37请选择您要恢复数据库的编号3现在开始恢复数据库:auth到auth已经恢复到[root@~]#mysqldump进行完全备份，备份的数据中有重复数据，备份时间与恢复时间长。·二进制日志在启动MySQLmax_binlog_size所设置flushlogs命令后重新创建新的日志文件。[root@mysql~]# 52max_binlog_size ·flushlogs方法重新创建新的日志，生成二进制文件序列，并及时把这方法一：MySQL的配置文件的[mysqld]项中加入log-bin=文件存放路径/文件前缀，如 格式：mysqlbinlogno-defaults]|mysqlu-mysqlbinlogno-defaults]--stop-datetime=’年-月-:分钟:秒’|-u用户名-pmysqlbinlogno-defaults]--start-datetime=’年-月-:分钟:秒’|-u用户名-pmysqlbinlogno-defaults]--start-datetime=’年-月-:分钟:秒’--stop-datetime=’年-月-日小时:分钟:秒’二进制日志|mysql-u用户名-pmysqlbinlogstop-position=’id’|mysqlu-pmysqlbinlogstart-position=’id’|mysqlu-p1mysql是处于哪种表类型下工作的，它们支持事物处理还是非事物的，因为我大的灵活性，可以基于时间点或是位置进行恢复。考虑到数据库性能，我们可以将binlog 架 f"？y[root@mysql~]#vim [root@mysql~mysqlurootp123123mysql>createdatabase;mysql>usemysql>createtableuser_info(char(20)notnull,char(20)notnull,char(4)用ID号char(10)notnull,资费int(10));mysql>insertintouser_infovalues(' mysql>insertintouser_infovalues(' mysql>insertintouser_infovalues(' [root@mysql~]#mysqldump-uroot-p123123>/mysql_bak/-$(date+%F).sql[root@mysql~]#ls/mysql_bak/ [root@mysql~]#lsmysql [root@mysql~]#mysqladmin-uroot-p123123flush-logs[root@mysql~]#ls/var/lib/mysql/mysql [root@mysql~]#mysql-uroot-p123123mysql>use;mysql>insertintouser_infovalues(' mysql>insertintouser_infovalues(' [root@mysql~]#lsmysql [root@mysql~]#cd[root@mysqlmysqlmysqlbinlogno-defaultsmysql-bin.000006//查看新操作的日志记录[root@mysqlmysql]#cp-pmysql-bin.000006/mysql_bak/[root@mysql~]#mysql-uroot-p123123-e'droptable.user_info;[root@mysql~]#mysql-uroot-p123123-e'select*from.user_info;'ERROR1146(42S02)atline1:Table'.user_info'doesn't[root@mysql~]#mysql-uroot-p123123</mysql_bak/_userinfo-2016-05-07.sql[root@mysql~]#mysql-uroot-p123123-e'select*from.user_info;'[root@mysql~]#mysqlbinlog--no-defaults/mysql_bak/mysql-bin.000006|mysql-[root@mysql~]#mysql-uroot-p123123-e'select*from[root@mysql~]#mysql-uroot-p123123-e'droptable.user_info;'[root@mysql~]#mysql-uroot-p123123-e'select*from.user_info;'ERROR1146(42S02)atline1:Table'.user_info'doesn't/*!40019SET/*!40019SET [root@mysql~]#mysql-uroot-p123123-e'select* [root@mysql~]#mysql-uroot-p123123-e'droptable[root@mysql~]#mysql-uroot-p123123</mysql_bak/_userinfo-2016-05-07.sql [root@mysql~]#mysql-uroot-p123123-e'select*from[root@mysql~]#mysql-uroot-p123123-e'droptable.user_info;'[root@mysql~]#mysql-uroot-p123123-e'select*from.user_info;'ERROR1146(42S02)atline1:Table'.user_info'doesn't[root@mysql~]#mysql-uroot-p123123</mysql_bak/_userinfo-2016-05-07.sql[root@mysql~]#mysqlbinlog--no-defaults--stop-position='241'/mysql_bak/mysql-bin.000006[root@mysql~]#mysql-uroot-p123123-e'select*from[root@mysql~]#mysql-uroot-p123123-e'droptable[root@mysql~]#mysql-uroot-p123123</mysql_bak/_userinfo-2016-05-07.sql [root@mysql~]#mysql-uroot-p123123-e'select* #设置登录变量#设置登录变量#设置备份的数据库(或表) 定义备份路径、工具、时间、文件名BF_TIME=$(date+%Y%m%d-%H%M)#备份为.sql [-d$BF_DIR]||mkdir-p$BF_DIRcd$BF_CMD$MY_CONN--databases$MY_DB>[root@mysql~vim #设置登录变量定义备份路径、工具、二进制日志前缀、二进制日志存放路径BF_TIME="$(date+%Y%m%d)"#拷贝二进制日志[-d$BF_DIR]||mkdir-p/bin/cp-p$(ls$LOG_DIR/$QZ.*|awk-vRS=""'{print$(NF-2)}')[root@mysql~]#od+x/opt/mysql_bak_*[root@mysql~]#crontab-e00 00 MySQL中的数据用各种不同的技术在文件系统中，每一种技术都使用不同的在MySQL中称为引擎。目前MySQL常用的两种引擎：MyISAM、InnoDBMySQL引擎是MySQL数据库服务器中的组件，负责为数据库执行实际的数据I/OMySQL系统中，引擎处于文件系统之上，在数据保存到数据文件之前会传输到存ISAM是一个定义明确且经历时间考验的数据表格管理方法，在设计之时就考虑到数据 .frm文件表定.MYD文件数据.MYI文件索引文件 信息数据库，用户数据库，商品库等业务，MyISAM引擎数据读写都比较频繁的场三、InnoDB[root@mysql~]#mysql-uroot-p123123mysql>showengines\G;***************************1.row***************************Engine:MRG_MYISAMSupport:Comment:CollectionofidenticalMyISAMtablesTransactions:NOXA:Engine:Comment:CSVstorageengineTransactions:NOXA:***************************3.row***************************Engine:MyISAMSupport:Comment:DefaultengineasofMySQL3.23withgreatperformanceTransactions:NOXA:***************************4.row***************************Engine:InnoDBSupport:Comment:Supportstransactions,row-levellocking,andforeignkeysTransactions:YESXA:***************************5.row***************************Engine:MEMORYSupport:Comment:Hashbased,storedinmemory,usefulfortemporarytablesTransactions:NOXA:5rowsinset(0.00Noquery方法一：SHOWTABLESTATUSFROMWHEREname表名mysql>showtablestatusfromwherename='user_info'***************************1.row***************************Name:user_infoEngine:MyISAMVersion:10Data_:0Auto_increment:NULLUpdate_time:2016-05-0716:11:35Check_time:NULLChecksum:NULL1rowinset(0.00sec)方法二：SHOWCREATETABLE表名***************************1.row***************************Table:user_info``char(20)NOT``char(20)NOT`ID号`char(10NOT1rowinset(0.00方法一：ALTERTABLEENGINE=引擎mysql>altertable.user_infoQueryOK,4rowsaffected(0.11sec)Records:4Duplicates:0Warnings:0mysql>showcreatetable.user_info***************************1.row***************************Table:user_info``char(20)NOT``char(20)NOT`ID号`char(10NOT1rowinset(0.00 f的default-storage-engine为引擎[root@mysql~]# [root@mysql~]#mysql-uroot-p123123-e'showengines***************************1.row***************************Engine:MRG_MYISAMSupport:Comment:CollectionofidenticalMyISAMtablesTransactions:NOXA:***************************2.row***************************Engine:CSVSupport:Comment:CSVstorageengineTransactions:NOXA:***************************3.row****************