mysql主从服务配置

主从MySQL服务器
[root@localhost ~]# yum -y install ntpdate
[root@localhost ~]# ntpdate cn.ntp.org.cn
[root@localhost ~]# yum -y install rsync
[root@localhost ~]# vim mysql.sh 
#!/bin/bash
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
[root@localhost ~]# bash mysql.sh 
9,Rg+?q3jxlc
[root@localhost ~]# service mysql8 start
[root@localhost ~]# vim /usr/local/mysql/my.cnf
[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
[root@localhost ~]# service mysql8 start
Starting MySQL.Logging to '/usr/local/mysql/data/db01-master.err'.
 SUCCESS! 
[root@localhost ~]# chkconfig --add mysql8 //开机自启
[root@localhost ~]# chkconfig mysql8 on //同上
[root@localhost ~]# chkconfig --list //同上
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# setenforce 0
从服务器配置
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# setenforce 0
[root@localhost ~]# 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
[root@localhost ~]# source mysql.sh 
[root@localhost ~]# vim /usr/local/mysql/my.cnf
[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
[root@localhost ~]# service mysql8 stop //停止主服务器
[root@localhost ~]# rm -rf /usr/local/mysql/data/auto.cnf //防止主从失败
[root@localhost ~]# yum list installed | grep rsync //检查rsync是否安装
[root@localhost ~]# rsync -av /usr/local/mysql/data root@192.168.1.32:/usr/local/mysql/
[root@localhost ~]# service mysql8 start //主从服务器启动服务
[root@localhost ~]# mysql -P3310 -p9,Rg+?q3jxlc //从服务器使用主服务器账户密码可以登录
[root@localhost ~]# cd /usr/local/mysql/
[root@localhost mysql]# ./bin/mysql_secure_installation //无法修改密码所需操作
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
[root@localhost mysql]# ./bin/mysql -p9,Rg+?q3jxlc
mysql> show variables like 'validate%';
mysql>  set global validate_password.length=0; //修改密码所需操作
mysql> show variables like 'validate%';
mysql> create user 'zhangmin'@'%' identified by 'abc_123';
mysql> grant replication slave on *.* to 'zhangmin'@'%';
mysql> flush tables with read lock;
mysql> 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;
从服务器
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;
mysql> start slave;
mysql> show slave status\G
[root@localhost mysql]# mysql -uzhangmin -pabc_123 -h 192.168.1.31 -P3306 --get-server-public-key //获得远程master主机的公钥
[root@localhost mysql]# mysql -P3310 -p
mysql> stop slave;
mysql> reset slave;
mysql> 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;
mysql> start slave;
主服务器
mysql> unlock tables; //解锁⽂件
mysql> create database if not exists test charset utf8mb4;