MySQL restore to a new MySQL master from the database

Environment: There are two sets of MySQL master and slave (A and B), now requires a set of MySQL from the library to restore to the B set of MySQL Master library and from the library. The execution process is as follows:

1. First, a set of MySQL from the library back to the B set of servers

Operation from Library

Stop the master-slave relationship

Slave stop;

Then restore the master and slave databases of B sets respectively.

Mysql-uroot-proot

Use dbname

Source/data/tmp/dbname_20141010.sql

User slave password slave (if you forgot to create a new user http://xficc.blog.51cto.com/1189288/1562168) from the library link to the main library

The main library queries the values of FILE and Position

Show master status;

Configure from Library

Mysql> Change Master to

master_host= ' main Library IP ',

Master_user= ' slave ',

master_password= ' slave ',

Master_log_file= ' mysql-bin.000007 ', #主库 check out the show master status;

master_log_pos=7; #主库 check out the show master status;

then execute slave start;

##################################################################

View the main library status

Show Processlist\g;

State status should be have sent all binlog to slave; Waiting for Binlog to be updated

View from library status

show slave status\g;

Slave_io_running and slave_sql_running states must be Yes

Show Processlist;

There should be two rows with the state value:

Have read all relay log; Waiting for the slave I/O thread to update it

Waiting for master to send event

This article is from the "XFICC" blog, make sure to keep this source http://xficc.blog.51cto.com/1189288/1562173

MySQL restore to a new MySQL master from the database