mysql主從復制

1. 首先配置主從復制結構
2. 主節點：
   1. # vim /etc/my.cnf.d/server.cnf

[mysqld]

server_id=1

log_bin=master-bin

innodb_file_per_table=on

skip_name_resolve=on

1. # systemctl start mairadb
2. mysql> grant replication slave,replication client on *.* to ‘USERNAME’@’172.16.%.%’ identified by ‘PASSWORD’
3. mysql> flush privileges;
4. mysql> install plugin rpl_semi_sync_master soname ‘semisync_master.so’; rpl_semi_sync_master：插件的名稱；semisync_master.so：插件文件的名稱
5. mysql> show global variables like ‘%semi%’; 插件安裝完后就多了些參數
   1. rpl_semi_sync_master_enabled ：半同步復制的功能，master端是否啟動
   2. rpl_semi_sync_master_timeout ：超時時間，同步復制時需要從節點必須完成數據重放后，主節點才能釋放，如果從節點一直不做響應，超過這個時間，主節點釋放，并降級為異步，單位ms
   3. rpl_semi_sync_master_wait_no_slave ：同步復制時，如果沒有一個從節點連上來時，是否等待，on表示等待
6. mysql> set @@global.rpl_semi_sync_master_enabled=on;
7. mysql> show global status like ‘%semi%’; 查看到統計數據，基本都是一些計數器
   1. Rpl_semi_sync_master_clients
   2. Rpl_semi_sync_master_net_avg_wait_time 主節點平均等待時間，總時間/等待次數=平均時間，如果這個數值過大，可能網絡有問題
   3. Rpl_semi_sync_master_net_wait_time 主節點等待和從節點同步復制的整個網絡傳輸過程的總時間
   4. Rpl_semi_sync_master_net_waits 主節點等待次數
8. 測試：在主節點上添加數據庫或表，并查看status統計數據
9. 從節點：
   1. # vim /etc/my.cnf.d/server.cnf

[mysqld]

server_id=2

relay_bin=relay-log

innodb_file_per_table=on

skip_name_resolve=on

1. mysql> change master to master_host=’172.16.100.67’ , master_user=’USERNAME’ , master_password=’PASSWD’ , master_log_file=’master-bin.000003’ , master_log_pos=496;
2. mysql> start slave;
3. mysql> install plugin rpl_semi_sync_slave soname ‘semisync_slave.so’;
4. mysql> show plugins; 可以查看到對應的插件安裝完成
5. mysql> show global variables like ‘%semi%’;
   1. rpl_semi_sync_slave_enabled ：半同步復制功能，slave端是否開啟
6. mysql> set @@global.rpl_semi_sync_slave_enabled=on; 設定完后并不能立即從異步升級為同步，需要將連接主節點的線程io_thread重啟，才能在主節點上看到“連接到半同步復制主節點的客戶端數量”的狀態數據rpl_semi_sync_master_clients為1
7. mysql> stop slave io_thread;
8. mysql> start slave io_thread;
9. 下面添加節點3
10. # vim /etc/my.cnf.d/server.cnf

11. [mysqld]

    server_id=3

    relay_bin=relay-log

    innodb_file_per_table=on

    skip_name_resolve=on

12. 先對節點3做全備恢復：因為節點3是從中途加入的，那么就需要將主節點先做全備
    1. 在主節點上：# mysqldump –all-databases –master-data=2 –routine –trigger –event –lock-all-tables > all.sql
       1. MariaDB [(none)]> show binary logs;

13. +——————-+———–+

    | Log_name          | File_size |

    +——————-+———–+

    | master-bin.000001 |     30343 |

    | master-bin.000002 |   1038814 |

    | master-bin.000003 |      1013 |

    | master-bin.000004 |       332 |

    +——————-+———–+

14. 將all.sql復制到節點3上：# scp all.sql root@172.16.8.93:/root
15. # mysql < all.sql
16. 再對節點3做二進制恢復
    1. mysql> change master to master_host='172.16.8.95',master_user='repluser',master_password='replpass',master_log_file='master-bin.000004',master_log_pos=332;

1. 加入讀寫分離節點：
   1. ProxySQL：
      1. 在172.16.8.91上安裝proxysql：# yum install proxysql-1.4.13-1-centos7.x86_64.rpm
      2. # rpm -ql proxysql
         1. /etc/init.d/proxysql
         2. /etc/proxysql.cnf proxysql的配置文件
         3. /usr/bin/proxysql
      3. 配置有兩種方式：一是連入proxysql將數據插入到main數據庫的表中，二是編輯配置文件
      4. 編輯配置文件：# vim /etc/proxysql.conf

datadir="/var/lib/proxysql" 定義工作目錄

