Shulou(Shulou.com)06/01 Report--

The previous article introduced "innobackupex hot backup specified database table operation" and analyzed the whole process, that is, the principle of exporting table dictionaries and data files to import. Then for the backup and recovery of a single table (restore in a new instance or new library), we can directly use the method of physically exporting innodb tables.

The specific operations are as follows:

1. Lock the backup table and export cfg.

Mysql > select * from T1

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

| | 4 |

| | 2 |

| | 3 |

| | 4 |

+-+

7 rows in set (0.00 sec)

Mysql > flush table T1 with read lock

Query OK, 0 rows affected (0.01 sec)

It was found that T1 generated the cfg file.

[root@222 test] # ls

Db.opt t1.cfg t1.frm t1.ibd t2.frm t2.ibd

Perform unlock tables, cfg file collection.

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

[root@222 test] # ls.. / test/

Db.opt t1.frm t1.ibd t2.frm t2.ibd

two。 Back up the cfg and ibd files of T1 before unlock tables.

[root@222 test] # cp t1.ibd t1.cfg / home/backup/

3. Create a new library and create a T1 table with the same structure.

Mysql > create database test1

Query OK, 1 row affected (0.00 sec)

Mysql >

Mysql > use test1

Database changed

Mysql >

Mysql > create table T1 like test.t1

Query OK, 0 rows affected (0.03 sec)

4. Delete the newly created T1 tablespace.

Mysql > alter table T1 discard tablespace

Query OK, 0 rows affected (0.02 sec)

It was found that the ibd file for the newly created T1 tablespace was cleared.

[root@222 test] # ls.. / test1/

Db.opt t1.frm

5. Copy the cfg and ibd files of the backed up T1 table to the newly created library.

[root@222 test] # cd / home/backup/

[root@222 test1] # ll-trh

Total dosage 116K

-rw-rw----. 1 mysql mysql 61 December 16 09:49 db.opt

-rw-rw----. 1 mysql mysql 8.4K December 16 09:49 t1.frm

-rw-r-. 1 root root 96K December 16 09:51 t1.ibd

-rw-r-. 1 root root 354 December 16 09:51 t1.cfg

[root@222 test1] # chown-R mysql.mysql *

[root@222 test1] #

[root@222 test1] #

[root@222 test1] # ll-trh

Total dosage 116K

-rw-rw----. 1 mysql mysql 61 December 16 09:49 db.opt

-rw-rw----. 1 mysql mysql 8.4K December 16 09:49 t1.frm

-rw-r-. 1 mysql mysql 96K December 16 09:51 t1.ibd

-rw-r-. 1 mysql mysql 354 December 16 09:51 t1.cfg

6. Perform a new T1 table import tablespace operation.

Mysql > alter table T1 import tablespace

Query OK, 0 rows affected (0.08 sec)

7. The query result is consistent with the table backed up in step 1, and the operation is complete.

Mysql > select * from test1.t1

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

| | 4 |

| | 2 |

| | 3 |

| | 4 |

+-+

7 rows in set (0.00 sec)

Btw.

a. Because the cfg file exists in the new table, an error will be reported when deleting the library operation

ERROR 1010 (HY000): Error dropping database (can't rmdir'. / test1/', errno: 17)

If you use innobackupex backup and imported exp files, you will find that after deleting the library, the exp file cannot be deleted, so delete the exp file manually and delete the library.

If you import the cfg file by manual command using the above method, an error is reported when deleting the library, but no file is found when you look at the library file, then you can execute it again; for the newly imported cfg file, you can execute the following command next time, and the cfg file disappears.

Mysql > flush table T1 for export

Query OK, 0 rows affected (0.00 sec)

[root@222 test1] # ll-trh

Total dosage 116K

-rw-rw----. 1 mysql mysql 61 December 16 09:49 db.opt

-rw-rw----. 1 mysql mysql 8.4K December 16 09:56 t1.frm

-rw-r-. 1 mysql mysql 96K December 16 09:56 t1.ibd

-rw-r-. 1 mysql mysql 355 December 16 09:57 t1.cfg

Mysql >

Mysql >

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

[root@222 test1] # ll-trh

Total dosage 112K

-rw-rw----. 1 mysql mysql 61 December 16 09:49 db.opt

-rw-rw----. 1 mysql mysql 8.4K December 16 09:56 t1.frm

-rw-r-. 1 mysql mysql 96K December 16 09:56 t1.ibd

b. It is found that manual command line backup and recovery is more convenient, but there will be a process of locking tables, so choose different methods for backup according to different circumstances. Innobackupex mode is used for tables written online, which will not lead to replication delay; for tables without writes, locking and importing cfg files directly makes the operation more convenient.

OK,done .

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.