云計算課程筆記第六冊mysql數(shù)據(jù)庫

三、主流數(shù)據(jù)庫介 四、用戶權(quán)限設(shè)置 操 五、企業(yè)數(shù)據(jù)庫備份..................................................................................................... 一、引擎概念介 四、企業(yè)選擇引擎的依 二、主從原理原 （Data， 用戶可以通過DBMS操作數(shù)據(jù)庫，也可以通過應(yīng)用程序操作數(shù)據(jù)庫20701970IBM研究員2080年代初，IBMDB2Oracle 中。例如，Oracle支持的“關(guān)系-對象”數(shù)據(jù)庫模型。2080~90年代是關(guān)系數(shù)據(jù)庫產(chǎn)品發(fā)展和競爭的時代，SQLServer、Oracle、DB2、MySQL等一批很有實(shí)力的關(guān)系數(shù)據(jù)庫產(chǎn)品走到了主流商品數(shù)據(jù)庫的位置。面向Windows操作系統(tǒng)DB2（IBM公司產(chǎn)品）關(guān)系”（ER）圖來直接表示。E-R圖中包含了實(shí)體（數(shù)據(jù)對象、關(guān)系和屬性三個要素。 關(guān)系數(shù)據(jù)庫的結(jié)構(gòu)是二維表格，反映事物及其聯(lián)系的數(shù)據(jù)是以表格形式保存的，在云計 第六 orientedsession共享：在負(fù)載均衡集群中，用戶時，前一秒與后一秒提供服務(wù)的sessionMemcachedsession共享可實(shí)現(xiàn)同一個用戶登錄時能獲得同一個session也是一個以key-value方式數(shù)據(jù)看，數(shù)據(jù)也是保存在內(nèi)存中，但會定期將數(shù)據(jù)寫入磁盤中，相對于Memcached有一下特點(diǎn)： MySQL社區(qū)版是由分散在世界各地的MySQL開發(fā)者、者一起開發(fā)與，可以免·商業(yè)版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數(shù)據(jù)庫引擎[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:關(guān)閉1:關(guān)閉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數(shù)據(jù)庫名；

格式：describe[數(shù)據(jù)庫.]表名格式：createdatabase數(shù)據(jù)庫名;createtable(定義字段); QueryOK,1rowaffected(0.00mysqluse mysql>createtableusers(user_namechar(20)notnull,user_passwdchar(30)default'',primarykey(user_name)); 在auth庫中，創(chuàng)建名為users的表，表內(nèi)兩個字段為（user_name20user_passwd30鍵字user_name）QueryOK,0rowsaffected(0.10格式：droptable數(shù)據(jù)庫名.]表名;dropdatabase數(shù)據(jù)庫名;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條件表達(dá)式;QueryOK,1rowaffected(0.04 Changed: mysql>select*from2rowsinset(0.00) QueryOK,3rowsaffected(0.00 Changed: mysqlflush QueryOK,0rowsaffected(0.00格式：deletefromwhere條件表達(dá)式;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條件表達(dá)式建立IT_salary數(shù)據(jù)表，以保存IT運(yùn)營部員工的工資信息，如下表所示：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數(shù)字類型、char字符串類型、notnull不能為空、char()指定最多字節(jié)個數(shù)、primarykey()指定索引字段IT運(yùn)營部的員工工資信息插入到,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[][選項(xiàng)][數(shù)據(jù)庫名][表名]>/備份路徑/備份文件名--[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數(shù)據(jù)庫名備份路徑/備份文件名[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回滾一個事務(wù)（撤銷）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‘原用戶名’@’原主機(jī)’to‘新用戶名’@’新主機(jī)’; //可以只修改用戶名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權(quán)限列表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：用戶管理權(quán)限D(zhuǎn)ELETE：允許使用DELETE，刪除數(shù)據(jù)DROP：允許使用DROPTABLE，刪除表MySQL啟動和停止時，以及服務(wù)器在運(yùn)行過程中發(fā)生的任何錯誤時的相關(guān)信息，默認(rèn)在安裝/usr/local/mysql/data/下的“主機(jī)名err可在登錄時使用“--log-error=mysqld保存錯誤日志的位置；或者修改主配置文件f，在[mysqld]下方添加“l(fā)og-error=文件路徑及文件mysqld服務(wù)。[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所有連接和語句都將會被記錄。默認(rèn)關(guān)閉此項(xiàng)日志記錄，一般用作調(diào)試用，平可在登錄時使用“--log=文件路徑及文件名”或“-l文件路及文件名”選項(xiàng)指定；或修改主配置文件f，在[mysqld]下添加“l(fā)ogON”和“l(fā)og=文件路徑及文件名”，重啟mysqld服務(wù)。[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; 要目的是在恢復(fù)時能夠最大可能地恢復(fù)數(shù)據(jù)庫。默認(rèn)是開啟的，默認(rèn)路徑在mysqlbinlog命令查看二進(jìn)制日志文件。[root@mysql~]# [root@mysql~]#cd/usr/local/mysql/data/[root@mysqldata]#mysqlbinlogmysql-bin.000001徑及文件會，會在/usr/local/mysql/data下生成“主機(jī)名-slow.log”；或修改主配置文件f，在[mysqld]下添加“l(fā)ong_query_time”和“l(fā)og-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]?。?！否則啟動錯！?。。ㄈ魹閥um安裝的不會報錯ShuttingdownMySQL.SUCCESS!StartingMySQL..SUCCESS!·（如火災(zāi)、）11、直接打包數(shù)據(jù)庫文件夾，如/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庫、表或全部的庫導(dǎo)出為SQL，在需要恢復(fù)時可進(jìn)行數(shù)據(jù)恢復(fù)。格式：mysqldump-u用戶名-p[][選項(xiàng)][數(shù)據(jù)庫名]>/備份路徑/備份文件名[root@mysql~]#mysqldump-uroot-p123123auth>/backup/auth-$(date+%Y%m%d).sql[root@mysql~]#echo$?0 格式：mysqldump-u用戶名-p[][選項(xiàng)]--databases庫名1[庫名2]…>/備份路徑 - /backup/mysql+auth-$(date+%Y%m%d).sql[root@mysql~]#cat/backup/mysql+auth- 格式：mysqldump-u用戶名-p[][選項(xiàng)]--all-databases>/備份路徑/備份文件名[root@mysql~]#mysqldump-uroot-p123123--opt--all-databases //--opt[root@mysql~]#cat 格式：mysqldump-u用戶名-p[][選項(xiàng)]數(shù)據(jù)庫名表名>/備份路徑/備份文件名[root@mysql~]#mysqldump-uroot-p123123authuser>/backup/auth_user- 格式：mysqldump-u用戶名-p[]-d數(shù)據(jù)庫名表名>/備份路徑/備份文件名[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進(jìn)行最大化還原）3、使用統(tǒng)一和易理解的備份名稱，推薦使用庫名或者表名加上時間名規(guī)則，如 user_info，表結(jié)構(gòu)如[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 ','藍(lán)凌','女QueryOK,1rowaffected(0.01','姜紋','女QueryOK,1rowaffected(0.00 ','關(guān)園','男QueryOK,1rowaffected(0.01 QueryOK,1rowaffected(0.01mysql>select*from ||||用戶ID |資 1 | 11 100 2|藍(lán) | 12 98 3|姜 | 13 12 4|關(guān) | 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|藍(lán) | 12 3|姜 | 13 4|關(guān) | 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 實(shí)驗(yàn)要求：對mysql-server的auth庫和 MySQL服務(wù)端，給予select和locktables權(quán)限，以備 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數(shù)據(jù)庫備份#設(shè)置登錄變量MySQL數(shù)據(jù)庫備份#設(shè)置登錄變量#設(shè)置備份的數(shù)據(jù)庫 定義備份路徑、工具、時間、文件名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數(shù)據(jù)庫數(shù)據(jù)#設(shè)置變量#MySQL數(shù)據(jù)庫數(shù)據(jù)#設(shè)置變量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"搜索到的可恢復(fù)數(shù)據(jù)庫如下:"readp請選擇您要恢復(fù)數(shù)據(jù)庫的編號nm=$(awkF$dt/'{printNR,$1aaa/db_list|awk$nb/'{print$2}')echo"現(xiàn)在開始恢復(fù)數(shù)據(jù)庫:$nm到$dt"cdmkdir.bbbtarxf$onm-Cmysqlu$MY_USERp$MY_PASSh$MY_HOST$nmbbb/*echo"$nm已經(jīng)恢復(fù)到$dt"cd-&>/dev/null[root@~]#od+xmysql>grantallonauth.*toQueryOK,0rowsaffected(0.00mysql>grantallon.*toQueryOK,0rowsaffected(0.00mysql>flushQueryOK,0rowsaffected(0.00[root@~]#37請選擇您要恢復(fù)數(shù)據(jù)庫的編號3現(xiàn)在開始恢復(fù)數(shù)據(jù)庫:auth到auth已經(jīng)恢復(fù)到[root@~]#mysqldump進(jìn)行完全備份，備份的數(shù)據(jù)中有重復(fù)數(shù)據(jù)，備份時間與恢復(fù)時間長?！ざM(jìn)制日志在啟動MySQLmax_binlog_size所設(shè)置flushlogs命令后重新創(chuàng)建新的日志文件。[root@mysql~]# 52max_binlog_size ·flushlogs方法重新創(chuàng)建新的日志，生成二進(jìn)制文件序列，并及時把這方法一：MySQL的配置文件的[mysqld]項(xiàng)中加入log-bin=文件存放路徑/文件前綴，如 格式：mysqlbinlogno-defaults]|mysqlu-mysqlbinlogno-defaults]--stop-datetime=’年-月-:分鐘:秒’|-u用戶名-pmysqlbinlogno-defaults]--start-datetime=’年-月-:分鐘:秒’|-u用戶名-pmysqlbinlogno-defaults]--start-datetime=’年-月-:分鐘:秒’--stop-datetime=’年-月-日小時:分鐘:秒’二進(jìn)制日志|mysql-u用戶名-pmysqlbinlogstop-position=’id’|mysqlu-pmysqlbinlogstart-position=’id’|mysqlu-p1mysql是處于哪種表類型下工作的，它們支持事物處理還是非事物的，因?yàn)槲掖蟮撵`活性，可以基于時間點(diǎn)或是位置進(jìn)行恢復(fù)?？紤]到數(shù)據(jù)庫性能，我們可以將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,資費(fèi)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* #設(shè)置登錄變量#設(shè)置登錄變量#設(shè)置備份的數(shù)據(jù)庫(或表) 定義備份路徑、工具、時間、文件名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 #設(shè)置登錄變量定義備份路徑、工具、二進(jìn)制日志前綴、二進(jìn)制日志存放路徑BF_TIME="$(date+%Y%m%d)"#拷貝二進(jìn)制日志[-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中的數(shù)據(jù)用各種不同的技術(shù)在文件系統(tǒng)中，每一種技術(shù)都使用不同的在MySQL中稱為引擎。目前MySQL常用的兩種引擎：MyISAM、InnoDBMySQL引擎是MySQL數(shù)據(jù)庫服務(wù)器中的組件，負(fù)責(zé)為數(shù)據(jù)庫執(zhí)行實(shí)際的數(shù)據(jù)I/OMySQL系統(tǒng)中，引擎處于文件系統(tǒng)之上，在數(shù)據(jù)保存到數(shù)據(jù)文件之前會傳輸?shù)酱鍵SAM是一個定義明確且經(jīng)歷時間考驗(yàn)的數(shù)據(jù)表格管理方法，在設(shè)計之時就考慮到數(shù)據(jù) .frm文件表定.MYD文件數(shù)據(jù).MYI文件索引文件 信息數(shù)據(jù)庫，用戶數(shù)據(jù)庫，商品庫等業(yè)務(wù)，MyISAM引擎數(shù)據(jù)讀寫都比較頻繁的場三、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****************