Principle and implementation process of MySQL master-slave replication (synchronization of mysql-5.5, half-step replication process)

I. Principles of MySQL master-slave Replication

1. mysql replication process: each time a write operation is executed, it will save a copy to its own database. At the same time, this write operation will also be stored in a binary log file, and save them as events. Therefore, each write operation or modification operation on the front-end data in this database will save an event, we will send the event to another server through port 3306 of the mysql server. The other server will receive the event and save it to a local log file, then read an event from this log file and execute it locally, and save it in the database. This process is called mysql replication. As shown in:

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image002 [6] "border =" 0 "alt =" clip_image002 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P0293294-0.jpg "width =" 468 "height =" 331 "/>

2. In such a model, the server that allows external operations to be recorded in the database and saved to the binary file is called the master server in the replication architecture ). The log files in the master server are binary logs.

3. slave: the log files on the server are copied from the master server, called Relay logs. The main purpose is to copy the logs and use them locally, it is equivalent to a relay process.

4. The master server allows parallel execution. If the master server has multiple CPUs, it allows parallel execution by multiple servers. However, we can write binary files only one by one, and the slave server can only execute one by one. By default, the slave server can only be one process and read and run one process. Therefore, the slave server is slower than the master server.

II. Implementation of MySQL master-slave Replication

1. master server configuration process

1) Add and modify system users

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image004 [6] "border =" 0 "alt =" clip_image004 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P0292264-1.jpg "width =" 633 "height =" 88 "/>

2) download the mysql package, decompress it, and create a link.

[Root @ node1 ~] # Lftp 172.16.0.1/pub/Sources/mysql-5.5/download mysql-5.5 package

Cd OK, cwd =/pub/Sources/mysql-5.5

Lftp 172.16.0.1:/pub/Sources/mysql-5.5> ls

...

Lftp 172.16.0.1:/pub/Sources/mysql-5.5> get mysql-5.5.28-linux2.6-i686.tar.gz

179907710 bytes transferred in 25 seconds (6.89 M/s)

Lftp 172.16.0.1:/pub/Sources/mysql-5.5> bye

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image006 [6] "border =" 0 "alt =" clip_image006 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P0295058-2.jpg "width =" 752 "height =" 136 "/>

3) initialize the database and copy files

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image008 [6] "border =" 0 "alt =" clip_image008 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P0295947-3.jpg "width =" 686 "height =" 281 "/>

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image010 [6] "border =" 0 "alt =" clip_image010 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P0294205-4.jpg "width =" 648 "height =" 57 "/>

4) modify the configuration file and copy it to the slave server

[Root @ node1 mysql] # vim/etc/my. cnf modify the configuration file as follows:

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image012 [6] "border =" 0 "alt =" clip_image012 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P029BC-5.jpg "width =" 435 "height =" 56 "/>

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image014 [6] "border =" 0 "alt =" clip_image014 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P0294102-6.jpg "width =" 626 "height =" 82 "/>

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image016 [6] "border =" 0 "alt =" clip_image016 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P0291015-7.jpg "width =" 526 "height =" 64 "/>

[Root @ node1 ~] # Scp/etc/my. cnf node2:/etc/copy the configuration file to the slave server

My. cnf 100% 4746

5) Start mysql and test whether mysql can be connected.

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image018 [6] "border =" 0 "alt =" clip_image018 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P029C36-8.jpg "width =" 604 "height =" 37 "/>

[Root @ node1 mysql] # vim/etc/profile. d/mysql. sh

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image020 [6] "border =" 0 "alt =" clip_image020 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P02910E-9.jpg "width =" 527 "height =" 55 "/>

[Root @ node1 mysql] #./etc/profile. d/mysql. sh run the script

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image022 [6] "border =" 0 "alt =" clip_image022 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P02953Z-10.jpg "width =" 669 "height =" 244 "/>

6) copy the mysql software package to the slave server and connect to mysql to create a user.

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image024 [6] "border =" 0 "alt =" clip_image024 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P0295K1-11.jpg "width =" 661 "height =" 91 "/>

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image026 [6] "border =" 0 "alt =" clip_image026 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P0292454-12.jpg "width =" 634 "height =" 134 "/>

2. Configure the slave server

1) create a user to modify permissions and decompress the software package to connect to the database.

[Root @ node2 ~] # Mkdir-pv/mydata/data

Mkdir: created directory '/mydata'

Mkdir: created directory '/mydata/data'

[Root @ node2 ~] # Useradd-r mysql

[Root @ node2 ~] # Chown-R mysql. mysql/mydata/data/

