Shulou(Shulou.com)05/31 Report--

This article is about how to use mysqldump backup to restore the database. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

Using mysqldump backup to restore database

1. Create a test database

mysql> create database allen;

Query OK, 1 row affected (0.01 sec)

mysql> use allen;

Database changed

mysql> create table tab1 (ID tinyint unsigned not null primary key auto_increment,Name char(20) not null unique key,Age tinyint unsigned,Gender char(1) default 'M');

Query OK, 0 rows affected (0.38 sec)

mysql> insert into tab1 (Name,Age,Gender) values ('Zhang WuJi',26,'M'),('Zhao Min',24,'F');

Query OK, 2 rows affected (0.05 sec)

Records: 2 Duplicates: 0 Warnings: 0

Complete backup of allen database

[root@mysql ~]# mysqldump -uroot -puplooking --lock-all-tables --routines --events --triggers --master-data=2 --flush-logs --databases allen > /backup/allen_`date +

%F`.sql

3. Make incremental backups based on binary logs (last full backup to the current log location)

mysql> create table tab2 (ID tinyint unsigned not null primary key auto_increment,Name char(20) not null unique key,Age tinyint unsigned,Gender char(1) default 'M');

Query OK, 0 rows affected (0.34 sec)

mysql> insert into tab2 (Name,Age,Gender) values ('Liang ShanBo',26,'M'),('Zhu YingTai',24,'F'),(3,'Feng Songtao',25,M);

Query OK, 2 rows affected (0.08 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> show master status; --Log location during incremental backup

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000006 | 812 | | |

+------------------+----------+--------------+------------------+

[root@mysql ~]# mysqlbinlog --start-position=107 /binlog/mysql-bin.000006 > /backup/allen_increment.sql

Description: --start-position=107 is the position of the log when it was last complete (you can get it by viewing the complete file content). Default--stop-position parameter defaults to the current log.

Insert data into allen database again, then delete allen database

mysql> insert into tab2 (Name,Age,Gender) values ('Zhu BaJie',26,'M'),('Sun WuKong',24,'F');

mysql> drop database allen;

Export logs from the last incremental backup to the point in time to be restored (before allen database is deleted)

[root@mysql ~]# mysqlbinlog --start-position=812 --stop-position=1084 /binlog/mysql-bin.000006 > /backup/allen_1084.sql

Description: 1, --start-position=812 obtained for viewing above, --stop-position=1084 obtained from binary log records

2. The/backup/allen_1084.sql file can be modified. If there is a misoperation in the middle of this log (such as deleting a piece of data), you can comment out the misoperation.

6. Copy the backed up files to the database server to be restored for recovery

[root@mysql ~]# scp -p /backup/allen_* 192.168.122.11:/backup/

Data recovery on target server

mysql> source /backup/allen_2016-06-25.sql

mysql> source /backup/allen_increment.sql

mysql> source /backup/allen_1084.sql

7. View data

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| allen |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.03 sec)

mysql> use allen

Database changed

mysql> show tables;

+-----------------+

| Tables_in_allen |

+-----------------+

| tab1 |

| tab2 |

+-----------------+

2 rows in set (0.00 sec)

mysql> select * from tab2;

+----+--------------+------+--------+

| ID | Name | Age | Gender |

+----+--------------+------+--------+

| 1 | Liang ShanBo | 26 | M |

| 2 | Zhu YingTai | 24 | F |

| 3 | Feng Songtao | 25 | M |

| 4 | Sun WuKong | 24 | F |

+----+--------------+------+--------+

4 rows in set (0.02 sec)

Note: Zhu BaJie is not recorded in tab2 because I modified/backup/allen_1084.sql as follows:

#insert into tab2 (Name,Age,Gender) values ('Zhu BaJie',26,'M'),('Sun WuKong',24,'F')

insert into tab2 (Name,Age,Gender) values ('Sun WuKong',24,'F')

Thank you for reading! About "how to use mysqldump backup different machine recovery database" this article is shared here, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge, if you think the article is good, you can share it to let more people see it!

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.