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

Xiaosheng blog: http://xsboke.blog.51cto.com

-Thank you for your reference. If you have any questions, you are welcome to communicate.

Environment description:

Mysql master-slave replication, master library A downtime, upgrade from library B to master database (assuming you open the binlog log of slave library at this time) now we need to create a new database C to replace the old master library A, then the data in library C must be based on B library, so we use the backup of B library to restore data from B as master database and C library as slave. You need to restore the data from the C library to the B library as the master library before you can use the C library as a new slave library (only then you can change master)

This paper introduces the current backup status based on the binlog log of library A:

Mysql-bin.00100 mysql-bin.00101 made a backup from the library during mysql-bin.00101 and kept the slave status information. The mysql-bin.00102 mysql-bin.00103 mysql-bin.00104 master library is the mysql-bin.00105 that went down during mysql-bin.00104.

So here's what I'm going to do:

1. Use the full recovery data from the library, 2. Then use the binlog of the main database to recover the later data, 3. Then use the relay log from the library to recover the missing data from the main library. In the process, I found a problem (about [3.] Explanation): when the main database is down, I found that there are several binlog logs of the main database that are not available, but there are from the relay log of the library, so I copied the sql that the master database does not have but the sql of the slave library from the relay log and put it in the file named addsql.sql

When I was in mysql-bin.00101, the slave status of the slave library when I backed up was like this:

Master_Log_File: mysql-bin.00101 Read_Master_Log_Pos: 888301479 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 31921757 Relay_Master_Log_File: mysql-bin.00100 # binlog log to which the SQL thread has executed from the library Slave_IO_Running: No Slave _ SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 31921467 # position Relay_Log_Space: 1962055058 of the binlog log that has been executed by the SQL thread from the library

Note: if you want to make the C library data the same as the B library, then you should use the parameters Relay_Master_Log_File and Exec_Master_Log_Pos to restore the data.

My procedure is as follows:

1. First import on the C library, backup from the library during mysql-bin.00101, 2. According to the slave status retained when doing full preparation from the library, export the data in mysql-bin.00100 that has not been synchronized to sql, (mysqlbinlog-- start-position=31921467 / mysql-bin.00100 >. / 100.sql). 3. Then convert the binlog log of mysql-bin.00104 after mysql-bin.00101 and when the main database is down to sql. 4. Import these sql. 5. Import addsql.sql. 6. B library authorizes replication slave, and then C library change master to B library.

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.