Brief description and example of MySQL Replication

Brief description and example of MySQL Replication

Master-slave replication is widely used in MySQL. It is mainly used to synchronize data on one server to multiple slave servers. It can be used for load balancing, high availability and failover, and backup. MySQL supports a variety of Replication technologies, such as unidirectional and semi-synchronous asynchronous replication and different levels of replication, such as database, table, and cross-database synchronization. This article briefly describes a basic master-slave replication and provides an example.

1. Basic principles of replication (STEP)

A. binary log recorded for data change on the master database)

B. the I/O thread on the slave Database connects to the master database and requests to send its binary log file (the binlog dump thread on the master database sends the binary log content to the slave database)

C. Read the binary content sent by the master service from the I/O thread on the database and copy it to the relay log.

D. Read the relay log from the SQL thread on the database and execute the updates contained in the log

2. Add a copy for the configuration file

# The demonstration in this article is based on the multi-instance Environment on the same server. Port 3406 is used as the master database, and port 3506 is used as the slave database.
# For details about multi-instance deployment, refer:
# MySQL multi-instance Configuration
# Both 3406 and 3506 are newly installed and contain the default database. Therefore, the steps for migrating data from the master database to the slave database are not involved in this demonstration.
A. configuration files on the master database
# More my3406.cnf
[Mysqld]
Socket =/tmp/mysql3406.sock
Port = 3406
Pid-file =/data/inst3406/data3406/my3406.pid
User = mysql
Log-error =/data/inst3406/data3406/inst3406.err
Datadir =/data/inst3406/data3406
Basedir =/app/soft/mysql5

#### For master items ####
Servers-id = 3406
Log_bin =/data/inst3406/log/bin/inst3406bin
Innodb_flush_log_at_trx_commit = 1
Sync_binlog = 1

B. Slave Database Configuration File
# More my3506.cnf
[Mysqld]
Socket =/tmp/mysql3506.sock # Author: Leshami
Port = 3506 # Blog: <A href = "www.bkjia.com" target = _ blank> http://blog.csdn.net/leshami
Pid-file </A> =/data/inst3506/data3506/my3506.pid
User = mysql
Log-error =/data/inst3506/data3506/inst3506.err
Datadir =/data/inst3506/data3506
Basedir =/app/soft/mysql5

#### For slave items ####
Servers-id = 3506
Relay_log =/data/inst3506/log/relay-bin
Read_only = 1

3. Create a copy account

# Start an instance with port 3406 and add an account
[Mysql @ app ~] $ Mysqld_safe -- defaults-file =/data/inst3406/data3406/my3406.cnf &
[Mysql @ app ~] $ Mysql-P3406 # log on to 3406

Master @ localhost [(none)]> show variables like 'server _ id ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server ID | 3406 |
+ --------------- + ------- +

# Create an account for copying
Master @ localhost [(none)]> grant replication slave, replication client on *.*
-> To repl @ '192. 168.1.177 'identified by 'repl ';

# Initialize the log file of the master database and use the reset with caution when generating the environment
Master @ localhost [(none)]> reset master;
Query OK, 0 rows affected (0.01 sec)

# Check the status of the master database. The log is initialized to 000001,
Master @ localhost [(none)]> show master status, Position: 120
+ -------------------- + ---------- + -------------- + -------------------- + ------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ -------------------- + ---------- + -------------- + -------------------- + ------------------- +
| Inst3406bin. 000001 | 120 |
+ -------------------- + ---------- + -------------- + -------------------- + ------------------- +

4. Configure master-slave Synchronization

# Start an instance whose port is 3506
[Mysql @ app ~] $ Mysqld_safe -- defaults-file =/data/inst3506/data3506/my3506.cnf &

[Mysql @ app ~] $ Msyql-P3506

Slave @ localhost [(none)]> show variables like 'server _ id ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server ID | 3506 |
+ --------------- + ------- +
1 row in set (0.00 sec)

