By

总阅读 次

文章目录
  1. 1. Mysql集群搭建
    1. 1.0.1. 单节点安装多mysql实例

最近项目变更，为适应支撑更大的场景需求，需要在db层面做分库分表来提升系统的性能，同时需要做大量的数据迁移工作，量级过亿。于是，把mysql的搭建，主从，读写分离以及基于mycat分库分表又系统的实践了一遍。纸上得来终觉浅，绝知此事要躬行。收获满满。

Mysql集群搭建

由于资源有限，使用Vmware起了3台虚拟机，每台2core4G，Mac本勉强可以支撑住，同时准备搭建一个3主3从的集群，所有需要在单节点搭建2个mysql实例，分别使用不同的端口号，做一主一从。

单节点安装多mysql实例

MySQL多实例部署主要有以下两种方式：

1.使用官方自带的mysqld_multi来配置管理，特点是使用同一份MySQL配置文件，这种方式属于集中式管理，管理起来较为方便；
2.使用单独的MySQL配置文件来单独配置实例，这种方式逻辑简单，数据库之间没有关联。

因为本人使用的是第二种，第一种就不详加说明了，可参考：https://www.cnblogs.com/lijiaman/p/12587630.html

过程如下：

  1. 在安装MySQL之前，需要卸载服务器自带的MySQL包和MySQL数据库分支mariadb的包
1
2
rpm -qa |grep mariadb
rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps
  1. 创建用户和用户组
1
2
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
  1. 解压安装包
1
2
3
cd /usr/local/
tar -xzvf /opt/package/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz 
mv mysql-5.7.31-linux-glibc2.12-x86_64/ mysql
  1. 创建数据文件存放路径
1
2
3
4
5
6
7
mkdir -p /opt/data/mysql/{3306,3307}/data
cd /opt/data/mysql/
chown -R mysql:mysql /opt/data/mysql/
cd 3306/
mkdir run
mkdir logs
mkdir tmp
  1. 创建MySQL参数配置文件

此处贴一份本人使用完整的配置文件如下,和上面创建的文件路径要匹配,my.cnf文件/opt/data/mysql/3306,3307端口按照其路径配置:

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
[mysql]
# CLIENT #
port                           = 3306
socket                         = /tmp/mysql_3306.sock

[mysqld]
# GENERAL #
server-id                      = 1
port                           = 3306
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /tmp/mysql_3306.sock
pid-file                       = /opt/data/mysql/3306/run/mysqld.pid
default_authentication_plugin  = mysql_native_password
basedir                        = /usr/local/mysql/
datadir                        = /opt/data/mysql/3306/data
transaction_isolation = READ-COMMITTED
tmpdir                         = /opt/data/mysql/3306/tmp
bind-address                   = 0.0.0.0
slave-skip-errors              = all
default-time_zone              = '+0:00'
character-set-server           = utf8mb4
collation-server               = utf8mb4_unicode_ci
tls_version                    = TLSv1.2
secure_file_priv               = /tmp/mysql-files
net_read_timeout               = 60
interactive_timeout=31536000
wait_timeout=31536000

# BINARY LOGGING #
log-bin                        = mysql-bin
relay-log                      = relay-bin
sync_binlog                    = 1
binlog_format                  = MIXED
relay_log_purge                = 0
expire_logs_days               = 7

# slave
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

# GTID #
gtid-mode = ON
enforce-gtid-consistency = ON

# replication #
#rpl_semi_sync_master_enabled    = 1
#rpl_semi_sync_master_wait_no_slave = On

# MyISAM #
# key-buffer-size                = 32M
# myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 256M
max-connect-errors             = 1000000
skip-name-resolve
sql-mode                       = NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER
sysdate-is-now                 = 1
innodb-strict-mode             = 1

# CACHES AND LIMITS #
max-connections                = 65535
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 768M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 128M

# LOGGING #
log-error                      = /opt/data/mysql/3306/logs/mysqld.log
slow-query-log                 = 1
slow-query-log-file            = /opt/data/mysql/3306/logs/mysqld-slow.log
log-queries-not-using-indexes  = OFF
long_query_time                = 30

[mysqldump]
max-allowed-packet             = 256M

部分参数说明：（ 在后续的过程中碰到的参数，这里提前说明下）
1). server-id 集群的配置，每个集群里所有实例需要不一样

2). secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。否则会报：ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement。
可通过：show global variables like ‘%secure_file_priv%’;查看
secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出。
secure_file_priv 为 /tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。
secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。

3). log-bin 打开binlog，主从必须要打开

1
2
3
4
只查看第一个binlog文件的内容:show binlog events;
查看指定binlog文件的内容:show binlog events in 'mysql-bin.000002';
查看当前正在写入的binlog文件:show master status\G;
获取binlog文件列表:show binary logs;

