MySQL main master +keepalived high availability

MySQL main master +keepalived high Availability 1. Two hosts

host:db1  外网：11.0.0.51  内网：172.16.1.51db2  外网：11.0.0.52  内网：172.16.1.52

2. Two hosts download MySQL separately

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 Database Operations

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. Create data directory, modify startup

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 configuration is roughly the same, the configuration file needs to be modified

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.log Log_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# Startup Change Password:/etc/init.d/mysqld startmysql-uroot-ppassword:mysql> set password for [email protected] = password (' 123456 ');mysql> flush privileges;

6. Database Master settings (inter-master)

--------------------------DB1 Operation:--------------------------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 Binlo G_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) # # # " This step requires yourself to view the latest Mysql-bin and POS for the main library, below to see "mysql> change Master to > master_host= ' 172.16.1.52 ', > Master_us         Er= ' rep ', > master_password= ' 123456 ', > master_log_file= ' mysql-bin.000024 ', #这个地方是主库的最新mysql-bin                        > master_log_pos= ' 234 '; #主库的最新posmysql > Start slave;mysql> show slave status\g## #生产环境如果要修改数据库架构不建议这么做 for new deployments, turn on Gtid features # # #mysql > Stop Slave; # # #mysql > Change master to master_auto_position=1;-------------------------- DB2 Operation:----------------------------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 Binlo G_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) # # # " This step requires you to view the latest Mysql-bin and POS in the main library, with the view "mysql> change Master to > master_host= ' 172.16.1.51 ', > Master_us Er= ' rep ', > master_password= ' 123456 ', > master_log_file= ' mysql-bin.000015 ', > Master_log_ pos= ' 234 ';mysql> start slave;mysql> show slave status\g## #生产环境不建议这么做, turn on Gtid function # # #mysql > Stop slave;## #mysql > Change Master to Master_auto_position=1; 

7.keepalived installation Configuration

# # #db1和db2分别安装yum Install keepalived-y## #db1的keepalived. conf configuration mkdir/etc/keepalived/scripts-pvim/etc/keepalived/ keepalived.confglobal_defs {router_id db}vrrp_instance vi_1 {State MASTER interface eth1 Virtual_rou                TER_ID 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_time Out protocol TCP real_server 172.16.1.51 3306 {weight 3 notify_down/etc/k eepalived/scripts/mysql_check.sh Tcp_check {connect_timeout 3 nb_get _retry 3 delay_before_retry 3 Connect_port 3306}}}## #此处还要配置一个触发脚本 to detect if the database is alive (my simpleRough, you can write your own connection database judgment) vim/etc/keepalived/scripts/mysql_check.sh#!/bin/bashdb1_num= ' Netstat-luntp|grep 3306|wc-l ' if [ $Db 1_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 configuration Global_defs {Router        _id db}vrrp_instance vi_1 {State BACKUP interface eth1 virtual_router_id 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 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}}}## #触发脚本同上 (in the same situation as the environment, the port is 3306, note that there are script execution permissions)

8. Swaiiow not tested.

###自己停掉数据库查看vip是否飘移

MySQL main master +keepalived high availability