【MySql】主從複製

為什麼使用主從複製？
1、主伺服器/從伺服器設定增加了健壯性。主伺服器出現問題時，你可以切換到從伺服器作為備份。
2、透過在主伺服器和從伺服器之間切分處理客戶查詢的負荷，可以得到更好的客戶響應時間。但是不要同時在主從伺服器上進行更新，這樣可能引起衝突。
3、使用複製的另一個好處是可以使用一個從伺服器執行備份，而不會干擾主伺服器。在備份過程中主伺服器可以繼續處理更新。
MySQL使用3個執行緒來執行復制功能(其中1個在主伺服器上，另兩個在從伺服器上。當發出START SLAVE時，從伺服器建立一個I/O執行緒，以連線主伺服器並讓主伺服器傳送二進位制日誌。主伺服器建立一個執行緒將二進位制日誌中的內容傳送到從伺服器。從伺服器I/O執行緒讀取主伺服器Binlog Dump執行緒傳送的內容並將該資料複製到從伺服器資料目錄中的本地檔案中，即中繼日誌。第3個執行緒是SQL執行緒，從伺服器使用此執行緒讀取中繼日誌並執行日誌中包含的更新。SHOW PROCESSLIST語句可以查詢在主伺服器上和從伺服器上發生的關於複製的資訊。
預設中繼日誌使用host_name-relay-bin.nnnnnn形式的檔名，其中host_name是從伺服器主機名，nnnnnn是序列號。用連續序列號來建立連續中繼日誌檔案，從000001開始。從伺服器跟蹤中繼日誌索引檔案來識別目前正使用的中繼日誌。預設中繼日誌索引檔名為host_name-relay-bin.index。在預設情況，這些檔案在從伺服器的資料目錄中被建立。中繼日誌與二進位制日誌的格式相同，並且可以用MySQLbinlog讀取。當SQL執行緒執行完中繼日誌中的所有事件後，中繼日誌將會被自動刪除。
從伺服器在資料目錄中另外建立兩個狀態檔案--master.info和relay-log.info。狀態檔案儲存在硬碟上，從伺服器關閉時不會丟失。
下次從伺服器啟動時，讀取這些檔案以確定它已經從主伺服器讀取了多少二進位制日誌，以及處理自己的中繼日誌的程度。
rac3 主庫：10.250.7.241
rac4 備庫：10.250.7.220
具體配置步驟：
1 修改主庫和備庫的my.cnf檔案
主庫my.cnf 與master-slave 相關的配置
server-id       = 1
log-bin         = master-bin
log-bin-index   = master-log-bin.index
log-error       = master-error.log
binlog_format   = mixed
relay-log       = slave-relay.log
relay-log-info-file = slave-relay-log.info
relay-log-index = slave-relay-log.index
備庫my.cnf 與master-slave 相關的配置
server-id       = 2
log-bin         = master-bin
log-bin-index   = master-log-bin.index
log-error       = master-error.log
#中繼日誌名。預設名為host_name-relay-bin.nnnnnn，其中host_name是從伺服器主機的名，nnnnnn表示中繼日誌在編號序列中建立。
relay-log       = slave-relay.log
relay-log-info-file = slave-relay-log.info
relay-log-index = slave-relay-log.index

#啟用從庫日誌，這樣可以進行鏈式複製
log-slave-updates
#從庫是否只讀，0表示可讀寫，1表示只讀
read-only=1
#只複製某個表
replicate-do-table=tablename
#只複製某些表（可用匹配符）
replicate-wild-do-table=tablename%
#只複製某個庫
replicate-do-db=dbname
#只複製某些庫
replicte-wild-do-db=dbname%
#不復制某個表
replicate-ignore-table=tablename
#不復制某些表
replicate-wild-ignore-table=tablename%
#不復制某個庫
replicate-ignore-db=dbname
#複製完的sql語句是否立即從中繼日誌中清除，1表示立即清除
relay-log-purge=1
#從伺服器主機，用於show slave hosts生成從庫清單
report-host=hostname
2 在主庫進行建立複製帳號，允許備庫rac4能夠讀取複製主庫的資料。
mysql> GRANT  REPLICATION  SLAVE  ON *.* TO 'rep'@'10.250.7.220 '  IDENTIFIED  BY 'rep123';
Query OK, 0 rows affected (0.03 sec)
mysql> GRANT  REPLICATION  CLIENT ON *.* TO 'rep'@'10.250.7.220 ';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT  SUPER  ON *.* TO 'rep'@'10.250.7.220 ';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT  RELOAD  ON *.* TO 'rep'@'10.250.7.220 ';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT  SELECT  ON *.* TO 'rep'@'10.250.7.220 ';
Query OK, 0 rows affected (0.00 sec)
mysql> flush  privileges;
Query OK, 0 rows affected (0.00 sec)
3 鎖住主庫的table，清空所有表和塊寫入語句，製作主庫的一致性快照，以便備份資料檔案到從庫進行初始化。
mysql>  FLUSH  TABLES  WITH  READ  LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW  MASTER  STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      822 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.13 sec)
4 保持鎖表的客戶端程式不退出，在另一個視窗對主伺服器資料目錄做快照。
[root@rac3 mysql]# tar zcvf data.tar.gz  ./data
複製主庫的資料檔案到備庫上。
5 在rac4上，啟動從庫：
mysql> CHANGE  MASTER  TO  MASTER_HOST='10.250.7.241',
    -> MASTER_USER ='rep',
    -> MASTER_PASSWORD ='rep123',
    -> MASTER_LOG_FILE ='master-bin.000001',
    -> MASTER_LOG_POS =822;
