MySQL設(shè)置主從復(fù)制

主從復(fù)制：

單向，雙向，環(huán)形，級(jí)聯(lián)，一主多從

雙機(jī)復(fù)制的5種情形
1、異步主從（默認(rèn)常規(guī)）
2、雙寫（前段程序?qū)蓚€(gè)數(shù)據(jù)庫(kù)同時(shí)寫，必須兩邊都落實(shí)，程序才返回成功）
3、利用外掛軟件實(shí)現(xiàn)實(shí)時(shí)主庫(kù)Binlog日志抓取，從而可以在當(dāng)機(jī)的時(shí)候補(bǔ)全從庫(kù)
4、谷歌開發(fā)的半同步插件
5、DRBD

主從讀寫分離
1、通過程序?qū)崿F(xiàn)（性能，效率最佳，推薦）
php,java等程序可以通過設(shè)置多個(gè)連接文件輕松實(shí)現(xiàn)主從讀寫分離。
2、通過軟件實(shí)現(xiàn)讀寫分離
MySQL-proxy, Amoeba等代理軟件也可以實(shí)現(xiàn)讀寫分離功能，但是最好還是程序?qū)崿F(xiàn)。
3、開發(fā)dbproxy
讀寫分離邏輯圖展示：

主從同步原理：
實(shí)際上是異步的，即總是主庫(kù)寫完日志，日志才可能被從庫(kù)應(yīng)用。
master slave
線程：IO 線程：IO/SQL
開啟log-bin 需要設(shè)置的啟動(dòng)參數(shù)：CHANGE MASTER TO
怎刪改會(huì)去寫log-bin MASTER_HOST='192.168.1.111'
MASTER_PORT=3308
MASTER_USER='alrinrep'
MASTER_PASSWORD='password123'
MASTER_LOG_FILE='mysql-bin.000047'
MASTER_LOG_POS='5632'
開啟同步方式：start slave

                從庫(kù)IO向主庫(kù)發(fā)起復(fù)制請(qǐng)求，主庫(kù)開始對(duì)從庫(kù)認(rèn)證，通過后主庫(kù)IO根據(jù)從庫(kù)
                日志要求開始發(fā)送bin-log到從庫(kù)relay-log。一個(gè)周期后，從庫(kù)更新master.info
                ，并重新向主庫(kù)發(fā)起請(qǐng)求。
                SQL線程從relay-log發(fā)現(xiàn)新數(shù)據(jù)，并寫到slave的數(shù)據(jù)文件和日志系統(tǒng)。

                相關(guān)語句：--master-data=1,
                CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000020', MASTER_LOG_POS=1191;
建立相關(guān)的賬號(hào)，即alrinrep
并授予應(yīng)有的權(quán)限

MySQL主從復(fù)制原理過程
1、Slave服務(wù)器上執(zhí)行start slave，開啟主從復(fù)制開關(guān)
2、Slave服務(wù)器的IO線程會(huì)通過在Master上授權(quán)的復(fù)制用戶權(quán)限請(qǐng)求連接Master服務(wù)器，并請(qǐng)求從指定的bin-log日志文件指定位置
（change master to 命令相關(guān)參數(shù)）之后發(fā)送binlog日志
3、Master服務(wù)器接受到來自Slave服務(wù)器的IO線程的請(qǐng)求后，Master服務(wù)器上負(fù)責(zé)復(fù)制IO線程根據(jù)Slave服務(wù)器的IO線程請(qǐng)求的信息
讀取指定binlog日志文件指定位置之后的binlog日志信息，然后返回給Slave端的IO線程。返回的信息中除了binlog日志的內(nèi)容外，
還有本次返回日志內(nèi)容后在Master服務(wù)器端的新的binlog文件名稱以及在binlog中的下一個(gè)指定更新位置
4、當(dāng)Slave服務(wù)器的IO線程獲取到來自Master服務(wù)器上的IO線程發(fā)送的日志以及日志文件及位置點(diǎn)后，將binlog日志內(nèi)容一次寫入到
slave端的Relay log（中繼日志）文件（MySQL-relay-bin.xxxxxx）的最后端，并將新的binlog文件名和位置記錄到master-info文件
中，以便下一次讀取Master端新Binlog日志時(shí)能夠告訴Master服務(wù)器需要從新binlog日志的那個(gè)文件哪個(gè)文件開始新的binlog內(nèi)容。
5、Slave服務(wù)器端的SQL線程會(huì)實(shí)時(shí)的檢測(cè)本地relay log中新增的日志內(nèi)容，然后及時(shí)把LOG文件中的內(nèi)容解析成MASTER端曾經(jīng)執(zhí)行過
的SQL語句，并在自身SLAVE服務(wù)器上安順序執(zhí)行這些SQL。應(yīng)用玩后清理應(yīng)用過的日志。
6，經(jīng)過了上述過程，就可以確保MASTER和SLAVE執(zhí)行了同樣的SQL。在復(fù)制正常的情況下，MASTER和SLAVE數(shù)據(jù)是完全一致的。

