mysql-8.0.x搭建主从

概述

master/slave用作备库的形式数据库的可用性

开干

env

• centos7.x 2c4g x2
• mysql 8.0.28

x01、mysql安装

• mysql-8.0.x搭建

x02、修改my.cnf

master

[mysql]
auto-rehash
default-character-set = utf8mb4
connect-timeout = 3

[mysqld]
server-id = 7670
port = 3306

#base
basedir = /servyou/mysql
datadir = /servyou/mysql_data

#innodb
default-storage-engine = INNODB
innodb_buffer_pool_size = 2G

#set character
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'

#optimazed
skip-name-resolve

#default_authentication_plugin = mysql_native_password
authentication_policy = mysql_native_password

max_allowed_packet = 250M
lower_case_table_names = 1
max_connections = 2000
max_user_connections = 1500
explicit_defaults_for_timestamp = true
default-time-zone = system

#log
binlog_format = ROW
log-bin = mysql-bin
max_binlog_size = 1G
#expire_logs_days = 30
binlog_expire_logs_seconds = 2592000
slow_query_log = 1
long-query-time = 30
log_timestamps = SYSTEM
log_bin_trust_function_creators = 1

#master/slave optimized
gtid_mode = ON
enforce_gtid_consistency = ON
#log_slave_updates = 1    --deprecated
log_replica_updates = 1
#slave-parallel-workers = 4  --deprecated
replica_parallel_workers = 4
relay-log = relay-bin

#read_only = 1
#super_read_only = 1

[mysqld_safe]
socket = /servyou/mysql_data/mysql.sock
log-error = /servyou/mysql_data/mysql_error.log
pid-file = /servyou/mysql_data/mysqld.pid

slave

[mysql]
auto-rehash
default-character-set = utf8mb4
connect-timeout = 3

[mysqld]
server-id = 76701  ### master/salve 不能一样
port = 3306

#base
basedir = /servyou/mysql
datadir = /servyou/mysql_data

#innodb
default-storage-engine = INNODB
innodb_buffer_pool_size = 2G

#set character
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'

#optimazed
skip-name-resolve

#default_authentication_plugin = mysql_native_password
authentication_policy = mysql_native_password

max_allowed_packet = 250M
lower_case_table_names = 1
max_connections = 2000
max_user_connections = 1500
explicit_defaults_for_timestamp = true
default-time-zone = system

#log
binlog_format = ROW
log-bin = mysql-bin
max_binlog_size = 1G
#expire_logs_days = 30
binlog_expire_logs_seconds = 2592000
slow_query_log = 1
long-query-time = 30
log_timestamps = SYSTEM
log_bin_trust_function_creators = 1

#master/slave optimized
gtid_mode = ON
enforce_gtid_consistency = ON
#log_slave_updates = 1    --deprecated
log_replica_updates = 1
#slave-parallel-workers = 4  --deprecated
replica_parallel_workers = 4
relay-log = relay-bin

### slave要开启禁止写
read_only = 1
super_read_only = 1

[mysqld_safe]
socket = /servyou/mysql_data/mysql.sock
log-error = /servyou/mysql_data/mysql_error.log
pid-file = /servyou/mysql_data/mysqld.pid

x03、创建repl账户及开启复制通道

create user on master

create user 'repl'@'172.%' identified by '123456';
grant replication slave on *.* to 'repl'@'172.%';
flush privileges;

on master

show master status;
SHOW BINARY LOGS;
show slave hosts;

open replication channel

#第一次安装pos ->FIRST
CHANGE MASTER TO
MASTER_HOST='192.168.211.128',
MASTER_USER='repl',
MASTER_PASSWORD='mysql',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;

#从特定的pos开始复制
change MASTER to MASTER_HOST = '172.24.20.22',
MASTER_USER = 'repl',
MASTER_PASSWORD = '123456',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 834;

start slave

start slave;
show slave status\G;

x04、测试主从复制

#slave测试，是否可以创建表及库

#master测试，进入创建库及表，看看slave节点是否有对应的数据