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]

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 = 10
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 = 6442450944 #约默认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

设置root密码

第一次安装完成，先启动服务，再查看默认生成的密码并修改为自己的密码；
查看默认密码：grep password /var/log/mysqld.log
也可在my.cnf中配置skip-grant-tables参数，root跳过密码登录，具体看主从安装文档

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

UPDATE user SET password=PASSWORD('新密码') WHERE user='root';
grant all privileges on *.* to '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 创建一个数据库，看是否可以同步