环境:
OS:Centos 7
mysql:5.6.40
部署机器:192.168.1.14
背景说明:
线上对一个大表执行了语句insert into a select * from b,b表数据2000多万的记录,导致binlog远远超过了max_binlog_size设定的值(大事物不会根据设置的值进行切换,等该事物完成后才会切换)
后面用户有误操作,要求进行时间点恢复,要求恢复的时间点需要用到该日志文件,常规的采用mysqlbinlog应用日志一直报Got a packet bigger than 'max_allowed_packet' bytes的错误
该方法无解后,尝试采用sql_thread的方法应用日志,步骤如下:
1.停掉当前的数据库
停掉数据库
/opt/mysql56_rds/bin/mysqladmin -h localhost -S /home/mysql/data/mysql.sock -uroot shutdown
2.创建新的数据库目录
[root@localhost mysql5640]#cd /home/mysql/
[root@localhost mysql5640]#mv data bakdata ##备份原有的数据库目录
创建空的data目录,用于新的数据库使用
[root@localhost mysql5640]#mkdir data
3.解压阿里云rds下载的备份集
阿里云上的rds常用的压缩格式有gz格式和xb格式,我们这里这个实例是xb格式,不同的格式采用不同的恢复方式
安装好 XtraBackup 之后,使用 xbstream 命令将备份文件解包到目标目录。
[root@localhost bin]# /opt/xtrabackup-2.4.7/bin/xbstream -x -v -C /home/mysql/data</soft/rds/hins18998840_data_20210914055629_qp.xb ##说明/opt/mysql5640/data是数据还原的目录,hins18998840_data_20210914055629_qp.xb为rds下载文件
4.解压还原
/opt/xtrabackup-2.4.7/bin/xtrabackup --decompress --remove-original --target-dir=/home/mysql/data
参数说明:
--remove-original##意思是还原后,将原有的qp后缀的文件删除掉
5.应用日志
/opt/xtrabackup-2.4.7/bin/innobackupex --defaults-file=/opt/mysql56_rds/conf/my.cnf --apply-log /home/mysql/data
配置文件参数
[root@localhost conf]# more my.cnf
[mysqld]
port=23306
server-id=130
datadir=/home/mysql/data
socket=/home/mysql/data/mysql.sock
character-set-server=utf8
max_connections = 1500
skip-external-locking
key_buffer_size=16M
max_allowed_packet=16M
myisam_sort_buffer_size=16M
query_cache_size=32M
read_buffer_size=2M
sort_buffer_size=2M
interactive_timeout=86400
wait_timeout=86400
innodb_file_per_table=1
innodb_buffer_pool_size=128M
event_scheduler=1
binlog_format=row
log-bin=/opt/mysql56_rds/binlog/binlog.bin
expire_logs_days=1
max_binlog_size=128m
character_set_server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
skip-grant-tables=1
gtid_mode=on
log_slave_updates=1
enforce_gtid_consistency=ON
sync_binlog=0 ##关闭双1
innodb_flush_log_at_trx_commit=0 ##关闭双1
max_allowed_packet=1073741824
wait_timeout=86400
interactive_timeout=86400
log-warnings=2
relay-log-index=/opt/mysql56_rds/mysqllog/relaylog/slave-relay-bin.index ##从库配置
relay-log=/opt/mysql56_rds/mysqllog/relaylog/relaylog-binlog ##从库配置
skip-slave-start
[client]
port=23306
loose-default-character-set = utf8
default-character-set=utf8
[mysql]
no-auto-rehash
port=3306
max_allowed_packet=1073741824
[mysqldump]
max_allowed_packet=1073741824
6.修改目录权限
[root@localhost home]#cd /home
[root@localhost home]#chown -R mysql:mysql ./mysql/
[root@localhost home]#pwd
7.启动mysql
/opt/mysql56_rds/bin/mysqld_safe --defaults-file=/opt/mysql56_rds/conf/my.cnf --user=mysql &
发现启动报错误信息:
2021-09-22 15:12:48 94824 [ERROR] Error creating master info: Error removing old repository.
2021-09-22 15:12:48 94824 [ERROR] Failed to create or recover replication info repository.
解决办法:
use mysql
drop table slave_master_info;
drop table slave_relay_log_info;
drop table slave_worker_info;
drop table innodb_index_stats;
drop table innodb_table_stats;
source /opt/mysql56_rds/share/mysql_system_tables.sql;
然后重启动数据库
/opt/mysql56_rds/bin/mysqladmin -h localhost -S /home/mysql/data/mysql.sock -uroot shutdown
/opt/mysql56_rds/bin/mysqld_safe --defaults-file=/opt/mysql56_rds/conf/my.cnf --user=mysql &
8.使用sql_thread进行日志应用
登陆数据库执行如下命令,模拟从库
登陆数据库:
/opt/mysql56_rds/bin/mysql -h localhost -uroot -P23306 -S /home/mysql/data/mysql.sock
set global relay_log_info_repository='FILE';
change master to master_host='1',master_password='1',master_user='1',master_log_file='1',master_log_pos=4;
show variables like '%master_auto_position%';
解决办法:
change master to master_auto_position=0;
8.关闭数据库
这一步很重要
/opt/mysql56_rds/bin/mysqladmin -h localhost -S /home/mysql/data/mysql.sock -uroot shutdown
9.拷贝binlog到relay目录
cp /soft/rds/binlog/mysql-bin.000175 /opt/mysql56_rds/mysqllog/relaylog/
cp /soft/rds/binlog/mysql-bin.000176 /opt/mysql56_rds/mysqllog/relaylog/
修改文件名,修改成relaylog的格式
[root@localhost relaylog]# cd /opt/mysql56_rds/mysqllog/relaylog
[root@localhost relaylog]# mv mysql-bin.000175 relaylog-binlog.000001
[root@localhost relaylog]# mv mysql-bin.000176 relaylog-binlog.000002
然后修改权限
[root@localhost relaylog]#chown mysql:mysql relaylog-binlog.000001
[root@localhost relaylog]#chown mysql:mysql relaylog-binlog.000002
然后修改slave-relay-bin.index文件,将需要应用的binlog都写上去
[root@localhost relaylog]# cd /opt/mysql56_rds/mysqllog/relaylog
[root@localhost relaylog]# more slave-relay-bin.index
/opt/mysql56_rds/mysqllog/relaylog/relaylog-binlog.000001
/opt/mysql56_rds/mysqllog/relaylog/relaylog-binlog.000002
10.修改relay-log.info文件指定从那个pos开始
[root@localhost data]# cd /home/mysql/data
[root@localhost data]# more relay-log.info
7
/opt/mysql56_rds/mysqllog/relaylog/relaylog-binlog.000001
4
1
4
0
0
1
修改文件的第二和第三行,第二行是指从那个文件开始,第三行是指从那个pos开始,修改后的内容如下:
[root@localhost data]# more relay-log.info
7
/opt/mysql56_rds/mysqllog/relaylog/relaylog-binlog.000001
594508
1
4
0
0
11.启动数据库和启动sql_thread进程
启动数据库
/opt/mysql56_rds/bin/mysqld_safe --defaults-file=/opt/mysql56_rds/conf/my.cnf --user=mysql &
登陆数据库
/opt/mysql56_rds/bin/mysql -h localhost -uroot -P23306 -S /home/mysql/data/mysql.sock
查看当前的从库状态
mysql> show slave status G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 1
Master_User: 1
Master_Port: 3236
Connect_Retry: 60
Master_Log_File: 1
Read_Master_Log_Pos: 4
Relay_Log_File: relaylog-binlog.000001
Relay_Log_Pos: 594508
Relay_Master_Log_File: 1
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4
Relay_Log_Space: 2320569428
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /home/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 2534e8d3-fb7b-11eb-9563-0c42a1f03f4e:1-63275039,
ca9c7c42-8524-11ea-8c66-7cd30ae4344c:1-382132157,
caf13323-8524-11ea-8c66-506b4bbe201c:1-7962515
Executed_Gtid_Set: 7db1c8b0-1b74-11ec-8fb3-d094667047fb:1-87
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
发现这里就是我们修改的pos,应用会从这里开始
Relay_Log_Pos: 594508
启动sql_thread进程,这里只启动sql_thread进程,不要启动io进程
mysql>start slave sql_thread;
再次查看从库状态
mysql> show slave status G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 1
Master_User: 1
Master_Port: 3236
Connect_Retry: 60
Master_Log_File: 1
Read_Master_Log_Pos: 4
Relay_Log_File: relaylog-binlog.000001
Relay_Log_Pos: 637936
Relay_Master_Log_File: 1
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 637936
Relay_Log_Space: 2320569428
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 728631
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /home/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Opening tables
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 2534e8d3-fb7b-11eb-9563-0c42a1f03f4e:1-63275039,
ca9c7c42-8524-11ea-8c66-7cd30ae4344c:1-382132157,
caf13323-8524-11ea-8c66-506b4bbe201c:1-7962515
Executed_Gtid_Set: 2534e8d3-fb7b-11eb-9563-0c42a1f03f4e:62910819-62910911,
7db1c8b0-1b74-11ec-8fb3-d094667047fb:1-87
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
Seconds_Behind_Master的值一直在变化,耐心等待,Seconds_Behind_Master=0 说明日志应用完成了
12.继续日志应用
等上面的日志应用完成后再添加日志
停掉mysql
/opt/mysql56_rds/bin/mysqladmin -h localhost -S /home/mysql/data/mysql.sock -uroot shutdown
拷贝需要应用的日志到relaylog目录
cp /soft/rds/binlog/mysql-bin.000177 /opt/mysql56_rds/mysqllog/relaylog/
重命名文件
[root@localhost relaylog]# mv mysql-bin.000177 relaylog-binlog.000003
修改权限
chown mysql:mysql relaylog-binlog.000003
修改slave-relay-bin.index文件
[root@localhost relaylog]# more slave-relay-bin.index
/opt/mysql56_rds/mysqllog/relaylog/relaylog-binlog.000003
修改relay-log.info
[root@localhost data]# more relay-log.info
7
/opt/mysql56_rds/mysqllog/relaylog/relaylog-binlog.000003
4
mysql-bin.000177
4
0
0
1
4
0
0
1
3
0
0
1
启动mysql
/opt/mysql56_rds/bin/mysqld_safe --defaults-file=/opt/mysql56_rds/conf/my.cnf --user=mysql &
登陆数据库
/opt/mysql56_rds/bin/mysql -h localhost -uroot -P23306 -S /home/mysql/data/mysql.sock
启动sql_thread
start slave sql_thread;
继续应用日志
-- The End --