admin_variables= 定義proxysql管理進程的屬性

{

       admin_credentials="admin:admin" 指定管理proxsql服務的用戶名和密碼

       mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock" proxysql管理進程監聽的ip地址和端口（客戶端和proxysql通信的管理IP和端口），用以管理員的連接；

     refresh_interval=2000

       debug=true

}

mysql_variables= 定義proxysql服務進程的工作屬性

{

        threads=4 服務進程提供的線程數

        max_connections=2048 響應客戶端的最大并發連接數

        default_query_delay=0

        default_query_timeout=36000000

        have_compress=true

        poll_timeout=2000

      interfaces="0.0.0.0:6033;/tmp/mysql.sock" 

        interfaces="0.0.0.0:6033"

        default_schema="information_schema"

        stacksize=1048576

        server_version="5.5.30"

        connect_timeout_server=3000

        monitor_username="monitor"

        monitor_password="monitor"

        monitor_history=600000

        monitor_connect_interval=60000

        monitor_ping_interval=10000 proxysql時隔多久發送一次心跳信息給后端mysql服務器

        monitor_read_only_interval=1500

        monitor_read_only_timeout=500

        ping_interval_server_msec=120000

        ping_timeout_server=500

        commands_stats=true

        sessions_sort=true

        connect_retries_on_failure=10

}

mysql_servers = 定義反代到的后端mysql服務器

(

       {

               address = "172.16.8.95" 定義反代到的后端mysql服務器的IP（proxysql和后端mysql通信的ip和端口）

               port = 3306           

               hostgroup = 0            定義這個主機所屬組的id，主節點和從節點的組id需要不同， no default, required

               status = "ONLINE"     # default: ONLINE

               weight = 1            # default: 1 權重

               compression = 0       # default: 0 壓縮與否

 #max_replication_lag = 10   定義從節點復制時延遲主節點多少時間，防止主節點誤操作，默認0不延遲

       }, 各服務器之間用逗號隔開，最后一個無需逗號

)

mysql_users: 定義proxysql使用什么用戶來連接后端mysql服務器，需要在mysql中事先為該用戶授權，該用戶為mysql上的用戶

(

{

username= 指定連接的用戶名，（構建好了主從結構后，可在主節點上授權，從節點上自動就授權了），mysql> grant all on  *.* to 'tom'@'172.16.8.92' identified by 'tom';

password=

default_hostgroup=# 默認連接到哪個組上

max_connections=# proxysql和后端mysql服務器的最大并發連接數

default_schema=”DATABASE” 默認連接到的數據庫

active=#

}, 多個用戶使用逗號隔開

)

mysql_query_rules: 定義語句路由規則

(

{

rule_id=#

active=#

match_pattern=”^select .* for update$” 匹配語句

destination_hostgroup=0 指定路由到組的id

apply=1

}

)

mysql_replication_hostgroups= 定義組的屬性

(

        {

                writer_hostgroup=0 定義哪個組為寫

                reader_hostgroup=1 定義哪個組為讀

                comment="test repl 1" 注釋

       }

)

1. 測試：
   1. 配置完后重啟服務：# systemctl restart proxysql
   2. 在任意一臺安裝了mysql客戶端的主機上連接到proxsql的服務進程：# mysql -utom -ptom -h172.16.8.91 -P3306
   3. 通過proxysql對數據做修改，可以看到主節點和從節點上都有數據變化

