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

This article shows you how to achieve full backup in MySQL, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

First, recover the data of a database (database) with full availability.

Case: friends in the group asked, MySQL full database backup. How to restore only one library?

1. Use the-- one-database option. This method is not recommended and problems often occur.

# mysql-uroot-pxx-D db1-o

2. Take out the table-building statements and INSERT data of the required library from the full backup file, and then import them

# sed-n'/ ^-- Current Database: `db1` /, / ^-- Current Database: `/ p' all.dmp > db1.sql # mysql-uroot-pxx-D db1

Second, recover the data of a table (table) with complete equipment.

In production, when a developer colleague updates the table without a where condition, it is said that all data in the order_status column is updated to 0. 0.

Typically, mysqldump backs up the entire library. If you restore the whole library during recovery, it will take too much time and is not necessary. So, we can just restore the watch we want.

In mysqldump backup, there are both table structure and INSERT INTO statements containing data. So the winner can have two parts.

1. Get the table structure

# sed-estrangement TABLE. / {Htinct 23.sql;}'- e'x; create create; create Info` /! d q' mysqldump_2017-05

2. Get the INSERT INTO statement for data recovery

# grep-I 'INSERT INTO `ecs_ugo_order_ info`' mysqldump_2017-05-23.sql > data.sql &

3. Create the table according to the table structure and import the data

Mysql-uroot-pxxx xx

4. Concatenate update statements

Mysql > select concat ('update xx.ecs_ugo_order_info set order_status=',order_status,' where order_id=',order_id,';') from ecs_ugo_order_info into outfile' / tmp/ecs_ugo_order_info_rercovery.sql'

The results are as follows:

Update xx.ecs_ugo_order_info set order_status=6 where order_id=3254778; update xx.ecs_ugo_order_info set order_status=6 where order_id=3254824; update xx.ecs_ugo_order_info set order_status=6 where order_id=3254870

5. Restore order_status to normal value in production warehouse

# mysql-uroot-pxxx xx < / tmp/ecs_ugo_order_info_rercovery.sql the above is how to achieve full backup in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.