说明：
操作系统：CentOS 7.x 64位
MySQL数据库版本：mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
MySQL主服务器：192.168.21.128
MySQL从服务器：192.168.21.129
准备篇：
说明：在两台MySQL服务器192.168.21.128和192.168.21.129上分别进行如下操作
一、防火墙配置
CentOS 7.x 默认使用的是firewall作为防火墙，这里改为iptables防火墙。
1、关闭firewall：
systemctl stop firewalld.service #停止firewall
systemctl disable firewalld.service #禁止firewall开机启动
systemctl mask firewalld
systemctl stop firewalld
yum remove firewalld
2、安装iptables防火墙
yum install iptables-services #安装
vi /etc/sysconfig/iptables #编辑防火墙配置文件
# sample configuration for iptables service
# you can edit this manually or use system-config-firewall
# please do not ask us to add additional ports/services to this default configuration
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT
:wq! #保存退出
systemctl restart iptables.service #最后重启防火墙使配置生效
systemctl enable iptables.service #设置防火墙开机启动
/usr/libexec/iptables/iptables.init restart #重启防火墙
二、关闭SELINUX
vi /etc/selinux/config
#SELINUX=enforcing #注释掉
#SELINUXTYPE=targeted #注释掉
SELINUX=disabled #增加
:wq! #保存退出
setenforce 0 #使配置立即生效
三、修改主机名称
#设置主机名为mysql-master
hostname mysql-master
hostnamectl set-hostname mysql-master
vi /etc/hostname #编辑配置文件
mysql-master #修改localhost.localdomain为mysql-master
:wq! #保存退出
vi /etc/hosts #编辑配置文件
127.0.0.1 localhost mysql-master #修改localhost.localdomain为mysql-master
#从服务器执行相同的操作，把主机名称分别修改为服务器角色对应的名称mysql-slave
四、下载软件包
MySQL二进制版本下载地址：https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
上传mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz安装包到/usr/local/src目录下
五、安装mysql依赖包（初始化mysql数据库需要）
#yum源安装
yum install libaio-devel
#rpm包安装
#下载地址
cd /usr/local/src
https://mirrors.163.com/centos-vault/7.6.1810/os/x86_64/Packages/libaio-0.3.109-13.el7.x86_64.rpm
https://mirrors.163.com/centos-vault/7.6.1810/os/x86_64/Packages/libaio-devel-0.3.109-13.el7.x86_64.rpm
cd /usr/local/src
rpm -ivh libaio-0.3.109-13.el7.x86_64.rpm
rpm -ivh libaio-devel-0.3.109-13.el7.x86_64.rpm
安装篇
说明：在两台MySQL服务器192.168.21.128和192.168.21.129上分别进行如下操作
备注：作为主从服务器的MySQL版本必须一致
一、安装mysql
1、创建mysql运行用户和组
groupadd mysql
useradd -g mysql mysql -s /bin/false
2、创建mysql安装目录和mysql数据库存放目录
mkdir -p /data/server/mysql #mysql安装目录
mkdir -p /data/server/mysql/data #mysql数据库存放目录
3、设置目录权限
chown -R mysql:mysql /data/server/mysql
chown -R mysql:mysql /data/server/mysql/data
4、安装mysql
#解压二进制版本mysql安装包到安装目录
cd /usr/local/src/
tar zxvf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /data/server/mysql --strip-components 1
二、设置my.cnf配置文件
vi /data/server/mysql/my.cnf #编辑添加
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
port = 3306
socket=/data/server/mysql/data/mysql.sock
[mysql]
port = 3306
socket = /data/server/mysql/data/mysql.sock
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
user=mysql
port = 3306
basedir = /data/server/mysql
datadir = /data/server/mysql/data
socket = /data/server/mysql/data/mysql.sock
pid-file= /data/server/mysql/data/mysql.pid
server_id = 1
character-set-server = utf8mb4
default-storage-engine = InnoDB
tmpdir = /tmp
max_connections = 1000
innodb_buffer_pool_size = 16GB #默认为128MB，mysql专用服务器建议设置为内存的70%
innodb_buffer_pool_chunk_size = 2GB
innodb_buffer_pool_instances = 8 #最大值64
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
default_authentication_plugin=mysql_native_password
explicit_defaults_for_timestamp=true
[mysqld_safe]
log-error = /data/server/mysql/data/error.log
:wq! #保存退出
三、添加mysql系统环境变量
vi /etc/profile #把mysql服务加入系统环境变量：在最后添加下面这一行
export PATH=$PATH:/data/server/mysql/bin
:wq! #保存退出
source /etc/profile #使配置立即生效
四、初始化mysql数据库
/data/server/mysql/bin/mysqld --defaults-file=/data/server/mysql/my.cnf --user=mysql --initialize-insecure --basedir=/data/server/mysql --datadir=/data/server/mysql/data --socket=/data/server/mysql/data/mysql.sock --pid-file=/data/server/mysql/data/mysql.pid
[root@mysql-slave mysql]# /data/server/mysql/bin/mysqld --defaults-file=/data/server/mysql/my.cnf --user=mysql --initialize-insecure --basedir=/data/server/mysql --datadir=/data/server/mysql/data --socket=/data/server/mysql/data/mysql.sock --pid-file=/data/server/mysql/data/mysql.pid
2022-08-20T09:50:56.536905Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2022-08-20T09:50:56.536961Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2022-08-20T09:50:56.775855Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-08-20T09:50:56.827826Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-08-20T09:50:56.892979Z 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: 969fc0b2-206d-11ed-a4d5-000c29ae577c.
2022-08-20T09:50:56.894562Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-08-20T09:50:57.079229Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-08-20T09:50:57.079243Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-08-20T09:50:57.079691Z 0 [Warning] CA certificate ca.pem is self signed.
2022-08-20T09:50:57.248135Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
五、启动mysql5.7.x数据库
/data/server/mysql/bin/mysqld_safe --defaults-file=/data/server/mysql/my.cnf --user=mysql --port=3306 --basedir=/data/server/mysql --datadir=/data/server/mysql/data --socket=/data/server/mysql/data/mysql.sock --pid-file=/data/server/mysql/data/mysql.pid &
六、添加软连接
ln -s /data/server/mysql/data/mysql.sock /tmp/mysql.sock
七、设置MySQL管理员root密码，根据提示设置密码
/data/server/mysql/bin/mysql_secure_installation --socket=/data/server/mysql/data/mysql.sock
y使用密码验证插件
选择密码规则（一般选择第二项：1 = MEDIUM）
LOW Length >= 8 #长度大于等于8
MEDIUM Length >= 8, numeric, mixed case, and special characters #长度大于等于8，数字、大小写字母、特殊符号
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file #长度大于等于8，数字、大小写字母、特殊符号和字典文件（慎选！）
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
设置新密码：12345678Abc++
y删除匿名用户
y禁止root远程登录
y删除测试数据库
y重新加载权限表
#关闭mysql5.7.x数据库
/data/server/mysql/bin/mysqladmin -uroot -S /data/server/mysql/data/mysql.sock shutdown -p
#根据提示输入上面设置的密码即可关闭数据库
八、设置mysql启动脚本
cp /data/server/mysql/support-files/mysql.server /data/server/mysql/mysql.sh
vi /data/server/mysql/mysql.sh #编辑修改
basedir=/data/server/mysql #MySQL程序安装路径
datadir=/data/server/mysql/data #MySQl数据库存放目录
conf=/data/server/mysql/my.cnf #mysql配置文件my.cnf路径
:wq! #保存退出
#添加执行权限
chmod +x /data/server/mysql/mysql.sh
#添加开机启动
vi /etc/rc.d/rc.local
/bin/sh /data/server/mysql/mysql.sh start
:wq! #保存退出
#默认/etc/rc.local没有执行权限，需要手动添加执行权限
chmod +x /etc/rc.d/rc.local
sh /data/server/mysql/mysql.sh start #启动mysql
配置篇
一、配置MySQL主服务器（192.168.21.128）
1、创建mysql数据库和用户
1.1
#创建数据库和用户
数据库名：mydb
数据库用户名：myuser
数据库密码：12345678Abc++
1.2
#创建mysql数据库管理员
mysql数据库管理员用户名：admin
密码：12345678Abc++
进入mysql5.7.x控制台
/data/server/mysql/bin/mysql -uroot -p -S /data/server/mysql/data/mysql.sock
Create DATABASE IF NOT EXISTS mydb default charset utf8mb4 COLLATE utf8mb4_general_ci; #创建数据库mydb
GRANT ALL PRIVILEGES ON mydb.* TO myuser@'192.168.21.%' IDENTIFIED BY '12345678Abc++' WITH GRANT OPTION; #创建用户myuser并授权
flush privileges; #刷新系统授权表
GRANT ALL PRIVILEGES ON *.* TO admin@'127.0.0.1' IDENTIFIED BY '12345678Abc++' WITH GRANT OPTION; #创建mysql数据库管理员admin并授权
GRANT ALL PRIVILEGES ON *.* TO admin@'localhost' IDENTIFIED BY '12345678Abc++' WITH GRANT OPTION; #创建mysql数据库管理员admin并授权
flush privileges; #刷新系统授权表
2、创建mysql主从同步账号并授权
#创建并授权用户repl只能从192.168.21.129这个IP访问主服务器192.168.21.128上面的数据库，并且只具有数据库备份的权限
grant replication slave on *.* to 'repl'@'192.168.21.129' identified by '12345678Abc++' with grant option;
flush privileges; #刷新系统授权表
exit; #退出控制台
3、导出主库中的mydb数据库
#在MySQL主服务器进行操作，导出数据库lst到/tmp/mydb.sql
/data/server/mysql/bin/mysqldump -u root -p --default-character-set=utf8mb4 --opt -Q -R --skip-lock-tables mydb > /tmp/mydb.sql
#把/tmp目录下的mydb.sql 数据库文件上传到MySQL从服务器的/tmp目录下面
scp /tmp/mydb.sql root@192.168.21.129:/tmp
备注：在导出之前可以先进入MySQL控制台执行下面命令
flush tables with read lock; #数据库只读锁定命令，防止导出数据库的时候有数据写入
unlock tables; #解除锁定
4、编辑修改MySQL主服务器（192.168.21.128）的my.cnf文件
vi /data/server/mysql/my.cnf
server-id=1 #设置服务器id，为1表示主服务器，注意：如果原来的配置文件中已经有这一行，就不用再添加了。
log-bin=mysql-bin #启动MySQ二进制日志系统，注意：如果原来的配置文件中已经有这一行，就不用再添加了。
binlog-do-db=mydb #同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行
binlog-ignore-db=mysql #不同步mysql系统数据库
:wq! #保存退出
sh /data/server/mysql/mysql.sh restart #重启MySQL
mysql -u root -p #进入mysql控制台
show variables like 'server_id'; #查看server-id的值是否为1
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
show master status; #查看主服务器，出现以下类似信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | mydb | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
注意：这里记住File的值：mysql-bin.000001和Position的值：154，后面会用到。
二、配置MySQL从服务器（192.168.21.129）
1、导入数据库文件到MySQL从服务器
mysql -u root -p #进入从服务器MySQL控制台
Create DATABASE IF NOT EXISTS mydb default charset utf8mb4 COLLATE utf8mb4_general_ci; #创建数据库
use mydb #进入数据库
source /tmp/mydb.sql #导入备份文件到数据库
exit; #退出控制台
2、编辑修改MySQL从服务器（192.168.21.129）的my.cnf文件
vi /data/server/mysql/my.cnf #编辑配置文件，在[mysqld]部分添加下面内容
server-id=2 #设置服务器id，修改其值为2，表示为从数据库
log-bin=mysql-bin #启动MySQ二进制日志系统，注意：如果原来的配置文件中已经有这一行，就不用再添加了。
replicate-do-db=mydb #需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行
replicate-ignore-db=mysql #不同步mysql系统数据库
read_only #设置数据库只读
:wq! #保存退出
sh /data/server/mysql/mysql.sh restart #重启MySQL
3、开启mysql主从同步
mysql -u root -p #进入mysql控制台
show variables like 'server_id'; #查看server-id的值是否为2
stop slave; #停止slave同步进程
#执行主从同步操作
change master to master_host='192.168.21.128',master_user='repl',master_password='12345678Abc++',master_log_file='mysql-bin.000001' ,master_log_pos=154;
start slave; #开启slave同步进程
SHOW SLAVE STATUS\G #查看slave同步信息，出现以下内容
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 event
Master_Host: 192.168.21.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-slave-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mydb
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 533
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: d6f0a1e7-2061-11ed-8594-000c292ccd74
Master_Info_File: /data/server/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql>
注意查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
以上这两个参数的值为Yes，即说明配置成功！
测试篇
1、进入MySQL主服务器（192.168.21.128）
mysql -u root -p #进入MySQL控制台
use mydb #进入数据库
CREATE TABLE test1 ( id int not null primary key,name char(20) ); #创建test表
2、进入MySQL从服务器
mysql -u root -p #进入MySQL控制台
use mydb #进入数据库
show tables; #查看mydb表结构，会看到有一个新建的表test1，表示数据库同步成功
扩展阅读：可以在MySQL主服务器上绑定一个VIP，对外提供这个VIP的ip地址进行连接
#绑定VIP
/usr/bin/sudo /sbin/ip addr add 192.168.21.130/24 dev ens33 label ens33:0
#添加到开机启动
vi /etc/rc.d/rc.local
/usr/bin/sudo /sbin/ip addr add 192.168.21.130/24 dev ens33 label ens33:0
:wq! #保存退出
#添加执行权限
chmod +x /etc/rc.d/rc.local
至此，Linux下MySQL数据库主从同步配置完成。
②190706903 ③203744115
