MySQL master-slave replication principle plus combat

This article will introduce the role of master-slave replication, principles and actual combat and other content

One, the master-slave replication role

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/89/B8/wKioL1ga3NGB3wsRAAFkC72n7vA957.png "title=" Clipboard.png "alt=" Wkiol1ga3ngb3wsraafkc72n7va957.png "/>

MySQL master-slave replication facilitates database architecture robustness, improved access speed and ease of management

Robustness:

When there is a problem with the primary server, you can switch to service from the server immediately

1. master-slave servers are backed up by each other

But asynchronous synchronization, sometimes can not be the same data, when the main hanging, can only pull the main binlog from the library, so that the library does not lose data

2. Read and write separation share site pressure (read and write separate database structure)

The update data operation to the main server, the query to the slave server, if the site to browse the main business, then multiple slave server load balancing is very effective

Medium and large companies: through the program (Php,java)

Test environment: Agent Software (Mysql-proxy,amoeda)

Second, the principle of master-slave replication

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/89/B8/wKioL1ga3PKxhilgAAFsf1fuhRU812.png "title=" Clipboard.png "alt=" Wkiol1ga3pkxhilgaafsf1fuhru812.png "/>

1. Is asynchronous synchronous, logical synchronization, through the SQL statement execution, the main library through the record Binlog implementation of the synchronization from the library

2. From the library there are two threads IO and SQL, the main library has one thread IO

3. From the library IO to the main library IO to bin-log the data in the file and the next location point, placed in the Relay-log relay log, and then Master.info file will update itself according to Relaylog record Bin-log file next location

4. From the library SQL thread will write the data from the library data file according to the log in the Relay-log

Three, master and slave copy of one of the main more from

1, the main library opens Binlog, modifies the server ID, creates the connection account

Vim/etc/my.cnflog_bin=mysql-binserver_id=1grant REPLICATION SLAVE on * * to ' rep ' @ '% ' identified by ' 456 '; flush Privile Ges

2, fully prepared

Mysqldump-uroot-a--events--master-data=2 >/opt/backup.sql

3, close binlog from library, modify ID, set read-only, import full data

Vim/etc/my.cnf#log_bin=mysql-binserver_id=2read-onlymysql-uroot </opt/backup.sql

4, from the library configuration connection, according to/opt/backup.sql recorded in the moment point, my is mysql-bin.000006,417

Change MASTER tomaster_host= ' 11.2.0.75 ', master_port=3306,master_user= ' rep ', master_password= ' 456 ', master_log_file     = ' mysql-bin.000006 ', master_log_pos=417;  If--master-data=1, these two do not write start slave;  show slave status\g; See two yes to indicate success

Four, master and slave copy of the actual battle of the two main mutual preparation

1, we are already a master of a cluster of state, data synchronization complete, modify the main library configuration file

Auto_increment_increment=2auto_increment_offset=1log-slave-updates=truelog-bin=mysql-bin

2, configure from the library configuration file, remove the Read-only

Auto_increment_increment=2auto_increment_offset=2log-slave-updates=truelog-bin=mysql-bin

3. Restart the database

Server mysqld Restart

4, the original main library as from the library, do the same configuration, I use a multi-instance, so only changed the port

Change MASTER tomaster_host= ' 11.2.0.75 ', master_port=3307master_user= ' rep ', master_password= ' 456 ', master_log_file=     ' mysql-bin.000006 ', master_log_pos=417;  If--master-data=1, these two do not write start slave;  show slave status\g; See two yes to indicate success

This article is from the "Database learning process" blog, so be sure to keep this source http://yuemu.blog.51cto.com/10688235/1869345

MySQL master-slave replication principle plus combat