SQL SERVER 2014 双机热备 mysql8双机热备

环境：centos7.9-Minimal
mysql：8.0.27
操作权限：root
主目录：/root

由于本人近期yum安装mysql反复报错，现整理出一篇纯手动安装mysql并组建双机热备份教程。
本文属于保姆级教程，过多次反复重装测试，只要保持和我文中同样的环境，几乎可以百分之百成功，小白用户可以跟做
文中安装的是mysql8.0。实际上经过测试，用本教程安装mysql5.7也是可以的。只需要将文中出现的8.0.27替换为你需要的版本号即可。如5.7.36

开始

• 清理旧的数据库和用户

service mysql stop
yum list installed | grep -E "mysql|mariadb" | xargs yum remove -y
rpm -qa | grep -E "mysql|mariadb" | xargs rpm -e --nodeps
yum clean all;yum makecache fast
find / -name mysql | xargs rm -rf
id mysql && userdel mysql
rm -f /etc/my.cnf /usr/my.cnf

• 开启防火墙端口

firewall-cmd --zone=public --permanent --add-port=3306/tcp --add-port=8080/tcp --add-port=3311/tcp;firewall-cmd --reload

• 安装必要软件

yum -y install cpanminus vim wget numactl

• 创建需要的用户和目录

groupadd mysql;useradd -g mysql mysql;echo 'NewPassword!@#123456' | passwd --stdin mysql
mkdir -p /data/mysql && chmod 777 /data/mysql && chown mysql:mysql -R /data/mysql
mkdir -p /usr/local/mysql && chmod 777 /usr/local/mysql && chown mysql:mysql -R /usr/local/mysql

• 下载官网完整安装包8.0.27，并解压到指定目录

