mysql 1主2从搭建

yum安装

##  上传 mysql57-community-release-el7-11.noarch.rpm
rpm -Uvh mysql57-community-release-el7-11.noarch.rpm
##  安装mysql  
yum -y install mysql mysql-server --nogpgcheck
##  查看 mysql 版本
mysql -V
## 启动mysql 
systemctl start mysqld
##  获取临时密码
grep 'temporary password' /var/log/mysqld.log
#  登录  并输入临时密码
mysql -uroot -p
# 设置 密码策略 长度只需要满足8位  生产环境建议默认
set global validate_password_policy=LOW;
## 修改密码  默认密码策略复杂
ALTER USER 'root'@'localhost' IDENTIFIED  BY '12345678';
use  mysql;
update user set user.Host='%' where user.User='root';
----------------------------------------------------
flush privileges;

1主2从搭建

##  安装 mysql  在三台机器上  
## 关闭防火墙   
## 修改 三台机器上的 my.cnf文件
## 在master 上创建用户及授权
CREATE USER 'app'@'%' IDENTIFIED WITH mysql_native_password BY '12345678';#创建用户
GRANT REPLICATION slave ON *.* TO 'app'@'%';
flush privileges;
##  如果有不满足密码测的地方 做如下修改
set global validate_password_length=1;
set global validate_password_policy=LOW;
## 在master 上执行 
show  master status  
#获取binlog 进度信息 填写到下方位置
# MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1938;
# 在备机上执行如下命令  192.168.83.123  192.168.83.124都需要执行
CHANGE MASTER TO MASTER_HOST='192.168.83.122', MASTER_USER='app',MASTER_PASSWORD='12345678',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1938;
# 启动主从服务 192.168.83.123  192.168.83.124
start slave;
## 执行命令
show slave status\G;
##  下方两项都是 yes  则正常
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

配置文件如下

 master配置文件

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 最大连接数
max_connections=200
# 最大连接数  字符集编码
character-set-server=utf8mb4
##  
innodb_buffer_pool_size=128M 
## 慢查询
slow_query_log=1
# 慢查询时间
long_query_time=5
## 每次事务提交后刷盘
innodb_flush_log_at_trx_commit=1
##  主服务器 配置为 1
server-id = 1
log-bin=mysql-bin
## 二进制格式
binlog_format=row
log-slave-updates=true

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

slave 配置文件

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[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
#
# 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
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_connections=200
character-set-server=utf8mb4
innodb_buffer_pool_size=128M 
slow_query_log=1
long_query_time=5
innodb_flush_log_at_trx_commit=1
##  备机配置 为2  
server-id = 2
log_bin=mysql-bin
##  缓冲区配置
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4