背景:

本次目标是跨服务器将mysql的全部数据迁移,从Ubuntu 18.04 mysql 5.7.33, for Linux (x86_64) ->CentOS Linux release 7.8.2003 (Core) mysql5.7.24, for linux-glibc2.12 (x86_64)
, 数据量在300G多,使用xtrabackup进行备份迁移。

xtrapbackup使用可以参考:

官网:https://www.percona.com/doc/percona-xtrabackup/2.4/backup_scenarios/full_backup.html

博客:https://www.jianshu.com/p/b01cd0fecfea

CentOS :innobackupex version 2.4.15 Linux (x86_64)

Ubuntu:innobackupex version 2.4.9 Linux (x86_64)

备份 innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=yourpwd --host=192.168.3.200  --port=3306  /home/mysqlbackup 保持一致性 innobackupex --apply-log /home/mysqlbackup/2021-02-23_17-45-50还原innobackupex --defaults-file=/etc/my.cnf  -uroot -yourpwd --copy-back /home/mysqlbackup/2021-02-23_17-45-50

chown -R mysql:mysql /usr/local/mysql

使用中注意事项及遇到的问题:

1./etc/my.cnf中的datadir = /usr/local/mysql/data目录一定要为空,迁移目标地服务器mysql需要stop

2.在还原过程中,报错:can't find ibdata2,但备份后的文件里本来就没有ibdata2,查找官网后,试着新建了空的ibdata2,还原成功,猜测可能是innobackupex 版本差异导致

3.再还原后,重启mysql ,报错:Starting MySQL.The server quit without updating PID file (/[失败]cal/mysql/data/hostname.pid),查看日志

2021-02-24T08:28:24.897827Z 0 [ERROR] InnoDB: The innodb_system data file '/usr/local/mysql/data/ibdata1' is of a different size 164608 pages (rounded down to MB) than the 65536 pages specified in the .cnf file!2021-02-24T08:28:24.897858Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error2021-02-24T08:28:25.498200Z 0 [ERROR] Plugin 'InnoDB' init function returned error.2021-02-24T08:28:25.498209Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.2021-02-24T08:28:25.498212Z 0 [ERROR] Failed to initialize builtin plugins.2021-02-24T08:28:25.498215Z 0 [ERROR] Aborting

参考mysql官网:https://bugs.mysql.com/bug.php?id=96497后,将/etc/my.cnf中innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend注释,innodb_data_file_path恢复成默认配置12M,启动成功