主從復(fù)制上機(jī)實(shí)驗(yàn)：

0，環(huán)境：3308/3309同機(jī)，IP為192.168.199.151
1，修改my.cnf，檢查server-id和log-bin，主從server-id必須不同。主庫(kù)必須開啟bin-log
2，添加復(fù)制賬號(hào)和對(duì)應(yīng)的 replication slave 權(quán)限

    mysql> select user,host,password from user;
    +-------+-----------+-------------------------------------------+
    | user  | host      | password                                  |
    +-------+-----------+-------------------------------------------+
    | root  | localhost | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B |
    | alrin | %         | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B |
    +-------+-----------+-------------------------------------------+
    2 rows in set (0.00 sec)

    mysql> grant replication slave on *.* to alrinrep@'192.168.199.%' identified by 'password123';
    Query OK, 0 rows affected (0.00 sec)

    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

    mysql> show variables like 'log_bin';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | log_bin       | ON    |
    +---------------+-------+
    1 row in set (0.00 sec)

    mysql> 

3，主庫(kù)上做備份。先鎖表，再備份

    mysql> flush table with read lock;
    Query OK, 0 rows affected (0.03 sec
    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000004 |      423 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

新開session窗口后執(zhí)行備份：

[root@localhost backup]# mysqldump -uroot -ppassword123 -S /data/mysqldata/3308/mysql.sock -A -B --events --master-data=2| gzip > /data/mysqldata/backup/rep.sql.gz

備份后檢查：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      423 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

解鎖表：

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> 

4，從庫(kù)操作

    mysql> change master to 
        -> MASTER_HOST='192.168.199.151',
        -> MASTER_PORT=3308,
        -> MASTER_USER='alrinrep', 
        -> MASTER_PASSWORD='password123', 
        -> MASTER_LOG_FILE='mysql-bin.000004', 
        -> MASTER_LOG_POS=423;
    Query OK, 0 rows affected, 2 warnings (0.05 sec)

5,開啟復(fù)制

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

6，檢查slave狀態(tài)：

mysql> show slave status\G
    *************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.199.151
              Master_User: alrinrep
              Master_Port: 3308
            Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 423
               Relay_Log_File: mysql-relay-bin.000002
            Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000004
             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: 423
              Relay_Log_Space: 456
              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: 1
              Master_UUID: 16b758c5-2455-11e9-8fc6-080027339667
             Master_Info_File: /data/mysqldata/3309/data/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
           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
    1 row in set (0.00 sec)

    mysql> 

7，嘗試在主庫(kù)DDL，檢查復(fù)制狀態(tài)
主庫(kù)：

    mysql> create database alrin;
    Query OK, 1 row affected (0.00 sec)
    從庫(kù)：
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | alrin              |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    5 rows in set (0.00 sec)

命令匯總：
1，grant replication slave on . to alrinrep@'192.168.199.%' identified by 'password123';
2，flush table with read lock;
3，show master status;
4，unlock tables;
5，change master to
MASTER_HOST='192.168.199.151',
MASTER_PORT=3308,
MASTER_USER='alrinrep',
MASTER_PASSWORD='password123',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=423;
6，start slave;
7，show slave status\G

8, ?關(guān)于log_slave_updates：
默認(rèn)情況下，從庫(kù)的SLAVE SQL讀了replay_bin日志并應(yīng)用后，數(shù)據(jù)庫(kù)就有了內(nèi)容，但是并沒有去寫自己的BINLOG，也就是說，如果slave要再去拖一個(gè)slave，所謂的級(jí)聯(lián)復(fù)制，是會(huì)失敗的，因?yàn)榈诙€(gè)slave會(huì)去讀第一個(gè)slave的binlog。那么有什么辦法讓第一個(gè)SLAVE寫binlog呢，辦法就是開啟log_slave_updates。