MySql单节点、主从、双主的构建方法

发表于 更新于 分类于 ， 置顶 精品
阅读次数： 0 本文字数： 6.2k 阅读时长 ≈ 22 分钟

摘要

常见面试题

1.单节点

安装

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
# 1.下载mysql，从mysql官网下载：https://dev.mysql.com/downloads/mysql/，以下连接如果不生效，请从官网下载对应版本
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz

# 2.解压
tar -Jxvf mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz

# 3.重命名
mv mysql-8.0.30-linux-glibc2.12-x86_64 mysql8

# 4.切换到root
sudo su

# 5.创建mysql用户和mysql组
groupadd mysql
useradd -r -g mysql -s /bin/false mysql

# 6.创建数据目录并赋予权限
cd mysql8
mkdir -p datas/mysql
chown -R mysql:mysql datas
chmod -R 750 datas

# 7.设置环境变量
vim /etc/bashrc
export PATH=$PATH:/usr/local/soft/mysql8/bin

# 8.创建配置文件
touch my.cnf

警告⚠️
默认情况下，mysql按照下面的文件顺序加载配置，后面的配置会覆盖前面的配置
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
建议将配置文件创建到/etc/my.cnf，这样所有用户都可以使用，特殊配置可以创建在当前用户的~/.my.cnf中。
本文以下内容除特殊指定配置文件路径外，都是基于/etc/my.cnf的默认配置文件

配置

配置段说明

  • [mysql] 和 [client] 都是针对客户端的配置，后面的配置会覆盖前面的相同配置
  • [mysql] 只针对 mysql 命令，可以通过mysql --help查看配置项及其缺省值
  • [client] 针对 mysql、mysqldump、mysqlimport、mysqladmin, 等等，所有的客户端命令
  • [mysqld] 只是针对服务器端的配置，针对 mysqld 命令，可以通过mysqld --verbose --help查看配置项及其缺省值

  • 配置文件示例

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
vim my.cnf

[mysql]
# 默认字符集，utf8mb4每个中文占四个字节，可以存入表情符号
# varchar(255)所表示的单位是字符，而一个汉字一个字母都是一字符。所以这里可以存储255个汉字或者255个字母。
# varchar的存储上限是65535字节，所以utf8mb4的varchar(16383)是上限(65535/4)
default-character-set=utf8mb4
# mysqlimport和 load data local infile 导入文件开关，mysql和mysqld都要开通，默认关闭
local_infile=ON

[client]
# 部分 client 不支持该属性，比如 mysqlsh
# default-character-set=utf8mb4
port       = 3306
# 本机socket保存路径，执行命令的用户要对该路径有访问权限
socket     = /tmp/mysql.sock

[mysqld]
# 关闭mysql服务端对客户端的DNS解析，可以加快连接效率，mysql主机查询DNS很慢或是有很多客户端主机时会导致连接很慢时可以配置这个选项
skip-name-resolve

# 使用MySQL outfile导出文件，mysqldump
# 限制mysql不允许导出，默认值
# secure_file_prive=null
# 限制mysql的导出只能发生在默认的/path/目录下
# secure_file_priv=/path/
# 不对mysql的导出做限制，可以导出到任意目录
secure_file_priv=""

# mysqlimport和 load data local infile 导入文件开关，mysql和mysqld都要开通，默认关闭
local_infile=ON

# 设置默认使用mysql_native_password插件认证
# Mysql8.0.4开始，默认使用的是caching_sha2_password插件认证，这里配置为mysql_native_password是为了兼容以前的版本
# 不过笔者在使用最新版的8.4.0时发现其默认已经不支持default_authentication_plugin了，所以如果是8.4.0+的版本，不用配置这个属性
# 也可以在配置文件中开启`mysql-native-password=ON`
default_authentication_plugin=mysql_native_password

# 端口号
port       = 3306
# 主从或集群中要保证唯一性
server-id  = 1001
# 执行用户
user       = mysql
# 本机socket保存路径
socket     = /tmp/mysql.sock
# 安装目录
basedir    = /usr/local/soft/mysql8
# 数据存放目录，下面的路径都是基于这个目录的
datadir    = /usr/local/soft/mysql8/datas/mysql

