mysql的複製叢集，及讀寫分離

標籤：監控   自己   client   monitor   tle   vpd   variable   resolve   img   

    為什麼要設定mysql叢集？

            為了減輕，mysql伺服器的IO壓力，設定多個其他mysql伺服器幫他分擔讀寫操作

    1.mysql複製叢集的類型

        主從架構（從伺服器唯讀，不可寫）

      一主一從，

      一主多重

  主主架構

2.複製原理

        1）若主伺服器上的資料集較大，則需要我們將主伺服器資料庫所有內容做備份，然後發送給從伺服器，隨後擷取二進位日誌的檔案，及其座標用於後續的資料同步

        2）所謂同步，是主伺服器上的二進位日誌中的SQL語句，發送到從伺服器上的中繼記錄檔中，然後把這些SQL語句重放實現同步

3.與主從複製相關的線程

        從伺服器上：IO線程：用來串連主伺服器的，監控二進位日誌的變化，並接受的

                            SQL線程：監控，讀取，並且重放中繼日誌中的SQL語句，並把資料寫入伺服器資料庫中；

        主伺服器上：DUMP線程：將slave請求的二進位日誌發送過去




4.主從複製的過程：

        1.主伺服器資料發生修改，寫入資料庫中，並且記錄二進位記錄檔中

        2.slave的IO線程複製發生變化的二進位日誌，並且記錄到自己的中繼日誌中

        3.slave的SQL線程，將複製過來的中繼日誌做重放，保持和master上資料的一致；

5.主從複製注意內容：

        1.保證每個伺服器的server_id設定不同，（後面讀寫分離做測試方便）

        2.主伺服器上開啟二進位記錄檔

        3.從伺服器關閉二進位記錄檔，開啟中繼記錄檔，並且設定read_only=ON

        4.主伺服器設定參數sync_binlog=1（每發生一次資料變化，就將變化內容寫入二進位日誌中）

            innodb_flush_log_at_trx_commit=1（沒提交一個事務，就將交易記錄儲存到磁碟中）

        5.記得記錄二進位日誌的檔案名稱和座標

        6.在主伺服器上授權一個可以用於主從複製的使用者


6.主從複製執行個體

    主伺服器檔案配置

innodb_file_per_table=ON  //開啟innodb的單獨資料表空間skip_name_resolve=ON    //跳過主機名稱解析log_bin=binlogserver_id=123sync_binlog=1innodb_flush_log_at_trx_commit=1

 從伺服器檔案配置

innodb_file_per_table=ONskip_name_resolve=ONrelay_log=slavelogserver_id=7read_only=ON

主伺服器做資料備份並發給從伺服器

MariaDB [(none)]> show master status;  //記錄此時二進位記錄檔及座標+---------------+----------+--------------+------------------+| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |+---------------+----------+--------------+------------------+| binlog.000015 |      617 |              |                  |+---------------+----------+--------------+------------------+1 row in set (0.00 sec)MariaDB [(none)]> grant replication slave on *.* to 'vuser'@'%' identified by '111111';  //授權一個用於主從複製的使用者Query OK, 0 rows affected (0.02 sec)[[email protected] ~]# mysqldump --all-databases --lock-all-tables > gg.sql  //複製當前資料庫並發給從伺服器[[email protected] ~]# scp gg.sql [email protected]:/root

從伺服器上操作

MariaDB [zz]> \. /root/gg.sql   //使用發送過來的資料庫檔案，初始化資料庫建立主從串連：MariaDB [zz]> change master to master_host='172.16.0.156',master_user='vuser',master_password='111111',master_port=3306,master_log_file='binlog.000015',master_log_pos=617;MariaDB [zz]> start slave;  開啟從伺服器MariaDB [zz]> show slave status\G; 查看主從狀態*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event   //若顯示此行則配置成功                  Master_Host: 172.16.0.156                  Master_User: vuser                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: binlog.000015          Read_Master_Log_Pos: 831               Relay_Log_File: slavelog.000002                Relay_Log_Pos: 740        Relay_Master_Log_File: binlog.000015             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: 831              Relay_Log_Space: 1027              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: 0Master_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: 1231 row in set (0.00 sec)

若想關閉主從配置，需要在從伺服器使用命令stop slave即可







二.雙主複製

    兩個伺服器，同時開啟二進位記錄檔和中繼記錄檔；並相互做change master to操作即可




三.讀寫分離（前提：主從複製）

mysqlrouter

  原理

通過調用倆個不同的介面，將mysql語句分別調用

安裝：mysqlrouter，配置設定檔/etc/mysqlrouter/mysqlrouter.conf

