mysqldump备份
MySQL数据库的备份和恢复
运维的意义: 公司的数据和7*24时的网站服务
数据中数据库一般是核心
mysqldump 逻辑备份单个(多个)数据库练习多种参数使用
mysqldump是mysql自带的一个命令
|
1
|
mysqldump -u username -p 数据库名 > 备份的文件名.sql |
mysqldump的备份原理
过程就是把数据从库里面以逻辑sql语句的形式直接导出会加入一些锁表写入的语句!
mysql恢复的原理
把备份的sql语句在数据库中重新执行一遍
举例1: 备份oldboy库
|
1
|
mysqldump -uroot -p12qwaszx -S /data/3307/mysql.sock oldboy > /tmp/oldboy.sql
|
参看具体内容
|
1
2
3
4
5
6
7
8
9
|
[[email protected]_server ~]# egrep -v "#|\*|--|^$" /tmp/oldboy.sql
DROP TABLE IF EXISTS `student`;CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;LOCK TABLES `student` WRITE;INSERT INTO `student` VALUES (1,'jack'),(2,'????'),(3,'盲赂颅忙∶ヂヂ
|
根据查看结果可知备份的数据库都是一些DDL和DML sql语句
|
1
|
mysql -uroot -p12qwaszx -S /data/3307/mysql.sock -e "drop table oldboy.student"
|
恢复:
|
1
|
mysql -uroot -p12qwaszx -S /data/3307/mysql.sock oldboy < /tmp/oldboy.sql
|
举例2:备份时加-B 参数
|
1
|
mysqldump -uroot -p12qwaszx -B -S /data/3307/mysql.sock oldboy > /tmp/oldboy_bak_B.sql
|
比较和前面的备份差异
[[email protected]_server ~]# diff /tmp/oldboy*
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
19,26d18< -- Current Database: `oldboy`< --< < CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` /*!40100 DEFAULT CHARACTER SET latin1 */;< < USE `oldboy`;< < --59c51< -- Dump completed on 2016-04-10 9:15:44---> -- Dump completed on 2016-04-10 8:58:52 |
提示:直观看加-B备份库 会在备份的时候会加上创建库和切换库的语句
|
1
2
|
[[email protected]_server ~]# mysqldump -uroot -p12qwaszx -B -S /data/3307/mysql.sock oldboy > /tmp/oldboy_bak_B.sql
[[email protected]_server ~]# mysql -uroot -p12qwaszx -S /data/3307/mysql.sock -e "drop database oldboy";
|
恢复:
|
1
2
|
[[email protected]_server ~]# mysql -uroot -p12qwaszx -S /data/3307/mysql.sock < /tmp/oldboy_bak_B.sql
[[email protected]_server ~]#
|
举例3:gzip压缩 mysql数据都是文本所以压缩效率高
|
1
|
mysqldump -uroot -p12qwaszx -B -S /data/3307/mysql.sock oldboy | gzip > /tmp/oldboy_bak_B.sql.gz
|
比较
|
1
2
3
4
|
[[email protected]_server ~]# ls -l /tmp/oldboy_bak_B.sql*
-rw-r--r-- 1 root root 2123 Apr 10 09:28 /tmp/oldboy_bak_B.sql
-rw-r--r-- 1 root root 859 Apr 10 09:28 /tmp/oldboy_bak_B.sql.gz
[[email protected]_server ~]#
|
举例4:-B 备份多个库
|
1
|
mysqldump -uroot -p12qwaszx -B -S /data/3307/mysql.sock oldboy mysql > /tmp/mult_B.sql
|
举例5: 分库备份
方法1
|
1
|
mysql -uroot -p12qwaszx -S /data/3307/mysql.sock -e 'show databases' | egrep -v "info|per|tes|Data" | sed -r 's#^([a-Z].*$)#mysqldump -uroot -p12qwaszx --events -S /data/3307/mysql.sock -B \1 | gzip > /tmp/\1.sql.gz#g ' | bash
|
方法2:
for 循环
举例6: 单表 多表进行备份(思考如何进行分库分表进行备份 命令为表名_bak.sql)
mysql -uroot -p密码 库名 表名1 表名2
查看简要内容:
|
1
|
mysqldump -uroot -p12qwaszx -S /data/3307/mysql.sock --compact mysql user
|
备份语句
|
1
|
mysqldump -uroot -p12qwaszx -S /data/3307/mysql.sock mysql user > /tmp/user.sql
|
恢复
|
1
|
mysql -uroot -p12qwaszx -S /data/3307/mysql.sock oldboy < /tmp/user.sql
|
举例7: 只备份表结构或者表数据
-d 备份表结构
mysqldump -uroot -p密码 -d 库名 表名
-t 备份表数据
mysqldump -uroot -p密码 -t 库名 表名
|
1
2
|
[[email protected]_server ~]# mysqldump -uroot -p12qwaszx -S /data/3307/mysql.sock oldboy student --compact -t
INSERT INTO `student` VALUES (1,'jack'),(2,'????'),(3,'盲赂颅忙);
|
举例8: 备份的同时刷新binlog
-F
--master-data=1 增加binlog日志文件名和对应的位置点
|
1
|
mysqldump -uroot -p12qwaszx -S /data/3307/mysql.sock -F oldboy
|
结论:
1 备份加上-B 参数
2 用gzip对备份的数据压缩 效率更高
3 -B 参数 连接多个数据库备份语句中加入create 数据库和use 数据库
4 一般是分库备份一个库一条备份语句 备份的文件名为:库名_bak.sql
5 一般都是单表备份但缺点是量大一般用途是导入其他人那将单个表
6 备份--compact参数一般不用是个鸡肋参数，适合调试的输出形式，生产场景不用
Use of --compact is the same as specifying --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset options.
7 -A 备份所有库
8 -x 锁定所有的表 --lock-all-tables
9 --single-transaction 适合innodb事务数据库备份
innodb表在备份时，通常启用选项 --single-transaction 来保证备份的一致性，实际工作原理是设定本次会话的隔离级别为: REPEATABLE READ, 已确定本次会话dump时，不会看到其他会话已经提交了的数据
生产环境运维DBA的备份命令：
myisam备份命令
mysql
|
1
|
mysqldump -uroot -p12qwaszx -S /data/3307/mysql.sock oldboy -B--events -F --master-data=1 -x
|
innodb
|
1
|
mysqldump -uroot -p12qwaszx -S /data/3307/mysql.sock oldboy -B --events -F --master-data=1 --single-transaction
|
深入理解--master-data的参数
mysqldump 命令中 master-data参数的详解
预知:
master-data=1
master-data=2
之间的区别
在备份库的时候常用:
mysqldump -uroot -p12qwaszx oldboy -B --events -F --master-data=1 --single-transaction
-B 备份语句中加入create database 库 和use 库的语句
-F 滚动刷新日志binlog
--single-transaction innodb中保持一致性的参数
--master-data 接下来详细介绍
master-data=1 产生的备份语句中多以一条，是可以执行的
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000047', MASTER_LOG_POS=107;
master-data=2 产生的备份语句中多以一条注释的语句，标识一下
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000051', MASTER_LOG_POS=107;
注意: 在sql语句中 --那行是注释的
master-master=1 产生的那条语句是 用途在于主备环境中slave上，slave通过恢复备份时候执行了那条语句就可以知道从master 那个位置进行接下来的主从同步了
一般数据库mysqldump备份是每天一次 在凌晨，但是如果中午数据库数据没了，需要恢复，这个时候就需要先恢复晚上的全部，但是凌晨--到中午的这一段时间就要通过binlog进行导入恢复了！
加入了CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000047', MASTER_LOG_POS=107; slave通过mysqldump下来的数据进行恢复就知道 从那一个去通过master的数据了