# 本文的演示基于同一服務器上的多實例環境，其中3406端口用作主庫，而3506用作從庫。 # 關于多實例的部署可參考： # MySQL多實例配置(一) http://blog.csdn.net/leshami/article/details/40339167 # MySQL多實例配置(二) http://blog.csdn.net/leshami/article/details/40339295 # 3406與3506為都為新裝且含缺省庫等，所以本文演示中未涉及先遷移主庫數據到備庫步驟 a、主庫上的配置文件 # 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、從庫上的配置文件 # 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</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 

#啟動端口為3406的實例并添加賬戶 [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 | +---------------+-------+  #創建用于復制的賬戶 master@localhost[(none)]> grant replication slave,replication client on *.*   -> to repl@'192.168.1.177' identified by 'repl';  #初始化主庫日志文件，生成環境慎用reset master@localhost[(none)]> reset master; Query OK, 0 rows affected (0.01 sec)  #查看主庫的狀態，日志初始化至000001， master@localhost[(none)]> show master status,Position為120 +--------------------+----------+--------------+------------------+-------------------+ | File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+-------------------+ | inst3406bin.000001 |   120 |       |         |          | +--------------------+----------+--------------+------------------+-------------------+ 

#啟動端口為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)  #為從庫添加指向主庫的相關配置信息，該命令會生成及修改備庫上的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)  #出現了2個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)  #此時查看從庫的狀態信息 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線程沒有運行       Slave_SQL_Running: No   #SQL線程沒有運行           ......................        Master_Info_File: /data/inst3506/data3506/master.info  slave@localhost[(none)]> start slave; #啟動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的狀態 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線程處于運行狀態        Slave_SQL_Running: Yes    #SQL線程處于運行狀態             ..............      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 #重要的提示信息  #可以看到從庫上的2個線程，一個是用于I/O線程，用于連接到主庫請求主庫發送binlog，一個是用于執行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 

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