# 开启binlog日志，定义日志存储路径
log-bin    = mysql-bin
# binlog格式，有3种，statement,row,mixed，mysql8默认就是row
# 推荐row，记录的是每一行数据变更前后的镜像，安全性高，但是比较占磁盘空间
# statement存储的是sql语句，执行效率高，但是遇到now()、uuid()等不确定的函数，可能主从执行结果不一致
# mixed，由主库在写binlog时按语句逐条选择用statement还是row(二选一，不是两份都存)，只有statement不安全时才改用row
# 注意：binlog_format从mysql8.0.34开始已被标记为废弃，未来只保留row，新系统不要再用statement/mixed
binlog-format = ROW
# binlog日志过期时间，单位秒，过期的binlog日志会被删除，保证磁盘空间，这里设置为10天，mysql8以前的版本这个参数是expire-logs-days，单位是天
binlog_expire_logs_seconds =864000
# 1：每次写入都会与磁盘同步，会影响性能，0：事务提交时mysql不做磁盘操作，由系统决定
sync-binlog=0

# innodb数据存储目录
innodb_data_home_dir      =./
innodb_log_group_home_dir =./
#日志及进程数据的存放目录
log-error =mysql.log
pid-file  =mysql.pid

# 服务端使用的字符集，默认值就是utf8mb4
character-set-server=utf8mb4
# 不区分表名称大小写
lower_case_table_names=1
# 默认就是1，是否自动提交，如果有事务，则跟着事务提交
autocommit =1

# 慢查询分析工具
# 1.mysqldumpslow，mysql自带
# 2.pt-query-digest，第三方:https://www.percona.com/downloads/percona-toolkit/LATEST/ß
# 开启慢查询日志，默认关闭
slow_query_log=1
# 慢查询日志存放路径
slow_query_log_file=db_slow.log
# 超过5秒就认为是慢查询语句，默认10秒
long_query_time=5
# 输出类型为文件类型，支持TABLE和FILE类型，如果是TABLE，select * from mysql.slow_log;
#log_output=FILE,TABLE
# 默认就是文件
log_output=FILE
# 记录没有使用索引的查询语句，默认关闭
log_queries_not_using_indexes=1

# 跳过外部锁定，默认配置。External-locking用于多进程条件下为MyISAM数据表进行锁定
# 当外部锁定（external-locking）起作用时，每个进程若要访问数据表，则必须等待之前的进程完成操作并解除锁定。
skip-external-locking
# 索引块的缓冲区的大小
key_buffer_size = 256M
# 指mysql服务器端和客户端在一次传送数据包的过程当中最大允许的数据包大小
max_allowed_packet = 64M
# 数据库打开表的缓存数量，即表的高速缓存
# 每个连接进来，都会至少打开一个表缓存。例如，对于 200 个并行运行的连接，应该让表的缓存至少有 200 × N ，这里 N 是应用可以执行的SQL语句中所需要表的最大数量。
table_open_cache = 1024
# 排序缓冲区大小
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 64M
# 线程缓存的数量，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)
thread_cache_size = 64

# 临时表的内存缓存大小
tmp_table_size = 128M
# 数据行更新时，timestamp类型字段不更新为当前时间
explicit_defaults_for_timestamp = true
# 最大连接数
max_connections = 500
# 某一客户端尝试连接服务器端，允许最大的失败次数，超过这个设置，则服务端会强制阻止该客户端的连接
max_connect_errors = 100
# 使用的最大文件描述(FD)符数量，这个值不一定是这个设置的值，与操作系统设置以及最大连接数等有关
open_files_limit = 65535

# 创建新表时将使用的默认存储引擎
default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:10M:autoextend
# buffer_pool缓冲区大小，默认128M
innodb_buffer_pool_size = 1024M
# redo log 文件大小，默认48M ,该参数已经过期，MySQL8.0.30引入了一个新特性：动态调整redo日志的大小
# innodb_log_file_size = 256M
# redo log buffer缓冲区大小，默认16M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
# 修改默认的事务隔离级别，默认为REPEATABLE-READ
# transaction-isolation=READ-COMMITTED

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

数据库初始化

