mysql主从复制

master配置

[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock

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

log-bin=mysql-bin  # 启用二进制日志
server-id=98   # 服务器唯一ID，可以任意设置，但必须唯一
#binlog-ignore-db=mysql  # 不要复制的数据库
#binlog-do-db=testdb     # 要复制的数据库名

max_connections = 2000
character_set_server=utf8mb4
lower_case_table_names = 1
binlog_expire_logs_seconds=8553600
innodb_flush_log_at_trx_commit=2
max_allowed_packet = 128M
innodb_buffer_pool_size=2G
binlog_cache_size = 2M
innodb_open_files = 4096
table_definition_cache = 4096
table_open_cache_instances = 64
thread_cache_size = 64

启动master，创建同步的用户CREATE USER tbuser IDENTIFIED BY 'Mytb@2022';

MySQL主从复制报错Authentication plugin ‘caching_sha2_password‘ reported error: Authentication,原因：主库repl的plugin是caching_sha2_password 导致连接不上，修改为mysql_native_password即可解决。

ALTER USER 'tbuser'@'%' IDENTIFIED WITH mysql_native_password BY 'Mytb@2022';

查看主服务器状态show master status;，获得File和Position

slave配置

# 修改server-id和master不一样，其他一样
server-id=99

slave启动进入mysql，设置主从同步

change master to master_host='192.168.105.98', master_user='tbuser',master_password='Mytb@2022',master_log_file='mysql-bin.000003',master_log_pos=3273;

master_log_file和master_log_pos是前面show master status;获得的File和Position

最后start slave；

Slave_IO_Running和Slave_SQL_Running都为yes即可