啟動slave 複製
mysql>  START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.250.7.241
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 65041
               Relay_Log_File: slave-relay.000002
                Relay_Log_Pos: 64473
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes  --這兩項都為yes的時候，表明slave正常執行。
            Slave_SQL_Running: Yes 

6 驗證：
在主庫：
mysql> use  yang;
Database changed
mysql> show  tables;
Empty set (0.00 sec)
mysql> create table yang as select * from test.t1;
mysql> show  tables;
+----------------+
| Tables_in_yang |
+----------------+
| yang           |
+----------------+
在備庫驗證：
mysql> use  yang;
Database changed
mysql> show  tables;
+----------------+
| Tables_in_yang |
+----------------+
| yang           |
+----------------+
mysql> select count(1) from yang;
+----------+
| count(1) |
+----------+
|     1000 |
+----------+
主庫執行：

mysql>  insert  into  yang   select * from  test.sbtest;
Query OK, 1000000 rows affected (19.83 sec)
Records: 1000000  Duplicates: 0  Warnings: 0
檢視備庫的show processlist，可以檢視主庫寫表yang的時候，備庫的程式的狀態變化，
等待主庫傳送日誌--解析日誌，執行dml語句--繼續等待主庫日誌的更新，這種方式和Oracle Streams的本質思想是一致的。
mysql> show   processlist;
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time  | State                                                                       | Info             |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
|  2 | system user |           | NULL | Connect | 88440 | Waiting for master to send event                                            | NULL             |
|  3 | system user |           | NULL | Connect |    90 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  8 | root        | localhost | yang | Query   |     0 | NULL                                                                        | show processlist |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> show processlist;
+----+-------------+-----------+------+---------+-------+----------------------------------+---------------------------------------------+
| Id | User        | Host      | db   | Command | Time  | State                            | Info                                        |
+----+-------------+-----------+------+---------+-------+----------------------------------+---------------------------------------------+
|  2 | system user |           | NULL | Connect | 88445 | Waiting for master to send event | NULL                                        |
|  3 | system user |           | yang | Connect |    24 | Sending data                     | insert into yang  select * from test.sbtest |
|  8 | root        | localhost | yang | Query   |     0 | NULL                             | show processlist                            |
+----+-------------+-----------+------+---------+-------+----------------------------------+---------------------------------------------+
3 rows in set (0.00 sec)
mysql> show processlist;
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time  | State                                                                       | Info             |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
|  2 | system user |           | NULL | Connect | 88459 | Waiting for master to send event                                            | NULL             |
|  3 | system user |           | NULL | Connect |    38 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  8 | root        | localhost | yang | Query   |     0 | NULL                                                                        | show processlist |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> select count(1) from yang;                
+----------+
| count(1) |
+----------+
|  1002000 |
+----------+
參考文章：
http://www.ningoo.net/html/2007/mysql_replication_configuration.html
http://www.oklinux.cn/html/sql/other/20080316/49421.html
http://www.cnblogs.com/rootq/archive/2008/11/11/1331267.html