1
2
3
4
5
6
# 说明：--initialize-insecure 初始化时无密码
# 从默认位置查找配置文件，因为配置文件中已经指定了user，所以这里其实可以不指定user
sudo mysqld --user=mysql --initialize-insecure

# 也可以指定配置文件的位置，注意这里--defaults-file要放在第一个参数位置
sudo mysqld --defaults-file=/usr/local/soft/mysql8/my.cnf --user=mysql --initialize-insecure

启动数据库

1
2
3
4
5
6
7
8
9
# 后台启动
# 从默认位置查找配置文件
sudo mysqld_safe --user=mysql &

# 也可以指定配置文件的位置
sudo mysqld_safe --defaults-file=/usr/local/soft/mysql8/my.cnf --user=mysql &

# 查看是否启动成功
ps -ef|grep mysql

开机启动+启动关闭mysql命令

方法1[推荐]:使用mysql提供的脚本

  • 开机自启动

1
2
3
4
5
6
7
8
9
10
cd /usr/local/soft/mysql8/support-files/
# 拷贝mysql启动脚本到启动路径，/etc/init.d 软连接到 /etc/rc.d/init.d
sudo cp mysql.server /etc/init.d/mysqld
# 加入开机启动服务,相当于 chkconfig mysqld on
sudo systemctl enable mysqld
# 查看开机启动项
chkconfig --list

# 关闭开机自启动,相当于 chkconfig mysqld off
sudo systemctl disable mysqld

  • 启动关闭

1
2
3
4
# 启动mysql,start/stop/status/reload/restart
# 注意，如果启动mysql时不是通过systemctl启动的，则需要先 sudo mysqladmin -uroot -p shutdown 关闭后再启动
sudo systemctl start mysqld
sudo systemctl stop mysqld

方法2:自己写命令

  • 开机自启动

1
2
3
4
5
6
7
8
9
10
11
sudo chmod +x /etc/rc.d/rc.local
# /etc/rc.local 软连接到 /etc/rc.d/rc.local
sudo vim /etc/rc.local

# start mysql8
# 从默认位置查找配置文件
sudo /usr/local/soft/mysql8/bin/mysqld_safe --user=mysql &

# 也可以指定配置文件的位置
sudo /usr/local/soft/mysql8/bin/mysqld_safe --defaults-file=/usr/local/soft/mysql8/my.cnf --user=mysql &

  • 启动关闭

1
2
3
4
5
6
7
8
9
10
# 也可以指定配置文件的位置
sudo /usr/local/soft/mysql8/bin/mysqld_safe --defaults-file=/usr/local/soft/mysql8/my.cnf --user=mysql &

# 关闭mysql
sudo /usr/local/soft/mysql8/bin/mysqladmin  -uroot -ppassword  shutdown

# 为了方便使用可以设置别名
sudo vim /etc/bashrc
alias mysql-start="sudo /usr/local/soft/mysql8/bin/mysqld_safe  --user=mysql &"
alias mysql-stop="sudo /usr/local/soft/mysql8/bin/mysqladmin  -uroot -ppassword  shutdown"

检查mysql是否启动成功

1
2
3
4
5
6
7
8
9
10
# 查看进程
> ps aux | grep mysqld
root      3960  0.0  0.0 124212  3240 ?        S    03:13   0:00 /bin/sh /usr/local/soft/mysql8/bin/mysqld_safe --datadir=/usr/local/soft/mysql8/datas/mysql --pid-file=/usr/local/soft/mysql8/datas/mysql/mysql.pid
mysql     5071  0.9 13.1 3167692 529720 ?      Sl   03:13   0:02 /usr/local/soft/mysql8/bin/mysqld --basedir=/usr/local/soft/mysql8 --datadir=/usr/local/soft/mysql8/datas/mysql --plugin-dir=/usr/local/soft/mysql8/lib/plugin --user=mysql --log-error=/usr/local/soft/mysql8/datas/mysql/mysql.log --open-files-limit=65535 --pid-file=/usr/local/soft/mysql8/datas/mysql/mysql.pid --socket=/tmp/mysql.sock --port=3306
root      5320  0.0  0.0 119448   920 pts/0    S+   03:18   0:00 grep --color=auto mysqld

