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

The following brings you about the contents of the mysql database recovery operation guide, I believe you must have read similar articles. What's the difference between what we bring to everyone? Let's take a look at the body. I believe you will gain something after reading the mysql database recovery operation guide.

1. System description:

Database version: MySql5.6.34

Operating system: CentOS release 6.8 (Final)

Database code: utf8

Database failure description: a field is updated in the test library, but no where condition is added, causing a column to be all updated to the same value.

Select * from test

->

+-+ +

| | id | name |

+-+ +

| | 1 | Sun Liren |

| | 2 | Xue Yue |

| | 3 | Li Zongren |

| | 4 | × × | |

| | 5 | Bai Chongxi |

| | 6 | Liao Yaoxiang |

| | 7 | Button |

| | 8 | Chiang Kai-shek |

| | 9 | KMT |

| | 10 | Hu Shi |

+-+ +

10 rows in set (0.00 sec)

Update test set name ='× ×'

Query OK, 10 rows affected (0.01sec)

Rows matched: 10 Changed: 10 Warnings: 0

Mysql > select * from test

+-+ +

| | id | name |

+-+ +

| | 1 | × × | |

| | 2 | × × | |

| | 3 | × × | |

| | 4 | × × | |

| | 5 | × × | |

| | 6 | × × | |

| | 7 | × × | |

| | 8 | × × | |

| | 9 | × × | |

| | 10 | × × | |

+-+ +

10 rows in set (0.00 sec)

2. Check whether binlog log is enabled:

In the [mysqld] domain in my.cnf

See that the log has been opened:

[mysqld]

# Remove leading # and set to the amount of RAM forthe most important data

# cache in MySQL. Start at 70% of total RAM fordedicated server, else 10%.

# innodb_buffer_pool_size = 128m

# Remove leading # to turn on a very important dataintegrity option: logging

# changes to the binary log between backups.

# log_bin

Log_bin=mysqlbin_oldboy

3. View the log files in the database location:

Cd / data/mysql

-rw-rw----. 1 mysql mysql 56 January 20 00:14 auto.cnf

-rw-r--r--. 1 root root 4412 February 8 16:05 bintest.sql

-rw-rw----. 1 mysql mysql 12582912 February 8 16:06 ibdata1

-rw-rw----. 1 mysql mysql 50331648 February 8 16:06 ib_logfile0

-rw-rw----. 1 mysql mysql 50331648 January 20 00:13 ib_logfile1

Drwx-. 2 mysql mysql 4096 January 20 00:14 mysql

-rw-rw----. 1 mysql mysql 3068 February 8 16:06mysqlbin_oldboy.000001

-rw-rw----. 1 mysql mysql 25 February 8 00:45 mysqlbin_oldboy.index

Srwxrwxrwx. 1 mysql mysql 0 February 8 00:45 mysql.sock

Drwx-. 2 mysql mysql 4096 February 7 22:58 oldboy

Drwx-. 2 mysql mysql 4096 February 7 22:39 oldgirl

Drwx-. 2 mysql mysql 4096 January 20 00:14 performance_schema

Drwx-. 2 mysql mysql 4096 February 8 00:17 test

-rw-r-. 1 mysql root 44363 February 8 15:42 web1.err

-rw-rw----. 1 mysql mysql 7 February 8 00:45 web1.pid

The red mark is the log file we want to use.

Use the mysqlbinlog command to convert mysqlbin_oldboy.000001 to a sql file

Mysqlbinlog-d test mysqlbin_oldboy.000001 > bin.sql

Description:-d specify database

[root@web1 mysql] # ll

The total dosage is 110696

-rw-rw----. 1 mysql mysql 56 January 20 00:14 auto.cnf

-rw-r--r--. 1 root root 7707 February 8 16:28 bin.sql

-rw-r--r--. 1 root root 4412 February 8 16:05 bintest.sql

-rw-rw----. 1 mysql mysql 12582912 February 8 16:25 ibdata1

-rw-rw----. 1 mysql mysql 50331648 February 8 16:25 ib_logfile0

-rw-rw----. 1 mysql mysql 50331648 January 20 00:13 ib_logfile1

Drwx-. 2 mysql mysql 4096 January 23 22:50 mydx

Drwx-. 2 mysql mysql 4096 January 20 00:14 mysql

-rw-rw----. 1 mysql mysql 3285 February 8 16:25 mysqlbin_oldboy.000001

-rw-rw----. 1 mysql mysql 25 February 8 00:45 mysqlbin_oldboy.index

Srwxrwxrwx. 1 mysql mysql 0 February 8 00:45 mysql.sock

Drwx-. 2 mysql mysql 4096 February 7 22:58 oldboy

Drwx-. 2 mysql mysql 4096 February 7 22:39 oldgirl

Drwx-. 2 mysql mysql 4096 January 20 00:14 performance_schema

Drwx-. 2 mysql mysql 4096 February 8 00:17 test

-rw-r-. 1 mysql root 44363 February 8 15:42 web1.err

-rw-rw----. 1 mysql mysql 7 February 8 00:45 web1.pid

Edit the bin.sql file

Vim bin.sql

# at 3147

# 170208 16:25:52 server id 1 end_log_pos 3254 CRC32 0xa4ab5836 Query thread_id=2 exec_time=0 error_code=0

SET timestamp 1486542352

Update test set name ='× × 'delete this line

/ *! * /

# at 3254

Then restore:

[root@web1 mysql] # mysql-uroot-p test-e "select * from test;"

Enter password:

+-+ +

| | id | name |

+-+ +

| | 1 | × × | |

| | 2 | × × | |

| | 3 | × × | |

| | 4 | × × | |

| | 5 | × × | |

| | 6 | × × | |

| | 7 | Button |

| | 8 | Chiang Kai-shek |

| | 9 | KMT |

| | 10 | Hu Shi |

+-+ +

The reason why I see 1-6 or × ×, that is because I did not open binlog log before, now I see the importance of mysql log!

Summary:

Mysql data must be updated with where conditions, and must be operated after a successful test on the test library. Data is the most important document for a company, so please pay attention to it.

For the above mysql database recovery operation guide, do you think it is what you want? If you want to know more about it, you can continue to follow our industry information section.

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.