cd ~
wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.27-el7-x86_64.tar
tar xvf mysql-8.0.27-el7-x86_64.tar mysql-8.0.27-el7-x86_64.tar.gz
tar zxvf mysql-8.0.27-el7-x86_64.tar.gz && mv mysql-8.0.27-el7-x86_64/* /usr/local/mysql/

• 创建数据库配置文件

vim /etc/my.cnf

[mysqld]
bind-address=0.0.0.0
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
#character config
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true

• 复制文件到服务，配置环境变量和软链接，初始化和启动数据库，并设置开机启动

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile;source /etc/profile
ln -s /usr/local/mysql/bin/mysql /usr/bin
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --user=mysql --initialize
/usr/local/mysql/bin/mysql_ssl_rsa_setup
/usr/local/mysql/support-files/mysql.server start
service mysql restart && service mysql status
chkconfig --add mysql;chkconfig --list mysql

• 查看并记住数据库初始密码，然后用初始密码进入数据库

grep "password is generated" /data/mysql/mysql.err
mysql -uroot -p

• 操作数据库，修改密码(数据库密码需要大小写字母加数字和符号组合)，并配置远程登录

>ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword!@#123456';
>create user 'root'@'%' identified with mysql_native_password by 'NewPassword!@#123456';
>grant all on *.* to 'root'@'%';
>use mysql;
>select host from user where user='root';
>select host,user from user where user='root';
>flush privileges;
>exit

补充：以上数据库登陆无IP限制，可以改成仅允许单一IP登录

>grant all on *.* to 'root'@'192.168.1.200';
>flush privileges;

• 数据库增加用户

>use mysql;
>CREATE USER `newuser1`@`%` IDENTIFIED WITH mysql_native_password BY 'NewPassword!@#123456';
>grant all on *.* to 'newuser1'@'%';
>select host,user,plugin from user;
>flush privileges;

以上数据库已经搭建完成，直接在本机或远程客户端登陆即可
以下内容是搭建双主机热备份，如果不需要双机热备可以忽略

---

---

搭建双机热备份

搭建双主备份–即双机实时同步、互为备份，双主备份的好处是俩端均可修改数据并实时同步到对端。

开始

准备两台同样环境的数据库系统。备份前需要确保俩机上需要备份的数据库表文件完全一致,并同时在两台数据库上建立两个名为testA、testB的数据库，包含一张空表，便于测试。

A机IP：192.168.1.15
B机IP：192.168.1.39
同步数据库：testA、testB

• A机编辑配置文件

vim /etc/my.cnf

[mysqld]
server-id=1
#主从服务器标识
auto_increment_offset=1
#1为奇数ID,2为偶数ID。主从互为奇偶，不可以一样
auto_increment_increment=2
#服务器主机有几台就设置为几
log-bin=mysql-bin
#打开binlog，进行replication的前提
sync_binlog=1
#数据库同步到磁盘
log-slave-updates
#配置主从日志同步
#Slave-skip-errors
#忽略错误（允许主从数据库不一致）
binlog-do-db=testA
binlog-do-db=testB
#需要记录日志的数据库，每个数据库添加一行
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
#不需要记录日志的数据库，每个数据库添加一行
replicate-do-db=testA
replicate-do-db=testB
#需要同步的数据库，每个数据库添加一行
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
#不需要同步的数据库，每个数据库添加一行
################################
bind-address=0.0.0.0
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
#character config
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true

• A机配置数据库,开启slave，新建用户允许对端IP登陆,并登陆对端数据库。

service mysql restart
mysql -uroot -p

>use mysql;
>CREATE USER `synuser`@`192.168.1.39` IDENTIFIED WITH mysql_native_password BY 'NewPassword!@#123456';
>grant replication slave on *.* to 'synuser'@'192.168.1.39';
>flush privileges;
>use testA;
>stop slave;
>reset slave;
>flush tables with read lock;
>show master status;
>change master to master_host='192.168.1.39',master_user='synuser',master_password='NewPassword!@#123456',master_log_file='mysql-bin.000001',master_log_pos=156;
>flush privileges;
>start slave;
>unlock tables;
>show slave status\G;

注意：以上命令中master_log_file/pos值根据对端主机(B)命令show master status;查询而来，替换成对端(B)相应值即可

• B机编辑配置文件

vim /etc/my.cnf

[mysqld]
server-id=2
#主从服务器标识
auto_increment_offset=2
#1为奇数ID,2为偶数ID。主从互为奇偶，不可以一样
auto_increment_increment=2
#服务器主机有几台就设置为几
log-bin=mysql-bin
#打开binlog，进行replication的前提
sync_binlog=1
#数据库同步到磁盘
log-slave-updates
#配置主从日志同步
#Slave-skip-errors
#忽略错误（允许主从数据库不一致）
binlog-do-db=testA
binlog-do-db=testB
#需要记录日志的数据库，每个数据库添加一行
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
#不需要记录日志的数据库，每个数据库添加一行
replicate-do-db=testA
replicate-do-db=testB
#需要同步的数据库，每个数据库添加一行
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
#不需要同步的数据库，每个数据库添加一行
################################
bind-address=0.0.0.0
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
#character config
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true

• B机配置数据库,开启slave，新建用户允许对端IP登陆,并登陆对端数据库。

service mysql restart
mysql -uroot -p

>use mysql;
>CREATE USER `synuser`@`192.168.1.15` IDENTIFIED WITH mysql_native_password BY 'NewPassword!@#123456';
>grant replication slave on *.* to 'synuser'@'192.168.1.15';
>flush privileges;
>use testA;
>stop slave;
>reset slave;
>flush tables with read lock;
>show master status;
>change master to master_host='192.168.1.15',master_user='synuser',master_password='NewPassword!@#123456',master_log_file='mysql-bin.000001',master_log_pos=156;
>flush privileges;
>start slave;
>unlock tables;
>show slave status\G;

注意：以上命令中master_log_file/pos值根据对端主机(A)命令show master status;查询而来，替换成对端(A)相应值即可

---

以上双master热备份完成。直接在本机或远程客户端登陆即可。
以后要新添加备份数据库只需要在my.cnf添加新行即可（首先确保要同步的数据库两端的数据表保持一致）
示例：

• 编辑文件

vim /etc/my.cnf

binlog-do-db=database_name
replicate-do-db=database_name
#每添加一个数据库加入上面两行

• 重启并打开数据库

service mysql restart
mysql -uroot -p

• 启动slave并刷新权限

>flush privileges;
>start slave;
>show slave status\G;