根据mysql的data文件导出sql语句

最近由于升级服务器系统,我仓促中,保存了下mysql的数据文件就直接重装系统了,导致mysql也从8.0.20升级到了8.0.21,但是直接移动数据文件到mysql的数据目录没办法启动,只能暂时重装博客,导入mysql文件了,中间折腾了很久,找了n多方法,都不行最后,自己想着是不是重新装一个8.0.20的版本就ok,所以就开始了尝试之路
1. 下载对应的mysql包

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-client-8.0.20-1.el7.x86_64.rpm
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-libs-8.0.20-1.el7.x86_64.rpm
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-common-8.0.20-1.el7.x86_64.rpm
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-server-8.0.20-1.el7.x86_64.rpm
  1. 安装对应的包
rpm -ivh mysql-community-common-8.0.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.20-1.el7.x86_64.rpm
  1. 将备份的mysql 文件拷贝到新的mysql目录下
cp -r mysql /var/lib/mysql

#修改文件夹权限
chmod -R 755 mysql
#修改用户组
chown -R mysql:mysql mysql
  1. 尝试启动 mysqld
service mysqld start

启动失败,查看mysql启动日志

tail -f /var/log/mysqld.log

提示报错

If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-04-15T10:47:30.776403Z 1 [ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation

尝试删除ib_logfile* 删除ibdata*等文件

提示新的报错

Operating system error number 2 in a file operation.
[ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2021-04-15T12:00:31.172856Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2021-04-15T12:00:31.172976Z 1 [ERROR] [MY-012594] [InnoDB] If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-04-15T12:00:31.173072Z 1 [ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation
2021-04-15T12:00:31.173150Z 1 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.

2021-04-15T12:02:26.565726Z 1 [ERROR] [MY-011971] [InnoDB] Tablespace 'innodb_undo_001' Page [page id: space=4294967279, page number=11] log sequence number 2122113581 is in the future! Current system log sequence number 17573900.

修改配置文件 /etc/my.cnf 添加如下配置

innodb_force_recovery = 6

binlog相关报错

mysqld: File './binlog.000062' not found (OS errno 2 - No such file or directory)

删除 binlog文件但是没有清空 binlog.index文件里的内容,清空就好了

再次执行启动命令就成功了,然后执行数据导出操作

mysqldump -uroot -p --all-databases > all-databases.sql

这样就能把之前的数据找回来了,对新的mysql数据库做数据导入就好了。