MySQL replication brief description and example _ MySQL

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, semi-synchronous, asynchronous, and complex master-slave replication technologies, which are widely used in MySQL to synchronize data on one server to multiple slave servers, it can be used to achieve load balancing, high availability, 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 to: # MySQL multi-instance configuration (1) http://blog.csdn.net/leshami/article/details/40339167# MySQL multi-instance configuration (2) http://blog.csdn.net/leshami/article/details/40339295# Both 3406 and 3506 are newly installed and contain default libraries, so this demonstration does not involve first migrating data from the master database to the slave database step a and the configuration file on the master database # more my3406.cnf [mysqld] socket =/tmp/mysql3406.sockport = 3406pid-file =/data/inst3406/ data3406/my3406.piduser = mysqllog-error =/data/inst3406/data3406/plugin =/data/inst3406/data3406basedir =/app/soft/mysql5 ### for master items ### # server-id = 3406log_bin =/data/inst3406/log/bin/logs = 1sync_binlog = 1b. slave database configuration file # more my3506.cnf [mysqld] socket =/tmp/mysql3506.sock # Author: leshamiport = 3506 # Blog: http://blog.csdn.net/leshamipid-file =/Data/inst3506/data3506/my3506.piduser = mysqllog-error =/data/inst3506/data3506/messages =/data/inst3506/data3506basedir =/app/soft/mysql5 #### for slave items #### server-id = 3506relay_log =/data/inst3506/log/relay/relay-binread_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 the 3406master @ localhost [(none)]> show variables like 'server _ id '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | server_id | 3406 | + --------------- + ------- + # create the master account for replication @ localhost [(none)]> grant replication slave, replication client on *. *-> to repl @ '2017. 168.1.177 'identified by 'repl'; # Initialize the log file of the master database. use resetmaster @ localhost [(none)]> reset master; Query OK, 0 rows affected (0.01 sec) with caution when generating the environment) # check the status of the master database. the log is initialized to 000001, master @ localhost [(none)]> show master status, position: 120 + bytes + ---------- + ------------ + bytes + | File | Position | Binlog_Do_DB | bytes | + bytes + ---------- + -------------- + ------------------ + bytes + | inst3406bin. 000001 | 120 | + -------------------- + ---------- + -------------- + ------------------ + --------------------- +

4. configure master-slave synchronization

# Instance with the startup Port 3506 [mysql @ app ~] $ Mysqld_safe -- defaults-file =/data/inst3506/data3506/my3506.cnf & [mysql @ app ~] $ Msyql-P3506slave @ localhost [(none)]> show variables like 'server _ id '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | server_id | 3506 | + --------------- + ------- + 1 row in set (0.00 sec) # for the slave database to add relevant configuration information pointing to the master database, this command will generate and modify the master database on the slave database and the relay-log.info file slave @ localhost [(none)]> change master to MASTER_HOST = '2017. 168.1.177 ',-> MASTER_USER = 'repl',-> MASTER_PASSWORD = 'repl',-> MAST ER_PORT = 3406,-> MASTER_LOG_FILE = 'inst3406bin. 000001 ',-> MASTER_LOG_POS = 0; Query OK, 0 rows affected, 2 warnings (0.04 sec) # Two warnings appear. check 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) # Check the slave database status information 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 does not run Slave_ SQL _Running: No # The SQL thread does not run ...................... master_Info_File:/data/inst3506/data3506/master. infoslave @ localhost [(none )]> Start slave; # START slaveQuery 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 readsevents from the master server and stores them in the relay log. the SQL thread reads events from therelay 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: the 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: 1 20 Relay_Log_Space: 452 ............ master_Server_Id: 3406 Master_UUID: export 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 Tips # you can see two threads in the slave database, one for the I/O thread, it is used to connect to the master database and request the master database to send binlog. one is the SQL thread used to execute SQL. Slave @ localhost [(none)]> show processlist \ G ***************************** 1. row ************************** Id: 4 User: system user Host: db: NULLCommand: Connect Time: 510993 State: Waiting for master to send event Info: NULL **************************** 2. row ************************** Id: 5 User: system user Host: db: NULLCommand: 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

# The following operations are performed on the master database to check synchronization from 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. for details, refer to master @ localhost [(none)]> show processlist \ G ***************************** 1. row ************************** Id: 12 User: repl Host: 192.168.1.177: 57440 db: NULLCommand: Binlog Dump Time: 511342 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL # create database and table master @ localhost [(none)]> create database tempdb; Query OK, 1 row affected (0.01 sec) master @ localhost [(none)]> use tempdbDatabase changedmaster @ 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 is the result of the slave database check: slave @ localhost [(none)]> select count (*) from tempdb. tb_engines; + ---------- + | count (*) | + ---------- + | 9 | + ---------- +