# mysql5.6双主 **Repository Path**: iq_java/mysql_56_dual_master ## Basic Information - **Project Name**: mysql5.6双主 - **Description**: No description available - **Primary Language**: Unknown - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2019-08-14 - **Last Updated**: 2021-11-02 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README 本次MySQL使用5.6.43版本来配置双主（互为主从），并使用keepalived软件实现高可用部分。 使用两台服务器： hostname IP地址 mysql01 192.0.2.81 mysql02 192.0.2.82 安装文件是二进制的tar包：mysql-5.6.43-linux-glibc2.12-x86_64.tar.gz 1、安装数据库 mysql01节点安装 1、解压安装文件，并创建软链接 [root@mysql01 ~]# tar -zxvf mysql-5.6.43-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ [root@mysql01 ~]# cd /usr/local/ [root@mysql01 local]# ln -s mysql-5.6.43-linux-glibc2.12-x86_64/ mysql 2、增加mysql用户和组 [root@mysql01 ~]# groupadd mysql [root@mysql01 ~]# useradd -r -g mysql -s /bin/false mysql 3、修改basedir的主属为mysql，并创建数据目录 [root@mysql01 ~]# chown -R mysql:mysql /usr/local/mysql [root@mysql01 ~]# mkdir -p /data/mysql/data [root@mysql01 ~]# chown -R mysql:mysql /data/mysql/data 4、修改mysql的参数文件 [root@mysql01 ~]# vi /etc/my.cnf [client] socket=/data/mysql/data/mysql.sock [mysqld] datadir=/data/mysql/data log-error=/data/mysql/data/error.log pid-file=/data/mysql/data/mysql.pid character_set_server=utf8 tmpdir=/tmp socket=/data/mysql/data/mysql.sock log_bin=/data/mysql/data/binlog binlog_format=row expire_logs_days=15 sync_binlog=1 innodb_flush_log_at_trx_commit=1 log_bin_trust_function_creators=on server_id=81 # server_id要和另一台区分开，不能是同一个值 auto-increment-increment=2 #因为是双主，自增序列的步调不能冲突 auto-increment-offset=1 #自增主键按1 3 5 7 的方式增长 5、初始化数据库 [root@mysql01 ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql 如果有报错： FATAL ERROR: please install the following Perl modules before executing /usr/local/mysql/scripts/mysql_install_db: Data::Dumper 则需要先安装：autoconf [root@mysql01 ~]# yum -y install autoconf 6、修改环境变量 [root@mysql01 ~]# vi /etc/profile 最下面增加：export PATH=$PATH:/usr/local/mysql/bin [root@mysql01 ~]# source /etc/profile 7、配置启动文件 [root@mysql01 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [root@mysql01 ~]# chmod 755 /etc/init.d/mysqld [root@mysql01 ~]# chkconfig mysqld 8、启动数据库mysql01 [root@mysql01 ~]# /etc/init.d/mysqld status ERROR! MySQL is not running [root@mysql01 ~]# /etc/init.d/mysqld start Starting MySQL. SUCCESS! [root@mysql01 ~]# /etc/init.d/mysqld status SUCCESS! MySQL running (2614) 9、登陆数据库，并修改root密码，5.6版本初始化后没有密码 [root@mysql01 ~]# mysql mysql> use mysql mysql> update user set password=password("123456") where user="root"; mysql> flush privileges; 10、创建主从同步需要的账户 mysql> grant replication slave, reload, super on *.* to 'repl'@'%' identified by 'repl123456'; 11、查看此时主库的日志信息 mysql> show master status ; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000006 | 333 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql02节点安装 1、解压安装文件，并创建软链接 [root@mysql02 ~]# tar -zxvf mysql-5.6.43-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ [root@mysql02 ~]# cd /usr/local/ [root@mysql02 local]# ln -s mysql-5.6.43-linux-glibc2.12-x86_64/ mysql 2、增加mysql用户和组 [root@mysql02 ~]# groupadd mysql [root@mysql02 ~]# useradd -r -g mysql -s /bin/false mysql 3、修改basedir的主属为mysql，并创建数据目录 [root@mysql02 ~]# chown -R mysql:mysql /usr/local/mysql [root@mysql02 ~]# mkdir -p /data/mysql/data [root@mysql02 ~]# chown -R mysql:mysql /data/mysql/data 4、修改mysql的参数文件 [root@mysql02 ~]# vi /etc/my.cnf [client] socket=/data/mysql/data/mysql.sock [mysqld] datadir=/data/mysql/data log-error=/data/mysql/data/error.log pid-file=/data/mysql/data/mysql.pid character_set_server=utf8 tmpdir=/tmp socket=/data/mysql/data/mysql.sock log_bin=/data/mysql/data/binlog binlog_format=row expire_logs_days=15 sync_binlog=1 innodb_flush_log_at_trx_commit=1 log_bin_trust_function_creators=on server_id=82 # server_id要和另一台区分开，不能是同一个值 auto-increment-increment=2 #因为是双主，自增序列的步调不能冲突 auto-increment-offset=2 #自增主键按2 4 6 8 的方式增长 5、初始化数据库 [root@mysql02 ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql 6、修改环境变量 [root@mysql02 ~]# vi /etc/profile 最下面增加：export PATH=$PATH:/usr/local/mysql/bin [root@mysql02 ~]# source /etc/profile 7、配置启动文件 [root@mysql02 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [root@mysql02 ~]# chmod 755 /etc/init.d/mysqld [root@mysql02 ~]# chkconfig mysqld 8、启动数据库mysql01 [root@mysql02 ~]# /etc/init.d/mysqld status ERROR! MySQL is not running [root@mysql02 ~]# /etc/init.d/mysqld start Starting MySQL. SUCCESS! [root@mysql02 ~]# /etc/init.d/mysqld status SUCCESS! MySQL running (8336) 9、登陆数据库，并修改root密码，5.6版本初始化后没有密码 [root@mysql02 ~]# mysql mysql> use mysql mysql> update user set password=password("123456") where user="root"; mysql> flush privileges; 10、创建主从同步需要的账户 mysql> grant replication slave, reload, super on *.* to 'repl'@'%' identified by 'repl123456'; 11、查看此时主库的日志信息 mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000003 | 1724 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 2、配置互为主从 mysql01配置主从环境 配置主从 mysql> CHANGE MASTER TO MASTER_HOST='192.0.2.82', #主库的IP MASTER_PORT=3306, #端口 MASTER_USER='repl', #同步使用的用户名和密码 MASTER_PASSWORD='repl123456', master_log_file='binlog.000003', #指向主库的日志名称 master_log_pos=1724; #日志的位置（偏移量） 启动主从日志应用 mysql> start slave; mysql02配置主从环境 配置主从 mysql> CHANGE MASTER TO MASTER_HOST='192.0.2.81', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='repl123456', master_log_file='binlog.000006', master_log_pos=333; 启动主从日志应用 mysql> start slave; 至此，主从环境就搭建完毕了。 3、配置keepalived 使用keepalived软件，对外提供一个VIP地址，应用系统只需要连接此VIP地址即可，从而实现高可用。 此次配置的是非抢占模式 1、两台服务器都安装keepalived软件 [root@mysql01 ~]# yum install -y keepalived.x86_64 [root@mysql02 ~]# yum install -y keepalived.x86_64 2、修改keepalived 配置文件 mysql01 的keepalived 配置文件结果如下： [root@mysql01 ~]# vi /etc/keepalived/keepalived.conf ! Configuration File for keepalived global_defs { router_id mysql_ha } vrrp_script chk_mysql { script "/etc/keepalived/script/mysql_check.sh" interval 5 timeout 6 weight -20 } vrrp_instance VI_1 { state MASTER nopreempt interface ens33 virtual_router_id 57 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 1111 } track_script { chk_mysql } virtual_ipaddress { 192.0.2.99 } } mysql02 的keepalived 配置文件结果如下： [root@mysql02 ~]# vi /etc/keepalived/keepalived.conf ! Configuration File for keepalived global_defs { router_id mysql_ha } vrrp_script chk_mysql { interval 5 timeout 6 weight -20 } vrrp_instance VI_1 { state BACKUP interface ens33 virtual_router_id 57 priority 99 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.0.2.99 } } 3、编写触发脚本 mysql01 的触发脚本如下： [root@mysql01 ~]# mkdir -p /etc/keepalived/script/ [root@mysql01 ~]# cd /etc/keepalived/script/ keepalive触发条件，当无法ping通一个指定IP，或者mysql端口不存在的时候，执行切换 [root@mysql01 script]# vi mysql_check.sh #!/bin/bash counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l) ping 192.0.2.66 -c5 if [ $? -ne 0 ]||[ "${counter}" -eq 0 ]; then echo 99 exit 1 fi 该脚本增加执行权限： [root@mysql01 script]# chmod +x mysql_check.sh 4、两台服务器都启动keepalived软件 [root@mysql01 ~]# systemctl start keepalived [root@mysql02 ~]# systemctl start keepalived [root@mysql01 ~]# systemctl enable keepalived [root@mysql02 ~]# systemctl enable keepalived 5、测试服务是否正常 查看VIP地址，此时在mysql01上 [root@mysql01 ~]# ip a | grep ens33 2: ens33: mtu 1500 qdisc pfifo_fast state UP qlen 1000 inet 192.0.2.81/24 brd 192.0.2.255 scope global ens33 inet 192.0.2.99/32 scope global ens33 使用VIP，测试连接mysql是否正常 [root@localhost ~]# mysql -urepl -prepl123456 -h192.0.2.99 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.6.43-log MySQL Community Server (GPL) mysql> show slave hosts; +-----------+------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+--------------------------------------+ | 82 | | 3306 | 81 | 8fea6567-8b20-11e9-ae14-000c29f642a0 | +-----------+------+------+-----------+--------------------------------------+ 1 row in set (0.00 sec) 至此，Keepalived搭建完成！！ 4、测试故障转移情况 1、mysql01停止mysql服务 [root@mysql01 ~]# /etc/init.d/mysqld stop Shutting down MySQL.. SUCCESS! 2、查看VIP所在服务器 vip 2.99不在mysql01 上了 [root@mysql01 ~]# ip a | grep ens33 2: ens33: mtu 1500 qdisc pfifo_fast state UP qlen 1000 inet 192.0.2.81/24 brd 192.0.2.255 scope global ens33 此时vip 漂移到了 mysql02 [root@mysql02 ~]# ip a | grep ens33 2: ens33: mtu 1500 qdisc pfifo_fast state UP qlen 1000 inet 192.0.2.82/24 brd 192.0.2.255 scope global ens33 inet 192.0.2.99/32 scope global ens33 3、mysql01启动MySQL服务 [root@mysql01 ~]# /etc/init.d/mysqld start Starting MySQL. SUCCESS! 4、此时查看VIP所在服务器 因为是非抢占模式，所以vip还在mysql02上 [root@mysql02 ~]# ip a | grep ens33 2: ens33: mtu 1500 qdisc pfifo_fast state UP qlen 1000 inet 192.0.2.82/24 brd 192.0.2.255 scope global ens33 inet 192.0.2.99/32 scope global ens33 如果需要将VIP漂移回mysql01服务器，只需要在mysql02服务器上重启keepalived软件即可。 至此，Keepalived测试完成！可以按照非抢占模式实现高可用！