[routing:master]   添加倆個配置段//master 介面bind_address = 172.16.0.155:40001   //綁定的ip地址和連接埠號碼destinations = 172.16.0.156:3306   //後端的mysql服務mode = read-write            //模式（是否允許寫）connect_time = 3        //連線時間[routing:slave]bind_address = 172.16.0.155:40002destinations = 172.16.0.155:3306mode = read-onlyconnect_time = 1

配置完成可以啟動服務

[[email protected] ~]# ss -tnl
State       Recv-Q Send-Q                     Local Address:Port                                    Peer Address:Port              
LISTEN      0      50                                     *:3306                                               *:*                  
LISTEN      0      128                                    *:111                                                *:*                  
LISTEN      0      5                          192.168.122.1:53                                                 *:*                  
LISTEN      0      128                                    *:22                                                 *:*                  
LISTEN      0      128                            127.0.0.1:631                                                *:*                  
LISTEN      0      100                            127.0.0.1:25                                                 *:*                  
LISTEN      0      128                         172.16.0.155:40001                                              *:*                  
LISTEN      0      128                         172.16.0.155:40002 





mysqlrouter 測試執行個體:（因為mysqlrouter是調度到後端的，所以需要我們在master授權使用者）

MariaDB [(none)]> grant all on *.* to 'cccc'@'172.16.0.%' identified by '111111';Query OK, 0 rows affected (0.01 sec)[[email protected] ~]# mysql -ucccc -h172.16.0.151 -P40002 -p111111 -e 'select @@server_id;'+-------------+| @@server_id |+-------------+|           7 |+-------------+[[email protected] ~]# mysql -ucccc -h172.16.0.151 -P40001 -p111111 -e 'select @@server_id;'+-------------+| @@server_id |+-------------+|         123 |+-------------+

四：proxysql實現讀寫分離

   1.安裝proxysql

   2.啟動ProxySQL服務

   3.進入proxysql的管理介面

[[email protected] ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032  //系統自己設定的使用者密碼，及連接埠號碼使用main資料庫給mysql_servers表添加監控點：insert into mysql_servers(hostgroup_id,username,port) values(10,'172.16.0.156',3306),(10,'172.16.0.150',3306),(10,'172.16.0.151',3306),(10,'172.16.0.152',3306);在master伺服器上建立監控使用者 MariaDB [(none)]> grant replication client,replication slave on *.* to 'proxyuser'@'172.16.0.%' identified by '111111';  在proxysql上修改變數參數，設定相應的監控使用者 MySQL [main]> set mysql-monitor_username='proxyuser';  //實質上是修改了main資料中的global_variables表 MySQL [main]> set mysql-monitor_password='111111'; 在proxysql伺服器上修改mysql_replication_hostgroups,為表添加更明細的分組 MySQL [main]> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values (10,20); 這裡必須有一個ID和之前mysql_servers表中設定的相同，至於主從，會到後端伺服器設定檔中讀取read_only參數  在master伺服器設定系統管理使用者 MariaDB [(none)]> grant all on *.* to 'root'@'172.16.0.%' identified by  '111111';MariaDB [(none)]> grant all on *.* to 'sql'@'172.16.0.%' identified by  '111111';在proxysql也添加各個系統管理使用者 MySQL [main]> insert into mysql_users (username,password,default_hostgroup) values ('root','111111',10),('sql','111111',20);這樣當使用root使用者訪問時，會訪問主伺服器，使用sql時，會訪問從伺服器在proxysql上同步剛剛配置的資訊，同步到運行時環境，並且同步到磁碟上    load mysql servers to runtime    save mysql servers to disk    load mysql variables to runtime    save mysql variables to disk    load mysql users to runtime    save mysql users to disk最後使用調度連接埠訪問mysql[[email protected] ~]# mysql -uroot -h172.16.0.154 -p111111 -P6033 -e "select @@server_id;"+-------------+| @@server_id |+-------------+|         123 |+-------------+[[email protected] ~]# mysql -usql -h172.16.0.154 -p111111 -P6033 -e "select @@server_id;"+-------------+| @@server_id |+-------------+|           2 |+-------------+[[email protected] ~]# mysql -usql -h172.16.0.154 -p111111 -P6033 -e "select @@server_id;"+-------------+| @@server_id |+-------------+|           3 |+-------------+[[email protected] ~]# mysql -usql -h172.16.0.154 -p111111 -P6033 -e "select @@server_id;"+-------------+| @@server_id |+-------------+|           6 |+-------------+

  注意：若主從串連不成功，嘗試更改防火牆策略或使用者授權

mysql的複製叢集，及讀寫分離