双主MySQL配置及所遇到的问题（新手小白向）

前言介绍：

双主 MySQL（Dual-Master MySQL 或 Master-Master Replication）是一种 MySQL 数据库复制的架构，其中两个 MySQL 服务器相互之间充当主服务器和从服务器的角色。这意味着每个服务器既可以接受写操作，也可以从对方复制数据。这个架构通常用于提高系统的可用性、容错能力和负载均衡。

双主 MySQL 的关键特点：

1. 双向复制： 每台服务器不仅可以处理自身的数据操作（写操作和读操作），还可以将这些操作复制到另一台服务器上，并从对方服务器上复制数据过来。

2. 高可用性： 如果其中一台服务器发生故障，另一台服务器可以继续工作，确保数据库服务不中断。

3. 负载均衡： 由于两台服务器都可以接受写入和读取操作，可以通过在应用层面进行合理的请求分配，来均衡负载。

4. 故障恢复： 在一台服务器发生故障时，另一台服务器的数据是最新的，可以迅速接管服务。这减少了数据丢失的风险。

第一台服务器配置

配置前须知
1.环境说明:操作系统 CentOS 64 7.6
2.部署目录：![[Pasted image 20240813144340.png]]
3.如果之前按照过必须删除干净
4.linux粘贴可以直接使用鼠标中键

1.删除之前下载过的MySQL

1.1连接服务器

注：使用Xshell 5进行连接服务器

1.2查看原有mysql并且停止

service mysqld stop

1.3查看并删除旧版mysql相关的rpm包

rpm -qa|grep -i mysql

mysql-libs-5.1.73-3.el6_5.x86_64

mysql-5.1.73-3.el6_5.x86_64

mysql-connector-odbc-5.1.5r1144-7.el6.x86_64

mysql-devel-5.1.73-3.el6_5.x86_64

perl-DBD-MySQL-4.013-3.el6.x86_64

mysql-server-5.1.73-3.el6_5.x86_64

MySQL-python-1.2.3-0.3.c1.1.el6.x86_64

#如果实际中查到了上述的rpm包，需要使用以下命令删除这些rpm包

 rpm -ev --nodeps mysql-libs-5.1.73-3.el6_5.x86_64

 rpm -ev --nodeps mysql-5.1.73-3.el6_5.x86_64

 rpm -ev --nodeps mysql-connector-odbc-5.1.5r1144-7.el6.x86_64

 rpm -ev --nodeps mysql-devel-5.1.73-3.el6_5.x86_64

 rpm -ev --nodeps perl-DBD-MySQL-4.013-3.el6.x86_64

 rpm -ev --nodeps mysql-server-5.1.73-3.el6_5.x86_64

 rpm -ev --nodeps MySQL-python-1.2.3-0.3.c1.1.el6.x86_64

1.4查找mysql相关的目录

find / -name mysql

/usr/bin/mysql

/usr/lib64/mysql

/usr/lib64/perl5/auto/DBD/mysql

/usr/lib64/perl5/DBD/mysql

/usr/share/mysql

/usr/include/mysql

/var/lib/mysql

/var/lib/mysql/mysql

#如果实际中查到了上述的目录，则需要使用以下命令删除这些mysql相关的目录

 rm -rf /usr/bin/mysql

 rm -rf /usr/lib64/mysql

 rm -rf /usr/lib64/perl5/auto/DBD/mysql

 rm -rf /usr/lib64/perl5/DBD/mysql

 rm -rf /usr/share/mysql

 rm -rf /usr/include/mysql

 rm -rf /var/lib/mysql

 rm -rf /var/lib/mysql/mysql

2.安装

2.1准备安装包

# 下载mysql-8.0.32-el7-x86_64.tar.gz到 /usr/local，如果你现在在root里面，cd ..到上一级可以看到usr目录

wget -P /usr/local https://cdn.mysql.com/Downloads/MySQL-8.0/mysql-8.0.20-el7-x86_64.tar.gz

2.2创建mysql用户、用户组

groupadd mysql

useradd -s /sbin/nologin -g mysql mysql

2.3 解压到/usr/local/mysql

# 解压安装包
tar -xzvf mysql-8.0.32-el7-x86_64.tar.gz
#将名为 mysql-8.0.32-el7-x86_64 的目录或文件重命名为 mysql
mv mysql-8.0.32-el7-x86_64 mysql

解压过程应该如下：

2.4创建数据存储目录/data/mysql及授权

