MySQL 複製簡要描述及樣本，mysql簡要描述樣本

MySQL 複製簡要描述及樣本，mysql簡要描述樣本

    主從複製技術在MySQL中被廣泛使用，主要用於同步一台伺服器上的資料至多台從伺服器，可以用於實現負載平衡，高可用和故障切換，以及提供備份等等。MySQL支援多種不同的複製技術，諸如單向，半同步非同步複製等以及不同層級的複製，諸如資料庫層級，表級，跨庫同步等等。本文簡要描述了一個基本的主從複製並給出樣本。

 

1、複製的基本原理(步驟)
    a、在主庫上把資料更改記錄的二進位日誌(binary log)
    b、從庫上的I/O線程串連到主庫並請求發送其二進位記錄檔(主庫上的binlog dump線程將二進位日誌內容發送到從庫)
    c、從庫上的I/O線程讀取主服務發送的二進位內容並將其拷貝到中繼日誌
    d、從庫上的SQL線程讀取中繼日誌並執行日誌中包含的更新

 

2、為設定檔添加複製項

# 本文的示範基於同一伺服器上的多執行個體環境，其中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.sockport = 3406pid-file = /data/inst3406/data3406/my3406.piduser = mysqllog-error=/data/inst3406/data3406/inst3406.errdatadir=/data/inst3406/data3406basedir=/app/soft/mysql5#### for master items ####server-id=3406log_bin=/data/inst3406/log/bin/inst3406bininnodb_flush_log_at_trx_commit=1sync_binlog=1b、從庫上的設定檔# 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/inst3506.errdatadir=/data/inst3506/data3506basedir=/app/soft/mysql5#### for slave items ####server-id=3506relay_log=/data/inst3506/log/relay/relay-binread_only=1

3、建立複製帳號

#啟動連接埠為3406的執行個體並添加賬戶[mysql@app ~]$ mysqld_safe --defaults-file=/data/inst3406/data3406/my3406.cnf &[mysql@app ~]$ mysql -P3406    #登陸到3406master@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';#初始化主庫記錄檔，產生環境慎用resetmaster@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 |              |                  |                   |+--------------------+----------+--------------+------------------+-------------------+

4、配置主從同步

#啟動連接埠為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)#為從庫添加指向主庫的相關配置資訊，該命令會產生及修改備庫上的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: 1759Message: Sending passwords in plain text without SSL/TLS is extremely insecure.*************************** 2. row ***************************  Level: Note   Code: 1760Message: 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.infoslave@localhost[(none)]> start slave;  #啟動slaveQuery OK, 0 rows affected (0.01 sec)#含義如下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.#再次查看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: 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、驗證同步情況

#下面在主庫上執行一些操作以檢查從庫的同步情況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: NULLCommand: 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 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#下面是在從庫上檢查的結果slave@localhost[(none)]> select count(*) from tempdb.tb_engines;+----------+| count(*) |+----------+|        9 |+----------+