MySQL master-slave replication configuration details

This experiment demonstrates the asynchronous replication of mysql. What is asynchronous replication? That is, when a statement is started, the master server does not wait until the slave server is executed.

This experiment demonstrates the asynchronous replication of mysql. What is asynchronous replication? That is, when a statement is started, the master server does not wait until the slave server is executed.

I. Basic principles of mysql master-slave replication:
This experiment demonstrates the asynchronous replication of mysql. What is asynchronous replication? That is, when a statement starts, the master server will not wait for execution from the slave server, instead, the master server starts the next statement after execution, which is also out of consideration of the master server and hairstyle. The slave client will enable two threads: Slave I/O thread and Slave SQL thread, i/O thread is responsible for reading events from the binary log of the master and storing the time information in relay-log. Slave SQL thread is responsible for reading events from relay-log, and replays the event and updates the slave data so that it is consistent with the data in the master.
Therefore, the binary log must be enabled on the master server, and the relay log must be enabled on the slave server.

Ii. Replication types supported by mysql:
1. Statement-based replication: SQL statements executed on the master server are executed on the slave server. MySQL uses statement-based replication by default, which is more efficient. The row-based replication option is automatically selected once the exact replication fails.
2. Row-based replication: copy the changed content instead of executing the command on the slave server.
3. Hybrid replication: Statement-based replication is used by default. Once a statement-based replication fails, row-based replication is used.

Step 3:

Configuration of the master mysql Server:
1. Enable binary log
2. Create a user with the copy permission
3. Unique Server-id
Configuration of the slave mysql Server:
1. Enable relay logs
2. Unique Server-id
3. Set to read-only mode

Assuming that the data on the master server is large and the synchronization process is slow, it is reasonable to use mysqldump to export the data on the master server, import the data to the slave server, and then start synchronization.

4. Preparation:
Create a test table on the master server
Mysql> create database data;
Query OK, 1 row affected (0.03 sec)
Mysql> select * from data.info;
+ ----- + ------- + ----- +
| Sid | name | age |
+ ----- + ------- + ----- +
| 1 | zhang | 23 |
| 2 | li | 42 |
| 3 | wang | 34 |
| 4 | qian | 25 |
| 5 | cheng | 29 |
+ ----- + ------- + ----- +
5 rows in set (0.00 sec)

5. Start Configuration

Master Server:
1. Enable binary logs and specify the server-id

[Root @ Oracle ~] # Vim/etc/my. cnf
[Client]
User = root
Password = 12345
[Mysqld]
Log-bin = mysql-bin
Server-id = 1
# Add these lines. The binary log file is not enabled by default in mysql5.6.

[Root @ oracle ~] # Service mysqld restart
Shutting down MySQL... [OK]
Starting MySQL. [OK]

2. Create a user with the copy permission
Mysql> grant replication slave on *. * to slave@192.168.2.96 identified by '2013 ';
Query OK, 0 rows affected (0.04 sec)

Slave Server:
1. Enable relay logs and specify the server-id and read-only
[Root @ node2 ~] # Vim/etc/my. cnf
[Client]
User = root
Password = 12345
[Mysqld]
Relay-log = mysql-relay-bin
Server-id = 2
Read-only = on
[Root @ node2 ~] # Service mysqld restart
Shutting down MySQL... [OK]
Starting MySQL. [OK]

Synchronization:
1. Perform logical backup on the master server
[Root @ oracle ~] # Mysqldump -- master-data = 2 -- databases data -- lock-all-tables>/root/bak. SQL
[Root @ oracle ~] # Scp/root/bak. SQL root@192.168.2.96:/root/

2. Execute the script on the slave server.
[Root @ node2 ~] # Mysql </root/bak. SQL
[Root @ node2 ~] # Mysql-e 'select * from data.info ;'
+ ----- + ------- + ----- +
| Sid | name | age |
+ ----- + ------- + ----- +
| 1 | zhang | 23 |
| 2 | li | 42 |
| 3 | wang | 34 |
| 4 | qian | 25 |
| 5 | cheng | 29 |
+ ----- + ------- + ----- +
# Data databases on the master server are imported to the slave server.

3. Turn slave servers into slave
[Root @ node2 ~] # Vim/root/bak. SQL
-- Change master to MASTER_LOG_FILE = 'mysql-bin.000004 ', MASTER_LOG_POS = 120;
Mysql> change master to master_host = '192. 168.2.93 ', master_user = 'slave', master_password = '000000', MASTER_LOG_FILE = 'mysql-bin.000004 ', MASTER_LOG_POS = 192;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
Mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

View logs:
20:45:47 17372 [Note] 'change master to executed '. previous state master_host = 'slave ', master_port = 3306, master_log_file = 'mysql-bin.000004', master_log_pos = 120, master_bind = ''. new state master_host = '2017. 168.2.93 ', master_port = 3306, master_log_file = 'mysql-bin.000004', master_log_pos = 120, master_bind = ''.
20:45:52 17372 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. please consider using the USER and PASSWORD connection options for start slave; see the 'start SLAVE Syntax 'in the MySQL Manual for more information.
20:45:52 17372 [Note] Slave I/O thread: connected to master 'slave @ 192.168.2.93: 3306 ', replication started in log 'mysql-bin.000004' at position 120
20:45:52 17372 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
20:45:52 17372 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000004 'at position 120, relay log'./mysql-relay-bin.000001' position: 4
# Slave I/O thread and Slave SQL thread have been started