# MySQL主从配置

首先准备两个MySQL服务器,具体mysql安装教程之前文章有介绍.

# 创建master

推荐是用mysqld_multi管理mysql服务器

[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
[mysqld1]
# 基础配置
port=3306
user=root
basedir	= /usr/local/mysql
datadir=/mysql/3306/data
pid-file = /mysql/3306/mysql.pid
socket=/mysql/3306/mysql.sock
log_error=/mysql/3306/error.log
# 服务器id,必须
server-id=1
# 开机二进制日志
log-bin=/mysql/3306/mysqlbin
# 可以忽略
binlog-ignore-db=mysql 
# 需要复制的数据库,必须
binlog-do-db=test
# STATEMENT不能解决时间函数的问题
# ROW记录每一行的改变,效率低
# MIXED自动切换,如果存在函数就用ROW,否则使用STATEMENT
binlog_format=mixed
# 二进制缓存大小
binlog_cache_size=4M
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

启动master

mysqld_multi --defaults-file=/root/mysql/my_multi.cnf start 1
1

创建用于同步数据的账户

# 连接到master节点: mysql -uroot -p -S /mysql/3306/mysql.sock
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
1
2

查看master状态和复制起始点

show master status;
# File为复制的文件 Position为开始复制的接入点
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mysqlbin.000001 |      682 | test         | mysql            |                   |
+-----------------+----------+--------------+------------------+-------------------+
1
2
3
4
5
6
7
8

# 创建slave节点

[mysqld2]
port=3307
user=root
basedir	= /usr/local/mysql
datadir=/mysql/3307/data
socket=/mysql/3307/mysql.sock
pid-file = /mysql/3307/mysql.pid
socket=/mysql/3307/mysql.sock
log_error=/mysql/3307/error.log
server-id=2
relay-log=mysql-relay
1
2
3
4
5
6
7
8
9
10
11
12

启动slave节点

mysqld_multi --defaults-file=/root/mysql/my_multi.cnf start 2
1

连接master

CHANGE MASTER TO MASTER_HOST='127.0.0.1',
MASTER_PORT=3307,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
# 主节点当前logbin文件
MASTER_LOG_FILE='mysqlbin.000001',
# logbin文件的偏移值
MASTER_LOG_POS=682;
1
2
3
4
5
6
7
8

开始复制

start slave;
1

查看连接状态

show slave status \G;
# 下面两个同时为yes,表示成功
 -----------------------------------
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
1
2
3
4
5
6

连接主机失败时,重试,也可以用以下命令提升slave为主节点

stop slave;
reset master;
1
2

# 配置多个主节点

只要开启了binlog功能的mysql服务器就支持同步数据,支持数据同步就支持做为主节点.

所以我们配置多个开启binlog的mysql服务器,然后设置互为主从模式就能实现多个主节点共存.

[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
[mysqld1]
port=3306
user=root
basedir	= /usr/local/mysql
datadir=/mysql/3306/data
pid-file = /mysql/3306/mysql.pid
socket=/mysql/3306/mysql.sock
log_error=/mysql/3306/error.log
server-id=1
# 自增列从2开始
auto_increment_offset = 1
# 每次递增的步长
auto_increment_increment = 2
log-slave-updates = true 
relay-log=mysql-relay
log-bin=/mysql/3306/mysqlbin
binlog-ignore-db=mysql 
binlog-do-db=test
binlog_format=mixed
binlog_cache_size=4M
[mysqld2]
port=3307
user=root
basedir	= /usr/local/mysql
datadir=/mysql/3307/data
pid-file = /mysql/3307/mysql.pid
socket=/mysql/3307/mysql.sock
log_error=/mysql/3307/error.log
server-id=2
auto_increment_offset = 2
auto_increment_increment = 2
log-slave-updates = true 
relay-log=mysql-relay
log-bin=/mysql/3307/mysqlbin
binlog-ignore-db=mysql 
binlog-do-db=test
binlog_format=mixed
binlog_cache_size=4M
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

这里需要注意的是防止插入过快导致id重复报错,所以设置不同的起始id,步长设置为主节点数

[mysqld1]
auto_increment_offset = 1
auto_increment_increment = 2
relay-log=mysql-relay
log-slave-updates = true
[mysqld2]
auto_increment_offset = 2
auto_increment_increment = 2
relay-log=mysql-relay
log-slave-updates = true 
1
2
3
4
5
6
7
8
9
10

相互连接

mysql:3306> CHANGE MASTER TO MASTER_HOST='127.0.0.1',
            MASTER_PORT=3307,
            MASTER_USER='slave',
            MASTER_PASSWORD='123456',
            MASTER_LOG_FILE='mysqlbin.000001',
            MASTER_LOG_POS=154;
mysql:3307> CHANGE MASTER TO MASTER_HOST='127.0.0.1',
            MASTER_PORT=3306,
            MASTER_USER='slave',
            MASTER_PASSWORD='123456',
            MASTER_LOG_FILE='mysqlbin.000001',
            MASTER_LOG_POS=154;
mysql:3306> start slave;
mysql:3307> start slave;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

这就是配置多主的所有步骤.

多主多从虽然能增加mysql的连接数,但是数据会始终同步到一张表中,对插入速度并不会有任何提高,而且还会导致每次插入id都递增2而造成id不连续,浪费id空间。

# 读写分离

MySQL中除具有super或all privileges权限之外的账号如果有写权限,则需要通过read_only参数来设置库是否是只读状态,设置只读有两种方式:

  1. 通过配置文件,每次修改配置需要重启服务
[mysqld]
read_only=1
1
2
  1. 通过命令,具有super/all privileges的账号才能执行,立即生效
set global read_only=1;
1

配置好库的只读状态后,还需要我们在所有的mysql服务器上创建另外两种账号:读账号、写账号 ,然后通过账号的权限来控制读写/分离

 GRANT Select ON *.* TO 'reader'@'%'  IDENTIFIED BY "123456";
 GRANT Select,Alter,Create,INDEX,Insert,Delete,Update ON *.* TO 'writer'@'%'  IDENTIFIED BY "123456";
1
2

# 故障/恢复

# master宕机

mysql没有给我们提供自动选主的功能,所以这些都需要自己动手搞定,当然也可以选择一些第三方工具帮我们自动搞定。

手动搞定:

  1. 选主

    在所有slave上执行下面命令,找到Read_Master_Log_Pos最大的那个,说明这个slave同步的数据最多,我们将它作为主节点

    show slave status \G;
    *************************** 1. row ***************************
    Read_Master_Log_Pos: 1393
    
    1
    2
    3
    4

    停止当前slave,并重设为master,也就是与之前的master断开连接

    stop slave;
    reset master;
    FLUSH PRIVILEGES;
    
    1
    2
    3

    修改这个slave的配置文件(按之前master的配置就行), 确保开启了binlog,如果有设置read_only,log-slave-updates 需要移除,最终效果如下:

    [mysqld2]
    port=3307
    user=root
    basedir	= /usr/local/mysql
    datadir=/mysql/3307/data
    pid-file = /mysql/3307/mysql.pid
    socket=/mysql/3307/mysql.sock
    log_error=/mysql/3307/error.log
    server-id=2
    log-bin=/mysql/3306/mysqlbin
    # 确保开启了binlog
    binlog-ignore-db=mysql 
    binlog-do-db=test
    binlog_format=mixed
    binlog_cache_size=4M
    
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16

    删除之前从master同步的信息文件:

    rm /mysql/3307/data/master.info
    rm /mysql/3307/data/relay-log.info
    
    1
    2

    重启当前节点

    mysqld_multi --defaults-file=/root/mysql/my_multi.cnf start 2
    
    1

    此时当前节点已经是master节点,查看当前节点状态

    show master status;
    +-----------------+----------+--------------+------------------+-------------------+
    | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +-----------------+----------+--------------+------------------+-------------------+
    | mysqlbin.000001 |      154 | test         | mysql            |                   |
    +-----------------+----------+--------------+------------------+-------------------+
    
    1
    2
    3
    4
    5
    6
    7
  2. 将其它slave重新连接到当前master

    登录到每个slave停止同步

    stop slave;
    
    1

    重新指向新的master

    CHANGE MASTER TO MASTER_HOST='127.0.0.1',
    MASTER_PORT=3307,
    MASTER_USER='slave',
    MASTER_PASSWORD='123456';
    
    1
    2
    3
    4

# slave宕机

因为slave记录了从master同步的信息,所以重启slave后会自动同步,不需要过多操作。