小白学习 | MySQL - 聊聊数据备份的重要性

[Note] InnoDB: Database was not shutdown normally!
[Note] InnoDB: Starting crash recovery.
...
[Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
[Note] InnoDB: 32 non-redo rollback segment(s) are active.
[Note] InnoDB: Waiting for purge to start
...
[ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
...
[ERROR] Can't open and lock privilege tables: Unknown error 1146
...
Status information:
Current dir: opt/app/mysql/data/
Running threads: 0  Stack size: 262144
Current locks:
lock: 0x35f53f0:
lock: 0x35ad680:
lock: 0x35a5560:
lock: 0x359e5a0:
lock: 0x3589bd0:
...
Events status:
LLA = Last Locked At  LUA = Last Unlocked At
WOC = Waiting On Condition  DL = Data Locked
Event scheduler status:
State      : INITIALIZED
Thread id  : 0
...
Event queue status:
Element count   : 0
Data locked     : NO
Attempting lock : NO
LLA             : init_queue:96
LUA             : init_queue:104
WOC             : NO
Next activation : never
2021-10-14T10:26:05.258906Z 0 [Warning] InnoDB: Cannot open table mysql/servers from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2021-10-14T10:26:05.258952Z 0 [ERROR] Can't open and lock privilege tables: Unknown error 1146

[Warning] InnoDB: Cannot open table test/AO_0456E7_BAM_MARKER from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
...
[ERROR] InnoDB: Page [page id: space=0, page number=7] log sequence number 43749257776 is in the future! Current system log sequence number 2589302.
2021-10-15T10:32:46.280173+08:00 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
...
InnoDB: Operating system error number 2 in a file operation.
2021-10-15T10:32:46.393644+08:00 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-10-15T10:32:46.393648+08:00 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-10-15T10:32:46.393652+08:00 0 [ERROR] InnoDB: Cannot open datafile for read-only: './test/AO_187CCC_SIDEBAR_LINK.ibd' OS error: 71
2021-10-15T10:32:46.393658+08:00 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2021-10-15T10:32:46.393662+08:00 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-10-15T10:32:46.393665+08:00 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-10-15T10:32:46.393670+08:00 0 [ERROR] InnoDB: Could not find a valid tablespace file for `test/AO_187CCC_SIDEBAR_LINK`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

[ERROR] InnoDB: Tablespace for table `mysql`.`innodb_table_stats` is missing.
2021-10-15T10:34:13.283461+08:00 2 [ERROR] InnoDB: Fetch of persistent statistics requested for table `test`.`AO_0456E7_RESOURCE_MAPPING` but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

mysqldump -uroot -p test > /opt/app/mysql/dump/test.sql
重新初始化库
create database test;
mysqldump -uroot -p test </opt/app/mysql/dump/test.sql

create user 'test'@'%' identified by 'test123';
grant all privileges on test.* to 'test'@'%';
flush privileges;

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql -utest -p -A

0 0 * * * /opt/app/mysql/dump/test_backup.sh >> /opt/app/mysql/dump/run.log 2>&1