# Add configuration information pointing to the master database for the slave database, this command will generate and modify the master.info and relay-log.info files on the slave Database
Slave @ localhost [(none)]> change master to MASTER_HOST = '2017. 168.1.177 ',
-> MASTER_USER = 'repl ',
-> MASTER_PASSWORD = 'repl ',
-> MASTER_PORT = 3406,
-> MASTER_LOG_FILE = 'inst3406bin. 100 ',
-> MASTER_LOG_POS = 0;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

# Two warnings are displayed. Check them.
Slave @ localhost [(none)]> show warnings \ G
* *************************** 1. row ***************************
Level: Note
Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
* *************************** 2. row ***************************
Level: Note
Code: 1760
Message: Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended.
Please see the MySQL Manual for more about this issue and possible alternatives.
2 rows in set (0.00 sec)

# View the status of the slave Database
Slave @ localhost [(none)]> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.177
Master_User: repl
Master_Port: 3406
Connect_Retry: 60
Master_Log_File: inst3406bin. 000001.
Read_Master_Log_Pos: 4
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: inst3406bin. 000001
Slave_IO_Running: No # The IO thread is not running.
Slave_ SQL _Running: No # the SQL thread is not running.
......................
Master_Info_File:/data/inst3506/data3506/master.info

Slave @ localhost [(none)]> start slave; # start slave
Query OK, 0 rows affected (0.01 sec)

# Meaning:
Start slave with no thread_type options starts both of the slave threads. The I/O thread reads
Events from the master server and stores them in the relay log. The SQL thread reads events from
Relay log and executes them.

# View the slave status again
Robin @ localhost [(none)]> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.177
Master_User: repl
Master_Port: 3406
Connect_Retry: 60
Master_Log_File: inst3406bin. 000001.
Read_Master_Log_Pos: 120
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 285
Relay_Master_Log_File: inst3406bin. 000001
Slave_IO_Running: Yes # The IO thread is running.
Slave_ SQL _Running: Yes # the SQL thread is running.
..............
Exec_Master_Log_Pos: 120
Relay_Log_Space: 452
............
Master_Server_Id: 3406
Master_UUID: 32f53a0a-63ef-11e4-93d9-8c89a5d424ae
Master_Info_File:/data/inst3506/data3506/master.info
SQL _Delay: 0
SQL _Remaining_Delay: NULL
Slave_ SQL _Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it # Important prompt information

# We can see two threads in the slave database. One is for the I/O thread, used to connect to the master database and request the master database to send the binlog, and the other is the SQL thread used to execute the SQL.
Slave @ localhost [(none)]> show processlist \ G
* *************************** 1. row ***************************
Id: 4
User: system user
Host:
Db: NULL
Command: Connect
Time: 510993
State: Waiting for master to send event
Info: NULL
* *************************** 2. row ***************************
Id: 5
User: system user
Host:
Db: NULL
Command: Connect
Time: 333943
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL

5. Verify Synchronization

# Perform the following operations on the master database to check the synchronization status of the slave database:
Master @ localhost [(none)]> show variables like 'server _ id ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server ID | 3406 |
+ --------------- + ------- +
1 row in set (0.00 sec)

# The Binlog Dump thread on the master database is used to send binlog log files to the slave database.
Master @ localhost [(none)]> show processlist \ G
* *************************** 1. row ***************************
Id: 12
User: repl
Host: 192.168.1.177: 57440
Db: NULL
Command: Binlog Dump
Time: 511342
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL

# Creating databases and tables in the master database
Master @ localhost [(none)]> create database tempdb;
Query OK, 1 row affected (0.01 sec)

Master @ localhost [(none)]> use tempdb
Database changed
Master @ localhost [tempdb]> create table tb_engines as select * from information_schema.engines;
Query OK, 9 rows affected (0.02 sec)
Records: 9 Duplicates: 0 Warnings: 0

# The following are the results of the slave database check.
Slave @ localhost [(none)]> select count (*) from tempdb. tb_engines;
+ ---------- +
| Count (*) |
+ ---------- +
| 9 |
+ ---------- +

This article permanently updates the link address: