為什么使用主從

主從復(fù)制、讀寫(xiě)分離主要目的就是為了提高數(shù)據(jù)庫(kù)的并發(fā)性，在主庫(kù)更新，在從庫(kù)查詢，在出庫(kù)出現(xiàn)問(wèn)題時(shí)，可以快速切換到從庫(kù)提供服務(wù)，不需要人為參與，提高了系統(tǒng)的可用性，同時(shí)數(shù)據(jù)在從庫(kù)進(jìn)行備份，提高數(shù)據(jù)安全性；

主從原理

• 1、master 服務(wù)器上數(shù)據(jù)發(fā)生改變時(shí)，將 sql 記錄在 binlog 日志中；
• 2、slave 服務(wù)器在一定的時(shí)間間隔內(nèi)對(duì) master 的 binlog 進(jìn)行探測(cè)，判斷是否發(fā)生改變，如果改變，則開(kāi)啟一個(gè) I/OThread 請(qǐng)求，獲取 master 的 binlog 日志；
• 3、此時(shí) master 會(huì)為 I/O 線程啟動(dòng)一個(gè) dump 線程，將 master 的 binlog 發(fā)送給 slave，保存在 slave 的 relay_log 中，salve 啟動(dòng) SQL 線程讀取中繼日志中的
  binlog，在本地執(zhí)行，使得 master 和 slave 數(shù)據(jù)保持一致；

主從配置步驟

• 1、采用兩臺(tái) ubuntu 虛擬機(jī)，分別開(kāi)啟一個(gè) mysql 服務(wù)，ip 地址分別為 189（主）和 188（從）；
• 2、進(jìn)入 mysql 主庫(kù)，添加一個(gè)從庫(kù)的用戶，

mysql -uroot -p
// 192.168.100.188 從庫(kù) IP 地址
// root 用戶名
// test123 密碼
grant replication slave on *.* to 'root'@'192.168.100.188' identified by 'test123';

• 3、修改主庫(kù)的配置文件，開(kāi)啟 binlog，重啟 mysql 服務(wù)，查看 binlog 信息；

# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
// 服務(wù)器的唯一標(biāo)識(shí)
server-id               = 100
// 開(kāi)啟 binlog
log_bin                 = mysql-bin
// binlog日志保留的天數(shù)，清除超過(guò)10天的日志
expire_logs_days        = 10
max_binlog_size   = 100M
// 需要同步的數(shù)據(jù)庫(kù)
binlog_do_db            = myproject
#binlog_ignore_db       = include_database_name
#

service mysql restart

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: myproject
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

• 4、修改從庫(kù)的配置文件中的 server-id，id 不能與主庫(kù)相同，如果存在多個(gè)從庫(kù)時(shí)，也不能與其他從庫(kù) id 相同；

server-id=101

• 5、進(jìn)入從庫(kù)，輸入一下命令，啟動(dòng) salve，查看 salve 運(yùn)行情況；

mysql> change master to
    // 主庫(kù) ip
    -> master_host='192.168.100.189',
    // 主庫(kù)中新建的用戶名
    -> master_user='root',
    // 新建用戶的密碼
    -> master_password='test123',
    // 主庫(kù) binlog 日志名稱
    -> master_log_file='mysql-bin.000001',
    // binlog 日志偏移量
    -> master_log_pos=862,
    -> master_port=3306;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

start slave;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.189
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 476
               Relay_Log_File: ubuntu-relay-bin.000002
                Relay_Log_Pos: 644
        Relay_Master_Log_File: mysql-bin.000008
             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: 476
              Relay_Log_Space: 854
              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: 100
                  Master_UUID: fd430c7c-6cb7-11ea-95dc-000c296fcfe0
             Master_Info_File: mysql.slave_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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

當(dāng) Slave_IO_State 為 Waiting for master to send event 時(shí)，則表示主從配置成功；

• 6、測(cè)試主從
  在主庫(kù)創(chuàng)建新表 demo， 并插入一條記錄；

CREATE TABLE `demo` (
    ->   `id` INT NOT NULL AUTO_INCREMENT,
    ->   `a` VARCHAR(45) NULL,
    ->   `b` VARCHAR(45) NULL,
    ->   PRIMARY KEY (`id`))
    -> ENGINE = InnoDB
    -> DEFAULT CHARACTER SET = utf8;

insert into demo5 (a, b) values ('aaa', 'bbb');

查看從庫(kù)記錄；

mysql> show tables;
+---------------------+
| Tables_in_myproject |
+---------------------+
| demo5               |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from demo5;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 | aaa  | bbb  |
+----+------+------+
1 row in set (0.00 sec)

配置過(guò)程中遇到的問(wèn)題

• 1、配置完從庫(kù)后查看從庫(kù)運(yùn)行情況，發(fā)現(xiàn)從庫(kù)正在連接主庫(kù)，并且有錯(cuò)誤提示；

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.100.189
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 756
               Relay_Log_File: ubuntu-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Connecting
            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: 756
              Relay_Log_Space: 156
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'root@192.168.100.189:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '192.168.100.189' (111)
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100
                  Master_UUID: fd430c7c-6cb7-11ea-95dc-000c296fcfe0
             Master_Info_File: mysql.slave_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: 210414 01:21:37
     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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

在命令行直接連接主庫(kù)，發(fā)現(xiàn)無(wú)法連接成功；

yzw@ubuntu:/etc$ mysql -h192.168.100.189 -uroot -p
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.100.189' (111)

此時(shí)只需要修改主庫(kù)配置文件中的 bind-address，將其注釋或者改為 0.0.0.0，此項(xiàng)配置意思是 mysql 服務(wù)只監(jiān)聽(tīng)本地，因此從庫(kù)無(wú)法連接成功；

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1

• 2、Slave_IO_State 無(wú)值并有錯(cuò)誤信息；

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.100.189
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000016
          Read_Master_Log_Pos: 862
               Relay_Log_File: ubuntu-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000016
             Slave_IO_Running: No
            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: 862
              Relay_Log_Space: 156
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 13114
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100
                  Master_UUID: fd430c7c-6cb7-11ea-95dc-000c296fcfe0
             Master_Info_File: mysql.slave_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: 210414 01:50:22
     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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

查看主庫(kù)，發(fā)現(xiàn)主庫(kù)的 Position 為 154，從庫(kù)設(shè)置了 862，因此才會(huì)報(bào)錯(cuò)，修改從庫(kù)設(shè)置后重新啟動(dòng)即可成功；

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000016
         Position: 154
     Binlog_Do_DB: myproject
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.01 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_log_pos=154;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

• 3、從庫(kù)數(shù)據(jù)庫(kù)名稱和主庫(kù)不同，主庫(kù)操作后，從庫(kù)無(wú)法同步；

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.189
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000016
          Read_Master_Log_Pos: 476
               Relay_Log_File: ubuntu-relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000016
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1049
                   Last_Error: Error 'Unknown database 'myproject'' on query. Default database: 'myproject'. Query: 'CREATE TABLE `demo7` (   `id` INT NOT NULL AUTO_INCREMENT,   `a` VARCHAR(45) NULL,   `b` VARCHAR(45) NULL,   PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 854
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1049
               Last_SQL_Error: Error 'Unknown database 'myproject'' on query. Default database: 'myproject'. Query: 'CREATE TABLE `demo7` (   `id` INT NOT NULL AUTO_INCREMENT,   `a` VARCHAR(45) NULL,   `b` VARCHAR(45) NULL,   PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100
                  Master_UUID: fd430c7c-6cb7-11ea-95dc-000c296fcfe0
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 210414 01:58:11
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

此時(shí)必須在從庫(kù)創(chuàng)建和主庫(kù)一樣的數(shù)據(jù)庫(kù)名稱，重新同步一下即可（主庫(kù)執(zhí)行 flush logs 即生成新的 binlog 日志，修改主庫(kù)讀取的 binlog 名稱）；

主從同步機(jī)制

• 1、binlog+pos（默認(rèn)為異步）

• 2、半同步復(fù)制（5.5版本后）
  master 執(zhí)行完一個(gè)事務(wù)后不是立即將結(jié)果返回客戶端，而是等待至少一個(gè) slave 接收到了主庫(kù) binlog，并保存在 slave 的 relay_log 后，才會(huì)向客戶端返回結(jié)果；

• 3、全同步復(fù)制
  當(dāng)主庫(kù)執(zhí)行完一個(gè)事務(wù)，所有的從庫(kù)都執(zhí)行了該事務(wù)才返回給客戶端。

• 4、數(shù)據(jù)庫(kù)中間件
  所有的讀寫(xiě)請(qǐng)求都走中間件，由中間件去分配請(qǐng)求是到主庫(kù)還是從庫(kù)，記錄所有請(qǐng)求主庫(kù)的 key，如果還沒(méi)有同步時(shí)，就有讀請(qǐng)求進(jìn)來(lái)，此時(shí)從庫(kù)書(shū)庫(kù)還沒(méi)有更新，則中間件會(huì)把請(qǐng)求發(fā)到主庫(kù)，如果已經(jīng)同步，則將請(qǐng)求發(fā)到從庫(kù)，這樣就能保證數(shù)據(jù)的一致性；