mysql 主从安装

mysql 版本: 8.0.45 

下载地址: MySQL :: Download MySQL Community Server

1. 安装mysql

## 1. mysql安装包上传到服务器
# tar -C 切换保存解压文档到指定目录下
## 2. 解压tar文件 tar -xvf mysql-8.0.40-1.el9.x86_64.rpm-bundle.tar -C rpm
    yum remove mariadb* -y  # 移除mariadb安装
    rpm -ivh mysql-community-server-8.0.40-1.el9.x86_64.rpm  --nodeps --force
    rpm -ivh mysql-community-common-8.0.40-1.el9.x86_64.rpm
    rpm -ivh mysql-community-icu-data-files-8.0.40-1.el9.x86_64.rpm
    rpm -ivh mysql-community-client-plugins-8.0.40-1.el9.x86_64.rpm
    rpm -ivh mysql-community-libs-8.0.40-1.el9.x86_64.rpm
    rpm -ivh mysql-community-client-8.0.40-1.el9.x86_64.rpm
    systemctl enable mysqld && systemctl start mysqld # 启动mysql
## 3. 查询临时root账户密码 grep 'password' /var/log/mysqld.log
    grep 'password' /var/log/mysqld.log
## 4. 修改root账户密码 mysqladmin -u root -pB_idgeauH1y# password xxx
    mysqladmin -u root -pB_idgeauH1y# password xxx

2. 创建同步账号

## 创建数据库
CREATE DATABASE test CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_520_ci';
## 创建用户名
create user 'test'@'%' IDENTIFIED BY 'Test#@2026';
## 用户授权
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%';
## 刷新权限
FLUSH PRIVILEGES;

3. 修改mysql的my.ini配置

# mysql-1
## 当前节点服务id
server-id=1
binlog_format=ROW
## 自动增长偏移量
auto_increment_increment=2
## 自动增长id开始值
auto_increment_offset=1

# mysql-2
server-id=2
binlog_format=ROW
auto_increment_increment=2
auto_increment_offset=2

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

# /opt/data 为数据库数据保存位置
[mysqld]
datadir=/opt/data/mysql
socket=/opt/data/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server-id=2
binlog_format=ROW
auto_increment_increment=2
auto_increment_offset=1

innodb_buffer_pool_size = 8G
innodb_log_file_size = 1G
max_connections = 500

thread_cache_size = 50

[mysqladmin]
socket=/opt/data/mysql/mysql.sock

[mysql]
socket=/opt/data/mysql/mysql.sock

 4. 配置同步连接

## 设置主机名
hostnamectl set-hostname mysql-1
## 同步服务公共秘钥
mysql -u test -p -h x.x.x.x -P3306 --get-server-public-key
## 查询当前log信息
show master status;
## 创建账户同步信息 master_log_file 和 master_log_pos 为主节点当前状态值
change master to master_host='x.x.x.x',master_user='test',master_password='xxx',master_log_file='binlog.000004',master_log_pos=157;
## 启动 
start slave;
## 查询slave状态
show slave status\G;

5. 问题总结

## 开启同步错误跳跃
SET GLOBAL slave_skip_errors=ON;
## 主从同步跳过错误次数
SET GLOBAL sql_slave_skip_counter = 20;
## 主从同步跳过指定错误码
SET GLOBAL sql_slave_skip_errors='1396';

## 日志文件落地，重新同步(相关命令)
flush logs;
reset replica;
start slave skip to last heartbeat;
stop slave io_thread;
reset slave all;