Mysql5.5.27 master-slave replication and mysql5.5.27 master-slave Replication
Master-slave replication principle:
1. Copy the architecture Diagram
2 initialization Diagram
3 replication Principle
Mysql uses three threads to execute the replication function (one of them is on the master server and the other two are on the slave server ). When start slave is issued, an I/O thread is created from the server to connect to the master server and send statements recorded in its binary log. The master server creates a thread to send the binary log content to the slave server. This thread can be the Binlog Dump thread in the show processlist output on the master server. Read the content sent by the Binlog Dump thread of the master server from the server I/O thread and copy the data to a local file in the data directory of the slave server, that is, relay logs. The first thread is an SQL thread created by the slave server. It is used to read relay logs and execute updates contained in logs. On the slave server, the read and execute update statements are divided into two independent tasks. When the slave server is started, its I/O thread can quickly obtain all binary logs from the master server.
1. The master and slave servers perform the following operations respectively:
1.1. The version is consistent. Mysql-5.5.27)
1.2 initialize the table and start mysql in the background
1.3 modify the root password
1.4 master: 192.168.142.131
1.4 slave server slave: 192.168.142.133
2. Modify the master server:
# Vi/etc/my. cnf
[Mysqld]
#
Log-bin = mysql-bin # [required] Enable binary log
Server-id = 131 # [required] unique server ID. The default value is 1. Generally, the last segment of the IP address is used.
# Binlog-do-db = test # You need to back up data and write multiple rows
# Binlog-ignore-db = mysql # databases that do not need to be backed up, multiple write lines
3. Modify the slave server slave:
# Vi/etc/my. cnf
[Mysqld]
Log-bin = mysql-bin # [not required] Enable binary log
Server-id = 133 # [required] unique server ID. The default value is 1. Generally, the last segment of the IP address is used.
# Master-connect-retry = 60 # Time Difference (in seconds) when the master server is disconnected from the server)
# Replicate-do-db = test # copy only one database
# Replicate-ignore-db = mysql # do not copy a database
4. Restart the mysql of the two servers.
Service mysqld restart
5. Create an account on the master server and authorize slave:
# Mysql-uroot-p123456
Mysql> grant replication slave on *. * to 'clevercode' @ '%' identified by 'q123456'; // generally, the root account is not required. & ldquo; % & rdquo; indicates that all clients may be connected, the password is correct and can be replaced by a specific client IP address, such as 192.168.145.226, to enhance security.
6. log on to mysql on the master server and query the master status.
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000011 | 248 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
Note: Do not operate MYSQL on the master server after this step is completed to prevent the status of the master server from changing.
7. Configure Slave server Slave:
Mysql> stop slave;
Mysql> change master to master_host = '192. 168.142.131 ', master_user = 'clevercode', master_password = 'q123456 ',
Master_log_file = 'mysql-bin.20.11 ', master_log_pos = 248; // do not disconnect. There is no single quotation mark before and after the number 248.
Mysql> start slave; // start the slave Server replication function. If the slave is in the startup status, stop slave first;
8. Check the slave server copy function status:
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.142.131 // master server address
Master_User: clevercode // authorize account name, avoid using root
Master_Port: 3306 // database port, which is unavailable in some versions
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 107 // # synchronous location for reading binary logs, greater than or equal to Exec_Master_Log_Pos
Relay_Log_File: centos64idx4-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000012
Slave_IO_Running: Yes // This status must be YES
Slave_ SQL _Running: Yes // This status must be YES
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: 107
Relay_Log_Space: 416
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/Slave latency.
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: 131
1 row in set (0.00 sec)
Note: The Slave_IO and Slave_ SQL processes must run properly, that is, the YES state. Otherwise, all processes are in the wrong state (for example, any of them is in the wrong state ).
9. Master/Slave server test:
1. Create a database on the master server Mysql and insert a data entry into the table in the database:
Mysql> create database db_clevercode;
Query OK, 1 row affected (1.77 sec)
Mysql> use db_clevercode;
Database changed
Mysql> create table tb_test (id int (3), name char (10 ));
Query OK, 0 rows affected (14.17 sec)
Mysql> insert into tb_test values (1, 'clevercode ');
Query OK, 1 row affected (1.60 sec)
2. query from the Mysql server:
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Db_clevercode |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
5 rows in set (0.00 sec)
Mysql> use db_clevercode;
Database changed
Mysql> select * from tb_test;
+ ------ + ------------ +
| Id | name |
+ ------ + ------------ +
| 1 | clevercode |
+ ------ + ------------ +
1 row in set (0.01 sec)
10. Master-slave synchronization latency:
Monitor the Seconds_Behind_Master parameter output by the show slave status \ G command to determine whether there is a master-slave latency. The values are as follows:
NULL-indicates that either io_thread or SQL _thread has a fault, that is, the Running status of the thread is No, rather than Yes.
0-the value is zero, which is a situation we are eager to see. It indicates that the master-slave replication is good and lag does not exist.
Positive Value: The Master/Slave database has a latency. A larger number indicates that the slave database lags behind the master database.
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.
