MySQL Master-Slave synchronization

MySQL master-slave synchronization is currently using a relatively broad database architecture, technology is relatively mature, configuration is not complex, especially for the load of large sites, master-slave synchronization can effectively alleviate the database read and write pressure.

MySQL master-slave synchronization mechanism

MySQL master-slave synchronization is implemented on the basis of MySQL master-slave replication (Master-slave Replication), by setting the Binlog on master MySQL (leaving it open), Slave MySQL through a i/ The o thread reads binlog from master MySQL and then transmits it to slave MySQL's trunk log, then slave mysql's SQL thread to read the relay log from the log and then apply it to the slave MySQL database. This realizes the master-slave data synchronization function.

the role of MySQL master-slave synchronization

1, as a backup mechanism, equivalent to hot backup
2, can be used to do read and write separation, balanced database load

MySQL master-slave synchronization steps

First, ready to operate
1, master-slave database version consistent, recommended version 5.5 or more
2, master-slave database data consistent

Second, the master database master modification

1. Modify the MySQL configuration:

[Plain]View Plaincopy

1. # log file name
2. Log-bin = Mysql-bin
4. # Primary database-side ID number
5. Server-id = 1

2. Restart MySQL to create an account for synchronization:

[Plain]View Plaincopy

1. # Create slave account slave_account, password 123456
2. Mysql>grant replication Slave on * * to ' slave_account ' @ ' percent ' identified by ' 123456 ';
4. # Update Database permissions
5. Mysql>flush privileges;

3. Query the status of master

[Plain]View Plaincopy

1. Mysql> Show master status;
2. +------------------+----------+--------------+------------------+
3. | File | Position | binlog_do_db | binlog_ignore_db |
4. +------------------+----------+--------------+------------------+
5. |      mysql-bin.000009 |              196 |                  | |
6. +------------------+----------+--------------+------------------+
7. 1 row in Set

Note: Do not operate the primary database until this step is complete, preventing changes in the primary database state value

Third, from the database slave modification

1. Modify the MySQL configuration:

[Plain]View Plaincopy

1. # from the database-side ID number
2. Server-id =2

2. Perform synchronous commands

[Plain]View Plaincopy

1. # Execute Sync command, set primary database IP, synchronize account password, sync location
2. Mysql>change Master to master_host= ' 192.168.1.2 ', master_user= ' slave_account ', master_password= ' 123456 ', master_ Log_file= ' mysql-bin.000009 ', master_log_pos=196;
4. # Enable sync function
5. Mysql>start slave;

3. Check the status from the database:

[Plain]View Plaincopy

2. Mysql> show Slave status\g;
4. 1. Row ***************************
6. Slave_io_state:waiting for Master to send event
8. master_host:192.168.1.2
10. Master_user:slave_account
12. master_port:3306
14. Connect_retry:60
16. master_log_file:mysql-bin.000009
18. read_master_log_pos:196
20. relay_log_file:vicky-relay-bin.000002
22. relay_log_pos:253
24. relay_master_log_file:mysql-bin.000009
26. Slave_io_running:yes
28. Slave_sql_running:yes
30. replicate_do_db:
32. replicate_ignore_db:
34. ...

Note: The slave_io_running and slave_sql_running processes must be running normally, that is, the Yes state, otherwise the synchronization fails.

Here, the master-slave database setup work has been completed, you can create a new database and tables, insert and modify data, test the success

Iv. other relevant parameters that may be used

1, Master side:

[Plain]View Plaincopy

2. # What databases are not synchronized
4. binlog-ignore-db = MySQL
6. BINLOG-IGNORE-DB = Test
8. Binlog-ignore-db = Information_schema
12. # only which databases are synchronized, other than those that are not synchronized
14. Binlog-do-db = Game
18. # Log retention Time
20. Expire_logs_days = 10
24. # controls the write frequency of the binlog. How many transactions per execution are written once
26. # This parameter consumes a lot of performance, but can reduce the loss caused by MySQL crash
28. Sync_binlog = 5
32. # log format, recommended mixed
34. # Statement Saving SQL statements
36. # row save affects record data
38. # Mixed in front of two combinations
40. Binlog_format = Mixed

2, Slave end:

[Plain]View Plaincopy

1. # Stop master-Slave synchronization
2. mysql> stop Slave;
4. # Reconnect time-out when the connection is disconnected
5. mysql> change Master to master_connect_retry=50;
7. # Turn on master-slave synchronization
8. mysql> start slave;

The above connection time-out settings, similar way can be used to set the primary database IP, synchronization account password, synchronization location

Expand reading:

MySQL log format Binlog_format

Transferred from: http://blog.csdn.net/mycwq/article/details/17136001

MySQL Master-Slave synchronization