innobackupex备份近2T的数据到远程服务器的实践笔记

尝试了uts同步数据，发现大表搞不了，需要加条件分批，几千万的记录，分批要执行很久，最后放弃此方案，选择热备工具：xtrabackup,具体实施过程如下：


1、配置免密登录：
在xx.17上执行：
sh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.xx.45
测试免密登录：
ssh root@192.168.xx.45 #能登录表示成功

2、备份到远程服务器：
/usr/bin/innobackupex --defaults-file=/usr/local/mysql/my.cnf --backup --slave-info -P3306 -u用户名 -p密码 --socket=/usr/local/mysql/mysql.sock --stream=tar /home/backup | ssh root@192.168.xx.45 cat " >"/home/backup/xtra_full_inno_`date +%Y%m%d%H%M%S`.tar #开始时间：2023-07-27 09:30 结束时间：230727 14:13:29 completed OK!

3、解压开始时间：14:15 大约结束时间：18.15 耗时大约4小时
tar -xf xtra_full_20230725135623.tar -C /home/backup/xtra_full_20230725135623/
#解压后删除备份文件以释放空间
rm -rf xtra_full_20230725135623.tar
4、重置数据目录
mv /home/mysql3306/mysql-5.7.26/data /home/mysql3306/mysql-5.7.26/data_bak
mv /home/mysql3306/mysql-5.7.26/logs /home/mysql3306/mysql-5.7.26/logs_bak
mkdir -p /home/mysql3306/mysql-5.7.26/data
mkdir -p /home/mysql3306/mysql-5.7.26/logs
授权：
chown -R mysql.mysql /home/mysql3306/mysql-5.7.26/data
chown -R mysql.mysql /home/mysql3306/mysql-5.7.26/logs
chown -R mysql.mysql /home/backup/xtra_full_20230725135623

5、应用日志：
innobackupex --apply-log --use-memory=4G /home/backup/xtra_full_20230725135623

6、还原数据：
innobackupex --defaults-file=/etc/my.cnf /home/backup/xtra_full_20230725135623 --copy-back #18.30左右开始 结束时间：230727 22:54:18 completed OK!


7、修改配置文件
因为数据目录不一样，需要修改配置文件，添加如下参数，才能启动成功：
innodb_temp_data_file_path=ibtmp1:200M:autoextend:max:20G
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:20G
#注意这是因为主库有这些参数，可能实际环境不一样，配置需要修改的参数不一样。
8、启动数据库
service mysql start

9、查找偏移量：
[root@localhost data]# cat xtrabackup_binlog_pos_innodb
mysql_bin.536117 361309905
编辑主从复制命令：
change master to
master_host='192.168.xx.17',
master_user='repl',
master_port=3306,
master_password='密码',
master_log_file='mysql_bin.536112',
master_log_pos=29123656;

10、xx.17上创建复制账号
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;
flush privileges;

11、配置主从复制：
11.1）登录数据库
mysql -uroot -p密码
11.2）执行复制配置命令
change master to
master_host='192.168.xx.17',
master_user='repl',
master_port=3306,
master_password='密码',
master_log_file='mysql_bin.536112',
master_log_pos=29123656;

12、启动复制服务：
start slave
13、查看复制状态：
show slave status\G;

14、确认同步完成
(product)root@localhost [(none)]> show slave status\G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8
Current database: *** NONE ***

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.xx.17
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.536120
Read_Master_Log_Pos: 184534655
Relay_Log_File: relay-bin.000016
Relay_Log_Pos: 50316301
Relay_Master_Log_File: mysql_bin.536120
Slave_IO_Running: Yes
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: 184534655
Relay_Log_Space: 184535172
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: 0 #0表示主从数据同步追平
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: 2217
Master_UUID: b60d2f07-bcb5-11e9-b99e-ac1f6bd379fe
Master_Info_File: /home/mysql3306/mysql-5.7.26/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: b60d2f07-bcb5-11e9-b99e-ac1f6bd379fe:490968107-491210978
Executed_Gtid_Set: b14379a0-bcad-11e9-9c9b-ac1f6bd3764e:5-9767299:9767301-200493179,
b60d2f07-bcb5-11e9-b99e-ac1f6bd379fe:1-490908088:490968107-491210978
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)




总结：
xtrabackup 备份 只支持 innodb跟xtradb的表

innobackupex 是对xtrabackup的封装并支持更多类型的表