MySQL replication detailed explanation and simple example

# The demonstration in this article is based on a multi-instance environment on the same server, where port 3406 is used as the master library and port 3506 is used as the slave library. 
# For more information about multi-instance deployment, please refer to: 
# MySQL multi-instance configuration (I) http://blog.csdn.net/leshami/article/details/40339167 
# MySQL multi-instance configuration (Part 2) http://blog.csdn.net/leshami/article/details/40339295 
# 3406 and 3506 are both newly installed and contain default libraries, so this article does not involve the step a of migrating the data from the primary library to the standby library and the configuration file on the primary library. # 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 #### 
server-id=3406 
log_bin=/data/inst3406/log/bin/inst3406bin 
innodb_flush_log_at_trx_commit=1 
sync_binlog=1 
 
b. From the configuration file on the library# more my3506.cnf  
[mysqld] 
socket = /tmp/mysql3506.sock # Author : Leshami 
port = 3506 # Blog : <a target="_blank" href="http://blog.csdn.net/leshamipid-file" rel="external nofollow" >http://blog.csdn.net/leshami 
pid-file = /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 #### 
server-id=3506 
relay_log=/data/inst3506/log/relay/relay-bin 
read_only=1 

#Start the instance with port 3406 and add the account [mysql@app ~]$ mysqld_safe --defaults-file=/data/inst3406/data3406/my3406.cnf & 
[mysql@app ~]$ mysql -P3406 #Log in to 3406 
 
master@localhost[(none)]> show variables like 'server_id'; 
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| server_id | 3406 | 
+---------------+-------+ 
 
#Create an account for replication master@localhost[(none)]> grant replication slave,replication client on *.* 
  -> to repl@'192.168.1.177' identified by 'repl'; 
 
#Initialize the main database log file, use reset with caution in the build environment 
master@localhost[(none)]> reset master; 
Query OK, 0 rows affected (0.01 sec) 
 
#Check the status of the main database, and initialize the log to 000001. 
master@localhost[(none)]> show master status, Position is 120 
+--------------------+----------+--------------+------------------+-------------------+ 
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 
+--------------------+----------+--------------+------------------+-------------------+ 
| inst3406bin.000001 | 120 | | | | 
+--------------------+----------+--------------+------------------+-------------------+ 

#Start the instance with port 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 relevant 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 standby database. slave@localhost[(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.177', 
  -> MASTER_USER='repl', 
  -> MASTER_PASSWORD='repl', 
  -> MASTER_PORT=3406, 
  -> MASTER_LOG_FILE='inst3406bin.000001', 
  -> MASTER_LOG_POS=0; 
Query OK, 0 rows affected, 2 warnings (0.04 sec) 
 
#Two warnings appeared, 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 status information at this time 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 #IO thread is not running Slave_SQL_Running: No #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) 
 
#The meaning is as follows 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 the 
relay log and executes them. 
 
# Check 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 #IO thread is in running state Slave_SQL_Running: Yes #SQL thread is in running state.............. 
     Exec_Master_Log_Pos: 120 
       Relay_Log_Space: 452 
           ............ 
       Master_Server_Id: 3406 
         Master_UUID: 32f53a0a-63ef-11e4-93d9-8c89a5d108ae 
       Master_Info_File: /data/inst3506/data3506/master.info 
          SQL_Delay: 0 
     SQL_Remaining_Delay: NULL   
   Slave_SQL_Running_State: Slave has read all relay logs; waiting for the slave I/O thread to update it #Important prompt information#You can see two threads on the slave library, one is for the I/O thread, which is used to connect to the master library to request the master library to send binlog, and the other is the SQL thread for executing 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 logs; waiting for the slave I/O thread to update it 
  Info: NULL 

#Next, perform some 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. The following query is 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 
   
#Create database and table in the main library 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 is the result of checking from the slave library slave@localhost[(none)]> select count(*) from tempdb.tb_engines; 
+----------+ 
| count(*) | 
+----------+ 
| 9 | 
+----------+