A thrilling Percona XTRADB cluster data repair process "MySQL"

a thrilling Percona XTRA Cluster DB data repair process

2014.12.27 noon at about 12:30, the telephone rang, is a colleague Yi's phone, told that there is a large number of deadlock in the library, with "service MySQL restart" can not be restarted. Let me explain here: what we're using in the Mobile music project is

Percona XTRA Cluster DB, the recommended minimum is 3 nodes in a build environment. But the mobile mobile machine is strained, causing the database to run on a single node. Although it has been previously reported that this led to a single point of failure, can not guarantee ha. But the move does not agree, finally caused the database crash occurred.

After the problem occurs, start the database with "/etc/init.d/mysql BOOTSTRAP-PXC", but display "Table not exists". After analysis, it is determined that the database crashes resulting in data loss. After that, the tension of data recovery is immediately put into work.

The recovery scenario is:

1, the new database;

2, the new table;

3, discard table space;

4. ibd file with copy backup;

5, import table space;

You are back to normal on the new library.

But again a new problem, the program has already referred to the previous database name, you must change back to the original database name. Now, hands-on.

The scheme is:

1, delete the original database;

2. Create a new database with the original library name;

3, copy the original backup directory (Idbata,. ibd file)

4, then repeat the above recovery plan

After discovering that the database does not start properly, change the my.cnf to "innodb_force_recovery = 4". The database can be started, but cannot be created, deleted, and updated. In this case, one option is to dump the data,

Dump it in again. To do this, another database is created. This is the official MySQL community version of the application. In the process of data dump, it is found that some tables cannot be dump. The data is then imported into it using a federated table. During the import process, there was also a "field too long, failed to import" issue, after looking to change the my.cnf to "# sql_mode=no_engine_substitution,strict_trans_tables" problem resolution.

At this point, the MySQL crash resulted in the completion of data recovery work, the data is not lost. The next thing to do is MySQL ha. The main scripts are as follows:

ALTER TABLE AUTH_USER discard tablespace;

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

ALTER TABLE AUTH_USER import tablespace;

Mysqldump-u root-pmunion123-c--default-character-set=gbk munion_db auth_user >/data/du Mp/auth_user

Mysqldump-u root-pmunion123--default-character-set=gbk munion_db Auth_User </data/d Ump/auth_user

MySQL data recovery is a rare experience, of course, it is best not to have such a problem. This requires HA to be in front of the first. With another data recovery scenario (not actually verified):

1. Drop these tables from MySQL:
Innodb_index_stats Innodb_table_statsSlave_master_infoSlave_relay_log_info Slave_worker_info 
2. Delete all. frm & ibd of the tables above.   
3. Run this file to recreate the tables above (source five-tables.sql).  
4. Restart mysqld.   
Cheers, CNL

A thrilling Percona XTRADB cluster data repair process "MySQL"