How to use innobackupex backup set to restore the specified library
This article mainly describes how to use innobackupex backup set to restore the specified library, the article is very detailed, has a certain reference value, interested friends must read!
1. Source library export table structure
mysqldump -uroot -p --no-data zabbix > info.sql
2. Create libraries, tables to restore
mysql> create database zabbix character set utf8 collate utf8_bin;
mysql> use zabbix;
mysql> source info.sql
3. View foreign key constraints
mysql> SELECT @@FOREIGN_KEY_CHECKS;
+----------------------+
| @@FOREIGN_KEY_CHECKS |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)
Check to see if the table you want to migrate has foreign key constraints:
mysql> select * from information_schema.TABLE_CONSTRAINTS where table_schema = 'zabbix' and constraint_type = 'FOREIGN KEY';
Disable foreign keys, if any:
mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)
4. Discard the table in the database
mysql> select concat('alter table ',table_name,' discard tablespace;') from information_schema.tables where table_schema = 'zabbix';
mysql> alter table acknowledges discard tablespace;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table actions discard tablespace;
Query OK, 0 rows affected (0.00 sec)
......
Then re-enable foreign key constraints:
mysql> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.00 sec)
5. Copy the ibd file of the apply-log backup set table to the data directory and modify the permissions:
cp *.ibd /opt/app/mysql/mysql5722/data/zabbix/
chown -R mysql.mysql /opt/app/mysql/mysql5722/data/zabbix/*
6. import tablespace for tables in the library
mysql> select concat('alter table ',table_name,' import tablespace;') from information_schema.tables where table_schema = 'zabbix';
ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)
Then modify the row_format of the table:
mysql> select concat('alter table ',table_name,' row_format=compact;') from information_schema.tables where table_schema = 'zabbix';
Note: After modifying the row_format of the table, go back to step 5
The above is "how to use innobackupex backup set to restore the specified library" all the content of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!