[Root @ node2 ~] # Tar mysql-5.5.28-linux2.6-i686.tar.gz-C/usr/local/

[Root @ node2 ~] # Cd/usr/local/

[Root @ node2 local] # ls

Bin etc games include lib libexec mysql-5.5.28-linux2.6-i686 sbin share src

[Root @ node2 local] # ln-sv mysql-5.5.28-linux2.6-i686 mysql

Create symbolic link 'mysql' to 'mysql-5.5.28-linux2.6-i686'

[Root @ node2 local] # cd mysql

[Root @ node2 mysql] # chown-R root. mysql changes the owner Group

[Root @ node2 mysql] # ll

[Root @ node2 mysql] # scripts/mysql_install_db -- user = mysql -- datadir =/mydata/data/initialize the database

2) copy the script content and modify the required configuration file

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image028 [6] "border =" 0 "alt =" clip_image028 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P0296242-13.jpg "width =" 678 "height =" 42 "/>

[Root @ node2 mysql] # vim/etc/my. cnf modify the configuration file as follows:

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image030 [6] "border =" 0 "alt =" clip_image030 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P0291W5-14.jpg "width =" 391 "height =" 38 "/>

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image032 [6] "border =" 0 "alt =" clip_image032 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P0291O4-15.jpg "width =" 641 "height =" 164 "/>

3) Start mysql

[Root @ node2 mysql] # service mysqld start

Starting MySQL... [OK]

[Root @ node2 mysql] # vim/etc/profile. d/mysql. sh:

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image034 [6] "border =" 0 "alt =" clip_image034 [6] "src =" http://www.bkjia.com/uploads/allimg/131229/1P0295092-16.jpg "width =" 509 "height =" 35 "/>

[Root @ node2 mysql] #.! $ Read the above script

./Etc/profile. d/mysql. sh

4) on the master node, that is, on HA1, check the node from which the replication starts.

[Root @ node1 ~] # Mysql

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image036 "border =" 0 "alt =" clip_image036 "src =" http://www.bkjia.com/uploads/allimg/131229/1P0293N3-17.jpg "width =" 640 "height =" 391 "/>

5) modify the attributes of the master server

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image038 "border =" 0 "alt =" clip_image038 "src =" http://www.bkjia.com/uploads/allimg/131229/1P0294A1-18.jpg "width =" 640 "height =" 270 "/>

6) Check the status before and after starting the slave server

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image040 "border =" 0 "alt =" clip_image040 "src =" http://www.bkjia.com/uploads/allimg/131229/1P02950R-19.jpg "width =" 617 "height =" 575 "/>

Mysql> start slave; start slave server

Query OK, 0 rows affected (0.00 sec)

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image042 "border =" 0 "alt =" clip_image042 "src =" http://www.bkjia.com/uploads/allimg/131229/1P02a005-20.jpg "width =" 531 "height =" 413 "/>

The master-slave replication architecture is still complete.

3. Create data on the master server and check whether the data appears on the slave server

1) connect to the database on the master server and create a database

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image044 "border =" 0 "alt =" clip_image044 "src =" http://www.bkjia.com/uploads/allimg/131229/1P0295316-21.jpg "width =" 329 "height =" 229 "/>

2) view the status of the slave server and the database

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image046 "border =" 0 "alt =" clip_image046 "src =" http://www.bkjia.com/uploads/allimg/131229/1P02a3a-22.jpg "width =" 536 "height =" 494 "/>

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image048 "border =" 0 "alt =" clip_image048 "src =" http://www.bkjia.com/uploads/allimg/131229/1P0294413-23.jpg "width =" 303 "height =" 190 "/>

4) change from server to read-only

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image050 "border =" 0 "alt =" clip_image050 "src =" http://www.bkjia.com/uploads/allimg/131229/1P0293110-24.jpg "width =" 522 "height =" 160 "/>

[Root @ node2 mysql] # vim/etc/my. cnf modify the configuration file to make read_only = ON permanently valid

650) this. width = 650; "style =" background-image: none; margin: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image052 "border =" 0 "alt =" clip_image052 "src =" http://www.bkjia.com/uploads/allimg/131229/1P02910Q-25.jpg "width =" 544 "height =" 90 "/>

5) restart the server and check whether read_only is enabled.

650) this. width = 650; "style =" background-image: none; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; "title =" clip_image054 "border =" 0 "alt =" clip_image054 "src =" http://www.bkjia.com/uploads/allimg/131229/1P02953Z-26.jpg "width =" 645 "height =" 374 "/>

(Read-only = YES does not take effect for users with SUPER permissions)

