使用场景
搭建完主从后，需要把另一台数据库的数据迁移到主从上。
关闭防火墙
service iptables stop; service firewalld stop
主机分配
| ip | 角色 |
|---|---|
| 10.12.0.111 | 主库 |
| 10.12.0.87 | 从库(相对而言，因为是双主) |
| 10.12.0.146 | 需要迁移数据库 |
即把10.12.0.146主机上的数据备份下来，迁移到10.12.0.111/87两台主机上。
使用 xtrabackup 备份数据
这里我已经下载好xtrabackup的二进制文件。
image.png
chmod 777 xtrabackup
修改xtrabackup为可执行权限
备份命令
./xtrabackup --default-files=/database/mysql//etc/3306/my.cnf --user=admin --password=GHHSGGS1234@ --datadir=/database/mysql/data/3306 --socket=/database/mysql/data/3306/mysqld.sock --backup --target-dir=/database/mysql_backup
提示201019 10:43:09 completed OK! 证明备份完成
Preparing数据
由于在备份的过程中有执行的事务，所以在备份后还需要对备份过程中的事务进行回放，commit/rollback
./xtrabackup --prepare --target-dir=/database/mysql_backup
InnoDB: Shutdown completed; log sequence number 29732392
201019 10:46:43 completed OK!
scp备份好的数据到主库
10.12.0.111/87两台主机都需要做
scp -r root@10.12.0.146:/database/mysql_backup /database/
开始恢复
注意在恢复的时候需要添加--default-files选择，否则会默认恢复到/var/lib/mysql
此项两个主机都需要恢复
./xtrabackup --default-files=/data02/mysql/etc/3306/my.cnf --copy-back --target-dir=/data02/mysql//data/3306_target --datadir=/data02/mysql//data/3306
反面教材：
[root@node2 mysql_bak]# ./xtrabackup --default-files=/data02/mysql/etc/3306/my.cnf --copy-back --target-dir=/data02/mysql//data/3306
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --datadir=/var/lib/mysql --copy-back=1 --target-dir=/data02/mysql//data/3306
./xtrabackup version 2.4.13 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3e7ca7c)
201022 10:07:14 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
提示 recognized server arguments: --datadir=/var/lib/mysql 就恢复到这个目录了。
注意点：
- target-dir和data-dir最好都在同一个盘，复制速度会快很多
- datadir 必须为空
- 保险起见可以先把原来的数据目录做一个备份，类似于 mv 3306 3306_bak
- --move-back 添加该参数会在恢复完成后把target目录删除。
- 恢复完成后记得chown 目录的用户及用户组的权限
开始主主复制
node3 10.12.0.111
create user hcjfmysql@'10.12.0.87' identified by 'hcjfmysql123@A';
grant replication slave on *.* to hcjfmysql@'10.12.0.87';
在M2主机执行：
change master to master_host='10.12.0.87', master_user='hcjfmysql', master_password='hcjfmysql123@A', master_port=3306, master_auto_position=1;
start slave;
show slave status\G;
node410.12.0.87
create user hcjfmysql@'10.12.0.111' identified by 'hcjfmysql123@A';
grant replication slave on *.* to hcjfmysql@'10.12.0.111';
在M2主机执行：
change master to master_host='10.12.0.111', master_user='hcjfmysql', master_password='hcjfmysql123@A', master_port=3306, master_auto_position=1;
start slave;
show slave status\G;
期间错误信息
1.恢复完成后，无法启动，报错信息如下
e of setpriority().
2020-10-22T10:21:44.865821+08:00 0 [ERROR] InnoDB: Expected to open 2 undo tablespaces but was able to find only 0 undo tablespaces. Set the innodb_undo_tablespaces parameter to the correct value and retry. Suggested value is 0
2020-10-22T10:21:44.865858+08:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-10-22T10:21:45.466233+08:00 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-10-22T10:21:45.466253+08:00 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-10-22T10:21:45.466270+08:00 0 [ERROR] Failed to initialize builtin plugins.
2020-10-22T10:21:45.466275+08:00 0 [ERROR] Aborting
2020-10-22T10:21:45.466294+08:00 0 [Note] Binlog end
2020-10-22T10:21:45.466850+08:00 0 [Note] /opt/mysql/base/5.7.31/bin/mysqld: Shutdown complete
原因：
由于配置文件上有如下配置项
innodb_undo_tablespaces=2
删除再次启动即可
2.可以进入MySQL，但是报错如下：
[ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2020-10-22T10:38:56.984759+08:00 0 [ERROR] InnoDB: Page [page id: space=1030, page number=1] log sequence number 422872132913 is in the future! Current system log sequence number 2658693.
网友评论