mysql主主+keepalived高可用

標籤：wrr   prot   密碼   ble   1.3   active   usr   pass   lib   

mysql主主+keepalived高可用1.兩台主機

host:db1  外網：11.0.0.51  內網：172.16.1.51db2  外網：11.0.0.52  內網：172.16.1.52

2.兩台主機分別下載mysql

cd /usr/chris/srcwget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.16-linux-glibc2.12-x86_64.tar.gzuseradd mysql -u 550 -s /sbin/nologin -Mtar xf mysql-5.7.16-linux-glibc2.5-x86_64.tar.gzmv mysql-5.7.16-linux-glibc2.5-x86_64 /usr/local/mysql-5.7.16

3.db1資料庫操作

ln -s /usr/local/mysql-5.7.16 /usr/local/mysqlvim /etc/my.cnf #begin[mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONdatadir=/data/mysql/databasedir=/usr/local/mysqlsocket=/tmp/mysql.sockuser=mysqlsymbolic-links=0#slow logslow-query-log=ONslow_query_log_file=/data/mysql/data/log/mysql_slow_query.loglong_query_time=3log_queries_not_using_indexes = OFFlog-slave-updates=truegtid-mode=onenforce-gtid-consistency=trueft_min_word_len = 2net_buffer_length = 8Kmax_allowed_packet = 64Mwait_timeout = 180000interactive_timeout=180000thread_cache_size = 128thread_stack = 512Ktable_open_cache = 512join_buffer_size    = 16Msort_buffer_size    = 16Mlog-error = /data/mysql/log/error.loglog_slave_updates = 1#character_name:valuecharacter-set-server=utf8  collation-server=utf8_general_cilog_bin = OFFlog-bin = mysql-bin#relay-log = mysql-binserver-id = 20#auto_increment_offset = 2#auto_increment_increment = 2max_connections = 2000group_concat_max_len = 2000max_allowed_packet = 1024Minnodb_buffer_pool_size = 512Mread-only=0binlog-ignore-db=mysqlbinlog-ignore-db=information_schemabinlog-ignore-db=performance_schemabinlog-ignore-db=amh[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid###end

4.建立資料目錄，修改啟動

mkdir /data/mysql/{data,log} -pcp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld#啟動指令碼修改vim /etc/init.d/mysqldbasedir=/usr/local/mysqldatadir=/data/mysql/data#環境變數修改（加入mysql路徑）vim ~/.bash_profileMYSQL_HOME=/usr/local/mysqlexport PATH=$JAVA_HOME/bin:$PATH:$MYSQL_HOME/binsource ~/.bash_profile#初始化啟動chown -R mysql:mysql /data/mysql/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data/etc/init.d/mysqld startmysql -uroot -p輸入初始密碼：mysql> set password for [email protected] = PASSWORD(‘123456‘);mysql> flush privileges;###這裡db1資料庫就配置完成了。

5.db2配置大致相同,就是設定檔需要修改

vim /etc/my.cnf[mysqld]sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONdatadir=/data/mysql/databasedir=/usr/local/mysqlsocket=/tmp/mysql.sockuser=mysqlsymbolic-links=0#slow logslow-query-log=ONslow_query_log_file=/data/mysql/data/log/mysql_slow_query.loglong_query_time=3log_queries_not_using_indexes = OFFlog-slave-updates = trueft_min_word_len = 2net_buffer_length = 8Kmax_allowed_packet = 64Mwait_timeout = 180000interactive_timeout=180000thread_cache_size = 128thread_stack = 512Ktable_open_cache = 512join_buffer_size    = 16Msort_buffer_size    = 16Mlog-error = /data/mysql/log/error.loglog_slave_updates = 1#character_name:valuecharacter-set-server=utf8  collation-server=utf8_general_cilog_bin = OFFlog-bin = mysql-bin#relay-log = mysql-binserver-id = 21#auto_increment_offset = 2#auto_increment_increment = 2max_connections = 2000group_concat_max_len = 2000max_allowed_packet = 1024Minnodb_buffer_pool_size = 512Mgtid-mode=onenforce-gtid-consistency=trueread-only=0binlog-ignore-db=mysqlbinlog-ignore-db=information_schemabinlog-ignore-db=performance_schemabinlog-ignore-db=amh[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid#啟動修改密碼：/etc/init.d/mysqld startmysql -uroot -ppassword:mysql> set password for [email protected] = PASSWORD(‘123456‘);mysql> flush privileges;

6.資料庫主主設定（互為主從）

