mysql主从安装

前期准备

• 每个节点设置hostname，并添加/etc/hosts
• mysql下载地址

https://downloads.mysql.com/archives/community/

彻底删除系统自带的mariadb，postfix和mysql

rpm -qa|grep mariadb postfix
yum remove mariadb-libs-5.5.60-1*  postfix-2:*     -y

rpm -qa|grep -i mysql
yum remove -y mysql-*       #卸载mysql

find / -name mysql
rm -rf /usr/share/mysql       #删除MySQL相关文件

rm -rf /etc/my.cnf
rm -rf /var/log/mysqld.log    #如果不删除这个文件,则重新安装的Mysql将无法设置password

安装perl

yum install perl -y

安装mysql

rpm -ivh mysql-community-client-plugins-8.0.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-common-8.0.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-debuginfo-8.0.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-embedded-compat-8.0.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-icu-data-files-8.0.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-8.0.30-1.el7.x86_64.rpm --force --nodeps          #强制安装
rpm -ivh mysql-community-server-8.0.30-1.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-community-server-debug-8.0.30-1.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-community-test-8.0.30-1.el7.x86_64.rpm --force --nodeps
rpm -ivh /root/gperftools-libs-2.6.1-1.el7.x86_64.rpm
rpm -ivh /root/gperftools-devel-2.6.1-1.el7.x86_64.rpm

 主节点my.cnf文件

[mysqld]
skip-grant-tables      #root跳过密码登录，安装完后注意去掉此参数重启。首次启动也可以通过grep password /var/log/mysqld.log查看密码

lower_case_table_names = 1      #大小写不敏感,首次启动必须有此参数，后续添加不生效且报错
authentication_policy = mysql_native_password
datadir=/data/mysql
max_connections = 4000
log_bin = mysql-bin
sync_binlog = 5
binlog_expire_logs_seconds = 259200
#注意，每台服务器的 server_id 必须不一样，其它配置一样。
server_id = 44
gtid_mode = ON
enforce_gtid_consistency = ON
log_replica_updates = 1
relay_log_purge = 0
#开启独立表空间
innodb_file_per_table = ON
##隔离级别
transaction_isolation=READ-COMMITTED
##跳过 DNS 解析
skip_name_resolve = ON
#binglog 格式
binlog_format = row
##开启定时任务
event-scheduler = ON
#############################error and slow log#####################
slow_query_log = 1
slow_query_log_file =/data/mysqllog/mysql-slow.log
long_query_time = 3
log_error=/data/mysqllog/mysql-error.log
########################### 配置 innodb###############
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 12884901888 #默认134217728byte(也就是128MB)，建议修改为机器的70%-75%

从节点my.cnf

[mysqld]
skip-grant-tables
lower_case_table_names = 1
authentication_policy = mysql_native_password
datadir=/data/mysql
max_connections = 4000
sync_binlog = 5
binlog_expire_logs_seconds = 259200
#注意，每台服务器的 server_id 必须不一样，其它配置一样。
server_id = 45
gtid_mode = ON
enforce_gtid_consistency = ON
log_replica_updates = 1
relay_log_purge = 0
#开启独立表空间
innodb_file_per_table = ON
##隔离级别
transaction_isolation=READ-COMMITTED
##跳过 DNS 解析
skip_name_resolve = ON
#binglog 格式
binlog_format = row
##开启定时任务
event-scheduler = ON
#############################从库开启多线程同步 只在 slave 上配置#####
slave_parallel_workers = 16
slave_parallel_type = logical_clock
#############################error and slow log#####################
slow_query_log = 1
slow_query_log_file =/data/mysqllog/mysql-slow.log
long_query_time = 3
log_error=/data/mysqllog/mysql-error.log
########################### 配置 innodb###############
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 12884901888 #默认134217728byte(也就是128MB)，建议修改为机器的70%=75%

创建数据目录并授权

创建存储目录
mkdir -p /data/mysql
创建日志目录
mkdir -p /data/mysqllog
配置权限
chown -R mysql:mysql /data/mysql
chown -R mysql:mysql /data/mysqllog

设置开机自启，启动服务

systemctl enable mysqld --now && systemctl status mysqld

登录 MySQL 修改密码，并授权可远程登录

UPDATE mysql.user SET authentication_string='*14CF315C27425870B6B841EC78E21378C8BEE3CA' WHERE user='root'; #密码是Aa.123456经过SHA256加密后的结果,后续必须使用alter语句更改密码

FLUSH PRIVILEGES;

sed -i '/skip-grant-tables/d' /etc/my.cnf           #删除跳过密码登录配置

systemctl restart mysqld

alter user 'root'@'localhost' identified with mysql_native_password by 'Aa.1234567';     #后续更改密码指令

update mysql.user set host='%' where user='root';    #设置远程登录
flush privileges;

设置密码策略

#密码策略0：(Low)密码长度最少8个字符，1：(Mediumpolicy)至少包含1个数字，1个小写字母，1个大写字母和1个特殊字符组成(默认值)

  2：(Strongpolicy)长度为4或更长的密码子字符串不得与字典文件中的单词匹配

set persist validate_password.policy=1;
set persist validate_password.length=8;

主机器上创建主从同步账号

#Mysql5.7 在 Master 创建主从同步账号
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' IDENTIFIED BY 'Weksops398$'; 
FLUSH PRIVILEGES;

#Mysql8.0 在 Master 创建主从同步账号
CREATE USER 'repluser'@'%' IDENTIFIED WITH mysql_native_password BY 'Weksops398$';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

从节点配置同步

change master to 
master_host='192.168.1.1',master_user='repluser',master_password='Weksops398$',master_aut
o_position=1;
start slave;
show slave status\G #查看主从复制状态

检查主从状态

Slave_IO_Running:Yes 
Slave_SQL_Running:Yes
#都为 YES 表示同步成功，此时可以在 Master 创建一个数据库，看是否可以同步