MySQL 8.x 数据库主从复制搭建

前提：MySQL修改 server-uuid的方法

前提:如果服务器是克隆master的服务器的，server-uuid值都是一样的。会导致主从复制报错误1593，修改一下server-uuid以后重启MySQL

1. 查找mysql的安装地址

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

-- 通过mysql生成一个uuid 进行记录 等会用于修改
mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 3d62ab83-8cc1-11ed-94da-000c29e9ce30 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> 

2. 然后进入datadir目录

cd /var/lib/mysql/
vim auto.cnf
//修改server-uuid

3. 重启mysql

systemctl restart mysqld

1. MySQL 主从复制原理

2. 主机配置(master)

#修改配置文件：
vim /etc/my.cnf

# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#[必须] 主服务器唯一ID
server-id=1
#[必须]启用二进制日志，指明路径。
log-bin=mysql-bin
binlog-do-db=mydb1
binlog_format=STATEMENT

3. 从机配置

#修改配置文件：
vim /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#[必须] 主服务器唯一ID
server-id=2
# 启用中继日志
relay-log=mysql-relay

4. 主机、从机重启 MySQL 服务

systemctl restart mysqld

5. 主机从机都关闭防火墙

6. 在主机上建立帐户并授权 slave

#在主机MySQL里执行授权命令
CREATE USER 'slave2'@'%' IDENTIFIED BY '123123';
GRANT REPLICATION SLAVE ON *.*
#此语句必须执行。否则主从复制时会报错：Authentication plugin ‘caching_sha2_password‘ reported error: Authentication
ALTER USER 'slave2'@'%' IDENTIFIED WITH mysql_native_password BY '123123';

flush privileges;

#查询master的状态
show master status;

#记录下File和Position的值
mysql-bin.000004   441
#执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化

7. 在从机上配置需要复制的主机

#在从机中执行下面命令
# MASTER_HOST: 为master节点ip
# MASTER_USER: master创建的主从复制的用户名
# MASTER_PASSWORD:  master创建的主从复制的密码
# MASTER_LOG_FILE: 上面记录的file文件名
# MASTER_LOG_POS: 上面记录的position值
CHANGE MASTER TO MASTER_HOST='192.168.200.132',
MASTER_USER='slave2',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=441;

启动从服务器的复制功能

start slave;

查看从服务器状态

show slave status\G;

下面两个参数都是Yes，则说明主从配置成功！

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

8. 主机新建库、新建表、insert 记录，从机复制

#建库语句
CREATE DATABASE mydb1;

#建表语句
CREATE TABLE mytbl(id INT,NAME VARCHAR(50));

#插入数据
INSERT INTO mytbl VALUES(1,"zhang3");

9. 如何停止从服务复制功能

stop slave;

10. 如何重新配置主从

stop slave;

reset master;

# 然后查询master的状态，继续往下
show master status;