Mysqldump Full recovery of single or single-table

first, the problem found

Usually use mysqldump backup when someone likes to use the-a–b parameter to complete, so the backup time will be simple, but when the recovery of the direct restore will all the library will be restored, this is not the result we want.

second, analysis of the problem

Restore a single or single table we can intercept the part we want from a full-database backup of the SQL file through the shell command. In addition, for the recovery of the library MySQL also has a parameter to solve this problem.

third, solve the problem

1. Restore a single library with MySQL's own parameters.

#  fully-prepared database mysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  wl_tj56_dict       | |  mysql              | |  performance_schema | |  test               | |  test01             | |  test02             |+------------------ --+ #执行全备 [[Email protected] ~]# mysqldump -uroot -pterjoy2016 -s /data/mysql /mysql_3306/mysql.sock -a -b --events > /opt/fullbackup.sqlwarning: using  a password on the command line interface Can be insecure. [[Email protected] ~]# ll /opt/fullbackup.sql -rw-r--r--.  1 root root  651037 dec 20 00:44 /opt/fullbackup.sql#drop Database mysql> show databases;+- -------------------+| database           |+------- -------------+| information_schema | |  wl_tj56_dict       | |  mysql              | |  performance_schema | |  test               | |  test01             | |  test02             |+------------------ --+7 rows in set  (0.00 sec) mysql> drop database test; Query ok, 2 rows affected  (0.07 sec) mysql> drop database test01; query ok, 1 row affected  (0.02 sec) mysql> drop database test02 ; query ok, 1 row affected  (0.02 sec) Mysql> drop database wl_ Tj56_dict; query ok, 1 row affected  (0.00 sec) mysql> show databases;+--------- -----------+| database           |+--------------- -----+| information_schema | |  mysql              | |  performance_schema |+--------------------+3 rows in set  (0.00 sec) #  Finally restore the test Library # Restore the database when the error, unable to find the test library. [[email protected] ~]# mysql -uroot -pterjoy2016 -s /data/mysql/mysql_3306/ Mysql.sock --one-database test < /opt/fulLbackup.sqlwarning: using a password on the command line interface  can be insecure. error 1049  (42000): unknown database  ' test ' #这时可以通过全备的SQL文件, locate the statement that created the library and create the test library [email  protected] ~]# grep -i  "^create database"  /opt/fullbackup.sql create  DATABASE /*!32312 IF NOT EXISTS*/  ' Wl_tj56_dict '  /*!40100 default  CHARACTER SET latin1 */; create database /*!32312 if not exists*/  ' MySQL '  /*!40100 default  CHARACTER SET utf8 */; create database /*!32312 if not exists*/  ' Test '  /*!40100 DEFAULT  character set utf8 */; create database /*!32312 if not exists*/  ' test01 '  /*!40100 default  CHARACTER SET utf8 */; Create database /*!32312 if not EXISTS*/  ' test02 '  /*!40100 DEFAULT CHARACTER SET utf8 */;mysql>  CREATE DATABASE /*!32312 IF NOT EXISTS*/  ' Test '  /*!40100 default  CHARACTER SET utf8 */; query ok, 1 row affected  (0.00 sec) mysql> show databases;+--------- -----------+| database           |+--------------- -----+| information_schema | |  mysql              | |  performance_schema | |  test               |+-------- ------------+4 rows in set  (0.00 sec) #test库创建好了, and then restore. #恢复成功没有报错 [[email protected] ~]# mysql -uroot -pterjoy2016 -s /data/mysql/ Mysql_3306/mysql.sock --one-database test < /opt/fullbackup.sql# the last to see if there's a data recovery # Strange?   Here's how the other libraries come out, not just the test library? mysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  wl_tj56_dict       | |  mysql              | |  performance_schema | |  test               | |  test01             |+------------------ --+6 rows in set  (0.01 sec) #赶紧查看test有没有数据, you can view the test data mysql> use test; reading table information for completion of table and column  Namesyou can turn off this feature to get a quicker startup with -adatabase changedmysql> select * from t;+----+---------+|  id | name    |+----+---------+|  1 | xm       | |   2 | xmj     | |   3 | xuwu    | |   4 | chuzan  | |   5 | chuzan2 | |   6 | chuzan3 |+----+---------+6 rows in set  (0.00 sec) #其它库呢?   There is no data to recover from. Here I also struggle for a long time, I clearly only restore test library how other libraries also recovered?   Only other libraries have no data. mysql> use test01database changedmysql> show tables; empty set  (0.00 sec) mysql>  at this point, the library is restored by parameter.

2. To recover the table structure and data of the library to be recovered by shell command

