关于使用xtrapbackup迁移mysql遇到的问题
背景:
本次目标是跨服务器将mysql的全部数据迁移,从Ubuntu 18.04 mysql 5.7.33, for Linux (x86_64) -> CentOS Linux release 7.8.2003 (Core) mysql 5.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),查看日志
1 问题1: 2 日志: 3 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! 4 2021-02-24T08:28:24.897858Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error 5 2021-02-24T08:28:25.498200Z 0 [ERROR] Plugin 'InnoDB' init function returned error. 6 2021-02-24T08:28:25.498209Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 7 2021-02-24T08:28:25.498212Z 0 [ERROR] Failed to initialize builtin plugins. 8 2021-02-24T08:28:25.498215Z 0 [ERROR] Aborting
1 解决1: 2 参考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,启动成功。 3 4 原因:其实这是默认文件大小为12M,开开始创建的也是12M大小的文件,修改成其他配置后,服务器寻找相应配置大小文件,这就导致了冲突问题2:InnoDB: Error number 24 means 'Too many open files'
1 问题2: 2 执行命令:innobackupex --defaults-file=/etc/my.cnf --user=root --password=yourpwd --host=192.168.0.201 --port=3007 --parallel=2 --throttle=200 /home/mysql_bak 3 进行全量备份时 4 报错:InnoDB: Error number 24 means 'Too many open files'
1 解决2: 2 显然这是系统打开文件限制了, 3 ulimit -a 先查看下当前系统限制为多少 4 我查出来限制为1024 5 然后ulimit -n 2048,设置2倍大, 6 再执行命令成功备份