MySQL 8.0.13 主从配置

软件版本:

1. mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz
2. CentOS Linux release 7.2.1511

角色	IP
MySQL主节点	192.168.1.103
MySQL从节点	192.168.1.156

软件上传到 /data/software 文件夹下

# 关闭防火墙和selinux
mkdir -p /data/software

# 上传 mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz

在两台主机上操作

groupadd mysql
useradd -r -g mysql mysql

mkdir -p /data/software
cd /data/software

tar -xvf mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz

mv mysql-8.0.13-linux-glibc2.12-x86_64  /usr/local/mysql8

mkdir /data/mysql8
cd /data/mysql8/
mkdir data log

chown -R mysql.mysql /data/mysql8/

echo "export PATH=$PATH:/usr/local/mysql8/bin"  >>  /etc/profile
source /etc/profile

touch /data/mysql8/log/mysql-err.log
chown mysql.mysql  /data/mysql8/log/mysql-err.log

/usr/local/mysql8/bin/mysqld --initialize --user=mysql --lower-case-table-names=1 --basedir=/usr/local/mysql8 --datadir=/data/mysql8/data

在 MySQL主节点（192.168.1.103） 上操作

cat > /etc/my.cnf << EOF
[mysqld]
port=3306
log-error=/data/mysql8/log/mysql-err.log
datadir=/data/mysql8/data
socket=/tmp/mysql.sock
symbolic-links=0

default-time_zone='+8:00'
lower_case_table_names=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLE
max_connect_errors=2000
max_connections=1500
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'

server-id = 1
log-bin=/data/mysql8/data/mysql-bin

gtid-mode=on                  
enforce-gtid-consistency=true 
log-slave-updates=1           

!includedir /etc/my.cnf.d
EOF


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

vi /etc/init.d/mysqld
# 找到
basedir=
datadir=

#改成
basedir=/usr/local/mysql8
datadir=/data/mysql8/data


chmod +x /etc/init.d/mysqld

/etc/init.d/mysqld start

# 修改 MySQL密码和创建同步用户, 注意同步用户的网段
mysql -uroot -p

ALTER USER 'root'@'localhost' IDENTIFIED BY 'IQ5%S1mUi4pcAxEd'; 

use mysql;

update user set host = '%' where user = 'root';
FLUSH PRIVILEGES;

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'IQ5%S1mUi4pcAxEd'; 
ALTER USER 'root'@'%' PASSWORD EXPIRE NEVER;

FLUSH PRIVILEGES;


CREATE USER 'rep'@'192.168.1.%' IDENTIFIED BY '123456';
alter USER rep@'192.168.1.%' IDENTIFIED WITH sha256_password BY '123456';

GRANT replication slave ON *.* TO 'rep'@'192.168.1.%' WITH GRANT OPTION;

flush privileges;

在 MySQL从节点（192.168.1.156） 上操作

cat > /etc/my.cnf << EOF
[mysqld]
port=3306
log-error=/data/mysql8/log/mysql-err.log
datadir=/data/mysql8/data
socket=/tmp/mysql.sock
symbolic-links=0

default-time_zone='+8:00'
lower_case_table_names=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLE
max_connect_errors=2000
max_connections=1500
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'

server-id = 2
read-only 

gtid-mode=on                 
enforce-gtid-consistency=true 
log-slave-updates=1           

!includedir /etc/my.cnf.d
EOF

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

vi /etc/init.d/mysqld
# 找到
basedir=
datadir=

#改成
basedir=/usr/local/mysql8
datadir=/data/mysql8/data


chmod +x /etc/init.d/mysqld

/etc/init.d/mysqld start

# 修改 MySQL密码和开启同步，注意核对同步账号网段
mysql -uroot -p

ALTER USER 'root'@'localhost' IDENTIFIED BY 'IQ5%S1mUi4pcAxEd'; 

change master to master_host='192.168.1.103'  ,master_user='rep', master_password='123456',master_auto_position=1;

start slave;
flush privileges;

show slave status\G;