Steps for restoring a single MySQL table

When I was taking a break, a phone call completely broke my sleepiness. "The developer forgot to add the where condition when writing the update SQL." I believe that every DBA classmate would have the urge to curse when hearing this news. Fortunately, it was just a single table that was messed up, and no expert was playing around with dropping tables in the DB. Although I haven't performed single table recovery for a long time, fortunately the steps are all imprinted in my mind, and I completed the recovery without any problems.

Now, let me record the steps and key points of single table recovery to remind myself and everyone else.

first step:

Find a server with relatively high performance as the restore machine, and restore the most recent backup from the backup pool to this restore machine. Of course, this assumes that you have a backup and that the backup is available. (What? You told me that you didn't make a backup. Then you can go to bed and enjoy the free air.)

Note: Do not start synchronization at this time, be sure to keep it in an asynchronous state.

ps: I would like to say one more thing. For DBAs, backup is the most important link. Not only must you have it, but you must also regularly check whether the backup is available. This is one of the necessary qualities of a DBA.

Step 2:

Contact the developer who made the mistake to get the wrong SQL statement and time point, and then find the execution point of this SQL statement from the binlog of the main database. The specific operation examples are as follows

### Use mysqlbinlog to convert binary logs into plaintext SQL logs mysqlbinlog mysql-bin.000123 > /data1/000123.sql

### Use the Linux grep command to find the location of the SQL that causes data corruption based on the "key word" cat 000123.sql |grep -C 10 'key word' --color

### The red ones are the problematic SQL, the start time of this SQL, and the start time of the next SQL. These two POS positions are very important# at 20393709
#131205 20:55:08 server id 18984603 end_log_pos 20393779 Query thread_id=16296016 exec_time=0 error_code=0
SET TIMESTAMP=1386248108/*!*/;
BEGIN
/*!*/;
# at 20393779
#131205 20:55:08 server id 18984603 end_log_pos 20394211 Query thread_id=16296016 exec_time=0 error_code=0
SET TIMESTAMP=1386248108/*!*/;
update table tablename set names='xxxx';
# at 20394211
#131205 20:55:08 server id 18984603 end_log_pos 20394238 Xid = 92465981
COMMIT /*!*/;
# at 20394238
#131205 20:55:10 server id 18984603 end_log_pos 20394308 Query thread_id=16296017 exec_time=0 error_code=0
SET TIMESTAMP=1386248110/*!*/;
BEGIN

Step 3:

According to the pos position obtained in the second step, start the synchronization relationship, but need to stop at the pos position before the problematic SQL, use the following command

### The pos position is equal to the pos position of the problem SQL begin slave start until master_log_file='mysql-bin.000123',master_log_pos=20393709;

Then skip this SQL and synchronize the change to the next POS position. Use the following command

### The pos position is equal to the pos position after the problematic SQL commit. change master to master_log_file='mysql-bin.000123',master_log_pos=20394238;

From the above two commands we can see that the two POS positions obtained in the second step are critical.

Step 4:

There are two purposes for renaming the table with the waste data on the main database. First, to stop writing to this table (of course, this will have a certain impact on the business, and there will be write failure alarms for a period of time, so you need to contact the business department in advance). Second, if the recovery fails, at least there is still a table with the waste data, which can be quickly restored to the state before our recovery operation.

### Execute rename table tablename to tablename_bak on the main database;

Then perform the dump operation on the restore machine. Be careful when using this operation. If there are Chinese characters in the table, remember to add the --default-chararter-set parameter.

mysqldump -uusername -ppassword -S/tmp/mysql.sock dbname tablename --opt> tablename.sql

Finally, transfer this file to the main library server to complete the final recovery operation

### You can choose to source tablename.sql after logging into MySQL;

### You can also use the cmd interface mysql -uusername -ppassword -S/tmp/mysql.sock < tablename.sql

Of course, you can also avoid this trouble and directly execute the following command, but my personal habit is to save historical operation records and intermediate results, so I follow the above steps to complete it. This way, I can save a text file that can be restored at any time, which makes me feel more at ease.

### A quick method is to follow the steps below. On the restore machine, run the command mysql -uusername -ppassword -S /tmp/mysql.sock dbname tablename | mysql -uusername -ppassowrd -hhost -Pport

The above commands can be used to directly complete the dump and import operations. The disadvantage is that no backup file will be generated.

Step 5:

The basic DBA's job is over. At this time, it is necessary to inform the development staff that the recovery is complete and conduct application testing and data correctness verification. If everything is OK, we need to drop the table that we just renamed, and the recovery operation is complete.

drop table if exists tablename_bak;

Database recovery is an essential skill for every DBA and needs to be mastered. I hope that students who have read this article can perform recovery operations with ease.

ps: It would be best if you don’t use this operation once in ten thousand years.

The above is the detailed content of the steps for MySQL single table recovery. For more information about MySQL single table recovery, please pay attention to other related articles on 123WORDPRESS.COM!