一 实验目标
1 掌握mysqldump用法 2 掌握 mysqldump 完全备份，增量备份 3 掌握 mysqldump 恢复数据库，二进制日志即时点还原
二 实验环境
操作系统：CentOS release 6.8 (Final) 应用软件：percona-server-5.6.29-76.2
三 备份策略
周完全+每日增量 完全备份： 使用mysqldump备份所有库 增量备份： 备份二进制日志文件
四 模拟过程
4.1 实验前准备
导入jiaowu.sql 数据 [root@paul ~]# mysql < /root/jiaowu.sql [root@paul ~]# mysql mysql> show databases; mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.10 sec) mysql> use jiaowu;
mysql> show tables; +------------------+ | Tables_in_jiaowu | +------------------+ | courses | | scores | | students | | tutors | +------------------+
4.2 用mysqldump 做完全备份
[root@paul ~]# mysqldump -uroot -p --master-data=2 --lock-all-tables --flush-logs --all-databases > /root/alldatabases.sql
连接mysql
[root@paul ~]# mysql
mysql> SHOW BINARY LOGS;
+------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 10389 | | mysql-bin.000002 | 120 | +------------------+-----------+
** 删掉旧二进制日志** mysql> PURGE BINARY LOGS TO 'mysql-bin.000002'; Query OK, 0 rows affected (0.01 sec)
4.3 第一天增量备份
模拟数据库修改，删掉部分数据
[root@paul ~]# mysql
mysql> use jiaowu;
mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+ | TID | Tname | Gender | Age | +-----+--------------+--------+------+ | 1 | HongQigong | M | 93 | | 2 | HuangYaoshi | M | 63 | | 3 | Miejueshitai | F | 72 | | 4 | OuYangfeng | M | 76 | | 5 | YiDeng | M | 90 | | 6 | YuCanghai | M | 56 | | 7 | Jinlunfawang | M | 67 | | 8 | HuYidao | M | 42 | | 9 | NingZhongze | F | 49 | +-----+--------------+--------+------+
mysql> DELETE FROM tutors WHERE Age>90; Query OK, 1 row affected (0.00 sec)
mysql> FLUSH LOGS;
mysql> SHOW BINARY LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000002 | 390 | | mysql-bin.000003 | 120 | +------------------+-----------+ mysql> quit; [root@paul ~]# ls -l /data/mysql [root@paul ~]# cp -a /data/mysql/mysql-bin.000002 /root/ 将二进制日志文件转换为sql脚本 [root@paul ~]# mysqlbinlog /root/mysql-bin.000002 > /root/mon-incremental.sql
4.4 第二天数据库增量备份
[root@paul ~]# mysql
mysql> use jiaowu; mysql> INSERT INTO tutors (Tname) VALUES ('stu123'); mysql> FLUSH LOGS; mysql> SHOW BINARY LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000002 | 390 | | mysql-bin.000003 | 435 | | mysql-bin.000004 | 120 | +------------------+-----------+ mysql> QUIT;
备份第二天二进制日志文件 [root@paul ~]# cp -a /data/mysql/mysql-bin.000003 /root/
4.5 删掉数据库，模拟数据损坏（已提前备份好数据和二进制日志文件）
[root@paul ~]# rm -rf /data/mysql/* [root@paul ~]# service mysqld stop MySQL (Percona Server) PID file could not be found! [FAILED] [root@paul ~]# killall mysqld 初始化mysql
[root@paul ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql [root@paul ~]# service mysqld start
4.6 还原完全备份
[root@paul ~]# mysql -uroot -p < /root/alldatabases.sql
4.7 恢复第一天增量备份
[root@paul ~]# mysqlbinlog /root/mysql-bin.000002 | mysql -uroot –p
4.8 恢复第二天增量备份
[root@paul ~]# mysqlbinlog /root/mysql-bin.000003 | mysql -uroot -p