#首先我drop掉用户的数据库, the full data I still use the previous backup. #现在只有mysql自带的库了mysql > show databases;+--------------------+| database            |+--------------------+| information_schema | |  mysql              | |  performance_schema |+--------------------+3 rows in set  (0.00 sec) # Now still to restore the test library, other libraries do not recover # in the operating system execute the following command [[email protected] ~]# cat /opt/fullbackup.sql |  > sed -n -e  '/^create database.* ' Test '/,/^create database/ p '  | > sed -e  ' $d '  | > mysql -uroot -pterjoy2016 -s  /data/mysql/mysql_3306/mysql.sock# now to see if the test library is successfully restored mysql> show databases;+------------------ --+| database           |+--------------------+|  information_schema | |  mysql              | |  performance_schema | |  test               |+-------- ------------+4 rows in set  (0.00 sec) mysql> use testreading table  information for completion of table and column namesyou can  turn off this feature to get a quicker startup with - adatabase changedmysql> show tables;+----------------+| tables_in_test |+--------- -------+| area           | |  t              |+----------------+ 2 rows in set  (0.00 sec) mysql> select * from t;+----+--------- +| id | name    |+----+---------+|  1 | xm       | |   2 | xmj     | |   3 | xuwu    | |   4 | chuzan  | |   5 | chuzan2 | |   6 | chuzan3 |+----+---------+6 rows in set  (0.00 sec) MySQL >  #从上面结果来看, Recovery is successful.

3, through the shell command interception to restore the table structure and data for recovery

#首先登陆test数据库, delete t-table mysql> use testreading table information for completion  of table and column namesyou can turn off this feature  to get a quicker startup with -adatabase changedmysql> show  tables;+----------------+| tables_in_test |+----------------+| area            | |  t              |+----------------+ 2 rows in set  (0.00 sec) mysql> select * from t;+----+--------- +| id | name    |+----+---------+|  1 | xm       | |   2 | xmj     | |   3 | xuwu    | |   4 | chuzan  | |   5 | chuzan2 | |   6 | chuzan3 |+----+---------+6 rows in set  (0.00 sec) MySQL > drop table t; query ok, 0 rows affected  (0.01 sec) mysql> show tables;+----------- -----+| tables_in_test |+----------------+| area            |+----------------+1 row in set  (0.00 sec) #  The operating system uses commands to intercept the structure of the table and the SQL of the data. [[email protected] ~]# cat /opt/fullbackup.sql | sed -n -e  '/^ create database.* ' test '/,/^create database/ p '  | sed -e  ' $d '  |  sed -n  '/-- table structure for table  ' t '/,/unlock tables;/p '   > create_t.sql  #查看生成的SQL语句. [[email protected] ~]# cat create_t.sql -- table structure for  table  ' t '--drop table if exists  ' t ';/*!40101 set  @saved_cs_client       = @ @character_set_client  */;/*!40101 SET character_set_client = utf8  */; create table  ' t '   (   ' id '  int (one)  NOT NULL AUTO_INCREMENT,    ' name '  varchar  NOT NULL DEFAULT  ',  primary key  (' id ') )  engine=innodb auto_increment=7 default charset=utf8;/*!40101 set character_set _client =  @saved_cs_client  */;---- Dumping data for table  ' t '--lock  TABLES  ' t '  WRITE;/*!40000 ALTER TABLE  ' t '  disable keys */;insert  INTO  ' t '  VALUES  (1, ' XM '), (2, ' XMJ '), (3, ' Xuwu '), (4, ' Chuzan '), (5, ' chuzan2 '), (6, ' chuzan3 ');/* !40000 alter table  ' t '  ENABLE KEYS */; unlock tables; #重新登录并切换到test库, use the source  command to execute the previously generatedSQL statement. Mysql> use testreading table information for completion of table  and column namesyou can turn off this feature to get a  quicker startup with -Amysql> source create_t.sqlQuery OK, 0  rows affected, 1 warning  (0.00 sec) query ok, 0 rows affected   (0.00 SEC) query ok, 0 rows affected  (0.00 sec) Query OK, 0  rows affected  (0.09 sec) query ok, 0 rows affected  (0.00 sec ) query ok, 0 rows affected  (0.00 sec) query ok, 0 rows  affected, 1 warning  (0.00 sec) query ok, 6 rows affected  (0.00  SEC) records: 6  duplicates: 0  warnings: 0query ok, 0  rows affected, 1 warning  (0.00 sec) query ok, 0 rows affected  (0.00 sec) # View data T Table Recovery success mysql> select  * from t;+----+---------+| id | name     |+----+---------+|  1 | xm      | |   2 | xmj     | |   3 | xuwu    | |   4 | chuzan  | |   5 | chuzan2 | |   6 | chuzan3 |+----+---------+6 rows in set  (0.00 sec)

The recovery of the single table was successful.

This article is from the "Xu Mingjiang blog" blog, make sure to keep this source http://xumingjiang.blog.51cto.com/703960/1884622

Mysqldump Full recovery of single or single-table