Mysql双主部署

本文作者：丁辉

部署 Mysql 双主

Mysql-Rpm文件下载

部署

双节点都执行以下操作

1. 下载 Mysql Rpm 文件

   yum install -y wget
   wget https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm

2. 安装 MySQL

   yum install -y mysql80-community-release-el7-7.noarch.rpm
   yum -y install mysql-community-server --enablerepo=mysql80-community --nogpgcheck

3. 启动 MySQL Server

   systemctl start mysqld
   systemctl enable mysqld
   systemctl status mysqld

配置

1. 查看临时密码

   grep 'temporary password' /var/log/mysqld.log

2. 修改临时密码并配置允许远程登录

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
   USE mysql;
   UPDATE user SET host = '%' WHERE user = 'root';
   FLUSH PRIVILEGES;

Mysql 1号主节点配置

1. 停止数据库

   systemctl stop mysqld

   备份并编辑配置文件

   cp /etc/my.cnf /etc/my.cnf.bak
   vi /etc/my.cnf

   [mysqld]
   pid-file=/var/run/mysqld/mysqld.pid
   socket=/var/lib/mysql/mysql.sock
   datadir=/var/lib/mysql
   log-error=/var/log/mysqld.log
   secure-file-priv=NULL
   # Disabling symbolic-links is recommended to prevent assorted security risks
   symbolic-links=0
   # 服务端默认utf8编码
   character-set-server=utf8mb4
   # 默认存储引擎
   default-storage-engine=INNODB
   
   
   # 主从配置
   log-bin=binlog
   server-id=121
   gtid-mode=on
   enforce-gtid-consistency=on
   log-slave-updates=on
   expire_logs_days=14
   
   # Compatible with versions before 8.0
   default_authentication_plugin=mysql_native_password
   skip-host-cache
   skip-name-resolve
   
   [client]
   #设置客户端编码
   default-character-set=utf8mb4
   [mysql]
   # 设置mysql客户端默认编码
   default-character-set=utf8mb4

2. 重新启动

   systemctl start mysqld

3. 登录数据库授权

   CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
   flush privileges;
   SHOW MASTER STATUS; #此信息记录等会配置另外一台节点需要用

Mysql 2号主配置

1. 停止数据库

   systemctl stop mysqld

   备份并编辑配置文件

   cp /etc/my.cnf /etc/my.cnf.bak
   vi /etc/my.cnf

   [mysqld]
   pid-file=/var/run/mysqld/mysqld.pid
   socket=/var/lib/mysql/mysql.sock
   datadir=/var/lib/mysql
   log-error=/var/log/mysqld.log
   secure-file-priv=NULL
   # Disabling symbolic-links is recommended to prevent assorted security risks
   symbolic-links=0
   # 服务端默认utf8编码
   character-set-server=utf8mb4
   # 默认存储引擎
   default-storage-engine=INNODB
   
   
   # 主从配置
   log-bin=binlog
   server-id=122
   gtid-mode=on
   enforce-gtid-consistency=on
   log-slave-updates=on
   expire_logs_days=14
   
   # Compatible with versions before 8.0
   default_authentication_plugin=mysql_native_password
   skip-host-cache
   skip-name-resolve
   
   [client]
   #设置客户端编码
   default-character-set=utf8mb4
   [mysql]
   # 设置mysql客户端默认编码
   default-character-set=utf8mb4

2. 重新启动

   systemctl start mysqld

3. 登录数据库授权

   CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
   flush privileges;
   SHOW MASTER STATUS; #此信息记录等会配置另外一台节点需要用

Mysql 1号主节点配置

1. 开启同步

   CHANGE MASTER TO
   MASTER_HOST='', #IP地址
   MASTER_USER='slave',
   MASTER_PASSWORD='password', #密码
   MASTER_PORT=3306,
   MASTER_LOG_FILE='binlog.000002', #节点2 binlog 信息
   MASTER_LOG_POS=821; #节点2 log_pos信息

2. 开启主从同步

   start slave;

3. 查看同步状态

   show slave status\G;

Mysql 2号主节点配置

1. 开启同步

   CHANGE MASTER TO
   MASTER_HOST='', #IP地址
   MASTER_USER='slave',
   MASTER_PASSWORD='password', #密码
   MASTER_PORT=3306,
   MASTER_LOG_FILE='binlog.000002', #节点1 binlog 信息
   MASTER_LOG_POS=821; #节点1 log_pos信息

2. 开启主从同步

   start slave;

3. 查看同步状态

   show slave status\G;

Mysql 配置密码等级

set global validate_password.policy=0;
set global validate_password.mixed_case_count=0;
set global validate_password.number_count=0;
set global validate_password.special_char_count=0;
set global validate_password.length=3;

1.修改密码强度等级为0，即是LOW;
2.修改密码至少要包含的大写字母和小写字母的个数为0;
3.修改密码至少要包含的数字个数为0;
4.修改密码至少要包含的特殊字符的个数为0;
5.修改密码的长度最小个数为3;