由于笔者这两天在准备项目上线mysql的相关知识点,涉及到配置mysql的主从库,于是就找了两台局域网的windows机器尝试着配置了一下,其中遇到过一些坑,以此记录一下配置的过程,方便查看。关于Mysql主从的概念及原理在这里就不多说了,这里只记录配置的过程。
1.Mysql下载及安装
笔者下载安装的mysql版本是5.6.35 下载地址
安装路径全部是默认的
2.主库Master配置
假设主库的IP地址是172.16.3.53
- 配置文件的配置
首先打开配置文件,配置文件默认在C:\ProgramData\MySQL\MySQL Server 5.6\my.ini
在[mysqld]下添加如下内容,配置文件修改之后重启mysql服务。
注意:在主库上用binlog_do_db是很危险的,复制很可能会丢数据的。建议不要在主库上用binlog_do_db,如果只需要复制部分库的数据,可以在从库上加replicate-ignore-db参数
# 注意区别从库的server-id
server-id=1
# 开启二进制日志,这里的master-bin是日志文件的前缀,不用刻意使用该名字
# 注意:=号左右两边不要有空格,笔者曾经在网上复制,都包含了空格,导致mysql服务一直启动不了,查了错误日志,还以为是不支持该配置,最后尝试着去掉=号左右两边的空格才能正常启动
log-bin=master-bin
log-bin-index=master-bin.index
# binlog_format可以有三种设置值:row、statement和mixed。row代表二进制日志中记录数据表每一行经过写操作后被修改的最终值。各个参与同步的Salve节点,也会参照这个最终值,将自己数据表上的数据进行修改;statement形式是在日志中记录数据操作过程,而非最终的执行结果。各个参与同步的Salve节点会解析这个过程,并形成最终记录;mixed设置值,是以上两种记录方式的混合体,MySQL服务会自动选择当前运行状态下最适合的日志记录方式。
binlog_format=mixed
- 登录mysql配置
cmd进入目录C:\Program Files\MySQL\MySQL Server 5.6\bin
输入以下命令:
//创建新用户
create user repl;
// repl用户必须具有REPLICATION SLAVE权限,除此之外没有必要添加不必要的权限,密码为mysql。
// 172.16.3.185为从库的IP地址
grant replication slave on *.* to 'repl'@'172.16.3.185' identified by 'mysql';
主库经常用到的命令:
show master status \G; #显示master 状态
show processlist \G; #显示进程列表
配置正常情况下的截图:
3.从库Slave配置
假设从库的IP地址是172.16.3.185
- 配置文件的配置
首先打开配置文件,配置文件默认在C:\ProgramData\MySQL\MySQL Server 5.6\my.ini
在[mysqld]下添加如下内容,配置文件修改之后重启mysql服务
# 注意区别主库的server-id
server-id=2
# slave没有必要开启二进制日志,但是在一些情况下,必须设置,例如,如果slave为其它slave的master,必须设置bin_log
# log-bin=master-bin
# log-bin-index=master-bin.index
log_slave_updates=1
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
# replicate-do-db设置从主库中要备份的数据库
# 注意:经过笔者多次尝试,如果配置replicate-do-db,状态一切都正常,但是并没有看到从库的实时备份,导致了数据丢失,建议使用replicate-ignore-db配置不备份的数据库
# replicate-do-db=test
# replicate-ignore-db设置不从主库中备份的数据库
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
- 登录mysql配置
cmd进入目录C:\Program Files\MySQL\MySQL Server 5.6\bin
输入以下命令:
# 注:'master-bin.000011'和2742 是在主库中输入show master status得到的File和Position(大概意思指的是从这个日志文件的第2742个位置开始复制备份),其他的都是主库的相关属性,所以在开启之前
change master to master_host='172.16.3.53',master_port=3306,master_user='repl',master_password='mysql', master_log_file='master-bin.000011',master_log_pos=2742;
# 开始复制线程
start slave;
从库经常用到的命令:
start slave; #启动复制线程
stop slave; #停止复制线程
reset slave; #重置复制线程
change master to; #动态改变到主服务器的配置
show slave status \G; #显示slave 状态
配置正常情况下的截图:
4.小结:
- 主库一定要开启二进制日志
- 主库不要配置binlog-do-db和binlog-ignore-db;
- my.ini配置文件中的=号左右两边最好不要出现空格,避免出错概率;
- 从库不要配置replicate-do-db,只配置replicate-ignore-db就好了;
- 主库从库的server-id不能相同。
- 在确保配置没有问题的情况下,当显示状态不对,或者修改了my.ini文件重启之后,或者显示的状态没问题,但是没有实时复制的时候,可以使用如下几个命令尝试,注意先后顺序:
change master to; #动态改变到主服务器的配置
stop slave; #停止复制线程
reset slave; #重置复制线程
start slave; #启动复制线程注:使用change master to 命令的时候,要去主库中使用命令(show master status \G;)获取相关的参数。在启动备份线程之前,要手工将主库中所有的数据备份到从库中。如果在备份的时候,有可能有用户对数据库进行修改操作的话,记得给主库加上锁,避免备份的数据不全。
flush tables with read lock; //主库上锁表
unlock tables; //主库表解锁