mysql主从配置 | 张扎瓦的博客

mysql主从配置

MySQL主从配置详细步骤


为什么要配置主从

  1. 数据备份,防止主库因为某些原因损坏导致数据丢失问题
  2. 结合读写分离,可以减轻单台数据库压力,提高查询效率

准备工作

主数据库版本要跟从数据库版本一致

主数据数据要与从数据库数据一致

可以通过sql备份,手动导入数据来保持一致,备份命令如下:

  1. 导出所有数据

    1
    mysqldump -uroot -proot --all-databases >/tmp/all.sql
  2. 导出指定数据库数据

    1
    mysqldump -uroot -proot --databases db1 db2 >/tmp/db.sql

原理说明

  1. 主数据库会把数据的更新记录写入bin-log日志中
  2. 从库启动IO线程将主库中的bin-log日志复制到自己的中继日志中
  3. 从库通过SQL线程读取中继日志中的数据,存入自己的数据库中

原理图

配置步骤

以下演示步骤为windows环境,mysql版本为 5.7.27

主库从库都在同一台机器上,主库端口为3306,从库端口为3307

主库配置

修改my.cnf配置文件

linux环境下,mysql配置文件为/etc/my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8


[mysqld]
#设置3306端口
port=3306
#设置mysql的安装目录
basedir=C:/Users/huayun/Desktop/hkdl/mysql
# 设置mysql数据库的数据的存放目录
datadir=C:/Users/huayun/Desktop/hkdl/mysql/data
#允许最大连接数
max_connections=500
# 服务端字符编码
character-set-server=utf8
#创建新表时将使用的默认存储引擎
default-storage-engine=InnoDB

#解决group by 问题
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#单条sql大小
max_allowed_packet=64M

slow_query_log=1
#慢查询日志位置
slow_query_log_file=C:/Users/huayun/Desktop/hkdl/mysql/log/slow.log
#慢查询时间 2秒
long_query_time=2
#未使用索引的sql也记录
log_queries_not_using_indexes=1


#开启二进制日志
log-bin=mysql-bin
#设置server-id,必须唯一
server-id=1
#binlog记录内容的方式,记录被操作的每一行
binlog_format=ROW
#减少记录日志的内容,只记录受影响的列
binlog_row_image=minimal
# 指定需要复制的数据库名为sas_hkdl
binlog-do-db=sas_hkdl
#bin log 日志定时清除周期10天
expire_logs_days=10

重启mysql

修改完配置文件后,需要重启mysql服务,使其生效

创建同步从库的用户

使用mysql命令行执行

1
2
grant replication slave on *.* to slaveUser@'%' identified by '123456';
flush privileges;

查看主库的状态

查看主库状态,记下 FilePosition

主库状态

从库配置

修改my.cnf文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8


[mysqld]
#设置3306端口
port=3307
#设置mysql的安装目录
basedir=C:/Users/huayun/Desktop/hkdl/mysql
# 设置mysql数据库的数据的存放目录
datadir=C:/Users/huayun/Desktop/hkdl/mysql2/data
#允许最大连接数
max_connections=500
# 服务端字符编码
character-set-server=utf8
#创建新表时将使用的默认存储引擎
default-storage-engine=InnoDB

#解决group by 问题
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#单条sql大小
max_allowed_packet=64M

slow_query_log=1
#慢查询日志位置
slow_query_log_file=C:/Users/huayun/Desktop/hkdl/mysql2/log/slow.log
#慢查询时间 2秒
long_query_time=2
#未使用索引的sql也记录
log_queries_not_using_indexes=1



#开启二进制日志
log-bin=mysql-bin
#设置server-id 必须唯一
server-id=2
#binlog记录内容的方式,记录被操作的每一行
binlog_format=ROW
#减少记录日志的内容,只记录受影响的列
binlog_row_image=minimal
# 指定需要复制的数据库名为sas_hkdl
replicate-do-db=sas_hkdl
#bin log 日志定时清除周期10天
expire_logs_days=10

重启mysql

修改完配置文件后,需要重启mysql服务,使其生效

执行同步命令

注意:

master_log_file 就是上面查看主库状态的那个 File 字段的值

master_log_pos就是主库的Position字段的值

1
2
3
4
5
# 设置主服务器ip,同步账号密码,同步位置
change master to master_host='localhost',master_user='slaveUser',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=24478;

# 开启同步功能
start slave;

查看从库状态

命令行连接从库,执行

1
show slave status\G;

从库状态

如果Slave_IO_RunningSlave_SQL_Running都是Yes,表示配置成功

可能的问题

如果从库查看状态时,有

The slave I/O thread stops because master and slave have equal MySQL server UUIDs

问题,如图:

异常

停止从库的mysqld服务,删除auto.cnf文件即可

window版本的auto.cnf 文件在 数据库的 数据 存放目录

linux版本的文件在 /var/lib/mysql/auto.cnf

验证

先在主库中analyze_mark 中插入一条数据

主库

然后打开从库的 analyze_mark 表

从库

可以看到,数据同步过来了

如果我的文章对您有所帮助,不妨打赏一杯豆浆以资鼓励(○` 3′○)