一、說明
前面我們說了mysql的安裝配置,mysql語句使用以及備份恢復mysql數據;本次要介紹的是mysql的主從復制,讀寫分離;及高可用MHA;
環境如下:
master:CentOS7_x64 mysql5.721 172.16.3.175 db1
slave1:CentOS7_x64 mysql5.7.21 172.16.3.235 db2
slave2:CentOS7_x64 mysql5.7.21 172.16.3.235 db3
proxysql/MHA:CentOS7_x64 mysql5.7.21 172.16.3.235 proxysql
架構圖:
說明:
配置測試時為了方便關閉了防火墻頭,selinux安全策略;
現實中請開放防火墻策略;myslqdb的安裝已經有腳本一鍵安裝并配置好;這里就不在重復配置;只對對應的角色貼出對應的配置或安裝與之相關的軟件;
二、主從復制配置
一臺主數據庫,N從節點;從節點開啟兩個線程,通過Slave_IO_Running線程和主節點上有權限的賬號從 主數據庫節點復制binlog日志到本地,能過Slave_SQL_Running線程在本地執行binlog日志,達到主從節點內容同步;
master配置:
| egrep -v '(^$|^#)' /usr/local/mysql/etc/my.cnf[mysqld]datadir=/data1/mysqldbsocket=/tmp/mysql.sockkey_buffer_size = 16Mmax_allowed_packet = 16Mthread_stack = 192Kthread_cache_size = 8query_cache_limit = 1Mquery_cache_size = 64Mquery_cache_type = 1symbolic-links=0innodb_file_per_table=ONskip_name_resolve=ONserver-id = 1log_bin = /data1/mysqldb/mysql-bin.log[mysqld_safe]log-error=/usr/local/mysql/logs/error.logpid-file=/data1/mysqldb/mysql.pid!includedir /usr/local/mysql/etc/my.cnf.d |
創建從節點同步賬號:
| mysql > grant replication client,replication slave on *.* to 'repluser'@'172.16.3.%' identified by 'replpass';mysql > flush privileges;mysql >show master logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 622 | |
主節點上的binlog日志文件及位置;請記下;從節點第一次同步時需要用;slave節點:
| egrep -v '(^$|^#)' /usr/local/mysql/etc/my.cnf[mysqld]datadir=/data1/mysqldbsocket=/data1/mysqldb/mysql.sockkey_buffer_size = 16Mmax_allowed_packet = 16Mthread_stack = 192Kthread_cache_size = 8query_cache_limit = 1Mquery_cache_size = 64Mquery_cache_type = 1symbolic-links=0innodb_file_per_table=ONskip_name_resolve=ONserver-id = 11 #從節點標識ID 各從節點均不一樣 relay_log = relay-logread_only=ON[mysqld_safe]log-error=/usr/local/mysql/log/error.logpid-file=/var/run/mysql/mysql.pid!includedir /usr/local/mysql/etc/my.cnf.d |
啟動mysq數據庫
注意:兩臺從節點的server-id 值不一樣;其他的都一樣;因此從節點只展示一個配置文件;
登錄數據庫并同步數據啟動slave
新聞熱點
疑難解答