# 查看端口
> netstat -tunpl | grep 3306
tcp6       0      0 :::33060                :::*                    LISTEN      5071/mysqld
tcp6       0      0 :::3306                 :::*                    LISTEN      5071/mysqld

登录

1
2
3
4
5
6
7
8
9
# 无密码登录，第一次登录没有密码，所以使用这种方式登录
# 只有没有密码时才能使用这种方式，如果已经设置了密码是不能使用这种方式的
mysql -uroot --skip-password

# 密码登录
mysql -uroot -p

# 远程登录
mysql -uroot -p -hxxx.xxx.xxx.xxx -P3306

  • 遇到的问题

1
error while loading shared libraries:libtinfo.so.5: cannot open shared object file : No such file or directory

解决方法

1
2
sudo ln -s /usr/lib64/libtinfo.so.6.0 /usr/lib64/libtinfo.so.5
# 注意：不同系统的版本可能这个文件名称后缀不一样，总之找到类似libtinfo.so.6.x的就行

修改密码

1
2
3
4
5
6
7
# 修改密码，这里使用密码插件mysql_native_password，8.4以后的版本不支持了，可以使用caching_sha2_password
# 也可以在配置文件中开启`mysql-native-password=ON`
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
# 或者直接使用缺省的配置：caching_sha2_password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
# 刷新权限
mysql> FLUSH PRIVILEGES;

授予root用户system_user权限

1
2
mysql> grant system_user on *.* to 'root'@'localhost';
mysql> flush privileges;

开启远程访问

1
2
3
4
5
# 允许远程访问
mysql> use mysql;
mysql> update user set user.Host='%' where user.User='root';
mysql> flush privileges;
mysql> select user,host from user;

警告⚠️
非常不建议开启root用户的远程访问权限，建议新创建一个用户，并仅授予必要的权限

1
2
3
4
5
6
7
8
9
10
11
12
# 创建新用户
mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'password';
# 创建数据库
mysql> CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 授予权限
mysql> GRANT all privileges ON my_database.* TO 'username'@'%';
# 刷新权限
mysql> FLUSH PRIVILEGES;
# 查询用户权限
mysql> show grants for username@"%"\G
# 查询当前系统用户
mysql> select user,host from mysql.user;

关闭mysql

1
sudo mysqladmin -uroot -p shutdown

2.主从

  • 参照单节点的构建方式搭建好两台mysql，主从计划如下:

1
2
master: 10.250.0.243
slave:  10.250.0.82

  • master和slave的server-id不能相同

  • master必须开启binlog功能

  • slave必须开启中继日志

  • 在云环境中，如果slave是通过master的镜像创建的，要修改slave的datadir中auto.cnf文件中的uuid的值，master和salve是不能相同的。

auto.cnf

1
2
[auto]
server-uuid=6e9a571e-330e-11ed-a3f8-0a53e7cced43

master配置文件

1
2
3
4
# 主从或集群中要保证唯一性
server-id = 1001
# 开启binlog日志
log-bin    = mysql-bin

slave配置文件

1
2
3
4
5
6
7
8
9
10
# 主从或集群中要保证唯一性
server-id = 1002
# 打开mysql中继日志，从库必须打开
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
# 是否自动清空不再需要中继日志，默认1：自动清空，0：不清空
relay_log_purge=1
# 默认从库同步主库的数据时，是不会写入从库的binlog日志的，所以要想同步数据也记录binlog，则需要开启该参数
# 如果从库作为其它库的主库时，必须开启该参数
log_slave_updates=1

主库创建同步帐号

1
2
3
4
5
# '10.250.%.%' 表示只能局域网段内的ip才能访问，如果不限制ip，可以配置为'%'
mysql> CREATE USER 'vagrant'@'10.250.%.%' IDENTIFIED BY 'vagrant';
mysql> ALTER USER 'vagrant'@'10.250.%.%' IDENTIFIED WITH mysql_native_password BY 'vagrant';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'vagrant'@'10.250.%.%';
mysql> FLUSH PRIVILEGES;

如果此时主库已经有数据，则需要先将主库数据导入到从库后再开启主从复制

1
2
3
4
5
6
7
8
9
10
11
12
# 主库执行导出数据
mysqldump -uroot --all-databases --triggers --routines --events -p > all_databases.sql

