9、mysql的备份与恢复
mysql中 /*! ....*/ 不是注释，mysql为了保持兼容，它把一些特有的仅在mysql上用的语句放在/*!....*/中，
这样这些语句如果在其他数据库中是不会被执行，但在mysql中它会执行。
9.1、备份数据的意义：
9.2、备份数据库的方法：
mysql建库字符集是utf8,客户端也是utf8；
1、备份语法：
mysql数据库自带了一个很好用的备份命令，就是mysqldump;
利用mysqldump命令备份数据的过程，实际上就是把数据从mysql库里以逻辑的sql语句的形式直接输出
或者生成备份的文件的过程；物理备份是把磁盘上的数据直接打包的过程；
它的语法为：mysqldump -u<用户名> -p<密码> <数据库名> >备份的文件
2、数据库备份：
mysqldump -uroot -p123456 -B lc | gzip >/tmp/lc_bak.sql.gz
# -B：表示备份时指定库进行备份，可以指定多个库进行备份; 还原时库不存在会创建，如果库存在则不创建；
#CREATE DATABASE /*!32312 IF NOT EXISTS*/ `lc` /*!40100 DEFAULT CHARACTER SET utf8 */;
#USE `lc`；
mysql dump -uroot -p123456 -A -B --events | gzip >/tmp/mysql_bak.sql.gz
#-A：表示备份所有的库；
#表示备份mysql中的所有的库，包括mysql的用户库'mysql'等mysql初始化时创建的库；
3、查看备份库的sql语句：
[root@db01 ~]# egrep -v "^$|#|\*|--" /tmp/lc_bak.sql
USE `lc`;
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (3,'刘畅'),(4,'刘本德');
UNLOCK TABLES;
4、--compact参数说明：
5、分库备份脚本操作：
（1）普通的模式：
mysql -uroot -p123456 -e "show databases;" | egrep -vi "database|infor|perfor" | awk '{print "mysqldump -uroot -p123456 -B",$1}'
mysql -uroot -p123456 -e "show databases;" | egrep -vi "database|infor|perfor" | sed 's#^#mysqldump -uroot -p123456 -B #g'
mysqldump -uroot -p123456 -B lc | gzip >/tmp/lc_bak.sql.gzip
mysqldump -uroot -p123456 -B lc1 | gzip >/tmp/lc1_bak.sql.gzip
（2）for循环的模式：
for dbname in `mysql -uroot -p123456 -e "show databases;" | egrep -vi "database|infor|perfor"`; do
mysqldump -uroot -p123456 -B $dbname | gzip >/tmp/${dbname}_bak.sql.gz
done
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.#该错误可以忽略不计；
9.3、备份表（单库操作）：
#不需要加-B：-B代表备份的是库，或多个库；
1、备份库中的表和表上的数据：
（1）语法：
mysqldump -u<用户名> -p<密码> <数据库名> <表名> >备份的文件名
（2）用法：
mysqldump -uroot -p123456 lc | gzip >/tmp/lc_bak.sql.gz
#表示备份lc库中所有的表和数据；
mysqldump -uroot -p123456 lc test | gzip >/tmp/lc_test_bak.sql.gz
#表示备份lc库中test表和数据，后面还可以跟表；
2、备份库中表的表结构,不包含数据：
（1）使用‘-d’参数；
（2）用法：
mysqldump -uroot -p123456 -d lc | gzip >/tmp/lc_table_all_bak.sql.gz
#表示备份lc库中所有表的表结构，不包括表上的内容；
mysqldump -uroot -p123456 -d lc test | gzip >/tmp/lc_table_test_bak.sql.gz
#表示备份lc库中test表的表结构，后面还可以跟表；
3、只备份库中表的数据：
（1）使用‘-t’参数，等价参数为--no-create-info ；经常
‘--quick --no-create-info --extended-insert’三个参数一起使用；
--quick：表示一行一行的读；
--no-create-info：表示不导出表结构；
--extended-insert：表示insert语句插入数据时放到一行进行插入；
（2）用法：
mysqldump -uroot -p123456 -t lc | gzip >/tmp/lc_data_all_bak.sql.gz
#表示备份lc库中所有表上的数据;
mysqldump -uroot -p123456 -t lc test | gzip >/tmp/lc_data_test_bal.sql.gz
#表示备份lc库中test表上的数据，后面还可以跟表；
（4）总结：
一般使用备份表时使用的是备份库中的表和表上的数据一起备份，最好再做个库的整备，
这样在还原库的时候可以放置备份表的数据杂碎，也能根据需要还原特定的表上的数据；
9.4、备份表（多库的操作）：
#需要加-B：-B代表备份的是库，或多个库；
1、备份多库的表结构：
mysqldump -uroot -p123456 -d -B lc | gzip >/tmp/lc_table_all.sql.gz
#表示备份lc库的表结构，后面还可以跟其它的库；
mysqldump -uroot -p123456 -d -A -B --events | gzip >/tmp/mysql_table_all.sql.gz
#表示备份mysql所有的库的表结构；
2、备份多库的表数据：
mysqldump -uroot -p123456 -t -B lc | gzip >/tmp/lc_data_all.sql.gz
#表示备份lc库的所有表上的数据，后面还可以跟其它的库；
mysqldump -uroot -p123456 -t -A -B --events | gzip >/tmp/mysql_table_all.sql.gz
#表示备份mysql所有的库的表上的数据；
9.5、备份库和表时刷新(切割)mysql的binlog参数：
1、备份数据库时刷新binlog:
（1）-F 参数：
mysqldump -uroot -p123456 -B -F lc | gzip >/tmp/lc_bak.sql.gz
#-F 自动分割binlog，自己需要确定binlog的位置；
（2）--master-data=1 参数：
mysqldump -uroot -p123456 -F --master-data=1 -B lc | gzip >/tmp/lc_bak.sql.gz
gzip -d /tmp/lc_bak.sql.gz
vim /tmp/lc_bak.sql #在备份的sql文件中有以下的语句来确定binlog的恢复点
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=107;
9.6、mysqlduamp的关键参数说明：
1、-B ：指定多个库，增加建库语句和use连接库的语句；
2、--compact：去掉注释，适合调试输出，生产不用；
3、-A ：备份所有的库；
4、-F： 刷新binlog日志；
5、--master-data=1：增加binlog日志文件名及对应的位置点；
#master-data=1或2使用的场合主要用于mysql的主从复制，使用'1'代表的是标记的位置点
#不会被注释掉,在slave上导入数据时就会执，使用'2'时代表标记的位置点会被注释掉；
#此参数在mysql的InnoDB引擎上使用
6、-x ：--lock-all-tables，锁所有的表；
适用于myisam引擎，所有的用户都不能对表进行操作；
7、-l：--lock-tables，只读锁表，默认；
8、-t ：只备份表的数据；
9、-d：只备份表的结构；
10、--single-transaction：适合innode事物数据库备份，导出的过程不能执行ddl语句，自动关闭默认锁--lock-tables；
--single-transaction参数的作用，设置事务的隔离级别为可重复读，即REPEATABLE READ，这样能保证在一个事务中
所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，对
该dump线程的数据并无影响，在这期间不会锁表；
11、-R：备份转存储的函数；
12、-E：--events：导出调度事件；
9.7、企业生产场景不同引擎备份命令：
1、myisam引擎备份：
mysqldump -uroot -p123456 -A -B -F -x -R --master-data=1 --events | gzip >/tmp/mysql_bak.sql.gz
2、innodb引擎备份：
mysqldump -uroot -p123456 -A -B -F --single-transaction -R --master-data=1 --events | gzip >/tmp/mysql_bak.sql.gz
3、如果是混合引擎备份：
推荐使用myisam的备份方式；
4、专职dba的备份：
9.8、数据库的恢复：
还原的时候如果表存在会自动drop table存在的表，不存在会自动创建;
有-B参数的备份库还原时无需指定库和建库操作(指定了库或指定的库错误也不影响)，如果库存在则不创建；
没有-B参数备份的库需要指定库，如果没有库需要手动进行创建；
1、source命令恢复（需要登录数据库）：
mysql -uroot -p123456
mysql> drop database lc;
mysql> source /tmp/lc_bak.sql;
2、普通恢复的命令：
1）解压数据：
gzip -d /tmp/lc_bak.sql.gz
2）备份库时指定了-B参数：
mysql -uroot -p123456 </tmp/lc_bak.sql
3）备份库时没有指定-B参数：
mysql -uroot -p123456 lc </tmp/lc_bak.sql
4）批量还原的方法：
[root@db01 tmp]# gzip -d *
[root@db01 tmp]# ls *.sql | sed -n "s#_bak.sql##gp"
mysql
for dbname in `ls *.sql | sed -n "s#_bak.sql##gp"`; do
mysql -uroot -p123456 <${dbname}_bak.sql
done
5）参数混用的方法：
mysql -uroot -p123456 -d -B lc lc1 | gzip >/tmp/table_lc
9.9、总结：
库到表到数据
还原数据需要库和表
还原表需要库
库必须要存在，表始终会被覆盖，数据不会被覆盖；
正常的企业环境备份是，对整个库进行备份，然后在对库的单表进行备份；
