MySQL主从服务器

主从MySQL服务器配置过程如下：

1. 在主服务器上执行以下操作：

# 安装必要的软件包

yum -y install ntpdate rsync

# 同步时间

ntpdate cn.ntp.org.cn

# 创建MySQL安装脚本

vim mysql.sh

将以下内容粘贴到mysql.sh文件中：

#!/bin/

yum list installed |grep libaio

if [ $? ne 0 ]; then

    yum -y install libaio

fi

echo libaio yes

rm -rf /etc/my.cnf

echo remo my.cnf yes

tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

echo tar zx yes

cp -r ~/mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql

echo copy file to /usr/local/mysql yes

mkdir /usr/local/mysql/mysql-files

echo mysql-files yes

grep mysql /etc/passwd

    useradd -r -s /sbin/nologin mysql

chown mysql:mysql /usr/local/mysql/mysql-files

chmod 750 /usr/local/mysql/mysql-files

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

/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data

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

sed -i '$aexport PATH=/usr/local/mysql/bin:$PATH' /etc/profile

source /etc/profile

保存并退出，然后执行以下命令：

 mysql.sh

service mysql8 start

vim /usr/local/mysql/my.cnf

将以下内容粘贴到my.cnf文件中：

ini

[mysqld]

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

socket=/tmp/mysql.sock

port=3306

log-error=/usr/local/mysql/data/db01-master.err

log-bin=/usr/local/mysql/data/binlog

server-id=10

character_set_server=utf8mb4

保存并退出，然后执行以下命令：

service mysql8 start

chkconfig --add mysql8

chkconfig mysql8 on

chkconfig --list

systemctl stop firewalld

setenforce 0

2. 在从服务器上执行以下操作：

# 关闭防火墙和SELinux

systemctl stop firewalld

setenforce 0

# 注释掉mysql.sh文件中的初始化部分

vim mysql.sh

注释掉以下两行：

#/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/

#/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data

保存并退出，然后执行以下命令：

source mysql.sh

vim /usr/local/mysql/my.cnf

将以下内容粘贴到my.cnf文件中：

ini

[mysqld]

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

socket=/tmp/mysql.sock

port=3310

log-error=/usr/local/mysql/data/db01-slave.err

relay-log=/usr/local/mysql/data/relaylog

server-id=11

character_set_server=utf8mb4

保存并退出，然后执行以下命令：

service mysql8 stop

rm -rf /usr/local/mysql/data/auto.cnf

yum list installed | grep rsync

rsync -av /usr/local/mysql/data root@192.168.1.32:/usr/local/mysql/

service mysql8 start

mysql -P3310 -p9,Rg+?q3jxlc

cd /usr/local/mysql/

./bin/mysql_secure_installation

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0

./bin/mysql -p9,Rg+?q3jxlc

create user 'zhangmin'@'%' identified by 'abc_123';

grant replication slave on *.* to 'zhangmin'@'%';

flush tables with read lock;

change master to

    master_host='192.168.1.31',

    master_user='zhangmin',

    master_password='abc_123',

    master_port=3306,

    master_log_file='binlog.000003',

    master_log_pos=707;

start slave;

show slave status\G

mysql -uzhangmin -pabc_123 -h 192.168.1.31 -P3306 --get-server-public-key

mysql -P3310 -p

stop slave;

reset slave;

change master to master_host='192.168.1.31', master_user='zhangmin', master_password='abc_123', master_port=3306, master_log_file='binlog.000004', master_log_pos=1092;

start slave;

3. 在主服务器上执行以下操作：

mysql> unlock tables;

mysql> create database if not exists test charset utf8mb4;