MySQL8主主

Edited on In ,

前言

都说MySql8.x比5.7快两倍，可以参考一下。
但不支持从 MySQL 8.0 降级到 MySQL 5.7（或从某个 MySQL 8.0 版本降级到任意一个更早的 MySQL 8.0 版本）。数据备份方式还是可以的。

  • 注意MySql8 的用户安全策略的改变
  • 注意MySql8 编码格式
  • 支撑内网服务器安装

准备

架构设计

主主

服务器配置

  • 192.168.2.33 master33 server_id=33
  • 192.168.2.34 master34 server_id=34

对于目前的状况，很少使用 MySQL 自增主键，解决主键冲突问题的最简单的方式，就是错开主键，可以在 my.cnf 文件中配置。

安装

MySQL

Centos7 RPM安装MYSQL8
Centos7 解压安装MYSQL8

关闭防火墙

1
2
systemctl stop firewalld
systemctl disable firewalld

配置

1、MySQL

1
vim /etc/my.cnf

2、配置

  • master33 :server_id=33 `log-bin=mysql-bin`
  • master34 :server_id=34 `log-bin=mysql-bin`
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
socket=/opt/db/mysql_data/mysql.sock
[mysqld]
skip-name-resolve
#设置3306端口
port=3306 
datadir=/opt/db/mysql_data
socket=/opt/db/mysql_data/mysql.sock
log-error=/opt/db/mysql_log/mysqld.log
pid-file=/opt/db/mysqld/mysqld.pid
# 服务ID，集群中唯一
server-id=33
# 二进制日志
log-bin=mysql-bin
# 指定mysql的binlog日志记录哪个db，如果复制多个数据库，重复设置这个选项即可
# binlog-do-db：
# 参数是在slave上配置，指定slave要同步的数据库，默认所有库，如果复制多个数据库，重复设置这个选项即可
# replicate-do-db=aa   
# 自增主键步值auto_imcrement。一般有n台主MySQL就填n，保证主键不冲突
auto_increment_increment=2   
# 自增起始值。一般填第n台主MySQL。此时为第一台主MySQL
auto_increment_offset=1   
# 密码认证插件
default_authentication_plugin=mysql_native_password
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
#排序规则
collation-server=utf8mb4_0900_ai_ci
#utf8mb4_0900_ai_ci  排序规则：ai 口音不敏感  ci 不区分大小写  ,默认支持表情符号
#utf8mb4_0900_ai_ci  属于 utf8mb4_unicode_ci 中的一种
#utf8mb4_general_ci  没有实现utf8mb4_unicode_ci 的排序规则。没有utf8mb4_unicode_ci 准备。但是比较和排序的时候更快
# 创建新表时将使用的默认存储引擎,innodb支持事物
default-storage-engine=INNODB 
# 数据库不区分大小写
lower_case_table_names=1
max_allowed_packet=16M
lc-messages-dir=/usr/share/mysql-8.0

重启 master33、master34

1
2
3
4
systemctl restart mysqld
systemctl status mysqld
# 查看日志
cat /opt/db/mysql_log/mysqld.log

master33 master34 SQL命令：

1
2
3
4
5
6
7
8
9
10
11
# 查询默认认证插件
show variables like 'default_authentication_plugin';
# 查询master slave 保证不重复
show variables like '%server_id%';
# 查询master slave,如果是复制的数据库data，则 uuid有可能一样
show variables like '%server_uuid%';
# 查询账号认证模式
select host, user, plugin
from mysql.user;
# 重置数据库slave信息
reset slave;

3、master33为主库

主备配置

master33配置同步账号

SQL命令：

1
2
3
4
5
6
7
8
9
10
11
# 这里创建账号不指定地址，一般情况下，需要指定 slave 账号的 IP
create user sync@'192.168.2.34' identified by 'sync';
grant replication slave on *.* to sync@'192.168.2.34';
# 使用 mysql_native_password 认证码模式
alter user sync@'192.168.2.34' identified with mysql_native_password by 'sync';
flush privileges;
# 查询账号认证模式
select host, user, plugin
from mysql.user;
# 查看配置
show master status;

image-20210826090146083

在 master34写入 master33的配置

master34 SQL命令：

1
2
3
4
5
6
7
8
-- 停止 slave 库
stop slave;
-- 写入配置
CHANGE MASTER TO MASTER_HOST ='192.168.2.33',MASTER_USER ='sync',MASTER_PASSWORD ='sync',MASTER_LOG_FILE ='mysql-bin.000009',MASTER_LOG_POS =156;
-- 启动从库
start slave;
-- 查看从库状态
SHOW SLAVE STATUS\G;

两个都为 Yes 则为成功。

测试 master33为主库

登录

1
mysql -uroot -p

查看主库状态

master33 SQL命令：

1
2
3
show processlist
\
G

Command: Binlog Dump状态为成功。

插入数据

1
2
3
4
5
6
7
8
show databases;
create database test_db2;
use test_db2;
create table test_table(
	id int
);
insert into test_table values(1);
select * from test_table;

image-20210826143802988

master34 查询数据

SQL命令：

1
2
3
show databases;
use test_db2;
select * from test_table;

image-20210826143849915

master34插入数据

1
insert into test_table values (2);

在 master33 上数据没有增加。

4、master34为主库

与步骤 3、master33 为主库步骤一样。

master34 SQL命令

1
2
3
4
5
6
7
8
9
10
# 这里创建账号不指定地址，一般情况下，需要指定 slave 账号的 IP
create user sync@'192.168.2.33' identified by 'sync';
grant replication slave on *.* to sync@'192.168.2.33';
# 使用 mysql_native_password 认证码模式
alter user sync@'192.168.2.33' identified with mysql_native_password by 'sync';
flush privileges;
# 查询账号认证模式
select host,user,plugin from mysql.user;
# 查看配置
show master status;

master33 SQL 命令：

1
2
3
4
5
6
7
8
-- 停止 slave 库
stop slave;
-- 写入配置
CHANGE MASTER TO MASTER_HOST='192.168.2.34',MASTER_USER='sync',MASTER_PASSWORD='sync',MASTER_LOG_FILE='mysql-bin.000009',MASTER_LOG_POS=1916;
-- 启动从库
start slave;
-- 查看从库状态
SHOW SLAVE STATUS\G;

错误处理

查看错误日志

1
cat /opt/db/mysql_log/mysqld.log

这是因为的 mysql_data 是复制的，只要修改 auto.cnf 中的

1
2
[auto]
server-uuid=f2c4ab17-01ce-11ec-9d35-000c29d3bb32

重启服务

1
systemctl restart mysqld

正确的 slave结果

操作

同步

  1. master锁表

    1
    2
    FLUSH TABLES WITH READ LOCK;
    show master status;

  2. slave从库

    1
    2
    3
    4
    5
    6
    -- 写入配置
    CHANGE MASTER TO MASTER_HOST='192.168.2.31',MASTER_USER='sync',MASTER_PASSWORD='sync',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=156;
    -- 启动从库
    start slave;
    -- 查看从库状态
    SHOW SLAVE STATUS\G;

  3. master解锁

    1
    unlock tables;

推荐

MySQL8.0创建用户及其配置
MySQL8.0新特性-新的索引方式
MySQL8.0新特性-通用表表达式（CTE）
MySQL8.0新特性-窗口函数
MySQL8.0新特性-InnoDB增强
MySQL8.0新特性-JSON增强
[官方介绍]([https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts

本文地址： https://github.com/maxzhao-it/blog/post/3b97adfc/