keepalived+MySQL 5.7编译安装及双活配置

1.keepalived地址规划

192.168.217.33  xtgsadmin/nx!H01142
192.168.217.34  xtgsadmin/nx!H01141

vip
192.168.217.64
掩码255.255.255.0
网关192.168.217.254

2.keepalived配置信息

节点一：
[root@H01089 keepalived]# cat keepalived.conf
! Configuration File for keepalived

vrrp_instance VI_1 {
    state MASTER
    interface eth5
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.217.64
    }
}

节点二：
vrrp_instance VI_1 {
    state MASTER
    interface enP1p24s0f0
    virtual_router_id 51
    priority 50 
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.217.64
    }
}

3.keepalived编译安装

yum install -y gcc gcc-c++ ncurses* 

./configure --prefix=/usr/local/keepalived

cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
ln -s /usr/local/keepalived/sbin/keepalived /usr/sbin/
chmod +x /etc/init.d/keepalived
chkconfig --add keepalived
chkconfig keepalived on

3.MySQL安装前的准备工作

需要的软件：

boost_1_59_0.tar.gz，cmake-3.6.1.tar.gz，mysql-5.7.22.tar.gz

4.开始安装MySQL

4.1 安装cmake
[root@oracle cmake-3.6.1]# ./bootstrap && make && make install

4.2 开始编译安装MySQL

创建用户和组

[root@oracle cmake-3.6.1]#groupadd mysql

[root@oracle cmake-3.6.1]#useradd -g mysql -s /sbin/nologin -M mysql

创建安装数据库的目录和数据存放目录

[root@oracle src]# mkdir mysql5
[root@oracle src]# mkdir mysql

解压数据库软件

[root@oracle src]# tar -xvf mysql-5.7.22.tar.gz 

[root@oracle mysql-5.7.22]# 
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DMYSQL_TCP_PORT=13306 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_DEBUG=0 -DWITH_BOOST=/usr/local/boost 

出现报错，这个的解决方法是：

在/usr/local下创建一个名为boost的文件夹

mkdir -p /usr/local/boost
把上面的名为boost_1_59_0.tar.gz的压缩包解压就可以了
tar -xvf boost_1_59_0.tar.gz
继续CMAKE 这里要注意红色的部分

[root@oracle src]# 
cmake . -DCMAKE_INSTALL_PREFIX=/data/src/mysql -DMYSQL_DATADIR=/data/src/mysql5 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DMYSQL_TCP_PORT=3306 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DWITH_BOOST=/usr/local/boost -DDEFAULT_COLLATION=utf8_general_ci -DWITH_DEBUG=0  这是根据刚才按装的路径添加的

看到上面的信息就证明cmake 成功了

[root@oracle mysql-5.7.22]# make && make install

5.配置MySQL服务及开机自启动

[root@oracle mysql-5.7.22]# cp support-files/mysql.server /etc/init.d/mysqld

[root@oracle mysql-5.7.22]# chmod +x /etc/init.d/mysqld
[root@oracle mysql-5.7.22]# chkconfig --add mysqld

6.进行MySQL初始化
备份：
tar -cvf mysql.tar mysql
创建相关目录：
cd /var/log
mkdir mysql
chown -R mysql:mysql mysql
chmod -R 755 mysql

cd /var/lib
mkdir mysql
chown -R mysql:mysql mysql
chmod -R 755 mysql

初始化MySQL数据库

bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data

环境变量设置：
export PATH=$PATH:/usr/local/mysql/bin

设置启动服务：

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

chown mysql:mysql /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld  
  
  
节点一：

[mysql@h01142 mysql]$ bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data
2024-06-07T11:55:19.835534Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2024-06-07T11:55:19.835610Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2024-06-07T11:55:19.835740Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-06-07T11:55:20.337311Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-06-07T11:55:20.460416Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-06-07T11:55:20.588781Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a73f1ac8-6a49-11e8-b713-a0369fa17118.
2024-06-07T11:55:20.597877Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-06-07T11:55:20.598535Z 1 [Note] A temporary password is generated for root@localhost: ideEjBtk#4hl
[mysql@h01142 mysql]$ 

节点二：

[mysql@h01141 mysql]$ bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data
2024-06-07T11:31:31.464259Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2024-06-07T11:31:31.464335Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2024-06-07T11:31:31.464465Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-06-07T11:31:31.915747Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-06-07T11:31:32.035531Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-06-07T11:31:32.077875Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 53c9a388-6a46-11e8-8ae2-a0369fa17b14.
2024-06-07T11:31:32.078552Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-06-07T11:31:32.079526Z 1 [Note] A temporary password is generated for root@localhost: uu0cjLYa>Ph*
[mysql@h01141 mysql]$ 

7.配置文件
vi my.cnf

[mysqld]
datadir=/data
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mysql/mysql.log
pid-file=/var/run/mysql/mysql.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

~

创建软连接：
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

登录mysql：

