上海网站建设安全,网页美工设计一套多少钱,网站怎么做移动图片不显示不出来,曹县网站开发公司MySQL笔记-高可用方案一、概述MYSQL高可用方案有多种#xff0c;本次针对其中部分方案进行实践。包括主从#xff0c;双主#xff0c;myqlkeepalived, mysqlmycatkeepalived。纸上得来终觉浅#xff0c;亲自实验一下。环境信息注意两台机器时间需要保持同步#xff0c;网络…MySQL笔记-高可用方案一、概述MYSQL高可用方案有多种本次针对其中部分方案进行实践。包括主从双主myqlkeepalived, mysqlmycatkeepalived。纸上得来终觉浅亲自实验一下。环境信息注意两台机器时间需要保持同步网络和端口权限临时关闭防火墙systemctl stop firewalld.service服务器环境:CentOS7.6MySQL版本mysql-community-server-5.7.28-1mysql, master192.168.1.100mysql, slave192.168.1.101二、主从复制(一主一从)2.1、配置参数(my.cnf)注意修改后需要重启数据库服务mysql主从复制重要配置参数说明(my.cnf)#通用重要配置server-id1 # 不能和其他重复必须全局唯一log-binmysql-bin#从库重要配置relay_logmysql-relay-binlog-slave-updates1#其他重要参数binlog-ignore-dbmysqlreplicate-ignore-dbmysqlmax_binlog_size1Gexpire_logs_days15 #日志保留天数我的Master完整配置(my.cnf)[mysqld]server-id1#binlog_formatROW#log-binmysql-binlog-bin/var/lib/mysql/mysql-bin#innodb_flush_log_at_trx_commit1#sync_binlog1max_binlog_size1Gexpire_logs_days15 #日志保留天数binlog-ignore-dbmysqlbinlog-ignore-dbinformation_schemabinlog-ignore-dbperformance_schemabinlog-ignore-dbsys# 只同步哪些数据库除此之外其他不同步# binlog-do-dbtestdbdatadir/var/lib/mysqlsocket/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links0log-error/var/log/mysqld.logpid-file/var/run/mysqld/mysqld.pidmax_connections3000lower_case_table_names1max_allowed_packet40Mwait_timeout1800interactive_timeout1800max_connect_errors3000sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION[mysql]default-character-setutf8mb4[client]default-character-setutf8mb4[mysqldump]userlocalpassword123456我的Slave完整配置(my.cnf)[mysqld]server-id2#log-binmysql-binlog-bin/var/lib/mysql/mysql-binlog-slave-updates1 # 在作为从数据库的时候有写入操作也要更新二进制日志文件(主主配置一定要)#innodb_flush_log_at_trx_commit1#sync_binlog1max_binlog_size1Gexpire_logs_days15 #日志保留天数# 不同步mysqlinformation_schemaperformance_schema和sys库replicate-ignore-dbmysqlreplicate-ignore-dbinformation_schemareplicate-ignore-dbperformance_schemareplicate-ignore-dbsysrelay_log/var/lib/mysql/mysql-relay-bin#read_only1#Enhanced Multi-Threaded Slave配置#根据实际情况决定开启多少个线程用于主从复制slave_parallel_workers4#基于组提交的并行复制方式slave_parallel_typeLOGICAL_CLOCKmaster_info_repositoryTABLErelay_log_info_repositoryTABLErelay_log_recoveryONdatadir/var/lib/mysqlsocket/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links0log-error/var/log/mysqld.logpid-file/var/run/mysqld/mysqld.pidmax_connections3000lower_case_table_names1max_allowed_packet40Mwait_timeout1800interactive_timeout1800max_connect_errors3000sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION[mysql]default-character-setutf8mb4[client]default-character-setutf8mb4[mysqldump]userlocalpassword1234562.2、Master数据库创建复制用户并授权mysql create user repl identified by 123456;Query OK, 0 rows affected (0.03 sec)mysql grant replication slave on *.* to repl% identified by 123456;Query OK, 0 rows affected, 1 warning (0.03 sec)mysql flush privileges;Query OK, 0 rows affected (0.04 sec)mysql show master status;--------------------------------------------------------------------------------------------------------------| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |--------------------------------------------------------------------------------------------------------------| mysql-bin.000001 | 1078 | | mysql,information_schema,performance_schema,sys | |--------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)2.3、Slave数据库指定Master及启用slavemysql change master to master_host192.168.1.100,master_port3306,master_userrepl,master_password123456,master_log_filemysql-bin.000001,master_log_pos1078;Query OK, 0 rows affected, 2 warnings (0.03 sec)mysql start slave;Query OK, 0 rows affected (0.00 sec)mysql show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.100Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1078Relay_Log_File: 0002-relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB: mysql,information_schema,performance_schema,sysReplicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 1078Relay_Log_Space: 526Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: 77f466ff-926e-11ea-bbba-408d5c963463Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1 row in set (0.00 sec)2.3、验证主从复制主库创建测试库、表和数据mysql CREATE DATABASE testdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;Query OK, 1 row affected (0.03 sec)mysql CREATE TABLE testdb.t_user(id int(11) NOT NULL AUTO_INCREMENT,name varchar(255) NULL,PRIMARY KEY (id));Query OK, 0 rows affected (0.21 sec)mysql INSERT INTO testdb.t_user(name) VALUES (jack);Query OK, 1 row affected (0.07 sec)mysql select * from testdb.t_user;----------| id | name |----------| 1 | jack |----------1 row in set (0.00 sec)mysql show master status;--------------------------------------------------------------------------------------------------------------| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |--------------------------------------------------------------------------------------------------------------| mysql-bin.000001 | 1813 | | mysql,information_schema,performance_schema,sys | |--------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)从库查看同步情况mysql show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventRead_Master_Log_Pos: 1813Relay_Log_File: 0002-relay-bin.000002Relay_Log_Pos: 1055Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesExec_Master_Log_Pos: 1813Relay_Log_Space: 1261...mysql show databases;--------------------| Database |--------------------| information_schema || mysql || performance_schema || sys || testdb |--------------------5 rows in set (0.00 sec)mysql select * from testdb.t_user;----------| id | name |----------| 1 | jack |----------1 row in set (0.00 sec)三、双主配置3.1 配置说明实验环境服务器环境:CentOS7.6MySQL版本mysql-community-server-5.7.28-1mysql,master1192.168.1.100mysql,master2192.168.1.101双主与一主一从配置差不多区别是1.两台机器都配置对方为从即在master1上执行change master to master_host192.168.1.101,master_port3306,master_userrepl,master_password123456,master_log_filemysql-bin.000001,master_log_pos1098;在master2上执行change master to master_host192.168.1.100,master_port3306,master_userrepl,master_password123456,master_log_filemysql-bin.000001,master_log_pos1098;2.两方都配置replicate-ignore-dbbinlog-ignore-db3.为保险起见调整两方自增ID规则(非必须)把两个节点的auto_increment_increment(自增步长)和auto_increment_offset(字增起始值)设置成不同值其目的是为了避免master节点意外宕机时可能会有部分binlog未能及时复制到slave上被应用从而会导致slave新写入数据的自增值和原master上冲突因此一开始就错开#masterA自增长IDauto_increment_offset1auto_increment_increment2 #奇数ID#masterB自增加IDauto_increment_offset2auto_increment_increment2 #偶数ID3.2 完整配置(my.cnf)master1[mysqld]server-id1#binlog_formatROW#log-binmysql-binlog-bin/var/lib/mysql/mysql-bin#innodb_flush_log_at_trx_commit1#sync_binlog1max_binlog_size1G#日志保留天数expire_logs_days15binlog-ignore-dbmysqlbinlog-ignore-dbinformation_schemabinlog-ignore-dbperformance_schemabinlog-ignore-dbsys# 不同步mysqlinformation_schemaperformance_schema和sys库replicate-ignore-dbmysqlreplicate-ignore-dbinformation_schemareplicate-ignore-dbperformance_schemareplicate-ignore-dbsysauto_increment_offset1#奇数IDauto_increment_increment2# 只同步哪些数据库除此之外其他不同步# binlog-do-dbtestdbdatadir/var/lib/mysqlsocket/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links0log-error/var/log/mysqld.logpid-file/var/run/mysqld/mysqld.pidmax_connections3000lower_case_table_names1max_allowed_packet40Mwait_timeout1800interactive_timeout1800max_connect_errors3000sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION[mysql]default-character-setutf8mb4[client]default-character-setutf8mb4[mysqldump]userlocalpassword1234563.2 完整配置(my.cnf)master2[mysqld]server-id2#log-binmysql-binlog-bin/var/lib/mysql/mysql-binlog-slave-updates1 # 在作为从数据库的时候有写入操作也要更新二进制日志文件(主主配置一定要)#innodb_flush_log_at_trx_commit1#sync_binlog1max_binlog_size1G#日志保留天数expire_logs_days15binlog-ignore-dbmysqlbinlog-ignore-dbinformation_schemabinlog-ignore-dbperformance_schemabinlog-ignore-dbsys# 不同步mysqlinformation_schemaperformance_schema和sys库replicate-ignore-dbmysqlreplicate-ignore-dbinformation_schemareplicate-ignore-dbperformance_schemareplicate-ignore-dbsysauto_increment_offset2#偶数IDauto_increment_increment2relay_log/var/lib/mysql/mysql-relay-bin#read_only1#Enhanced Multi-Threaded Slave配置#根据实际情况决定开启多少个线程用于主从复制slave_parallel_workers4#基于组提交的并行复制方式slave_parallel_typeLOGICAL_CLOCKmaster_info_repositoryTABLErelay_log_info_repositoryTABLErelay_log_recoveryONdatadir/var/lib/mysqlsocket/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links0log-error/var/log/mysqld.logpid-file/var/run/mysqld/mysqld.pidmax_connections3000lower_case_table_names1max_allowed_packet40Mwait_timeout1800interactive_timeout1800max_connect_errors3000sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION[mysql]default-character-setutf8mb4[client]default-character-setutf8mb4[mysqldump]userlocalpassword1234563.3 配置同步#master1 (192.168.1.100):mysql create user repl identified by 123456;mysql grant replication slave on *.* to repl% identified by 123456;mysql change master to master_host192.168.1.101,master_port3306,master_userrepl,master_password123456,master_log_filemysql-bin.000001,master_log_pos1098;mysql start slave;#master1 (192.168.1.101):mysql create user repl identified by 123456;mysql grant replication slave on *.* to repl% identified by 123456;mysql change master to master_host192.168.1.100,master_port3306,master_userrepl,master_password123456,master_log_filemysql-bin.000001,master_log_pos1098;mysql start slave;3.4 验证双主# 连接 master1 (192.168.1.100):mysql CREATE DATABASE testdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;mysql CREATE TABLE testdb.t_user(id int(11) NOT NULL AUTO_INCREMENT,name varchar(255) NULL,PRIMARY KEY (id));mysql INSERT INTO testdb.t_user(name) VALUES (jack);mysql INSERT INTO testdb.t_user(name) VALUES (rose);# 连接 master2 (192.168.1.101):mysql select * from testdb.t_user;----------| id | name |----------| 1 | jack || 3 | rose |----------INSERT INTO testdb.t_user(name) VALUES (tony);INSERT INTO testdb.t_user(name) VALUES (alice);# 连接 master1 (192.168.1.100):mysql select * from testdb.t_user;-----------| id | name |-----------| 1 | jack || 3 | rose || 4 | tony || 6 | alice |-----------4 rows in set (0.00 sec)至此双主环境配置和验证完毕四、基于Keeplived配置双主高可用4.1、实验环境实验环境基于上述双主配置服务器环境:CentOS7.6MySQL版本mysql-community-server-5.7.28-1mysql,master1192.168.1.100mysql,master2192.168.1.101虚拟mysql-IP(VIP),192.168.1.102验证方式通过虚拟IP(192.168.1.102:3306)连接访问mysql,然后插入记录期望记录ID为奇数然后关闭master1,期望通过虚拟IP(192.168.1.102:3306)仍能连接访问MYSQL服务再插入记录期望记录ID为偶数。4.2、Keepalived安装一、yum安装方式yum install keepalived -y二、编译安装方式# yum -y install gcc openssl-devel libnl libnl-devel libnfnetlink-devel net-tools# cd /usr/local/src/# wget https://www.keepalived.org/software/keepalived-2.0.20.tar.gz# mv keepalived-2.0.20 /usr/local/keepalived# cd /usr/local/keepalived/# ./configure# make make install# mkdir /etc/keepalived# cp /usr/local/keepalived/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/# cp /usr/local/keepalived/keepalived/etc/init.d/keepalived /etc/rc.d/init.d/# cp /usr/local/keepalived/keepalived/etc/sysconfig/keepalived /etc/sysconfig/# cp /usr/local/sbin/keepalived /usr/sbin/安装参考 CentOS安装Keepalived https://www.cnblogs.com/kingsonfu/p/11392470.html服务启动和暂停# service keepalived start 启动服务# service keepalived stop //停止服务# service keepalived status //查看服务状态查看keepalived日志# tail -f /var/log/message4.3 mysql-keepalived配置采用keepalived作为高可用方案时两个节点最好都设置成BACKUP模式避免因为意外情况下相互抢占导致两个节点内写入相同的数据而引发冲突master1配置(/etc/keepalived/keepalived.conf)! Configuration File for keepalivedglobal_defs {notification_email {acassenfirewall.locfailoverfirewall.locsysadminfirewall.loc}notification_email_from Alexandre.Cassenfirewall.locsmtp_server 127.0.0.1smtp_connect_timeout 30router_id MYSQL_HA}vrrp_instance VI_1 {state BACKUPinterface p4p1virtual_router_id 51priority 100advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.1.102/24}}virtual_server 192.168.1.102 3306 {delay_loop 6protocol TCPreal_server 192.168.1.100 3306 {notify_down kill -9 $(cat /var/run/keepalived.pid)TCP_CHECK {connect_port 3306connect_timeout 3nb_get_retry 3delay_before_retry 3}}}master2配置(/etc/keepalived/keepalived.conf)! Configuration File for keepalivedglobal_defs {notification_email {acassenfirewall.locfailoverfirewall.locsysadminfirewall.loc}notification_email_from Alexandre.Cassenfirewall.locsmtp_server 127.0.0.1smtp_connect_timeout 30router_id MYSQL_HA}vrrp_instance VI_1 {state BACKUPinterface p4p1virtual_router_id 51priority 100advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.1.102/24}}virtual_server 192.168.1.102 3306 {delay_loop 6protocol TCPreal_server 192.168.1.101 3306 {notify_down kill -9 $(cat /var/run/keepalived.pid)TCP_CHECK {connect_port 3306connect_timeout 3nb_get_retry 3delay_before_retry 3}}}4.4、验证-五、基于KeepalivedMyCat配置双主高可用5.1、实验环境实验环境基于上述双主配置环境服务器环境:CentOS7.6MySQL版本mysql-community-server-5.7.28-1MyCat版本Mycat-server-1.6.7.4-release-20200105164103-linuxmysql, master1192.168.1.100mysql, master2192.168.1.101mycat, mycat1: 192.168.1.1025.2、安装配置MyCatwget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gztar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gzcd mycat5.3、MyCat配置(最简)mycat主要配置 server.xml和schema.xml两个即可。这里需要注意相应mysql用户需要能其他服务器可访问-- 默认root用户其他机器无法访问GRANT ALL PRIVILEGES ON *.* TO root% IDENTIFIED BY 123456;/conf/server.xml123456testdb/conf/schema.xmldbTypemysql dbDrivernativeselect user()dataHost标签上属性释义balance:负载均衡类型0不开启读写分离机制所有读操作都发送到当前可用的writeHost上1全部的readHost与stand by writeHost参与select语句的负载均衡2所有读操作都随机在writeHost、readHost上分发3所有读请求随机分发到writeHost对应的readHost执行writeHost不负担读压力writeType:负载均衡类型0所有写操作发送到配置的第一个writeHost当第一个writeHost宕机时切换到第二个writeHost重新启动后以切换后的为准切换记录在配置文件dnindex.properties中1所有写操作都随发送到配置的writeHost2尚未实现switchType:切换方式-1不自动切换1自动切换(默认)2基于MySql主从同步的状态来决定是否切换5.4、启动MyCat./bin/mycat start查看日志logs目录下 有 mycat.log switch.log wrapper.log5.5、验证MyCat验证方式通过mycat端口8066连接mysql, 插入记录期望记录ID为奇数然后停掉192.168.1.100上mysql服务,然后再插入记录期望记录ID为偶数第一步插入3条记录mysql INSERT INTO testdb.t_user(name) VALUES (mycat1);mysql INSERT INTO testdb.t_user(name) VALUES (mycat2);mysql INSERT INTO testdb.t_user(name) VALUES (mycat3);mysql select * from testdb.t_user;------------| id | name |------------| 1 | jack || 3 | rose || 4 | tony || 6 | alice || 7 | mycat1 || 9 | mycat2 || 11 | mycat3 |------------7 rows in set (0.00 sec)ID都是奇数说明请求在master1上第二步停止master1,再插入3条记录mysql INSERT INTO testdb.t_user(name) VALUES (mycat4);mysql INSERT INTO testdb.t_user(name) VALUES (mycat5);mysql INSERT INTO testdb.t_user(name) VALUES (mycat6);mysql select * from testdb.t_user;------------| id | name |------------| 1 | jack || 3 | rose || 4 | tony || 6 | alice || 7 | mycat1 || 9 | mycat2 || 11 | mycat3 || 12 | mycat4 || 14 | mycat5 || 16 | mycat6 |------------10 rows in set (0.00 sec)ID都是偶数说明请求都在master2至此MyCat验证完毕5.7、通过Keepalived配置MyCat高可用实验环境服务器环境:CentOS7.6MySQL版本mysql-community-server-5.7.28-1MyCat版本Mycat-server-1.6.7.4-release-20200105164103-linuxmysql, master1192.168.1.100mysql, master2192.168.1.101mycat, mycat1: 192.168.1.103mycat, mycat2: 192.168.1.104虚拟mycat-IP(VIP): 192.168.1.105基于上面mysql双主和mycat配置(mycat1,mycat2配置完全相同)验证方式通过连接 192.168.1.105:8066 访问MySQL服务通过切换关闭mycat1、mycat2服务验证mycat的高可用性。5.8、附mycat的keepalived配置mycat1-keepalived配置(/etc/keepalived/keepalived.conf)! Configuration File for keepalivedglobal_defs {notification_email {acassenfirewall.locfailoverfirewall.locsysadminfirewall.loc}notification_email_from Alexandre.Cassenfirewall.locsmtp_server 127.0.0.1smtp_connect_timeout 30router_id MYCAT_HA}vrrp_instance VI_1 {state MASTERinterface p4p1virtual_router_id 53priority 100advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.1.105/24}}virtual_server 192.168.1.105 8066 {delay_loop 6protocol TCPreal_server 192.168.1.103 8066 {notify_down kill -9 $(cat /var/run/keepalived.pid)TCP_CHECK {connect_port 8066connect_timeout 3nb_get_retry 3delay_before_retry 3}}}mycat2-keepalived配置(/etc/keepalived/keepalived.conf)! Configuration File for keepalivedglobal_defs {notification_email {acassenfirewall.locfailoverfirewall.locsysadminfirewall.loc}notification_email_from Alexandre.Cassenfirewall.locsmtp_server 127.0.0.1smtp_connect_timeout 30router_id MYCAT_HA}vrrp_instance VI_1 {state BACKUPinterface p4p1virtual_router_id 53priority 50advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.1.105/24}}virtual_server 192.168.1.105 8066 {delay_loop 6protocol TCPreal_server 192.168.1.104 8066 {notify_down kill -9 $(cat /var/run/keepalived.pid)TCP_CHECK {connect_port 8066connect_timeout 3nb_get_retry 3delay_before_retry 3}}}六、双主双从读写分离dbTypemysql dbDrivernativeselect user()七、附其他一些配置参考mycat-server.xml123456testdbkeepalived.conf! Configuration File for keepalivedglobal_defs {notification_email {acassenfirewall.locfailoverfirewall.locsysadminfirewall.loc}notification_email_from Alexandre.Cassenfirewall.locsmtp_server 127.0.0.1smtp_connect_timeout 30router_id web1vrrp_skip_check_adv_addr# vrrp_strictvrrp_garp_interval 0vrrp_gna_interval 0}vrrp_script check_mycat {script /etc/keepalived/mycat_check.shinterval 2weight 2}vrrp_instance VI_1 { //实例定义虚拟路由state MASTER //节点的初始状态主为master备为backupinterface ens160 //绑定虚拟ip的网络接口必须与本地ip所在接口相同virtual_router_id 51 //虚拟路由id号两个节点必须一样priority 100 //节点优先级master要比backup高advert_int 1 //两个节点发送组播信息间隔时间主备需要设置相同的时间默认为秒authentication { //配置认证auth_type PASS //PASS即为密码认证auth_pass 1111 //设置密码可自定义}track_script {check_mycat}virtual_ipaddress { //设置虚拟ip(VIP)两个节点需要一样192.168.1.105}}------------------mycat_check.sh#!/bin/bashresultnc -v -z $1 $2flagsucceededif [[ $result ~ $flag ]]thenexit 0else;exit 1fimycat_check.sh#!/bin/bashresultnc -v -z $1 $2flagsucceededif [[ $result ~ $flag ]]thenexit 0else;exit 1fi八、其他MySQL高可用方案1.MGR(MySQL Group Replication、Galera ClusterMySQL Cluster、PXC(Percona XtraDB Cluster)2.客户端解决方案(应用层)TDDL、 Sharding-Jdbc (常用shardding-jdbc)3.中间件解决方案(代理层)mysql proxy、mycat、altas ,ProxySQL4.CanalOtter九、相关参考
