MySQL Failure case

1th Chapter MySQLFailure Cases: 1.1Background Notes:

MySQL architecture does not have a master-slave replication system and does not backup on time

There are two libraries conf libraries and Jira libraries in the database, only one copy of the backup data , or a year ago

1.2Failure Scenarios:

unexpected downtime , resulting in physical damage to the disk, which means that the Jira files in the data directory in the MySQL software program are not

The database failed to start , checked , the Conf Library data files are still there, that is, IBD and frm files exist

Leadership Requirements , Conf or good , can temporarily first run the database , and then find a way to repair the Jira library

1.3Fault Environment Simulation: 1.3.1into theDataIn the database directory,Delete a library's. ibdfile

[[email protected] jiang]# pwd/application/mysql/data/jiang[[email protected] jiang]# lltotal 16-rw-rw----1 MySQL MySQL Apr 3 19:01 db.opt-rw-rw----1 mysql mysql 8556 Apr 8 21:19 stu.frm-rw-rw----1 mysql mysql 0 Apr 8 21:25 stu.i Bd[[email protected] jiang]# Echo 123 >stu.ibd

1.3.2 RestartMysqlService

[Email protected] jiang]#/etc/init.d/mysqld startstarting MySQL ... error! The server quit without updating PID file (/application/mysql/data/db01.pid).

1.4Interim Solution: 1.4.1build a temporary node,alsoMysqlMulti-instance,Multi-Instance Building no demo here,then start the multi-instance

[Email protected] 3307]# sh/server/scripts/mysql.sh startmysql 3307 starting [O                   K][[email protected] 3307]# ss-tunlp|grep 3307tcp LISTEN 0::: 3307:::* Users: (("mysqld", pid=5219,fd=11))

1.4.2Enter the database,CREATE TABLE Structure

to recover a table , you need to create a table structure that is the same as before the failure , and then import the. ibd file , or you cannot import it.

Mysql> CREATE TABLE people (ID int,name varchar (20));

1.4.3Delete the newly created library.. ibdfile,because there is no data,to import new data in a minute

Mysql> ALTER TABLE people discard tablespace; Query OK, 0 rows affected (0.27 sec)

1.4.4put. ibdfiles are copied to a multi-instance database directory,and authorized toMysqlUser

[Email protected] zabbix]# cp-a. /people.ibd. [Email protected] zabbix]# chown mysql.mysql people.ibd

1.4.5Re-import. ibdfile

mysql> alter table people import tablespace; query ok, 0 rows affected, 1 warning  (0.34 sec) mysql> select  * from people;+------+------------------------------------------------------------------------ -----------------------------------------+| id   | name                                                                                                                  |+------+----------------------------------------------------------------------------------- ------------------------------+| null | ang  ??         1       ssd             1       angbo        (??         1       angbosd                          | |  NULL | NULL                                                                                                                 | |  NULL | angbosd                                                                                                              | |  NULL | ssd            1        angbo      (??         1       angbosd                                                     |+------+----------------------------- ------------------------------------------------------------------------------------+4 rows in  set  (0.00 SEC)

View all the contents of the table, you can already see the data

Description: So the architecture, regardless of the size of the architecture system, or the amount of data storage, the backup must be backed up, this lazy to steal

For backup data, you can either schedule a full backup of the SQL file or copy the MySQL service from

MySQL Failure case