mysqldump企业案例

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