MySQL Database Migration document

Database Migration Documentation

First, the demand

Ensure that the database is running stably, providing developers with a convenient test database and production database environment.

Second, the overall structure of the database (Master/slave)

Third, database migration backup restore operation

1. Test Database Export Operations

mysqldump-uroot-p123456 miss8 > Miss8.sql

1. master node, slave node Import database

Mysql-uroot-piminerroot Miss8 < Miss8.sql

Iv. Database Migration Specific configuration

1. Make sure that the master node and the slave node have the same data
2. Configuring the Master Node

Establish a backup account in the master database: Each slave uses the standard MySQL username and password to connect to master. The user who makes the copy operation grants replication slave permissions. The password for the user name is stored in the text file Master.info

The command is as follows:

MySQL > GRANT REPLICATION slave,reload,super on * * to [e-mail protected] ' 192.168.32.47 ' identified by ' 123456 ';

GRANT REPLICATION slave,reload,super On * * to [e-mail protected] ' 192.168.32.95 ' identified by ' 123456 ';

Create an account backup, and only allow to log in from the 47,95 address, the password is 123456.

Modify master configuration file (/etc/my/cnf)

Server-id=1
Log-bin=mysql-bin

BINLOG-DO-DB=GQUAN_MISS8 (synchronous GQUAN_MISS8 only)

Servicemysqld Restart (restart MySQL service)

Show Masterstatus; (View master node status)

1. Configuring the Slave node (/ETC/MY/CNF)

#服务器id

Server-id= 2

# #同步数据库name

Replicate-do-db=miss8_master

# #不需要同步数据库name

Replicate-ignore-db=mysql

Relay-log-index=slave-relay-bin.index

# #中继日志

Relay-log=slave-relay-bin

# #将复制事件写进日志

Log_slave_updates= 1

Read_only=1

1. Start the slave node

The next step is to have slave connect to master and begin to redo the events in the master binary log. Instead of doing this with a configuration file, you should use the change MASTER to statement, which completely supersedes the modification of the configuration file, and it can specify a different MASTER for slave, without having to stop the server. As follows

Mysql>change Master tomaster_host= ' 192.168.32.96 ', master_user= ' backup ', master_password= ' 123456 ', Master_log_ File= ' mysql-bin.000002 ', master_log_pos=4;

mysql> slave start; Open slave Service

You can use the show Slavestatus statement to see if the slave settings are correct:

mysql> SHOW SLAVE Status\g

Slave_io_running, and slave_sql_running Yes indicates that the Slave node is configured properly

Five, database use precautions

1. Common commands

View Master Status

SHOW Masterstatus\g

View slave status

SHOW SLAVE Status\g

View the status of the master and slave threads. On the master

Show Processlist\g; The results are as follows

Finally, I remind you that the slave node does not allow write operations and cannot operate directly on the slave.

MySQL Database Migration document