A thrilling Percona XTRADB Cluster data restoration process [MySQL], perconaxtradb

A thrilling Percona XTRADB Cluster data restoration process [MySQL], perconaxtradb
A thrilling Percona XTRA Cluster DB data restoration process

At noon on January 27, the phone rang and was called by YI, a colleague. He told me that there were a lot of deadlocks in the database and "service mysql restart" could not be restarted. Here I will first explain: what we use in mobile music projects is

Percona XTRA Cluster DB. We recommend that you have at least three nodes in the production environment. However, the shortage of mobile machines causes the database to run on a single node. Although we have already told you that this causes spof and HA cannot be guaranteed. However, moving does not take it for granted, and finally causes the database to crash.

After the problem occurs, start the database with "/etc/init. d/mysql bootstrap-pxc" and display "table not exists ". After the analysis, it is determined that this is a database crash, resulting in data loss. Then, immediately put into the intense work of data recovery.

Recovery Plan:

1. Create a database;

2. Create a table;

3. discard tablespace;

4. Copy the backup. ibd file;

5. import tablespace;

At this point, the database is restored to normal.

But another new problem is that the previous database name has been referenced in the program and must be changed back to the original database name. Now, start immediately.

Solution:

1. Delete the original database;

2. Create a database with the original database name;

3. Copy the original Backup Directory (idbata and. ibd files)

4. Repeat the above recovery plan later

The database cannot be started normally. Change my. cnf to "innodb_force_recovery = 4 ". The database can be started, but cannot be created, deleted, or updated. In this case, one solution is to dump the data,

Then dump in. To this end, another database is created. This is the official MySQL Community edition. Some tables cannot be dumped during data dumping. Then, use a federated table to import the data. During the import process, the problem "the field is too long and the Import fails" occurs. After searching, change my. cnf to "# SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES" to solve the problem.

So far, the data recovery caused by this Mysql crash is complete, and the data is not lost. What we need to do below is MySQL HA. The main script is as follows:

Alter table AUTH_USER discard tablespace;

Cp-f/data/munion_db_bak/munion_db/AUTH_USER.ibd/data/munion_db

Alter table AUTH_USER import tablespace;

Mysqldump-u root-pmunion123-c -- default-character-set = gbk munion_db AUTH_USER>/data/dump/AUTH_USER

Mysqldump-u root-pmunion123 -- default-character-set = gbk munion_db AUTH_USER </data/dump/AUTH_USER

This Mysql Data Recovery is a rare experience. Of course, it is best not to have such problems. In this case, we need to put HA first. With another data recovery solution (not verified ):

1. drop these tables from mysql:
Innodb_index_stats innodb_table_stats slave_master_info slave_relay_log_info slave_worker_info
2. delete all. frm &. ibd of the tables above.
3. run this file to recreate the tables abve (source five-tables. SQL ).
4. restart mysqld.
Cheers, CNL