# 导出数据时，为了防止数据变化可以先锁表
# 锁
mysql> FLUSH TABLES WITH READ LOCK;

# 解锁
mysql> UNLOCK TABLES;

# 从库执行导入数据
mysql -uroot -p < all_databases.sql

从节点设置同步

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
# 查看主节点master状态信息，记录File和Position两个信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 |     1140 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 从节点设置同步信息，关联master中查询到的File和Position两个信息
mysql> CHANGE MASTER TO MASTER_HOST='10.250.0.243',
    MASTER_PORT=3306,
    MASTER_USER='vagrant',
    MASTER_PASSWORD='vagrant',
    MASTER_LOG_FILE='mysql-bin.000008',
    MASTER_LOG_POS=1140;

# 启动从库的复制线程
mysql> START slave;

# 查看从库同步状态
mysql> SHOW slave status \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 10.250.0.243
                  Master_User: vagrant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 1140
               Relay_Log_File: slave-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

如果Slave_IO_Running和Slave_SQL_Running都是Yes，则说明主从复制配置成功。

此时在master中执行SHOW PROCESSLIST;可以查看到同步信息

1
2
3
4
5
6
7
8
9
mysql>  SHOW PROCESSLIST;
+----+-----------------+-------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
| Id | User            | Host              | db   | Command     | Time | State                                                           | Info             |
+----+-----------------+-------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost         | NULL | Daemon      |   49 | Waiting on empty queue                                          | NULL             |
|  8 | vagrant         | 10.250.0.82:33924 | NULL | Binlog Dump |   49 | Source has sent all binlog to replica; waiting for more updates | NULL             |
|  9 | root            | 127.0.0.1:42410   | NULL | Query       |    0 | init                                                            | SHOW PROCESSLIST |
+----+-----------------+-------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

主从架构，从库应该是禁止写操作的，否则有可能会导致主从同步失败（如主键冲突），所以为了保证数据的一致性，应该禁止从库写操作。

1
2
3
4
5
# 从库的配置文件增加如下内容，并重启从库
# 开启普通用户只读
read_only=1
# 开启root用户只读
super_read_only=1

开启GTID（全局事务ID）主从复制模式

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和slave的my.cnf中分别加入，并重启mysql服务
gtid_mode=on
enforce_gtid_consistency=on

# 当有数据更新后查询master状态，可以看到Executed_Gtid_Set 中有值了，这个值会随着数据更新不断更新
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000014 |     1183 |              |                  | 6e9a571e-330e-11ed-a3f8-0a53e7cced42:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES like "%gtid%";
+----------------------------------+----------------------------------------+
| Variable_name                    | Value                                  |
+----------------------------------+----------------------------------------+
| binlog_gtid_simple_recovery      | ON                                     |
| enforce_gtid_consistency         | ON                                     |
| gtid_executed                    | 6e9a571e-330e-11ed-a3f8-0a53e7cced42:1 |
| gtid_executed_compression_period | 0                                      |
| gtid_mode                        | ON                                     |
| gtid_next                        | AUTOMATIC                              |
| gtid_owned                       |                                        |
| gtid_purged                      |                                        |
| session_track_gtids              | OFF                                    |
+----------------------------------+----------------------------------------+
9 rows in set (0.00 sec)

主从架构，一个master可以配置多个slave。

主从模式高可用架构，MHA(Master High Availability Manager and Tools for MySQL)

1
2
3
4
5
6
7
8
9
10
是由日本人开发的一个基于Perl脚本写的工具。这个工具专门用于监控主库的状态，
当发现master节点故障时，会提升其中拥有新数据的slave节点成为新的master节点，
在此期间，MHA会通过其他从节点获取额外的信息来避免数据一致性方面的问题。
MHA还提供了mater节点的在线切换功能，即按需切换master-slave节点。
MHA能够在30秒内实现故障切换，并能在故障切换过程中，最大程度的保证数据一致性。
MHA是需要单独部署的，分为Manager节点和Node节点，两种节点。
其中Manager节点一般是单独部署的一台机器。而Node节点一般是部署在每台MySQL机器上的。
Node节点得通过解析各个MySQL的日志来进行一些操作。
Manager节点会通过探测集群里的Node节点去判断各个Node所在机器上的MySQL运行是否正常，
如果发现某个Master故障了，就直接把他的一个Slave提升为Master，然后让其他Slave都挂到新的Master上去，完全透明。

