default
menu home
图标库 注册 登录 favorite_border message help_outline
在线笔记 - 人性化网络收藏夹

mysql8开启主从同步注意事项,否则会动不动就出错停止

style分类标签: 全部 CSS html javascript php/other IT
by: fanshome  2023-06-26 06:28(UTC)

默认情况下,mysql只允许本地登录,如果要开启主从同步,主机必须开启远程连接,则需要修改mysqld配置文件,以ubuntu 22.04+mysql8.0为例,需要修改:/etc/mysql/mysql.conf.d/mysqld.cnf

找到bind-address = 127.0.0.1这一行

改为bind-address = 0.0.0.0即可

安全起见,mysql使用ssl方式登录

mysql8.0的证书目录:

/var/lib/mysql,找到主机的ca证书文件,复制到从机备用。

设置主从同步,注意一定要 !跳!过! 不需要同步的库和表,否则很容易就报错并会停止同步了。

主机:

[mysqld]

server-id=1

log-bin=mysql-bin

log-slave-updates

binlog-ignore-db = mysql

binlog-ignore-db = information_schema

binlog-ignore-db = performance_schema

binlog-ignore-db = phpmyadmin#如果有使用的话

binlog-ignore-db = sys

replicate-wild-ignore-table = mysql.%

replicate-wild-ignore-table = information_schema.%

replicate-wild-ignore-table = performance_schema.%

replicate-wild-ignore-table = phpmyadmin.%

replicate-wild-ignore-table = sys.%

expire_logs_days=7

从机:

[mysqld]

server-id=2

log-bin=mysql-bin

log-slave-updates

binlog-ignore-db = mysql

binlog-ignore-db = information_schema

binlog-ignore-db = performance_schema

binlog-ignore-db = phpmyadmin#如果有使用的话

binlog-ignore-db = sys

replicate-wild-ignore-table = mysql.%

replicate-wild-ignore-table = information_schema.%

replicate-wild-ignore-table = performance_schema.%

replicate-wild-ignore-table = phpmyadmin.%

replicate-wild-ignore-table = sys.%

expire_logs_days=7

导出主机需要同步的库表数据到从机后:

主机:reset master;

从机:mysql -p

stop slave;

change master to master_host='*.*.*.*',master_user='*******',master_password='*******',master_log_file='mysql-bin.000001',master_log_pos=126,master_ssl=1,master_ssl_ca='/var/lib/mysql/*****.ca.pem';

start slave;

show slave status;

此时,从机就应该开始能自动同步了。

visibility 1549


- 重度网络用户必备 在线笔记
adimg
logo 发表评论

captcha
请正确填写验证码
取 消