1. 安裝配置 MHA
   1. 準備基于 ssh 互信通信環境
      1. MHA 集群中的各節點彼此之間均需要基于 ssh 互信通信，以實現遠程控制及數據管理功能。簡單起見，可在 Manager 節點生成密鑰對兒，并設置其可遠程連接本地主機后，將私鑰文件及 authorized_keys 文件復制給余下的所有節點即可。
      2. 在任意一臺節點操作，下面在manager節點上操作
         1. ~]# ssh-keygen -t rsa -P ''
         2. ~]# cat .ssh/id_rsa.pub >> .ssh/authorized_keys
         3. ~]# chmod 600 .ssh/authorized_keys
         4. ~]# scp -p .ssh/id_rsa .ssh/authorized_keys   root@node95:/root/.ssh/
         5. ~]# scp -p .ssh/id_rsa .ssh/authorized_keys   root@node94:/root/.ssh/
         6. ~]# scp -p .ssh/id_rsa .ssh/authorized_keys   root@node93:/root/.ssh/
         7. 如果節點較多時，以上步驟也可以直接用for循環實現
            1. # for i in 91 92 93 94 95 96 97;do scp -p /root/.ssh/id_rsa /root/.ssh/authorized_keys root@172.16.8.$i:/root/.ssh/ ;done
      3. 注意：請事先確保所有機器上的/root/.ssh 目錄存在，且在/root/.ssh 目錄下不能有id_rsa.pub，否則有id_rsa.pub的主機連接其他機器還是需要輸入密碼。或者，可以直接把manager上的id_rsa.pub也都拷貝到其他機器上就不存在這個問題了。
   2. 安裝 MHA
      1. 除了源碼包，MHA官方也提供了rpm 格式的程序包。CentOS 7 系統可直接使用適用于 el6 的程序包。另外，MHA Manage 和 MHA Node 程序包的版本并不強制要求一致
      2. Manager節點安裝manager包：注意，如果 mha4mysql-manager安裝不成功，建議先安裝mha4mysql-node后安裝mha4mysql-manager，因為存在依賴關系。另外，server端也沒有啟動腳本，需要手動啟動
         1. ~]# yum install mha4mysql-manager-0.56-0.el6.noarch.rpm
      3. 所有節點，包括 Manager，安裝node包：
         1. ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm
   3. 初始化 MHA
      1. Manger 節點需要為每個監控的 master/slave 集群提供一個專用的配置文件，而所有的master/slave 集群也可共享全局配置。全局配置文件默認為/etc/masterha_default.cnf，其為可選配置。如果僅監控一組 master/slave 集群，也可直接通過application 的配置來提供各服務器的默認配置信息。而每個application 的配置文件路徑為自定義，例如，本示例中將使用/etc/masterha/app1.cnf，其內容如下所示。
      2. 首先創建相關目錄首先創建相關目錄：# mkdir  /etc/masterha
      3. # vim /etc/masterha/app1.cnf

[server default]

user=mhaadmin MySQL 管理員，用來管理各個節點

password=mhaadmin MySQL 管理員mhaadmin的密碼

manager_workdir=/data/masterha/app1   這個目錄/data/masterha/app1不需要創建，不存在時會自動創建

manager_log=/data/masterha/app1/manager.log

remote_workdir=/data/masterha/app1 指各個node節點的工作目錄

ssh_user=root 基于密鑰連接，所以不需要配置密碼

ssh_port=22   這個選項是全局，如果不定義，就是使用默認的端口22，可以在每個節點上分別再定義端口，基于安全考慮，可以把22端口改成其他的

repl_user=repluser

repl_password=replpass

ping_interval=1 多長時間對各個節點發送一次心跳信息

[server1]

hostname=172.16.8.95

#ssh_port=22022 這個選項如果不定義，就是默認的22端口

candidate_master=1 將來是否可以成為主節點，如果不定義，就不能成為候選的主節點

[server2]

hostname=172.16.8.94

#ssh_port=22022

candidate_master=1

[server3]

hostname=172.16.8.93

#ssh_port=22022

#no_master=1 如果定義no_master就不能成為主節點

1. 檢測各節點間 ssh 互信通信配置是否 OK：–conf指定配置文件路徑
   1. ~]# masterha_check_ssh –conf=/etc/masterha/app1.cnf
   2. 輸出信息最后一行類似如下信息，表示其通過檢測。

[info] All SSH connection tests passed successfully.

1. 檢查管理的 MySQL 復制集群的連接配置參數是否 OK：
   1. ~]# masterha_check_repl –conf=/etc/masterha/app1.cnf
   2. 輸出信息如下所示，最后一行的“Health is OK”信息表示通過檢測。

Tue Jun 6 17:22:48 2017 – [info] Slaves settings check done.

Tue Jun 6 17:22:48 2017 – [info]

172.16.8.95(172.16.8.95:3306) (current master)

+–172.16.8.94(172.16.8.94:3306)

+–172.16.8.93(172.16.8.93:3306)

…… ……

MySQL Replication Health is OK.

1. 啟動 MHA
   1. 后臺運行，注意，masterha_manager沒有啟動的程序，需要手動啟動服務
      1. ~]# nohup masterha_manager –conf=/etc/masterha/app1.cnf > /data/masterha/app1/manager.log 2>&1 &
   2. 啟動成功后，可通過如下命令來查看 master 節點的狀態。
      1. ~]# masterha_check_status –conf=/etc/masterha/app1.cnf
      2. app1 (pid:4978) is running(0:PING_OK), master:172.16.8.95
      3. 上面的信息中“app1 (pid:4978) is running(0:PING_OK)”表示 MHA 服務運行 OK，否則，則會顯示為類似“app1 is stopped(1:NOT_RUNNING).”。
   3. 如果要停止 MHA，需要使用 masterha_stop 命令。
      1. ~]# masterha_stop –conf=/etc/masterha/app1.cnf Stopped app1 successfully.