MHA安装包下载地址：https://github.com/yoshinorim/mha4mysql-manager/releases
MHA使用说明：https://github.com/yoshinorim/mha4mysql-manager/wiki

3.双主

  • 双主模式就是两个mysql互为主从

  • 两个master都不能设置只读

  • 以上面主从为例，我们继续搭建双主架构

从库关闭只读并开启binlog

1
2
3
4
5
6
7
8
9
# 开启普通用户只读
# read_only=1
# 开启root用户只读
# super_read_only=1

# 主从或集群中要保证唯一性
server-id = 1002
# 开启binlog日志
log-bin    = mysql-bin

主库也要开启中继日志

1
2
3
4
5
6
7
8
9
10
# 主从或集群中要保证唯一性
server-id = 1001
# 打开mysql中继日志，从库必须打开
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
# 是否自动清空不再需要中继日志，默认1：自动清空，0：不清空
relay_log_purge=1
# 默认从库同步主库的数据时，是不会写入从库的binlog日志的，所以要想同步数据也记录binlog，则需要开启该参数
# 如果从库作为其它库的主库时，必须开启该参数
log_slave_updates=1

从库上创建同步帐号

1
2
3
4
5
# '10.250.%.%' 表示只能局域网段内的ip才能访问，如果不限制ip，可以配置为'%'
mysql> CREATE USER 'vagrant'@'10.250.%.%' IDENTIFIED BY 'vagrant';
mysql> ALTER USER 'vagrant'@'10.250.%.%' IDENTIFIED WITH mysql_native_password BY 'vagrant';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'vagrant'@'10.250.%.%';
mysql> FLUSH PRIVILEGES;

查看从库的master状态

1
2
3
4
5
6
7
mysql>  SHOW master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000014 |     1183 |              |                  | 6e9a571e-330e-11ed-a3f8-0a53e7cced42:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

主库上配置主从信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> CHANGE MASTER TO MASTER_HOST='10.250.0.82',
    MASTER_PORT=3306,
    MASTER_USER='vagrant',
    MASTER_PASSWORD='vagrant',
    MASTER_LOG_FILE='mysql-bin.000014',
    MASTER_LOG_POS=1183;

# 启动复制线程
mysql> START slave;

mysql> SHOW slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.250.0.82
                  Master_User: vagrant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000014
          Read_Master_Log_Pos: 1183
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000014
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

此时双主架构搭建完成。
双主架构下，每个master还可以配置多个slave用于数据备份。

双主架构自增主键冲突问题解决方法

1
2
3
4
5
6
7
8
9
10
# master1
# auto_increment字段产生的数值是：1, 3, 5, 7, …等奇数ID了
auto_increment_offset = 1
auto_increment_increment = 2


# master2
# auto_increment字段产生的数值是：2, 4, 6, 8, …等偶数ID了
auto_increment_offset = 2
auto_increment_increment = 2

双主高可用架构：MMM(Master-Master replication managerfor Mysql，Mysql主主复制管理器)

1
2
3
4
5
是一套由Perl语言实现的脚本程序，可以对mysql集群进行监控和故障迁移。
他需要两个Master，同一时间只有一个Master对外提供服务，可以说是主备模式。
他是通过一个VIP(虚拟IP)的机制来保证集群的高可用。
整个集群中，在主节点上会通过一个VIP地址来提供数据读写服务，
而当出现故障时，VIP就会从原来的主节点漂移到其他节点，由其他节点提供服务。

4.半同步复制

1
2
3
4
5
6
无论是主从复制还是双主复制，默认数据同步都是异步进行，主服务在向客户端反馈执行结果时，是不知道binlog是否同步成功了的。
这时候如果主服务宕机了，而从服务还没有备份到新执行的binlog，那就有可能会丢数据。
那怎么解决这个问题呢，这就要靠MySQL的半同步复制机制来保证数据安全。
半同步复制机制是一种介于异步复制和全同步复制之前的机制。
主库在执行完客户端提交的事务后，并不是立即返回客户端响应，而是等待至少一个从库接收并写到relay log中，才会返回给客户端。
MySQL在等待确认时，默认会等10秒，如果超过10秒没有收到ack，就会降级成为异步复制。

