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