--------------------------db1操作：--------------------------mysql -uroot -p123456mysql> grant replication slave on *.* to ‘rep‘@‘172.16.1.52‘ identified by ‘123456‘;mysql> flush privileges;mysql> show master status\G*************************** 1. row ***************************                         File: mysql-bin.000015                 Position: 234         Binlog_Do_DB:  Binlog_Ignore_DB: mysql,information_schema,performance_schema,amhExecuted_Gtid_Set: 79a90ed3-fa86-11e7-ac66-000c29542a93:1-3,9238586c-fa86-11e7-acab-000c29f6ed58:1-51 row in set (0.00 sec)###【這一步需要自己查看主庫最新mysql-bin和pos,下面有查看】mysql> change master to         > master_host=‘172.16.1.52‘,         > master_user=‘rep‘,         > master_password=‘123456‘,         > master_log_file=‘mysql-bin.000024‘,   #這個地方是主庫的最新mysql-bin         > master_log_pos=‘234‘;                        #主庫的最新posmysql> start slave;mysql> show slave status\G###生產環境如果要修改資料庫結構描述不建議這麼做，適合新部署，開啟gtid功能###mysql> stop slave;###mysql> change master to MASTER_AUTO_POSITION=1;--------------------------db2操作：----------------------------mysql> grant replication slave on *.* to ‘rep‘@‘172.16.1.51‘ identified by ‘123456‘;mysql> flush privileges;mysql> show master status\G*************************** 1. row ***************************                         File: mysql-bin.000024                 Position: 234         Binlog_Do_DB:  Binlog_Ignore_DB: mysql,information_schema,performance_schema,amhExecuted_Gtid_Set: 79a90ed3-fa86-11e7-ac66-000c29542a93:1-3,9238586c-fa86-11e7-acab-000c29f6ed58:1-51 row in set (0.00 sec)###【這一步需要自己查看主庫最新mysql-bin和pos,上面有查看】mysql> change master to         > master_host=‘172.16.1.51‘,         > master_user=‘rep‘,         > master_password=‘123456‘,         > master_log_file=‘mysql-bin.000015‘,         > master_log_pos=‘234‘;mysql> start slave;mysql> show slave status\G###生產環境不建議這麼做，開啟gtid功能###mysql> stop slave;###mysql> change master to MASTER_AUTO_POSITION=1;

7.keepalived安裝配置

###db1和db2分別安裝yum install keepalived -y###db1的keepalived.conf配置mkdir /etc/keepalived/scripts -pvim /etc/keepalived/keepalived.confglobal_defs {router_id db}vrrp_instance VI_1 {        state MASTER        interface eth1        virtual_router_id 61        priority 150        advert_int 1        nopreempt        authentication {                auth_type PASS                auth_pass 1111        }        virtual_ipaddress {                172.16.1.3        }}virtual_server 172.16.1.3 3306 {        delay_loop 2        lb_algo wrr        lb_kind DR         nat_mask 255.255.255.0        persistence_timeout 50        protocol TCP        real_server 172.16.1.51 3306 {                weight 3                notify_down /etc/keepalived/scripts/mysql_check.sh        TCP_CHECK {                        connect_timeout 3                        nb_get_retry 3                        delay_before_retry 3                        connect_port 3306                }        }}###此處還要配置一個觸發指令碼，用來檢測資料庫是否存活（我的很簡單粗暴，你們自己可以寫串連資料庫判斷）vim /etc/keepalived/scripts/mysql_check.sh#!/bin/bashDb1_Num=`netstat -luntp|grep 3306|wc -l`if [ $Db1_Num -eq 0 ];then        /etc/init.d/keepalived stop        echo "Mysql vip is moved."else        echo "Mysql is running."        exit 1fi###指令碼需要加執行許可權chmod +x /etc/keepalived/scripts/mysql_check.sh###db2的keepalived.conf配置    global_defs {        router_id db}vrrp_instance VI_1 {        state BACKUP         interface eth1        virtual_router_id 61        priority 100        advert_int 1        nopreempt        authentication {                auth_type PASS                auth_pass 1111        }        virtual_ipaddress {                172.16.1.3        }}virtual_server 172.16.1.3 3306 {        delay_loop 2        lb_algo wrr        lb_kind DR         nat_mask 255.255.255.0        persistence_timeout 50        protocol TCP        real_server 172.16.1.52 3306 {                weight 3                notify_down /etc/keepalived/scripts/mysql_check.sh        TCP_CHECK {                        connect_timeout 3                        nb_get_retry 3                        delay_before_retry 3                        connect_port 3306                }        }}###觸發指令碼同上（在環境配置一樣的情況下，類似連接埠都是3306，自己注意，還有指令碼的執行許可權）

8.老衲不測試了

###自己停掉資料庫查看vip是否飄移

mysql主主+keepalived高可用