mysql 5.7 停电导致无法启动、如何备份数据,重新安装mysql

时间:2022-10-05 19:33:25

  用于记录服务器停电导致,mysql启动失败后,如何备份数据,重新安装mysql,主要分为数据备份,mysql重新安装。

1、mysql无法启动时,进行数据备份。

执行:systemctl start mysqld,启动失败。

错误提示:Job for mysqld.service failed because start of the service was attempted too often. See "systemctl status mysqld.service" and "journalctl -xe" for details.

执行命令:journalctl -xe,发现由于文件损坏导致,myslq无法正常启动。

错误提示:

InnoDB: End of page dump
2019-07-08T10:32:02.562744Z 0 [Note] InnoDB: Uncompressed page, stored checksum in field1 1648005559, calculated checksums for field1: crc32 1648005559/4122850020, innodb 1498368068, none 3735928559, stored checksum in field2 878221460, calculated checksums for field2: crc32 1648005559/4122850020, innodb 2300197431, none 3735928559, page LSN 0 1480595787, low 4 bytes of LSN at page end 1480663732, page number (if stored to page already) 318, space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an update undo log page
2019-07-08T10:32:02.562804Z 0 [Note] InnoDB: It is also possible that your operating system has corrupted its own file cache and rebooting your computer removes the error. If the corrupt page is an index page. You can also try to fix the corruption by dumping, dropping, and reimporting the corrupt table. You can use CHECK TABLE to scan your table for corruption. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2019-07-08T10:32:02.562824Z 0 [ERROR] [FATAL] InnoDB: Aborting because of a corrupt database page in the system tablespace. Or, there was a failure in tagging the tablespace as corrupt.
2019-07-08 18:32:02 0x7f22aee65780 InnoDB: Assertion failure in thread 139786939946880 in file ut0ut.cc line 942
编辑mysql配置文件,使mysql启动时忽略检查到错误文件,在配置文件/etc/my.cnf中添加配置项innodb_force_recovery:

vim /etc/my.cnf

innodb_force_recovery=1

添加配置后启动mysql:systemctl start mysqld

执行一下命令,对数据进行备份:

mysqldump -uroot -proot --all-databases  > all_mysql_backup.sql

到此数据备份已完成,接下来会mysql进行重新安装。

2、重新安装mysql 5.7

先清除原有的mysql数据。如mysql安装路径为:/home/sdc/3306

rm -rf /home/sdc/3306/*

mysql 5.7 停电导致无法启动、如何备份数据,重新安装mysql

清理数据后重新进行安装:

mysqld --initialize --datadir=/home/sdc/3306 (如果mysql版本小于5.7,可用mysql_install_db --datadir=/home/sdc/3306命令进行重装)

安装成功后,配置文件修改如下:

mysql 5.7 停电导致无法启动、如何备份数据,重新安装mysql

启动mysql:systemctl start mysqld

启动之后,设置软连接,不设置软连接,会导致本地无法登录。出现can't find mysql.sock错误

ln -s /home/sdc/3306/mysql.sock /usr/lib/mysql/

对安装路径进行授权:chown -R mysql:mysql /home/sdc/3306/

获取重装后的初始化密码:grep 'temporary password' /var/log/mysqld.log

登录新安装的mysql,设置新密码:

mysql -uroot -p

>set password=password('root');

授权远程登录:

>grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;

>flush privileges;

>quit;

重新启动mysql,使用source命令导入之前备份的数据。

#mysql -u root -p root -h ip -P 3306

>source /root/all_mysql_backup.sql;

>quit;

用于记录停电导致mysql无法启动,备份数据、重新安装。