mysql配置主从同步:mysql配置主从同步
mysql配置主从同步:mysql配置主从同步slave mysql> start slave;7.主库创建表或者变更数据,在从库验证是否同步成功!slave mysql> CHANGE MASTER TO MASTER_HOST = '172.16.1.101' MASTER_USER = 'repl_user' MASTER_PASSWORD = 'pwd_123' MASTER_PORT = 3306 MASTER_LOG_FILE='mysql-bin.000001' MASTER_LOG_POS=11763 MASTER_RETRY_COUNT = 60 MASTER_HEARTBEAT_PERIOD = 10000; 6.启动从库线程#master master linux> systemctl restart mys
1.环境 :首先准备两台服务器或者虚拟机,分别安装好mysql的服务。
#master
mysql5.7 :172.16.1.101 3306
#slave
mysql5.7 :172.16.1.102 3306
mysql安装链接地址:centos7 安装mysql57
2.修改主库和从库的配置文件,并重启修改配置后的服务;
#master config
[mysqld]
server-id = 1 # 节点ID,确保唯一
# log config
log-bin = mysql-bin #binlog日志功能开启
sync_binlog = 1 #事务变更binlog是否写入磁盘,0否1写入
binlog_format = mixed #binlog日志格式,默认statement,建议使用mixed
expire_logs_days = 7 #binlog过期清理时间
max_binlog_size = 100m #binlog每个日志文件大小
binlog_cache_size = 4m #binlog缓存大小
max_binlog_cache_size= 512m #binlog缓存最大值
binlog-ignore-db=mysql #不生成日志文件的数据库,多个忽略数据库可以用逗号拼接,
#或者 复制这句话,写多行
auto-increment-offset = 1 # 自增值的偏移量
auto-increment-increment = 1 # 自增值的自增量
slave-skip-errors = all #跳过从库错误
#slave config
[mysqld]
server-id = 2
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.% #忽略表
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
重启服务:systemctl or service ,centos使用 systemctl
#master
master linux> systemctl restart mysqld
#slave
slave linux> ssystemctl restart mysqld
3.主库创建 用户并授权
master mysql> create user repl_user identified by 'pwd_123';
#密码设置失败,可以调整密码规则
master mysql> set global validate_password_policy=0;
master mysql> grant replication slave on *.* to 'repl_user'@'172.16.1.102' identified by 'pwd_123';
master mysql> flush privileges;
4.查询主库状态
master mysql> show master status;
5.根据上一步查出数据,配置从库
slave mysql>
CHANGE MASTER TO
MASTER_HOST = '172.16.1.101'
MASTER_USER = 'repl_user'
MASTER_PASSWORD = 'pwd_123'
MASTER_PORT = 3306
MASTER_LOG_FILE='mysql-bin.000001'
MASTER_LOG_POS=11763
MASTER_RETRY_COUNT = 60
MASTER_HEARTBEAT_PERIOD = 10000;
6.启动从库线程
slave mysql> start slave;
7.主库创建表或者变更数据,在从库验证是否同步成功!
end