MySQL master-Slave synchronization configuration

Home Server IP:192.168.0.101

from server IP:192.168.0.105

1. Primary server my.ini configuration (directory on my computer is D:\soft\MySQL\MySQL server 5.6\data\my.ini)

[Mysqld]

Log-bin=mysql-bin//[ must ] enable binary logging

Server-id=101//[ must be ] server unique ID, default is 1, usually take IP last paragraph

1. My.ini configuration from the server (the directory on my computer is D:\soft\MySQL\MySQL server 5.6\data\my.ini)

[Mysqld]

Log-bin=mysql-bin//[ must ] enable binary logging

server-id=105//[ must be ] server unique ID, default is 1, usually take IP last paragraph

1. Restart the MySQL service on both servers

1. establish an account on the primary server and authorize slave:

log into MySQL as root first

Mysql-uroot-proot

create a MySQL account for synchronization and authorize only allow logins from the server to sync

GRANT REPLICATION SLAVE on * * to ' mysync ' @ ' 192.168.0.105 ' identified by ' q123456 ';

Log on to the master server for MySQLand query the status of master

Show master status;

Note: Do not re-operate the primary server after you have completed this step MYSQLto prevent changes in the state value of the primary server

1. 5. Configure the slave server Slave :

log into MySQL as root first

Mysql-uroot–proot

Configure the corresponding master server information (IP, username, password,master_log_file,master_log_pos , etc.)

Change Master to master_host= ' 192.168.0.101 ', master_user= ' Mysync ', master_password= ' q123456 ', master_log_file= ' Mysql-bin.000001 ', master_log_pos=332;

Be careful not to break the332 number without a single quotation mark around it.

Master_host: IP of the primary server

Master_user: user name for two-step user created on the primary server

Master_password: Password for two-step user created on the primary server

Master_log_file and master_log_pos: Front master server uses show Master status to see values

To start the Copy from Server feature

Mysql>start slave;

1. 6. Check the status of the replication feature from the server:

Show Slave Status\g

Note: Slave_io and Slave_sql the process must run correctly, i.e. YES state, otherwise it is the wrong state ( e.g. , one of the NO is an error )

The above operation process, the master and slave server configuration is complete.

1. 7. Master-Slave server testing:

Primary Server Mysql , set up a database, and insert a table in this library for a single piece of data:

mysql> CREATE DATABASE hi_db;
Query OK, 1 row Affected (0.00 sec)

mysql> use hi_db;
Database changed

mysql> CREATE TABLE HI_TB (ID int (3), name char);
Query OK, 0 rows Affected (0.00 sec)
 
mysql> INSERT INTO HI_TB values (001, ' Bobu ');
Query OK, 1 row Affected (0.00 sec)

Login from the server can see the corresponding database and table, etc. have been synchronized.

after using the above procedure for synchronization, although the database created later can be synchronized, the database before the configuration synchronization is not synchronized, for example, my host has a Jeecg_gjh The database is created before synchronization, the library is not automatically synchronized after the slave, if you want to synchronize manually do the following actions:

1. 1. Lock the write operation on the host first

mysql> FLUSH TABLES with READ LOCK;
Query OK, 0 rows Affected (0.00 sec)

mysql> SHOW MASTER STATUS;

record the latest position (required when operating from the machine), such as the value obtained here is 3308

1. 2. Open another window to export on the host JEECG This library

Mysqldump-u JEECG-PJEECG--opt-r jeecg_gjh > D:/temp/jeecg_gjh20150904.sql

3. Copy the exported SQL to the slave

4. Create the same user name, password, library, and import the data that you just exported from the host on the host

CREATE DATABASE ' Jeecg_gjh ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

Grant  All Privileges  on jeecg_gjh.* to [email protected] identified by ' JEECG ';

Use JEECG_GJH;

Source d:/temp/ Jeecg_gjh20150904.sql

1. 5. Restart slave

Sto P SLAVE;
Reset slave;

6. Re-Specify the host

Change Master to master_host= ' 192.168.0.101 ', master_user= ' Mysync ', master_password= ' q123456 ', master_log_file= ' Mysql-bin.000001 ', master_log_pos=3308;

Notice that the master_log_pos value Here is the value you just got on the host.

1. 7. Restart The slave machine again

Sto P SLAVE;
Reset slave;

1. 8. execute the following command on the slave machine

Show Slave Status\g

if Slave_io and Slave_sql the process must run correctly, i.e. YES the status indicates that the synchronization is OK.

MySQL master-Slave synchronization configuration