Mysql> show slave status \ G to check whether the replication thread can run automatically

* *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.66.6

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000001

Read_Master_Log_Pos: 430

Relay_Log_File: relay-log.000004

Relay_Log_Pos: 254

Relay_Master_Log_File: master-bin.000001

Slave_IO_Running: Yes after using change master

Slave_ SQL _Running: Yes, even if the Server replication thread is restarted

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 430

Relay_Log_Space: 404

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_ SQL _Errno: 0

Last_ SQL _Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)

6) view files under/mydata/data/

[Root @ node2 mysql] # Switch cd/mydata/data/to the/mydata/data Directory

[Root @ node2 data] # ls viewing files

Ibdata1 master-bin.000001 master-bin.index node2.magedu.com. pid relay-log.index

Ib_logfile0 master-bin.000002 master.info performance_schema relay-log.info

Ib_logfile1 master-bin.000003 mysql relay-log.000003 test

Magedudb master-bin.000004 node2.magedu.com. err relay-log.000004

[Root @ node2 data] # file master.info view master.info

Master.info: ASCII text files with pure ASCII code

[Root @ node2 data] # file relay-log.info

Relay-log.info: ASCII text files with plain ASCII code

[Root @ node2 data] # cat relay-log.info view the content in the relay-log.info

./The location of the relay-log File event currently used by the relay-log.000004

254

Location of binary file event on the master server that the master-bin.000001 is reading

430

Read-only = YES: read-only, on the slave server, but it does not take effect for users with SUPER permissions; therefore, the administrator can still write;

Sync-binlog = ON is set ON the master server for transaction security ;]

7. Is there such a problem? A transaction has been committed on the master server, and some transactions are still in the buffer zone. In case of a crash on the master server, can I get a replica from the server? How can we reduce the possibility of a different master? Configure the master server to synchronize binary logs.

Configure the master server to synchronize binary logs

Mysql> show global variables like '% log %'; view log-related content

8. After the slave server is started, it will automatically connect to the master server. If the master server crashes, the slave server will automatically connect to the master server to copy binary logs, generally, the slave server should not be automatically started from the replication thread. How can this problem be solved?

1) First, connect to mysql to view the content related to the slave server.

Mysql> show global variables like '% slave % ';

2) If you want to completely redefine the mysql slave server, you can manually stop the mysql server thread when it is executed. The specific process is as follows:

Mysql> stop slave IO_THREAD; stop the thread Service

Mysql> show slave status/G view status

Supplement: You can also stop the thread service one by one.

Mysql> start slave IO_THREAD;

Mysql> show slave status/G

Mysql> start slave SQL _THREAD;

Mysql> show slave status/G

Mysql> \ q

All server-related content will be stored in the error log of the database service.

[Root @ node2 mysql] # tail/mydata/data/node2.magedu.com. err

Iii. How to Implement semi-synchronous mysql Replication

1. Install a plug-in on the master server and slave server. This plug-in is provided by google.

[Root @ node2 data] # Switch cd/usr/local/mysql to the mysql path

[Root @ node2 mysql] # ls

[Root @ node2 mysql] # cd lib/plugin

(Semisync_master.so is the plug-in installed on the semi-synchronous master server, and semisync_slave.so is the plug-in installed on the semi-synchronous slave server)

1) configuration on the master server:

Mysql> install plugin rpl_semi_sync_master soname 'semisync _ master. so'; load Module

Rpl_semi_sync_master is the module (plug-in) name, And semisync_master.so is the module File Name.

Mysql> show global variables like '% rpl % ';

2) configuration on the slave server:

Mysql> install plugin rpl_semi_sync_slave soname 'semisync _ slave. so'; load Module

Mysql> show variables like 'rpl % '; view

2. Enable the rpl_semi_sync_master_enabled Module

1) on the master server:

Mysql> set global rpl_semi_sync_master_enabled = 1; Enable rpl_semi_sync_master_enabled

Mysql> show global varitables like '% rpl %'; rpl_semi_sync_master_enabled: ON

2) slave server:

Mysql> set global rpl_semi_sync_master_enabled = 1; Enable rpl_semi_sync_master_enabled

Mysql> show global varitables like '% rpl %'; rpl_semi_sync_master_enabled: ON

Mysql> show global status like 'rpl % '; view global status variables

Mysql> stop slave IO_THREAD; stop the IO thread

Mysql> start slave IO_THREAD; enable the IO thread

Mysql> show global status like 'rpl % ';

At this point, the semi-synchronous replication is complete.

This article is from the "show_only" blog, please be sure to keep this source http://10240214.blog.51cto.com/6634068/1216339