#创建一个名为 /data/mysql/binlog 的目录。
mkdir -p /data/mysql/binlog
#创建一个名为 /data/mysql/mysqld.log 的文件。
touch  /data/mysql/mysqld.log
#将 /data/mysql/ 目录及其所有子文件和子目录的权限设置为 755:
#(拥有者（第一个 `7`）：可以读、写、执行（rwx）
#组成员（第一个 `5`）：可以读、执行，但不能写（r-x）
#其他用户（第二个 `5`）：可以读、执行，但不能写（r-x）)
chmod -R 755 /data/mysql/
#将 /data/mysql 目录及其所有子文件和子目录的所有者更改为 mysql 用户。
chown -R mysql /data/mysql

2.5创建/etc/my.cnf文件

# 编辑/etc/my.cnf
vim /etc/my.cnf
#输入i编辑
#将下面复制粘贴
# The MySQL server

[client]

port            = 3306

socket          = /data/mysql/mysql.sock

[mysql]  

default-character-set = utf8mb4

[mysqld]

user            = mysql

port            = 3306

datadir=/data/mysql/data

socket=/data/mysql/mysql.sock

log-error=/data/mysql/mysqld.log

default-storage-engine=InnoDB

symbolic-links=0

lower_case_table_names=1

log_bin_trust_function_creators=1

explicit_defaults_for_timestamp=OFF

log_timestamps=SYSTEM

max_allowed_packet = 16M

max_connections=4000

key_buffer_size=200M

table_open_cache = 9000

back_log=1500

table_open_cache_instances=16

skip-grant-tables

#master slave setting

server-id   = 1

# slave

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=4

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

log-bin=/data/mysql/binlog/mysql-bin

binlog_format=mixed

expire_logs_days=30

skip-slave-start

slow_query_log_file = /data/mysql/slow_sql.log

slow_query_log = ON

long_query_time = 20

# files

innodb_file_per_table

innodb_log_file_size=1024M

innodb_log_files_in_group = 3

innodb_open_files=6000

# buffers

innodb_buffer_pool_size=4096M

innodb_buffer_pool_instances=32

##innodb_additional_mem_pool_size=20M

innodb_log_buffer_size=64M

join_buffer_size=2M

sort_buffer_size=2M

[mysqldump]

quick

single-transaction

max_allowed_packet = 16M

#按esc退出编辑
#输入：wq保存编辑并且退出
#再次检查一下（注意：直接粘贴有时候有可能会被直接注释掉)可以先粘贴在记事本直接上传my.cnf
vim /etc/my.cnf
#esc :q退出

2.6 修改/etc/init.d/mysql

#将mysql.server文件复制到 `/etc/init.d/` 目录，并重命名为 `mysql`
scp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
#将使 `mysql` 文件变为可执行脚本，以便系统可以调用它来启动或停止 MySQL 服务
chmod +x /etc/init.d/mysql
#类比前面
scp /root/mysql/support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql

2.7修改一些文件，创建软连接

#/usr/sbin/mysqld

scp /usr/local/mysql/bin/mysqld   /usr/sbin/

#/usr/bin/mysql

 ln  -s  /usr/local/mysql/bin/mysql  /usr/bin/mysql

#/usr/bin/mysqldump

scp /usr/local/mysql/bin/mysqldump   /usr/bin/

#/usr/bin/mysqld_safe

scp /usr/local/mysql/bin/mysqld_safe   /usr/bin/

#/usr/bin/mysqlbinlog

scp /usr/local/mysql/bin/mysqlbinlog  /usr/bin/

#建立libcrypto.so.1.1和libssl.so.1.1文件软连接

ln -s /usr/local/mysql/lib/private/libcrypto.so.1.1 /usr/lib64/

ln -s /usr/local/mysql/lib/private/libssl.so.1.1 /usr/lib64/

2.8手动初始化数据目录

#输入后等待一两分钟
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql

上面一步会随机生成一个root密码保存在/data/mysql/mysqld.log里面,如下图：

3.使用MySQL

3.1 启动服务

service  mysql start

如下，等待。。。走完

[root@vault etc]# service mysql start

Starting MySQL…

3.2登录

#root登录数据库
mysql -uroot -p
#SQL 语句用于确保 `root` 用户的密码不会被标记为过期，避免在登录时强制要求更改密码。
update mysql.user set password_expired='N' where User='root';
quit退出MySQL

3.3开启密码认证

vim /etc/my.cnf

#将skip-grant-tables注释
找到skip-grant-tables加上# （可以输入/skip- 搜索，按N是找下一个）
#skip-grant-tables

重启Mysql

