mysqldump企业案例
背景：
正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。
备份策略：
每天23：00，计划任务调用mysqldump执行全备脚本
故障时间点：
上午10点开发人员误删除一个核心业务表，如何恢复？
vim /root/6.sh
#!/bin/bash
mysql -uroot -p123 -e "drop database if exists prod;"
mysql -uroot -p123 -e "create database if not exists prod;"
mysql -uroot -p123 -e "create table if not exists prod.t1(id int);"
num=1
while true;do
mysql -uroot -p123 -e "insert into prod.t1 values($num);commit;"
((num++))
sleep 1
done
root@localhost [(none)] >create database backup;
Query OK, 1 row affected (0.00 sec)
root@localhost [(none)] >use backup
Database changed
root@localhost [backup] >create table backup(id int);
Query OK, 0 rows affected (0.01 sec)
root@localhost [backup] >insert into backup.backup values(1);
Query OK, 1 row affected (0.00 sec)
root@localhost [backup] >insert into backup.backup values(2);
Query OK, 1 row affected (0.00 sec)
root@localhost [backup] >insert into backup.backup values(3);
Query OK, 1 row affected (0.01 sec)
root@localhost [backup] >select * from backup.backup;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
[root@db04 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/backup_$(date +%F-%H).sql.gz
root@localhost [(none)] >insert into backup.backup values(4);
Query OK, 1 row affected (0.00 sec)
root@localhost [(none)] >insert into backup.backup values(5);
Query OK, 1 row affected (0.00 sec)
root@localhost [(none)] >insert into backup.backup values(6);
Query OK, 1 row affected (0.00 sec)
root@localhost [(none)] >select * from backup.backup;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
6 rows in set (0.00 sec)
root@localhost [(none)] >drop table backup.backup;
Query OK, 0 rows affected (0.01 sec)
# 1.准备新环境
rm -fr /app/mysql/data
[root@db02 scripts]# ./mysql_install_db --basedir=/app/mysql --datadir=/app/mysql/data
[root@db02 scripts]# /etc/init.d/mysqld start
# 1.1新环境创建用户
mysql> grant all on *.* to test@'%' identified by '123';
# 2.将旧库的全备恢复到新环境（如果全备文件很大，scp速度慢）
方法一：
[root@db01 ~]# scp /tmp/full_2023-08-02-11.sql.gz 172.16.1.52:/root
[root@db02 mysql]# zcat /root/full_2023-08-02-11.sql.gz |mysql -uroot -p123
方法二：
[root@db01 ~]# zcat /tmp/full_2023-08-02-11.sql.gz |mysql -utest -p123 -h10.0.0.52
# 3.截取binlog
1）第一段binlog，全备后：起始位置点 43863
[root@db02 mysql]# zcat /root/full_2023-08-02-11.sql.gz |head -25
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=43863;
2）第一段binlog，删表前：结束位置点 87023
[root@db04 data]# mysqlbinlog --base64-output=decode-rows -vvv /app/mysql/data/mysql-bin.000003 | grep -i 'drop table' -C 5
# at 87023
#230802 17:52:07 server id 1 end_log_pos 87149 CRC32 0xecd42856 Query thread_id=233 exec_time=0 error_code=0
SET TIMESTAMP=1690969927/*!*/;
SET @@session.pseudo_thread_id=233/*!*/;
DROP TABLE `backup`.`backup` /* generated by server */
/*!*/;
# at 87149
## 截取第一段
[root@db04 data]# mysqlbinlog --start-position=43863 --stop-position=87023 /app/mysql/data/mysql-bin.000003 > /tmp/inc1.sql
## 恢复第一段
[root@db01 ~]# mysql -utest -p123 -h10.0.0.52 < /tmp/inc1.sql
3）第二段binlog，删表后：起始位置点 87149
4）第二段binlog，停库前：结束位置点 176540
root@localhost [(none)] >show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 176540 | | | |
+------------------+----------+--------------+------------------+-------------------+
## 截取第二段binlog
mysqlbinlog --start-position=87149 --stop-position=176540 /app/mysql/data/mysql-bin.000003 > /tmp/inc2.sql
## 恢复第二段binlog
[root@db01 ~]# mysql -utest -p123 -h10.0.0.52 < /tmp/inc2.sql
# 先停连接数据库的程序
systemctl stop php-fpm tomcat resin
# 4.应用割接
- 改代码
- mysqldump 新环境的数据，恢复到旧环境
[root@db01 ~]# mysqldump -utest -p123 -h10.0.0.52 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_52.sql.gz
[root@db01 ~]# zcat /tmp/full_52.sql|mysql -uroot -p123
# 5.启动连接数据库的应用，取消维护页
systemctl start php-fpm tomcat resin