Mysql master-slave synchronization and queue Design

1. mysql master-slave Synchronization
Refer to the search article:
Http://7056824.blog.51cto.com/69854/400642
Http://lymrg.blog.51cto.com/1551327/696132
Http://tech.it168.com/a2009/0526/577/000000577322.shtml
Http://www.aslibra.com/blog/read.php? 1038
Http://www.cnblogs.com/yuanermen/archive/2010/08/11/1797206.html
Http://www.linux521.com/2009/database/200901/170.html
Http://www.xuephp.com/main/detail.php? Type = database & cid = 16894

Master database:
1> my. cnf
Log-bin = Name of the master-bin binary file. Enable binary log.
Log-bin-index = index directory of the binary file that the master-bin.index is using
Binlog-format = maxed
Server-id = 1

Binlog-do-db = Name of the database to be backed up by yourdb. If multiple databases are backed up, set this option repeatedly (which is said to be dangerous)
Binlog-ignore-db = mysql does not need to back up the database. If you back up multiple databases, set this option again.

2> server mysqld restart
3> check whether the modification is valid
Mysql> show master status; should not be empty
Mysql> show variables like 'Log _ bin'; NO
Mysql> reset master; (clear log files)
Mysql> show master logs;
4> Add a synchronization user
Mysql> grant replication client, replication slave on *. * TO repl @ '2017. 192.% 'identified by 'passwd ';
Mysql> flush privileges;
5> lock
Mysql> flush tables with read lock;
6> synchronize existing databases
Tar-zcvf/tmp/mysql-yourdb.tar.gz./yourdb
Scp mysql-yourdb.tar.gz root@192.168.1.103:/tmp/mysql-yourdb.tar.gz
7> unlock
Mysql> show master status;
Mysql-bin.000002 | 106 |
Record this value. These values will be used for setting slave servers later.
They represent the replication coordinates. The slave server should start from this point and perform new updates from the master server.
Mysql> unlock tables;

Slave database:
1> copy data from the original database
Service mysqld stop
Tar-zxvf mysql-yourdb.tar.gz pay attention to directory permissions after undo.
2> my. cnf
Server-id = 2
3> test master database connection
Mysql-urepl-h 192.168.1.105-P 3306-ppasswd
Check the network, port. (iptables)
3> Start slave Database
Mysql> change master
Master_host = '1970. 168.1.105 ',
Master_user = 'repl ',
Master_password = 'passwd ',
Master_port = 3306,
Master_log_file = 'mysql-bin.000002 ',
Master_log_pos = 106,
Master_connect_retry = 30;
4> Start the replication process
Mysql> start slave;
5> check status
Mysql> show slave status \ G;
As shown above: Slave_IO_Running: Yes and Slave_ SQL _Running: Yes indicates that the replication is normal.

2. Synchronization Process
Http://www.diybl.com/course/6_system/linux/Linuxjs/2008108/149090.html
Client -- D-> master database SQL -- A-> master database binlog -- B-> slave database relay-log -- C-> slave Database SQL.
If binlog is enabled for the slave database, you can continue cascade synchronization.
The synchronization process of mysql is asynchronous. The slave database can be enabled at any time to continue the previous synchronization. The log queue can ensure that a statement is not missed.
When the slave database is online, This asynchronous process is quite fast. Because the slave database is notified to retrieve logs when the master database is updated, instead of refreshing the master database regularly.
A should be atomic, and B and C operations make the data eventually consistent.
When a fault occurs in B and C, the synchronization process stops and waits for manual intervention.
D and C run the same statement. To achieve distributed execution, mysql writes log files on both the master and slave machines.
Simplified, independent of B's operations, B only needs to complete the binlog to relay-log process.

3. Queue Design
1> one-way Log File Growth
Log-bin-index
Mysql-bin.000002 a log file
Pos coordinates
The slave database uses the master_log_file = 'mysql-bin.000002 'and master_log_pos = 106 parameters to locate the position in the queue where data is read.
The same is true for relay-log.
3> log cleanup
Http://www.jzxue.com/shujuku/mysql/201102/22-6298.html