安装半同步复制插件

1
2
3
4
5
6
7
# master需要安装
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected, 1 warning (0.02 sec)

# 从库需要安装
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected, 1 warning (0.01 sec)

如果是双主架构，则都需要安装两个插件

开启配置

1
2
3
4
5
# 开启半同步复制，binlog至少传送到一个从节点，但不保证从节点一定应用了这个binlog，开启这个服务性能会有所下降
# master设置，需要安装插件install plugin rpl_semi_sync_master soname 'semisync_master.so';
rpl_semi_sync_master_enabled=1
# slave设置，需要安装插件install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
rpl_semi_sync_slave_enabled=1

重启mysql后查看是否配置成功

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show global variables like 'rpl_semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+

主从会丢数据吗？双主自增怎么防冲突？

场景：单机改一主一从读写分离，并评估是否上双主。

1. 搭主从时主库、从库至少要配对什么？

  • 两边：server-id 必须不同；若从库是主库镜像克隆的，还要改 datadir/auto.cnf 里的 server-uuid，uuid 也不能相同。

  • 主库：必须开 binlog（log-bin），复制账号要有 REPLICATION SLAVE；已有数据时先 mysqldump 导入从库，再按 SHOW MASTER STATUS 的 File/Position 做 CHANGE MASTER TO。

  • 从库：必须开中继日志（relay-log）；START SLAVE 后 Slave_IO_Running 与 Slave_SQL_Running 都为 Yes 才算通。从库若还要给更下游当主，再开 log_slave_updates。

  • 写保护：从库应 read_only / super_read_only，禁止业务写入，避免主键冲突把复制打挂。

2. 默认异步复制会丢数据吗？

  • 会。 异步复制下，主库本地事务提交成功就会给客户端返回「成功」，不等 binlog 传到从库。

  • 若返回成功后主库立刻宕机，而这条 binlog 还没到从库（甚至没 flush 完），切换到从库后这条提交就没了——客户端以为成功，从库上却看不到。

  • 主库 SHOW PROCESSLIST 里能看到 Binlog Dump 线程，那只说明「在推」；默认链路不保证「推到了才对客户端 ack」。

3. 半同步解决哪一步？等到什么？等不到怎样？

  • 半同步卡的是**「主库已提交 → 客户端收到成功」之间：至少等一个从库收到 binlog 并写入 relay log** 后，主库才返回客户端。

  • 注意：等到的是「写进中继日志」，不保证从库 SQL 线程已经应用完。所以是「至少进了从库磁盘侧的中继日志」，不是全同步（全同步要等从库执行完再返回）。

  • 默认超时约 10 秒（rpl_semi_sync_master_timeout=10000）。超时收不到 ack，会降级成异步复制，之后又回到可能丢数的模式，直到半同步重新建立。

  • 代价：提交延迟上升，换的是宕机时少丢已向客户端确认的事务。主从都要装对应插件并开启（双主则两边都装 master/slave 两套插件）。

4. 双主自增冲突怎么防？解决的是什么？

  • 两边都能写时，默认两边自增都从 1,2,3… 长，很容易主键冲突，复制中断。

  • 配法（文中示例）：

    • master1：auto_increment_offset=1，auto_increment_increment=2 → 1,3,5,7…
    • master2：auto_increment_offset=2，auto_increment_increment=2 → 2,4,6,8…

  • 只解决自增主键撞号，不是强一致：双主仍是复制（默认同异步/半同步），两边并发改同一行仍可能冲突；也解决不了业务唯一键、无自增主键的冲突。很多团队双主实际当主备（MMM 一类 VIP 漂移），同一时刻只让一个节点接写。

面试可背：主从要不同 server-id/uuid，主开 binlog、从开 relay，从库只读；异步提交先返回可能丢数；半同步等到 relay log 再返回，超时降级异步；双主用奇偶自增防撞号，不是分布式强一致。