MySql Replication主从环境搭建

1、主库

安装完毕之后,执行以下查询:

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'sync_binlog';
SHOW VARIABLES LIKE 'skip_networking';

其结果如下:

log_bin                                        ON
binlog_format                                  ROW
server_id                                      1
innodb_flush_log_at_trx_commit                 1
sync_binlog                                    1
skip_networking                                OFF

具体参数值查阅百度,如果值不对,适当调整.或者百度查询.

2、从库

修改my.ini配置文件(windows下在ProgramData文件夹的MySql文件夹下),新增修改如下配置:

server-id=101
skip-log-bin=ON

修改server_id和关闭binlog功能,接着重启mysql服务.

执行如下代码检查配置是否生效:

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE '%relay%';

正常结果如下:

log_bin                                        ON
server_id                                      101

中继日志相关配置如下图:

3、创建从库用户

CREATE USER 'replication' IDENTIFIED WITH  mysql_native_password BY 'replication';
GRANT REPLICATION SLAVE ON *.* TO 'replication';
FLUSH PRIVILEGES;

执行以下语句验证创建是否成功

SHOW CREATE USER 'replication';
SHOW GRANTS FOR 'replication';

4、锁定主库的相关表,并查看状态

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

 当前停止在那个binlog文件,位置在哪里.