MySQL主從複製配置詳述

一、mysql主從複製的基本原理：
我們這個實驗示範的是mysql的非同步複製，何謂非同步複製，即一條語句開始，主伺服器不會等待從伺服器執行完，而是主伺服器一執行完就開始下一條語句，這也是出於主伺服器並髮型的考慮。slave端會開啟兩個thread，即Slave I/O thread和Slave SQL thread，I/O thread負責從master的二進位日誌中讀取事件並將這些時間資訊存放發到relay-log中，Slave SQL thread負責從relay-log日誌讀取事件，並重放其中的事件而更新slave的資料，使其與master中的資料一致。
所以說：主伺服器上必須開啟二進位日誌，而從伺服器上只需開啟中繼日誌。

二、mysql支援的複製類型：
1. 基於語句的複製：在主伺服器上執行的SQL語句，在從伺服器上執行同樣的語句。MySQL預設採用基於語句的複製，效率比較高。一旦發現沒法精確複製時，會自動選著基於行的複製。   
2. 基於行的複製：把改變的內容複寫過去，而不是把命令在從伺服器上執行一遍。
3. 混合類型的複製: 預設採用基於語句的複製，一旦發現基於語句的無法精確的複製時，就會採用基於行的複製。

三、步驟：

主mysql伺服器的配置：
1. 開啟二進位日誌
2. 建立具有複製許可權的使用者
3. Server-id唯一
從mysql伺服器的配置：
1. 開啟中繼日誌
2. Server-id唯一
3. 設為唯讀模式

假設主伺服器上的資料比較大，同步會比較慢，所以要先用mysqldump匯出主伺服器的資料，再匯入到從伺服器上，然後再開始同步，這樣比較合理。

四、準備：
在主伺服器上建立一張測試表
mysql> create database data;
Query OK, 1 row affected (0.03 sec)
mysql> select * from data.info;
+-----+-------+-----+
| sid | name  | age |
+-----+-------+-----+
|  1 | zhang |  23 |
|  2 | li    |  42 |
|  3 | wang  |  34 |
|  4 | qian  |  25 |
|  5 | cheng |  29 |
+-----+-------+-----+
5 rows in set (0.00 sec)

五、開始配置

主伺服器：
1. 開啟二進位日誌並指定server-id

[root@Oracle ~]# vim /etc/my.cnf   
[client]
user=root
password=12345
[mysqld]
log-bin=mysql-bin
server-id=1
#添加這幾行，mysql5.6預設沒有開啟二進位記錄檔

[root@oracle ~]# service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]

2. 建立具有複製許可權的使用者
mysql> grant replication slave on *.* to slave@192.168.2.96 identified by '12345';
Query OK, 0 rows affected (0.04 sec)

從伺服器：
1. 開啟中繼日誌並指定server-id和唯讀
[root@node2 ~]# vim /etc/my.cnf
[client]
user=root
password=12345
[mysqld]
relay-log=mysql-relay-bin
server-id=2
read-only=on
[root@node2 ~]# service mysqld restart
Shutting down MySQL....                                    [確定]
Starting MySQL.                                            [確定]

同步：
1. 在主務器上進行邏輯備份
[root@oracle ~]# mysqldump --master-data=2 --databases data --lock-all-tables > /root/bak.sql
[root@oracle ~]# scp /root/bak.sql root@192.168.2.96:/root/

2. 在從伺服器上執行該指令碼
[root@node2 ~]# mysql < /root/bak.sql
[root@node2 ~]# mysql -e 'select * from data.info;'
+-----+-------+-----+
| sid | name  | age |
+-----+-------+-----+
|  1 | zhang |  23 |
|  2 | li    |  42 |
|  3 | wang  |  34 |
|  4 | qian  |  25 |
|  5 | cheng |  29 |
+-----+-------+-----+
#主伺服器上的data資料庫都匯入到從伺服器了

3. 讓從伺服器成為slave
[root@node2 ~]# vim /root/bak.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=120;
mysql> change master to master_host='192.168.2.93',master_user='slave',master_password='12345',MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

查看日誌：
2014-02-23 20:45:47 17372 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='slave', master_port= 3306, master_log_file='mysql-bin.000004', master_log_pos= 120, master_bind=''. New state master_host='192.168.2.93', master_port= 3306, master_log_file='mysql-bin.000004', master_log_pos= 120, master_bind=''.
2014-02-23 20:45:52 17372 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-02-23 20:45:52 17372 [Note] Slave I/O thread: connected to master 'slave@192.168.2.93:3306',replication started in log 'mysql-bin.000004' at position 120
2014-02-23 20:45:52 17372 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2014-02-23 20:45:52 17372 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000004' at position 120, relay log './mysql-relay-bin.000001' position: 4
#slave I/O thread和Slave SQL thread都已經啟動

1234567891011121314 mysql> show slave status\G;
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.93
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 2058
              Relay_Log_File: mysql-relay-bin.000005
                Relay_Log_Pos: 555
        Relay_Master_Log_File: mysql-bin.000005
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

4. 在主伺服器上再插入2行資料
[root@oracle ~]# mysql -e 'select * from data.info;'
+-----+-------+-----+
| sid | name  | age |
+-----+-------+-----+
|  1 | zhang |  23 |
|  2 | li    |  42 |
|  3 | wang  |  34 |
|  4 | qian  |  25 |
|  5 | cheng |  29 |
|  6 | six  |  33 |
|  7 | seven |  41 |
+-----+-------+-----+
#第6行和第7行是新插入的，本來是沒有的

5. 在從伺服器上查看
[root@node2 ~]# mysql -e 'select * from data.info;'
+-----+-------+-----+
| sid | name  | age |
+-----+-------+-----+
|  1 | zhang |  23 |
|  2 | li    |  42 |
|  3 | wang  |  34 |
|  4 | qian  |  25 |
|  5 | cheng |  29 |
|  6 | six  |  33 |
|  7 | seven |  41 |
+-----+-------+-----+

這樣就實現了主從同步了。