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

摘要

  • MySql单节点、主从、双主的构建过程,及其配置文件的说明
  • 本文基于mysql-8.0.30

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
29
30
# 1.下载
wget https://cdn.mysql.com//Downloads/MySQL-8.0/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
mkdir sock
chmod 777 sock

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

# 8.创建配置文件
touch 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
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
vim my.cnf

[mysql]
# 默认字符集,utf8mb4每个中文占四个字节,可以存入表情符号
# varchar(255)所表示的单位是字符,而一个汉字一个字母都是一字符。所以这里可以存储255个汉字或者255个字母。
# varchar的存储上限是65535字节,所以utf8mb4的varchar(16383)是上限(65535/4)
default-character-set=utf8mb4

[client]
port = 3306
socket = /usr/local/soft/mysql8/sock/mysql.sock

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

# 默认使用mysql_native_password插件认证
default_authentication_plugin=mysql_native_password

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

# 开启binlog日志,定义日志存储路径
log-bin = /usr/local/soft/mysql8/datas/mysql/mysql-bin
# binlog格式,有3种,statement,row,mixed,
# 推荐row,存储的每个sql的执行结果,安全性高,但是比较占磁盘空间
# statement存储的是sql语句,执行效率高,但是可能主从执行结果不一致
# mixed,statement和row都会存,主从同步时由从库的优化器决定具体的执行内容
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 =/usr/local/soft/mysql8/datas/mysql
innodb_log_group_home_dir =/usr/local/soft/mysql8/datas/mysql
#日志及进程数据的存放目录
log-error =/usr/local/soft/mysql8/datas/mysql/mysql.log
pid-file =/usr/local/soft/mysql8/datas/mysql/mysql.pid

# 服务端使用的字符集默认为8比特编码
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=/usr/local/soft/mysql8/datas/mysql/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
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
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
# 说明:--initialize-insecure 初始化时无密码
sudo mysqld --defaults-file=/usr/local/soft/mysql8/my.cnf --user=mysql --initialize-insecure

启动数据库

1
2
3
4
5
# 后台启动
sudo mysqld_safe --defaults-file=/usr/local/soft/mysql8/my.cnf --user=mysql &

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

开机启动

1
2
3
4
5
sudo chmod +x /etc/rc.d/rc.local
sudo vim /etc/rc.local

# start mysql8
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
# 无密码登录,第一次登录没有密码,所以使用这种方式登录
mysql -uroot --skip-password

# 密码登录
mysql -uroot -p

# 远程登录
mysql -uroot -p -hxxx.xxx.xxx.xxx -P3306
  • 错误1

    1
    Can't connect to local MySQL server through socket '/tmp/mysql.sock'

    解决方法1

    1
    2
    # 这种方式mysql重启就会失效,需要重新建立软连接
    sudo ln -s /usr/local/soft/mysql8/sock/mysql.sock /tmp

    解决方法2

    1
    mysql -uroot --skip-password -h127.0.0.1
  • 错误2

    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
# 修改密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
# 刷新权限
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;

关闭mysql

1
sudo mysqladmin -h127.0.0.1 -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_RunningSlave_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 |
+-------------------------------------------+------------+