第1章 数据库备份
1.1 备份的类型
- 热备份：这些动态备份在读取或修改数据的过程中进行，很少中断或者不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问。
- 冷备份：这些备份在用户不能访问数据时进行，因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是，对于某些应用程序，会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。
- 温备份：这些备份在读取数据时进行，但在多数情况下，在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是，其不足之处在于无法在进行备份时修改数据集，这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。
1.2 备份方式
- 逻辑备份（文本表示：SQL 语句）
- 物理备份（数据文件的二进制副本）
- 基于快照的备份
- 基于复制的备份
- 增量备份（刷新二进制日志）
1.3 备份工具
- mysqldump：mysql原生自带很好用的逻辑备份工具
- mysqlbinlog：实现binlog备份的原生态命令
- xtrabackup：precona公司开发的性能很高的物理备份工具
第2章 Mysqldump使用详解
2.1 mysqldump常用参数
- -A, --all-databases：全库备份
- -B：增加建库（create 库）及进库（use库）的语句，可以直接接多个库名，同时备份多个库：-B 库1 库2
- -d：仅表结构
- -t：仅数据
- --compact：减少无用数据输出(调试)
- -R, --routines：备份存储过程和函数数据
- --triggers：备份触发器数据
2.2 全库备份与还原
2.2.1 创建备份目录
[[email protected] ~]# mkdir -p /backup
2.2.2 全库备份
[[email protected] ~]# mysqldump -uroot -p -A > /backup/full.sql Enter password: # 或 [[email protected]db02 ~]# mysqldump -uroot -p --all-databases > /backup/full.sql Enter password:
2.2.3 手动删除数据库
[[email protected] ~]# cd /usr/local/mysql/data/ [[email protected] data]# rm -rf ./* [[email protected] data]# ls [[email protected] data]#
2.2.4 杀掉mysql数据库进程
[[email protected] ~]# pkill mysql [[email protected] ~]# ps -ef | grep [m]ysql
2.2.5 重新初始化数据库
[[email protected] ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/ --user=mysql [[email protected] ~]# /etc/init.d/mysqld start [[email protected] ~]# mysqladmin -uroot password '123456'
2.2.6 还原数据库
[[email protected] ~]# mysql -uroot -p123456 mysql> set sql_log_bin=0; # 临时关闭二进制日志记录 mysql> source /backup/full.sql # 还原数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | leon | | mysql | | performance_schema | | test | | world | +--------------------+ 6 rows in set (0.00 sec)
2.3 单库备份
2.3.1 备份单库
[[email protected] ~]# mysqldump -uroot -p123456 -B leon > /backup/leon.sql [[email protected] ~]# mysqldump -uroot -p123456 -R --triggers -B leon > /backup/leon1.sql # 推荐使用
[[email protected] ~]# diff /backup/leon.sql /backup/leon1.sql 78a79,82 > > -- > -- Dumping routines for database 'leon' > -- 89c93 < -- Dump completed on 2017-11-22 10:15:10 --- > -- Dump completed on 2017-11-22 10:19:18
2.3.2 分库备份:for循环
2.3.2.1 常规方式
mysqldump -uroot -p'123456' -B leon ... mysqldump -uroot -p'123456' -B leon_utf8 ... mysqldump -uroot -p'123456' -B mysql ...
2.3.2.2 for循环方式
for name in `mysql -uroot -p123456 -e "show databases;"|sed 1d`
do
mysqldump -uroot -p123456 -B $name
done
2.3.2.3 采用concat拼接方式
[[email protected] ~]# mysql -uroot -p123456 -e 'select concat("mysqldump"," -uroot -p123 -B " ,table_schema," ", " ",">/backup/",table_schema,".sql") from information_schema.tables group by(table_schema)' > /backup/dbbak.sh
2.4 备份全表
2.4.1 常规方式
mysqldump 库1 表1 表2 表3 >库1.sql mysqldump 库2 表1 表2 表3 >库2.sql
2.4.2 采用concat拼接方式
[[email protected] ~]# mysql -uroot -p123456 -e 'select concat("mysqldump"," -uroot -p123456 ",table_schema," ",table_name," >/backup/",table_name,".sql") from information_schema.tables' > /backup/bak.sh
2.5 定时备份
- -F, --flush-logs：自动在备份时滚动binlog，即备份之后自动切割出新的二进制日志，方便之后使用二进制日志进行后续还原（每个库都会刷新一次，只要从最后一个二进制日志文件恢复即可）。不推荐使用：因为需要手动查看。
- --master-data={1|2}：1：非注释,要执行(主从复制) ，2：注释；会自动在备份的sql文件头部中记录备份时用到了哪个binlog文件，并且记录了position号到哪里。推荐使用：一般使用2
实例 2-1：每天晚上0点备份数据库：
[[email protected] ~]# crontab -e # backup mysql on 00:00 00 00 * * * /usr/local/mysql/bin/mysqldump -uroot -p123456 -A -B -F >/backup/full_$(date +%F) .sql &> /dev/null
2.6 锁表:适合所有引擎(myisam,innodb)
相当于给数据库先拍了一个快照然后再进行备份。
- 基于事务引擎:不用锁表就可以获得一致性的备份
- 生产中99% 使用innodb事务引擎
- ACID四大特性中的隔离性
2.6.1 锁表参数
- -x, --lock-all-tables：锁住所有备份表
- -l, --lock-tables：锁住单表
- --master-data：自动进行锁表和释放锁
- --single-transaction：进行热备（仅对innodb引擎有效）
[[email protected] ~]# mysqldump -uroot -p123456 -B -x leon >/backup/leon_x.sql
2.7 压缩备份与还原
2.7.1 压缩备份
2.7.1.1 常规方式：
[[email protected] ~]# mysqldump -uroot -p123456 -A -B -R --triggers --master-data=2 --single-transaction --master-data=2 | gzip >/backup/full.sql.gz
2.7.1.2 带时间戳的方式：
[[email protected] ~]# mysqldump -uroot -p123456 -A -B -R --triggers --master-data=2 --single-transaction --master-data=2 | gzip >/backup/full_$(date +%F_%T).sql.gz
2.7.2 解压还原
# 解压 [[email protected] ~]# gzip -d /backup/full.sql.gz # 会自动删除压缩包 # 还原 mysql> set sql_log_bin=0; mysql> source /backup/full.sql
2.8 使用Mysqldump单库备份恢复实践
2.8.1 备份innodb引擎数据库leon并压缩
[[email protected] ~]# [[email protected] ~]# mysqldump -uroot -p123456 -B -R --triggers --master-data=2 --single-transaction --master-data=2 leon | gzip >/backup/leon_$(date +%F_%T).sql.gz [[email protected] ~]# ll /backup/leon_2017-11-22_13\:37\:51.sql.gz -rw-r--r-- 1 root root 934 Nov 22 13:37 /backup/leon_2017-11-22_13:37:51.sql.gz
2.8.2 人为删除leon数据库
mysql> drop database leon; Query OK, 2 rows affected (0.10 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | world | +--------------------+ 5 rows in set (0.00 sec)
2.8.3 恢复数据库：使用gzip解压
[[email protected] ~]# gzip -d /backup/leon_2017-11-22_13\:37\:51.sql.gz [[email protected] ~]# mysql -uroot -p123456 < /backup/leon_2017-11-22_13\:37\:51.sql
2.8.4 验证数据
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | leon | | mysql | | performance_schema | | test | | world | +--------------------+ 6 rows in set (0.00 sec) mysql> use leon; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from test; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | leon | 18 | | 2 | shadow | 20 | | 3 | shuaige | 26 | | 4 | meinv | 19 | | 5 | wangzi | 28 | +----+---------+------+ 5 rows in set (0.00 sec)
提示：推荐使用source命令进行恢复。
第3章 Mysqldump+Mysqlbinlog企业级增量备份恢复
3.1 企业环境要求
- 背景环境：正在运行的网站系统，mysql数据库，数据量25G，日业务增量10-15M。
- 备份方式：每天23:00点，计划任务调用mysqldump执行全备脚本
- 故障时间点：上午10点，误删除了一个表
3.2 备份思路
- 使用测试库恢复全备（使用source命令）
- 截取此表从23:00到10点之间的binlog
- 导出误删除的表（使用mysqldump命令）
- 恢复此表到生产库（使用source命令）
注意：在生产环境中如果出现这种误操作、数据损坏，为了避免对数据库进一步“伤害”，尽量关闭数据库的连接，如关闭端口或拔掉网线等，等到恢复完成再放开连接。
提示：skip-networking：mysql自带的防止网络连接的方法，但是需要加入配置文件并重启实例。
3.3 备份策略设计
3.3.1 全量备份
- 全量数据就是数据库中所有的数据（或某一个库的全部数据）
- 全量备份就是把数据库中所有的数据进行备份
- mysqldump会取得一个时刻的一致性数据
3.3.2 增量备份
- 增量数据就是指上一次全量备份数据之后到下一次全备之前数据库所更新的数据
- 对于mysqldump来说binlog就是增量数据
3.4 模拟实践
3.4.1 对数据库进行全量备份
[[email protected] ~]# mysqldump -uroot -p123456 -A -B -R --triggers --master-data=2 --single-transaction --master-data=2 | gzip >/backup/full_$(date +%F).sql.gz
3.4.2 模拟修改后误删表
mysql> use leon; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into test values(6,'guowang',35); Query OK, 1 row affected (1.90 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> update test set name='xiaofang' where id=4; Query OK, 1 row affected (0.41 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from test; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | leon | 18 | | 2 | shadow | 20 | | 3 | shuaige | 26 | | 4 | xiaofang | 19 | | 5 | wangzi | 28 | | 6 | guowang | 35 | +----+----------+------+ 6 rows in set (0.00 sec) mysql> delete from test; Query OK, 4 rows affected (0.01 sec) mysql> select * from test; Empty set (0.00 sec)
3.4.3 使用测试数据库还原全备
# 解压备份文件 [[email protected] ~]# gzip -d /backup/full_2017-11-22.sql.gz # 还原数据库 mysql> set sql_log_bin=0; mysql> source /backup/full_2017-11-22.sql;
3.4.4 查看备份文件position号
[[email protected] ~]# vim /backup/full_2017-11-22.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=120; # 查看二进制日志中是否有数据 [[email protected] ~]# ll /usr/local/mysql/data/mysql-bin.000016 -rw-rw---- 1 mysql mysql 836 Nov 22 14:24 /usr/local/mysql/data/mysql-bin.000016
3.4.5 分析二进制日志文件
发现Delete_rows误操作，因此其实点为120，结束点为549：
mysql> show binlog events in 'mysql-bin.000016'; +------------------+-----+-------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+---------------------------------------+ | mysql-bin.000016 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.34-log, Binlog ver: 4 | | mysql-bin.000016 | 120 | Query | 1 | 192 | BEGIN | | mysql-bin.000016 | 192 | Table_map | 1 | 243 | table_id: 123 (leon.test) | | mysql-bin.000016 | 243 | Write_rows | 1 | 295 | table_id: 123 flags: STMT_END_F | | mysql-bin.000016 | 295 | Xid | 1 | 326 | COMMIT /* xid=921 */ | | mysql-bin.000016 | 326 | Query | 1 | 398 | BEGIN | | mysql-bin.000016 | 398 | Table_map | 1 | 449 | table_id: 123 (leon.test) | | mysql-bin.000016 | 449 | Update_rows | 1 | 518 | table_id: 123 flags: STMT_END_F | | mysql-bin.000016 | 518 | Xid | 1 | 549 | COMMIT /* xid=923 */ | | mysql-bin.000016 | 549 | Query | 1 | 621 | BEGIN | | mysql-bin.000016 | 621 | Table_map | 1 | 672 | table_id: 123 (leon.test) | | mysql-bin.000016 | 672 | Delete_rows | 1 | 805 | table_id: 123 flags: STMT_END_F | | mysql-bin.000016 | 805 | Xid | 1 | 836 | COMMIT /* xid=926 */ | +------------------+-----+-------------+-----------+-------------+---------------------------------------+ 13 rows in set (0.00 sec)
3.4.6 生成二进制日志还原脚本
[[email protected] ~]# cd /usr/local/mysql/data/ [[email protected] data]# mysqlbinlog --start-position=120 --stop-position=549 mysql-bin.000016 > /backup/inc.sql
3.4.7 在生产数据库中还原
mysql> set sql_log_bin=0; mysql> source /backup/inc.sql;
3.4.8 查看还原结果
mysql> use leon; Database changed mysql> select * from test; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | leon | 18 | | 2 | shadow | 20 | | 3 | shuaige | 26 | | 4 | xiaofang | 19 | | 5 | wangzi | 28 | | 6 | guowang | 35 | +----+----------+------+ 6 rows in set (0.01 sec)
我的微信
如果有技术上的问题可以扫一扫我的微信
