# 本文的演示基于同一服務(wù)器上的多實(shí)例環(huán)境，其中3406端口用作主庫(kù)，而3506用作從庫(kù)。 
# 關(guān)于多實(shí)例的部署可參考： 
# MySQL多實(shí)例配置(一) http://blog.csdn.net/leshami/article/details/40339167 
# MySQL多實(shí)例配置(二) http://blog.csdn.net/leshami/article/details/40339295 
# 3406與3506為都為新裝且含缺省庫(kù)等，所以本文演示中未涉及先遷移主庫(kù)數(shù)據(jù)到備庫(kù)步驟 
a、主庫(kù)上的配置文件 
# 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、從庫(kù)上的配置文件 
# more my3506.cnf  
[mysqld] 
socket = /tmp/mysql3506.sock   # Author : Leshami 
port = 3506            # Blog  : <a target="_blank"  rel="external nofollow" >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 #### 
server-id=3506 
relay_log=/data/inst3506/log/relay/relay-bin 
read_only=1 

#啟動(dòng)端口為3406的實(shí)例并添加賬戶 
[mysql@app ~]$ mysqld_safe --defaults-file=/data/inst3406/data3406/my3406.cnf & 
[mysql@app ~]$ mysql -P3406  #登陸到3406 
 
master@localhost[(none)]> show variables like 'server_id'; 
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| server_id   | 3406 | 
+---------------+-------+ 
 
#創(chuàng)建用于復(fù)制的賬戶 
master@localhost[(none)]> grant replication slave,replication client on *.* 
  -> to repl@'192.168.1.177' identified by 'repl'; 
 
#初始化主庫(kù)日志文件，生成環(huán)境慎用reset 
master@localhost[(none)]> reset master; 
Query OK, 0 rows affected (0.01 sec) 
 
#查看主庫(kù)的狀態(tài)，日志初始化至000001， 
master@localhost[(none)]> show master status,Position為120 
+--------------------+----------+--------------+------------------+-------------------+ 
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 
+--------------------+----------+--------------+------------------+-------------------+ 
| inst3406bin.000001 |   120 |       |         |          | 
+--------------------+----------+--------------+------------------+-------------------+ 

#啟動(dòng)端口為3506的實(shí)例 
[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) 
 
#為從庫(kù)添加指向主庫(kù)的相關(guān)配置信息，該命令會(huì)生成及修改備庫(kù)上的master.info及relay-log.info文件 
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) 
 
#出現(xiàn)了2個(gè)warnings，查看一下 
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) 
 
#此時(shí)查看從庫(kù)的狀態(tài)信息 
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線程沒(méi)有運(yùn)行 
      Slave_SQL_Running: No   #SQL線程沒(méi)有運(yùn)行 
          ...................... 
       Master_Info_File: /data/inst3506/data3506/master.info 
 
slave@localhost[(none)]> start slave; #啟動(dòng)slave 
Query OK, 0 rows affected (0.01 sec) 
 
#含義如下 
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. 
 
#再次查看slave的狀態(tài) 
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線程處于運(yùn)行狀態(tài)  
      Slave_SQL_Running: Yes    #SQL線程處于運(yùn)行狀態(tài)  
           .............. 
     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 log; waiting for the slave I/O thread to update it #重要的提示信息 
 
#可以看到從庫(kù)上的2個(gè)線程，一個(gè)是用于I/O線程，用于連接到主庫(kù)請(qǐng)求主庫(kù)發(fā)送binlog，一個(gè)是用于執(zhí)行SQL的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 

#下面在主庫(kù)上執(zhí)行一些操作以檢查從庫(kù)的同步情況 
master@localhost[(none)]> show variables like 'server_id'; 
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| server_id   | 3406 | 
+---------------+-------+ 
1 row in set (0.00 sec) 
 
#主庫(kù)上Binlog Dump線程用于發(fā)送binlog日志文件到從庫(kù)，如下查詢 
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 
   
#主庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)及表 
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 
 
#下面是在從庫(kù)上檢查的結(jié)果 
slave@localhost[(none)]> select count(*) from tempdb.tb_engines; 
+----------+ 
| count(*) | 
+----------+ 
|    9 | 
+----------+