Using Spring+mysql for read-write separation (iii) master-slave replication

the principle of MySQL master (Master) copy from (slave) :

1. Master records data changes to binary logs (binary log ) , which is the configuration file log-bin Specified files ( These are called binary log events, binary logs event)

2. Slave copies the binary log events of master to its trunk logs (relay log)

3, slave redo the event in the trunk log , will change the reflection of its own data ( data Replay )

Note: the time difference between master and slave replication cannot be resolved

1.1. the principal and subordinate configuration needs to pay attention to the place

1. The primary DB Server is consistent with the version from the DB server database

2. The primary DB Server is consistent with the data from the DB server database [ The primary backup can be restored from the top, You can also copy the master Data directory directly to the corresponding data directory from

3. primary DB Server open binary log , primary DB server and db Server of the server_id must all be unique

1.2.

1 exporting data from a 3306 database

2 creating databases in 3380 and 3381 , respectively

3 importing SQL files separately

Master DB Server 3380 Open binary log

Primary Write log never writes log

comment out the 3381

server_id all must be unique

2.1. Main Library configuration (Windows, Linux also similar to the )

Modify in My.ini :

# Enable master-slave replication, main library configuration

Log-bin= "E:/0315/mysql/3380/logs/mysql-bin"

# Specify the primary library server ID

server-id= the

# Specifies the database that is synchronized, if synchronize all databases without specifying

binlog-do-db= Database name

Execution SQL statement Query status:
SHOW MASTER STATUS

after authorization, the location changes to 420

need to record the Position value, you need to set the synchronization start value from the library.

3.1. Create a sync user in the main library

# Authorized user slave01 log in to MySQL using 123456 Password

Grant Replication Slave on * * to ' slave01 ' @ ' 127.0.0.1 ' identified by ' 123456 ';

Flush privileges;

4.1. Configure from Library

Modify in My.ini :

# Specify the serverID, as long as you do not repeat it, and only this one configuration from the library , the others operate in SQL statements

server-id=81

following execution SQL:

Change MASTER to

Master_host= ' 127.0.0.1 ',

Master_user= ' Slave01 ',

Master_password= ' 123456 ',

master_port=3306,

Master_log_file= ' mysql3306-bin.000006 ',

master_log_pos=1120;

# Start slave synchronization

START SLAVE;

# View sync status

SHOW SLAVE STATUS;

Io is no need to look at the log

from the library Copy   Main Library First Look at the log of their 3381 err.log

For example , modify the database data modified Bay will synchronize the data with the change

Using Spring+mysql for read-write separation (iii) master-slave replication