關于使用xtrapbackup遷移mysql遇到的問題

背景:

本次目標是跨服務器將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,啟動成功