数据库备份mysqldump应用总结
[root@ansible~]#mysqldump-uroot-p123456xxx>/opt/xxx.sql#备份数据库xxx [root@ansible~]#egrep-v"#|\*|--|^$"/opt/xxx.sql DROPTABLEIFEXISTS`test`; CREATETABLE`test`( `id`int(4)NOTNULLAUTO_INCREMENT, `name`char(20)NOTNULL, PRIMARYKEY(`id`) )ENGINE=InnoDBAUTO_INCREMENT=7DEFAULTCHARSET=latin1; LOCKTABLES`test`WRITE; INSERTINTO`test`VALUES(1,'martin'),(2,'martin'),(3,'tom'),(4,'marry'),(5,'jacky'),(6,'脗铆'); UNLOCKTABLES; [root@ansible~]#mysqldump-uroot-p123456xxx--default-character-set=latin1>/opt/xxx1.sql#指定字符集导出数据库 [root@ansible~]#egrep-v"#|\*|--|^$"/opt/xxx1.sql DROPTABLEIFEXISTS`test`; CREATETABLE`test`( `id`int(4)NOTNULLAUTO_INCREMENT, `name`char(20)NOTNULL, PRIMARYKEY(`id`) )ENGINE=InnoDBAUTO_INCREMENT=7DEFAULTCHARSET=latin1; LOCKTABLES`test`WRITE; INSERTINTO`test`VALUES(1,'martin'),(2,'martin'),(3,'tom'),(4,'marry'),(5,'jacky'),(6,'马');可以看到乱码已经不存在了，建表默认是latin1 UNLOCKTABLES; [root@ansible~]#mysql-uroot-p123456-e"usexxx;droptabletest;"删除表test [root@ansible~]#mysql-uroot-p123456-e"usexxx;showtables;"可以看到test表已经没有了 [root@ansible~]# [root@ansible~]# [root@ansible~]#mysql-uroot-p123456xxx</opt/xxx1.sql导回数据库 [root@ansible~]# [root@ansible~]#mysql-uroot-p123456-e"usexxx;showtables;"再次查看test表已经有了 +---------------+ |Tables_in_xxx| +---------------+ |test| +---------------+ [root@ansible~]# [root@ansible~]#mysqldump-uroot-p123456-Bxxx--default-character-set=latin1>/opt/xxx1_B.sql加-B选项导出 [root@ansible~]#cd/opt/ [root@ansibleopt]#diffxxx1.sqlxxx1_B.sql对比没有加-B选项和加-B选项时候的区别 18a19,26 >--CurrentDatabase:`xxx` >-- > >CREATEDATABASE/*!32312IFNOTEXISTS*/`xxx`/*!40100DEFAULTCHARACTERSETlatin1*/; > >USE`xxx`; > >-- 51c59 <--Dumpcompletedon2016-08-1323:07:34 --- >--Dumpcompletedon2016-08-1323:17:33 说明：直观看加了-B参数的作用是在导出数据库的时候增加了创建数据库和连接数据库的命令了，即如下两条语句 CREATEDATABASE/*!32312IFNOTEXISTS*/`xxx`/*!40100DEFAULTCHARACTERSETlatin1*/; USE`xxx`; [root@ansibleopt]#mysql-uroot-p123456-e"dropdatabasexxx;showdatabases;"删除数据库xxx +--------------------+ |Database| +--------------------+ |information_schema| |mysql| |performance_schema| |test| +--------------------+ [root@ansibleopt]#mysql-uroot-p123456</opt/xxx1_B.sql因为这是加了-B选项导出来的数据，所以前面不需要再指定数据库 [root@ansibleopt]#mysql-uroot-p123456-e"showdatabases;"可以看到数据库已经恢复了 +--------------------+ |Database| +--------------------+ |information_schema| |mysql| |performance_schema| |test| |xxx| +--------------------+ [root@ansibleopt]#mysqldump-uroot-p123456-Bxxx--default-character-set=latin1|gzip>/opt/xxx1_B.sql.gz用gzip压缩后再导出 [root@ansibleopt]#ll -rw-r--r--1rootroot2069Aug1323:17xxx1_B.sql -rw-r--r--1rootroot805Aug1323:33xxx1_B.sql.gz可以看到压缩后导出的数据大小小了很多 总结： 1、导出数据用-B参数 2、用gzip对备份的数据压缩 mysqldump的工作原理 利用mysqldump命令备份数据的过程，实际上就是把数据从mysql库里面以逻辑的sql语句的形式输出 备份多个库 [root@centos02opt]#mysqldump-uroot-p123456-Btestzabbix|gzip>/opt/mul.sql.gz [root@centos02opt]#ll total4900 -rw-r--r--1rootroot5012554Aug1507:52mul.sql.gz -B参数是关键，表示连接多个库，并且增加usedb;和createdatabasedb的信息 [root@centos02opt]#mysql-uroot-p123456-e"showdatabases;"|grep-Evi"database|info|perf" martin martin_gbk martin_utf8 mysql test zabbix [root@centos02opt]#catmysql.sh#备份数据库多个库的脚本 #!/bin/bash fordbnamein`mysql-uroot-p123456-e"showdatabases;"|grep-Evi"database|info|perf"` do mysqldump-uroot-p123456--events-B${dbname}|gzip>/opt/${dbname}.sql.gz done [root@centos02opt]#shmysql.sh [root@centos02opt]#ll-h total5.1M -rw-r--r--1rootroot549Aug1508:34martin_gbk.sql.gz -rw-r--r--1rootroot845Aug1508:34martin.sql.gz -rw-r--r--1rootroot547Aug1508:34martin_utf8.sql.gz -rw-r--r--1rootroot199Aug1508:34mysql.sh -rw-r--r--1rootroot149KAug1508:34mysql.sql.gz -rw-r--r--1rootroot545Aug1508:34test.sql.gz -rw-r--r--1rootroot4.9MAug1508:34zabbix.sql.gz mysql>usemartin; Databasechanged mysql>showtables; +------------------+ |Tables_in_martin| +------------------+ |student| |student1| +------------------+ 2rowsinset(0.00sec) 备份单个表 [root@centos02opt]#mysqldump-uroot-p123456martinstudent>one.sql martin指的是库名student指的是表名这时候不能加-B参数了因为-B参数会将后面识别的都是库名 备份多个表 [root@centos02opt]#mysqldump-uroot-p123456martinstudentstudent1>two.sql martin指的是库名studentstudent1指的是两个表名 [root@centos02alertscripts]#mysqldump-uroot-p123456-dmartinstudent1 只备份student1表的结构martin代表数据库 [root@localhost~]#mysqldump-uroot-p123456-A-B--events|gzip>/opt/all.sql.gz-A代表所有数据库 [root@localhostmysql]#ll -rw-rw----1mysqlmysql236Aug1123:14mysql-bin.000001 -rw-rw----1mysqlmysql488Aug1200:51mysql-bin.000002 -rw-rw----1mysqlmysql722Aug1223:46mysql-bin.000003 -rw-rw----1mysqlmysql229Aug1301:06mysql-bin.000004 -rw-rw----1mysqlmysql244Aug1714:41mysql-bin.000005 -rw-rw----1mysqlmysql95Aug1714:41mysql-bin.index [root@localhostmysql]#mysqldump-uroot-p123456-A-B-F--events|gzip>/opt/all.sql.gz-F会刷新bin-log [root@localhostmysql]#ll -rw-rw----1mysqlmysql236Aug1123:14mysql-bin.000001 -rw-rw----1mysqlmysql488Aug1200:51mysql-bin.000002 -rw-rw----1mysqlmysql722Aug1223:46mysql-bin.000003 -rw-rw----1mysqlmysql229Aug1301:06mysql-bin.000004 -rw-rw----1mysqlmysql287Aug1714:50mysql-bin.000005 -rw-rw----1mysqlmysql149Aug1714:50mysql-bin.000006 -rw-rw----1mysqlmysql149Aug1714:50mysql-bin.000007 -rw-rw----1mysqlmysql106Aug1714:50mysql-bin.000008 -rw-rw----1mysqlmysql152Aug1714:50mysql-bin.index [root@localhostmysql]#mysqldump-uroot-p123456--master-data=1--compactmartin#--master-data=1该参数会找bin-log位置 CHANGEMASTERTOMASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=106; /*!40101SET@saved_cs_client=@@character_set_client*/; /*!40101SETcharacter_set_client=utf8*/; [root@localhostmysql]#mysql-uroot-p123456-e"showmasterstatus\G;" ***************************1.row*************************** File:mysql-bin.000008 Position:106 [root@localhostmysql]#mysqldump-uroot-p123456--master-data=2--compactmartin#--master-data=2该参数会找bin-log位置，但是语句被注释，实际并不执行 --CHANGEMASTERTOMASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=106; /*!40101SET@saved_cs_client=@@character_set_client*/; /*!40101SETcharacter_set_client=utf8*/; mysqldump的关键参数说明 1、-B 指定多个库，会增加建库语句和use语句 2、--compact 去掉注释，适合调试输出 生产环境不用 3、-A 备份所有库 4、-F 刷新binlog日志 5、--master-data=1 增加binglog日志文件名及对应的位置点 6、-x 锁表 7、-l 只读锁表 8、-d 只备份表结构 9、-t 只备份数据 10、--single-transaction 适合innodb事务数据库备份 生产场景myisam备份： mysqldump-uroot-p123456-A-B--master-data=1-x--events|gzip>/opt/all.sql.gz 生产场景innodb备份： mysqldump-uroot-p123456-A-B--master-data=1--events--single-transaction|gzip>/opt/all.sql.gz 数据库的恢复 mysql>dropdatabasexxx; QueryOK,1rowaffected(0.07sec) mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |mysql| |performance_schema| |test| +--------------------+ 4rowsinset(0.00sec) mysql>systemls/opt rhxxx1_B.sqlxxx1_B.sql.gzxxx1.sqlxxx.sql mysql>source/opt/xxx1_B.sql#用source命令恢复数据库 mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |mysql| |performance_schema| |test| |xxx| +--------------------+ 5rowsinset(0.00sec)
