安装mysql、keepalived

op用户登录两台mysql服务器

执行以下命令安装mysql和keepalived：

sudo yum localinstall -y /var/mozi-install/rpm/mysql-rpm/*
sudo yum install -y keepalived

如操作系统为ctyunos，还需执行以下命令：

sudo sed -i 's/-insecure//' /usr/libexec/mysql-prepare-db-dir

创建数据目录

sudo mkdir -p /data01/mysql/mysql/data
sudo chown -R mysql:mysql /data01/mysql

配置mysql

编辑/etc/my.cnf，第一台配置如下：

[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
basedir= /data01/mysql/mysql #设定工作目录
datadir= /data01/mysql/mysql/data #设定数据目录
log-error=/var/log/mysqld.log
expire_logs_days = 30 #增加日志老化机制，本文范例保留30天内的日志信息
event_scheduler=ON #启动数据库定时清理机制
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
myisam_sort_buffer_size = 8M
character_set_server=utf8 #设定字符集
max_connections=1000 #设定最大连接数
lower_case_table_names = 1 #设定数据库名和表名大小写不敏感
max_connect_errors = 100000 #设定连接错误为一个较大值
innodb_file_format = barracuda #接着的3个配置项目解决主键不能超过767 bytes的限制
innodb_file_per_table = true
innodb_large_prefix = true
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
lc-messages-dir = /usr/share/mysql
lc_messages = en_US
​
server-id = 1   #两台机器server-id必须不一样
auto_increment_offset = 1
auto_increment_increment = 2 #奇数ID
log-bin = mysql-bin
relay_log=mysql-relay-bin
log-slave-updates=true
replicate-ignore-db = mysql          #忽略不同步主从的数据库
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema

第二台配置如下：

[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
basedir= /data01/mysql/mysql #设定工作目录
datadir= /data01/mysql/mysql/data #设定数据目录
log-error=/var/log/mysqld.log
expire_logs_days = 30 #增加日志老化机制，本文范例保留30天内的日志信息
event_scheduler=ON #启动数据库定时清理机制
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
myisam_sort_buffer_size = 8M
character_set_server=utf8 #设定字符集
max_connections=1000 #设定最大连接数
lower_case_table_names = 1 #设定数据库名和表名大小写不敏感
max_connect_errors = 100000 #设定连接错误为一个较大值
innodb_file_format = barracuda #接着的3个配置项目解决主键不能超过767 bytes的限制
innodb_file_per_table = true
innodb_large_prefix = true
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
lc-messages-dir = /usr/share/mysql
lc_messages = en_US
​
server-id = 2 #主备机server-id必须不一样
auto_increment_offset = 2
auto_increment_increment = 2 #偶数ID
log-bin = mysql-bin
relay_log=mysql-relay-bin
log-slave-updates=true
replicate-ignore-db = mysql             #忽略不同步主从的数据库
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema

两台均执行以下命令

systemctl start mysqld
sudo grep 'temporary password' /var/log/mysqld.log
mysql -uroot -p
#修改密码
mysql> set password = password('密码');

双主复制配置

第一台主第二台从配置

第一台执行以下命令（假设第一台ip为10.0.21.56，第二台ip为10.0.21.57）：

mysql -uroot -p
mysql> grant replication slave on *.* to 'repl_user'@'10.0.21.57' identified by 'F$q5Z1VHYDboO';
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     459 |               |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

第二台执行以下命令：

注：下面log_file和log_pos参数取自第一台的show master status的结果

mysql -uroot -p
mysql> CHANGE MASTER TO 
       MASTER_HOST='10.0.21.56', 
       MASTER_USER='repl_user', 
       MASTER_PASSWORD='密码', 
       MASTER_LOG_FILE='mysql-bin.000001',   
       MASTER_LOG_POS=459;

第二台主第一台从配置

第二台执行以下命令：

mysql -uroot -p
mysql> grant replication slave on *.* to 'repl_user'@'10.0.21.56' identified by 'F$q5Z1VHYDboO';
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     459 |               |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

第一台执行以下命令：

注：下面log_file和log_pos参数取自第二台的show master status的结果

mysql -uroot -p
mysql> CHANGE MASTER TO 
       MASTER_HOST='10.0.21.57', 
       MASTER_USER='repl_user', 
       MASTER_PASSWORD='密码', 
       MASTER_LOG_FILE='mysql-bin.000001',   
       MASTER_LOG_POS=459;

两台上操作：

mysql> start slave;
mysql> show slave status \G

输出结果中如下两行均为Yes则正常

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

keepalived配置

两台均操作：

sudo vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived
​
global_defs {
   router_id MYSQL-HA
}
​
vrrp_script check_run {
script "/etc/keepalived/mysql_check.sh"
interval 5
}
​
vrrp_instance VI_1 {
    state BACKUP
    nopreempt
    interface bond2.380 #需要改成实际的端口
    virtual_router_id 251 #router_id同网段不能有冲突
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        10.0.21.194/24  #虚ip改为实际的虚ip
    }
    track_script {
       check_run
    }
}

sudo vi /etc/keepalived/mysql_check.sh

#!/bin/bash
mysql -uroot -p'实际密码' -S /tmp/mysql.sock -e "show status;" > /dev/null 2>&1
if [ $? -eq 0 ] 
then
   exit 0
else
   exit 1
fi

执行以下命令

chmod 700 /etc/keepalived/*.sh
systemctl start keepalived

启动完成后在第一台上通过ip addr命令查看VIP是否已存在