mysql -uroot -p

设置初始密码：
set password=password('Nx_123456!H');

root用户赋权：

create user 'repl'@'%' identified by 'Nx_123456!H';

grant all privileges on *.* to 'root'@'%' identified by 'Nx_123456!H';
grant all privileges on *.* to 'mysql'@'%' identified by 'Nx_123456!H';

mysql -uroot -pNx_123456!H

测试连接：
mysql -uroot -p'Nx_123456!H' -h192.168.217.64  -P13306
mysql -uroot -p'Nx_123456!H' -h192.168.217.33  -P13306
mysql -uroot -p'Nx_123456!H' -h192.168.217.34  -P13306

mysql -urepl -p'Nx_123456!H' -h192.168.217.64  -P13306
mysql -urepl -p'Nx_123456!H' -h192.168.217.33  -P13306
mysql -urepl -p'Nx_123456!H' -h192.168.217.34  -P13306

8.配置双活

use mysql;
set password=password('Nx_123456!H');

set passwd for 'repl'@'localhost'=password('Nx_123456!H');

创建备份用户
在A机上执行

两个节点执行：
grant replication slave on *.* to 'repl'@'%' identified by 'Nx_123456!H';
flush privileges;

9.双活配置文件
vi /etc/hosts

192.168.217.33    h01142
192.168.217.34    h01141
192.168.217.64    swid-mysql

节点一：

[mysqld]
basedir=/usr/local/mysql/
datadir=/data
socket=/var/lib/mysql/mysql.sock
user=mysql
port=13306
server-id=33
#skip-grant-tables
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#bind-address           = 127.0.0.1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
binlog_ignore_db        = mysql
binlog_ignore_db        = information_schema
binlog_ignore_db        = performance_schema
log-slave-updates
sync_binlog=0
auto-increment-increment= 2
auto-increment-offset    = 1
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

增加部分：
basedir=/usr/local/mysql/
user=mysql
port=13306
server-id=33

#bind-address           = 127.0.0.1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
binlog_ignore_db        = mysql
binlog_ignore_db        = information_schema
binlog_ignore_db        = performance_schema
log-slave-updates
sync_binlog=0
auto-increment-increment= 2
auto-increment-offset    = 1
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

节点二：

[mysqld]
basedir=/usr/local/mysql
datadir=/data
socket=/var/lib/mysql/mysql.sock
user=mysql
port=13306
server-id=34
#skip-grant-tables
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#bind-address           = 127.0.0.1
server-id               = 118
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
binlog_ignore_db        = mysql
binlog_ignore_db        = information_schema
binlog_ignore_db        = performance_schema
log-slave-updates
sync_binlog=0
auto-increment-increment = 2
auto-increment-offset    = 2
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

增加部分：

[mysqld]
basedir=/usr/local/mysql
user=mysql
port=13306
server-id=34

#bind-address           = 127.0.0.1
server-id               = 118
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
binlog_ignore_db        = mysql
binlog_ignore_db        = information_schema
binlog_ignore_db        = performance_schema
log-slave-updates
sync_binlog=0
auto-increment-increment = 2
auto-increment-offset    = 2
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

注意：
建立log_bin响应目录
并赋权

chown -R mysql:mysql mysql/
chmod 755 mysql/

重启两个节点mysql服务

192.168.217.33  H01142
192.168.217.34  H01141
192.168.217.64   swid-mysql

同步数据库：
在A机上执行
--创建数据库
create database testdb default charset utf8;
--切换数据库
use testdb;
--创建测试表
create table DM_TEST(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(100)
);
--插入测试数据
insert DM_TEST(NAME) values('user1');
insert DM_TEST(NAME) values('user2');

--将表设定为只读
FLUSH TABLES WITH READ LOCK;

--显示表日志情况
show master status\G

--备份数据库
mysqldump --master-data -uroot -p testdb > /home/mysql/testdb.sql
scp testdb.sql 192.168.217.34:/home/mysql
Nx_123456!H

在B机上执行
--创建数据库
create database testdb default charset utf8;
--恢复数据库
mysql -uroot -p testdb < /home/mysql/testdb.sql
--设定初始日志
change master to master_host='192.168.217.33',
master_user='repl',
master_password='Nx_123456!H',
master_log_file='mysql-bin.000002',
master_log_pos=2328;
--启动同步
start slave;
--锁定表
FLUSH TABLES WITH READ LOCK;
--显示表日志情况
show master status\G

在A机上执行
--解除只读
unlock tables;
--锁定表
change master to master_host='192.168.217.34',
master_user='repl',
master_password='Nx_123456!H',
master_log_file='mysql-bin.000002',
master_log_pos=2122;
--启动同步
start slave;
在B机上执行
--解除只读
unlock tables;

分别在两台机器上执行以下命令查看从服务器状态
mysql> show slave status\G;

确认Slave_IO_Running、Slave_SQL_Running为Yes状态。