service  mysql restar

3.4修改root密码 才能正常使用

#登录mysql，密码为上面的临时密码，把密码改为G.E.M.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'G.E.M.';
#将 `root` 用户的 `host` 字段设置为 `'%'`，允许 `root` 用户从任何主机连接到 MySQL 服务器。
update mysql.user set host='%'  where User='root';
#刷新 MySQL 的权限系统
FLUSH privileges;

3.5创建数据库用户

#用root用户登录数据库
mysql -u root -p
#然后输入root用户的密码
G.E.M.
#输入下面的命令创建Gloria用户并授权所有
CREATE USER 'Gloria'@'%' IDENTIFIED BY 'G.E.M.';

GRANT ALL privileges ON *.* TO 'Gloria'@'%';

FLUSH privileges;

3.6创建数据库

#下述为创建test数据库为例

#用root用户登录数据库

mysql -u root -p

#然后输入root用户的密码

#输入下面的命令创建数据库并导入数据

create database test default character set utf8;

#导入数据库文件

  source teststructure.sql

第二台服务器配置

重复上述操作即可

双主服务器配置

注意(一定要明确对应好两台机器不要混淆了)大多数错误都是因为这里
需要根据你的服务器修改一下IP

1. my.cnf配置

# 修改master-1的my.cnf配置文件

vi /etc/my.cnf
#找一下有没有下面三句没有就加上
#唯一的 `server-id`，用于区分不同的服务器
server-id=1
#配置启用了二进制日志（binary log），并指定了二进制日志文件的存储路径(生成的日志会被另外一台下载保存为日志)
log-bin=/data/mysql/binlog/mysql-bin
#：启用中继日志，中继日志用于从服务器在复制过程中记录从主服务器接收到的二进制日志。中继日志记录了从服务器执行的 SQL 语句，用于在从服务器上重放这些操作。
relay-log=mysql-relay-bin

# 修改master-2的my.cnf配置文件

vi /etc/my.cnf
#同上（注意要把id = 1改成2）
server-id=2

log-bin=/data/mysql/binlog/mysql-bin

relay-log=mysql-relay-bin

2.同步账号信息

# master-1上新增同步帐号
#注意：这里面的repl_user不用修改
#登录master-1的mysql

mysql -uroot -p #输入密码登录

#执行如下命令：
CREATE USER 'repl_user'@'192.168.6.64' IDENTIFIED WITH mysql_native_password BY 'G.E.M.';

grant REPLICATION SLAVE on *.* to 'repl_user'@'192.168.6.64';

flush privileges;

# master-2上新增同步帐号

#登录master-2的 mysql

mysql -uroot -p #输入密码登录

#执行如下命令：
CREATE USER 'repl_user'@'192.168.6.58' IDENTIFIED WITH mysql_native_password BY 'G.E.M.';

grant REPLICATION SLAVE on *.* to 'repl_user'@'192.168.6.58';

flush privileges;

3.配置master-1的主服务器(1是备2是主)

注意
1.主的操作会同步到备，即2建表后，1中会出现
2.这里要注意操作的是在哪台服务器，show是在2，命令是在1

# 获取master-2的MASTER_LOG_FILE和MASTER_LOG_POS值，步骤如下：
#注意一但改变了数据库的内容上述两个值都会发生改变，都需要重新查看，建议每次都查看
#登录master-2的mysql，执行如下命令：

show master status;

# 配置主服务器（让1变为从，2变为1的主服务器）

#登录master-1的mysql，执行如下命令：
stop slave;

reset slave;

CHANGE MASTER TO MASTER_HOST='192.168.6.64',MASTER_USER='repl_user',

MASTER_PASSWORD='G.E.M.',MASTER_LOG_FILE='mysqlbin.000006',MASTER_LOG_POS=2223;

start slave;

show slave status\G;

执行show slave status\G,查看结果有如下两个YES 表示配置成功了：

4.配置master-2的主服务器

#登录master-1的mysql，执行如下命令：
show master status;

#登录master-2的mysql，执行如下命令：
stop slave;

reset slave;

CHANGE MASTER TO MASTER_HOST='192.168.6.58',MASTER_USER='repl_user',

MASTER_PASSWORD='G.E.M.',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=4096;

start slave;

show slave status\G;

5.检查

可以使用可视化数据库根据例如Navicat Premium检查是否成功

连接两个服务器，建立数据库synctest（两个服务器都要建立！！！），在其中一个建表刷新另外一个会出现（这里会有几十秒延迟），如果成功出现就是成功了。