4). binlog_format binlog的日记模式

1
2
3
4
5
6
7
8
9
10
11
12
Statement level（默认）
每一条被修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行
优点：不需要记录每一行的数据变化，减少bin-log日志量，节约磁盘IO，提高新能
缺点：容易出现主从复制不一致

Row level
日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。
优点：能清楚的记录每一行数据修改的细节
缺点：数据量太大

Mixed（混合模式）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

5). sync_binlog

1
2
3
4
5
6
7
8
9
10
sync_binlog=0
当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。这个是性能最好的。

sync_binlog=1
当每进行1次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

sync_binlog=n
当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

注：大多数情况下，对数据的一致性并没有很严格的要求，所以并不会把 sync_binlog 配置成 1. 为了追求高并发，提升性能，可以设置为 100 或直接用 0，而对于支付服务这样的应用，还是比较推荐 sync_binlog = 1.

6). lower_case_table_names 区分大小写设置
lower_case_table_names： 此参数不可以动态修改，必须重启数据库
lower_case_table_names = 1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
lower_case_table_names=0 表名存储为给定的大小和比较是区分大小写的
lower_case_table_names=2, 表名存储为给定的大小写但是比较的时候是小写的

可通过：show variables like ‘lower_case_table_names’;查看

补充：关于mysql大小写
linux下：
数据库名与表名是严格区分大小写的；
表的别名是严格区分大小写的；
列名与列的别名在所有的情况下均是忽略大小写的；
变量名也是严格区分大小写的；
windows下：
都不区分大小写
Mac OS下（非UFS卷）：
都不区分大小写

7). gtid-mode 全局事务标识：global transaction identifiers。

从MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。通过 GTID 保证了每个在主库上提交的事务在集群中有一个唯一的ID。这种方式强化了数据库的主备一致性，故障恢复以及容错能力。

GTID (Global Transaction ID)是全局事务ID,当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务，对DBA来说意义就很大了，我们可以适当的解放出来，不用手工去可以找偏移量的值了，而是通过CHANGE MASTER TO MASTER_HOST=’xxx’, MASTER_AUTO_POSITION=1的即可方便的搭建从库，在故障修复中也可以采用MASTER_AUTO_POSITION=‘X’的方式。

1
2
3
gtid-mode=on                        --启用gtid类型，否则就是普通的复制架构
enforce-gtid-consistency=true       --强制GTID的一致性
log-slave-updates=1                 --强制slave记录二进制日志

可通过：show variables like ‘%gtid%’;查看

附详解一份：https://www.jianshu.com/p/f74ddf4f4372

  1. 初始化数据库
    1
    /usr/local/mysql/bin/mysqld --defaults-file=/opt/data/mysql/3306/my.cnf --initialize --basedir=/usr/local/mysql/ --datadir=/opt/data/mysql/3306/data

可在logs里查看到初始化的mysql密码，首次登录需要。

  1. 设置环境变量

    1
    2
    3
    4
    5
    [root@masterdb mysql]# vim /etc/profile
    # 在文件末尾添加下面信息
    export PATH=/usr/local/mysql/bin:$PATH
    #使环境变量生效
    [root@masterdb mysql]# source /etc/profile

  2. 启动数据库

    1
    2
    3
    4
    5
    # 经过测试，mysql在初始化的时候新生成的部分文件权限为root,所以最好在启动之前重新将datadir路径授权给mysql
    [root@masterdb mysql]# chown -R mysql:mysql /opt/data/mysql

    # 启动MySQL数据库实例
    [root@masterdb ~]# nohup /usr/local/mysql/bin/mysqld --defaults-file=/opt/data/mysql/3306/my.cnf --user=mysql &

  3. 访问多实例数据库
    mysql -S /tmp/mysql_3306.sock -p
    修改数据库root@localhost密码

    1
    2
    alter user  root@localhost identified by '123456';
    flush privileges;

  4. 本地无法连接远程服务器

    1
    2
    3
    select host from user where user = 'root';
    update user set host = '%' where user = 'root';
    重启数据库

  5. 主从配置

1
2
3
4
5
6
7
master上创建用于主从复制的账户：
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';

slave节点创建主节点复制的链接
change master to master_host='192.168.181.142',MASTER_PORT=3306,master_user='slave',master_password='123123',MASTER_AUTO_POSITION=1;
start slave;
show slave status\G;

按如下关系配置最终集群资源，同时创建测试库表，验证同步是否ok

192.168.181.141 3306(master) 对应 192.168.181.142 3307(slave)
192.168.181.142 3306(master) 对应 192.168.181.143 3307(slave)
192.168.181.143 3306(master) 对应 192.168.181.141 3307(slave)

文章目录
  1. 1. Mysql集群搭建
    1. 1.0.1. 单节点安装多mysql实例