CentOS 6.5 下MySQL 5.7.12主從複製配置詳解

為什麼使用主從架構？

1、實現伺服器負載平衡；

2、通過複製實現資料的異地備份；

3、提高資料庫系統的可用性；

4、可以分庫【垂直分割】，分表【水平分割】；

主從配置的前提條件

1、MySQL版本一致；

你還沒有安裝MySQL？

請參考：CentOS 6.5 下安裝MySQL 5.7.12，使用官網下載的rpm安裝包

2、MySQL中的資料一致；

不一致就把它們搞一致！

3、操作前停止一切更新操作（寫入、更新、刪除等）；

配置master（主伺服器）
vi /etc/my.cnf
#[必須]啟用二進位日誌
log-bin=mysql-bin
#[必須]伺服器唯一ID，預設是1，一般取IP最後一段
server-id=151
配置slave（從伺服器）
vi /etc/my.cnf
#[可選]啟用二進位日誌
log-bin=mysql-bin
#[必須]伺服器唯一ID，預設是1，一般取IP最後一段
server-id=152
如果你的從伺服器下面再掛從伺服器，啟用二進位日誌就是必選的！

重啟mysql服務
/etc/init.d/mysqld restart

重啟的目的是使用剛才的配置生效，主從都需要重啟；

注意：服務名是“mysqld”，不是某同學文章中的“mysql”！

在主伺服器上建立備份專用帳戶
mysql -uroot -ppassword -e "GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO 'backup'@'192.168.0.154' IDENTIFIED BY '123456';"
注意：

1、這裡是用GRANT建立使用者並授權遠程登入許可權，而不是使用“Create User”來建立；

2、命令是在Shell下執行，不是在“mysql”用戶端中執行；

我是為了方便，直接把命令都寫到一行裡了，當然你也可以先用“mysql -uroot -ppassword”登入後，

再執行“GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO ‘backup’@’192.168.0.154’ IDENTIFIED BY ‘123456’;”；

命令中的“password”是什麼鬼？我怎麼登入不了！泥瑪，我哪知道你的 root 使用者密碼是什麼鬼！

查詢master(主伺服器)的狀態
mysql -uroot -ppassword -e "show master status;"
這是在主伺服器上執行的，看準了。

不解釋了，看輸出：

mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
File列和Position列的值一會兒我們要用到。

配置Slave啟動主從複製
mysql -uroot -ppassword -e "change master to master_host='192.168.0.151',master_user='backup',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154; start slave;"
//注意不要斷開，154數字前後無單引號。
//啟動從伺服器複製功能
master_host=主伺服器IP
master_user=在主伺服器上建立的備份使用者名稱
master_password=備份使用者密碼
master_log_file=查詢master(主伺服器)的狀態得到的File列的值
master_log_pos=Position列的值
start slave：啟動從伺服器複製功能

檢查從伺服器複製功能狀態
mysql -uroot -ppassword -e "show slave status\G;"
輸出如下：

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.151
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: vir2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 526
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 151
                  Master_UUID: 959de288-197f-11e6-9615-525400190a25
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
主要查看以下兩項：

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
Slave_IO及Slave_SQL進程必須正常運行，即YES狀態，否則都是錯誤的狀態(如：其中一個NO均屬錯誤)。
以上操作過程，主從伺服器配置完成。

我在一次配置過程中忘記在主伺服器上建立備份使用者，Slave_IO_Running 就一直處於“connect”狀態。

主從伺服器測試
你還是登入Mysql再操作吧，這次命令比較多，不組合了！

在主伺服器上執行以下操作：

mysql -uroot -ppassword

create database db_test_slave;
use db_test_slave;
create table tb_test(id int(3), name varchar(50));
insert into tb_test values(1,'hello slave');
show databases;
在從伺服器上執行以下操作：

mysql -uroot -ppassword -e "show databases; use db_test_slave; select * from tb_test;"
查看輸出：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_test_slave      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | hello slave |
+------+-------------+
看到在主伺服器上建立的資料庫、資料表、插入的資料記錄了嗎？

沒有？自己找找原因吧，我一次就成功了。

結束語
上面提到了“如果你的從伺服器下面再掛從伺服器，啟用二進位日誌就是必選的！”，主從只是一個相對概念，一台MySQL服務即可以是主，也可以是從。

一開始講了主從複製的一些好處，但是單一的主從複製也有其不足：當更新操作增加到一定程度後，主伺服器的任務會過分繁重，成為瓶頸，從而使系統效能大幅度下降。另外當主機出現故障時，整個系統都涉及更新的功能都不能正常使用，因此系統的可靠性依然不高。