MySQL启动流程
mysqld_multi # 多实例管理程序
mysqld # MySQL最主要的启动方式，里面有很多参数；现在使用多实例就需要用新的mysql_safe 来启动mysql
mysql_safe # 实则还是调用mysqld，并且会读取mysqld中的my.cnf配置参数来启动mysql，mysql_safe本身也有很多参数，但是这些参数会优先于my.cnf
my.cnf # mysql的配置文件
my.sock # mysql创建的sock文件，开启、停止、登陆和管理mysql都是通过这个接口文件
安装MySQL数据库
环境描述
主机名称 IP地址 配置 系统版本 数据库版本 实例端口 db-node1.devopssec.cn 192.168.56.31 2核4G CentOS 7 MySQL 5.6.37 3306,3307
1
2
3
4
5
6
[root@db-node1 ~]# systemctl status firewalld.service
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
[root@db-node1 ~]# getenforce
Disabled
下载源码包
官网下载地址：http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.34.tar.gz https://pan.baidu.com/s/1RIdIMUvfMAZWXXOymcqx0g 密码：jo14
创建软件目录
首先我们需要创建一下统一的软件(softwares)、脚本(scirpts)、备份(backup)
1
2
3
4
5
6
7
8
9
10
11
12
[root@db-node1 ~]# mkdir -p /data/{softwares,scirpts,backup}
[root@db-node1 ~]# tree /data
/data
├── backup
├── scirpts
└── softwares
3 directories, 0 files
[root@db-node1 ~]# cd /data/softwares/
# 目录创建完成之后，我们进入到/data/softwares/目录，将我们下载的mysql源码包放到该目录
[root@db-node1 softwares]# ls mysql-5.6.37.tar.gz
mysql-5.6.37.tar.gz
安装依赖包
mysql的5.6编译安装需要使用cmake
1
[root@db-node1 softwares]# yum install -y make gcc-c++ cmake bison-devel ncurses-devel libaio libaio-devel perl-Data-Dumper
编译MySQL数据库
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
[root@db-node1 softwares]# tar xf mysql-5.6.37.tar.gz
[root@db-node1 softwares]# cd mysql-5.6.37
[root@db-node1 mysql-5.6.37]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
> -DMYSQL_DATADIR=/usr/local/mysql/data \
> -DMYSQL_UNIX_ADDR=/usr/local/mysql/tmp/mysql.sock \
> -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
> -DENABLED_LOCAL_INFILE=ON \
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \
> -DWITH_FEDERATED_STORAGE_ENGINE=1 \
> -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
> -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
> -DWITH_FAST_MUTEXES=1 \
> -DWITH_ZLIB=bundled \
> -DENABLED_LOCAL_INFILE=1 \
> -DWITH_EMBEDDED_SERVER=1 \
> -DWITH_DEBUG=0
[root@db-node1 mysql-5.6.37]# make && make install
# 编译安装需要很长的时间，请耐性等待。
[root@db-node1 mysql-5.6.37]# ll /usr/local/mysql/
total 48
drwxr-xr-x 2 root root 4096 Sep 2 20:31 bin
-rw-r--r-- 1 root root 17987 Jun 3 2017 COPYING
drwxr-xr-x 3 root root 17 Sep 2 20:30 data
drwxr-xr-x 2 root root 52 Sep 2 20:30 docs
drwxr-xr-x 3 root root 4096 Sep 2 20:30 include
drwxr-xr-x 3 root root 4096 Sep 2 20:30 lib
drwxr-xr-x 4 root root 28 Sep 2 20:30 man
drwxr-xr-x 10 root root 4096 Sep 2 20:31 mysql-test
-rw-r--r-- 1 root root 2496 Jun 3 2017 README
drwxr-xr-x 2 root root 29 Sep 2 20:31 scripts
drwxr-xr-x 28 root root 4096 Sep 2 20:31 share
drwxr-xr-x 4 root root 4096 Sep 2 20:31 sql-bench
drwxr-xr-x 2 root root 130 Sep 2 20:31 support-files
设置环境变量
1
2
3
4
5
6
7
[root@db-node1 mysql-5.6.37]# ln -s /usr/local/mysql/scripts/mysql_install_db /usr/local/mysql/bin/
[root@db-node1 mysql-5.6.37]# echo 'export PATH="/usr/local/mysql/bin:$PATH"' >>/etc/profile
[root@db-node1 mysql-5.6.37]# tail -1 /etc/profile
export PATH="/usr/local/mysql/bin:$PATH"
[root@db-node1 mysql-5.6.37]# export PATH="/usr/local/mysql/bin:$PATH"
[root@db-node1 mysql-5.6.37]# mysql -V
mysql Ver 14.14 Distrib 5.6.37, for Linux (x86_64) using EditLine wrapper
部署多实例数据库
创建数据库目录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[root@db-node1 mysql-5.6.37]# mkdir -p /data/dbdata/mysql_{3306,3307}/{binlogs,logs,mydata,relaylogs,socket,tmp}
[root@db-node1 mysql-5.6.37]# tree /data/dbdata/
/data/dbdata/
├── mysql_3306
│ ├── binlogs
│ ├── logs
│ ├── mydata
│ ├── relaylogs
│ ├── socket
│ └── tmp
└── mysql_3307
├── binlogs
├── logs
├── mydata
├── relaylogs
├── socket
└── tmp
14 directories, 0 files
创建mysql用户
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[root@db-node1 mysql-5.6.37]# groupadd -g 8000 mysql
[root@db-node1 mysql-5.6.37]# useradd -u 8000 -g 8000 mysql
[root@db-node1 mysql-5.6.37]# id mysql
uid=8000(mysql) gid=8000(mysql) groups=8000(mysql)
[root@db-node1 mysql-5.6.37]# chown -R mysql.mysql /data/dbdata/mysql_3306 /data/dbdata/mysql_3307
[root@db-node1 mysql-5.6.37]# ll /data/dbdata/mysql_3306 /data/dbdata/mysql_3307
/data/dbdata/mysql_3306:
total 0
drwxr-xr-x. 1 mysql mysql 0 Sep 2 21:31 binlogs
drwxr-xr-x. 1 mysql mysql 0 Sep 2 21:31 logs
drwxr-xr-x. 1 mysql mysql 0 Sep 2 21:31 mydata
drwxr-xr-x. 1 mysql mysql 0 Sep 2 21:31 relaylogs
drwxr-xr-x. 1 mysql mysql 0 Sep 2 21:31 socket
drwxr-xr-x. 1 mysql mysql 0 Sep 2 21:31 tmp
/data/dbdata/mysql_3307:
total 0
drwxr-xr-x. 1 mysql mysql 0 Sep 2 213:31 binlogs
drwxr-xr-x. 1 mysql mysql 0 Sep 2 213:31 logs
drwxr-xr-x. 1 mysql mysql 0 Sep 2 213:31 mydata
drwxr-xr-x. 1 mysql mysql 0 Sep 2 213:31 relaylogs
drwxr-xr-x. 1 mysql mysql 0 Sep 2 213:31 socket
drwxr-xr-x. 1 mysql mysql 0 Sep 2 213:31 tmp
创建配置文件
mysqld_multi用到/etc/mysqld_multi.cnf配置文件，该文件可以使用mysqld_multi –example导出来
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
[root@db-node1 mysql-5.6.37]# cat /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
# user = multi_admin # 该用户是mysqladmin命令停止数据库的时候使用的用户，也可以是其他用户
# password = my_password
[mysqld]
# innodb引擎配置
default_storage_engine = InnoDB
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 128M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 32M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_support_xa = 0
innodb_file_per_table = 1
innodb_change_buffering = all
innodb_purge_threads = 1
innodb_read_io_threads = 6
innodb_write_io_threads = 6
innodb_io_capacity = 2000
innodb_file_format = Barracuda
[mysqld3306]
port = 3306
socket = /data/dbdata/mysql_3306/socket/mysql.sock
pid-file = /data/dbdata/mysql_3306/logs/mysqld.pid
datadir = /data/dbdata/mysql_3306/mydata
tmpdir = /data/dbdata/mysql_3306/tmp
user = mysql
character_set_server = utf8
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_open_cache = 614
external-locking = FALSE
max_allowed_packet =128M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
thread_stack = 192K
tmp_table_size = 2M
max_heap_table_size = 2M
# 主从复制配置
server-id = 1
slow_query_log = 1
slow_query_log_file = /data/dbdata/mysql_3306/logs/slowq.log
long_query_time = 1
log-bin = /data/dbdata/mysql_3306/binlogs/mysql-bin
relay-log = /data/dbdata/mysql_3306/relaylogs/relay-bin
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 512M
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
[mysqld3307]
port = 3307
socket = /data/dbdata/mysql_3307/socket/mysql.sock
pid-file = /data/dbdata/mysql_3307/logs/mysqld.pid
datadir = /data/dbdata/mysql_3307/mydata
tmpdir = /data/dbdata/mysql_3307/tmp
user = mysql
character_set_server = utf8
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_open_cache = 614
external-locking = FALSE
max_allowed_packet =128M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
thread_stack = 192K
tmp_table_size = 2M
max_heap_table_size = 2M
# 主从复制配置
server-id = 1
slow_query_log = 1
slow_query_log_file = /data/dbdata/mysql_3307/logs/slowq.log
long_query_time = 1
log-bin = /data/dbdata/mysql_3307/binlogs/mysql-bin
relay-log = /data/dbdata/mysql_3307/relaylogs/relay-bin
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 512M
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql
[mysqldump]
quick
max_allowed_packet = 2M
[mysql]
no-auto-rehash
prompt=\\u@\\h \\r:\\m:\\s [\\d]>
[mysqld_safe]
log-error=/var/log/mysql/error.log
初始化数据库目录
初始化3306数据库目录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[root@db-node1 mysql-5.6.37]# mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/dbdata/mysql_3306/mydata
…………省略内容………………
[root@db-node1 mysql-5.6.37]# tree /data/dbdata/mysql_3306/ -L 2
/data/dbdata/mysql_3306/
├── binlogs
├── logs
├── mydata
│ ├── ibdata1
│ ├── ib_logfile0
│ ├── ib_logfile1
│ ├── mysql
│ ├── performance_schema
│ └── test
├── relaylogs
├── socket
└── tmp
9 directories, 3 files
初始化3307数据库目录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[root@db-node1 mysql-5.6.37]# mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/dbdata/mysql_3307/mydata
…………省略内容………………
[root@db-node1 mysql-5.6.37]# tree /data/dbdata/mysql_3307/ -L 2
/data/dbdata/mysql_3307/
├── binlogs
├── logs
├── mydata
│ ├── ibdata1
│ ├── ib_logfile0
│ ├── ib_logfile1
│ ├── mysql
│ ├── performance_schema
│ └── test
├── relaylogs
├── socket
└── tmp
9 directories, 3 files
启动数据库
mysqld_multi会在MySQL的配置文件my.cnf中查找分组名[mysqldN]，N为正整数，用于标识不同MySQL实例，并且是mysqld_multi命令start|stop|reload|report选项的参数。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
[root@db-node1 mysql-5.6.37]# mkdir /var/log/mysql/
[root@db-node1 mysql-5.6.37]# chown mysql.mysql /var/log/mysql/
[root@db-node1 mysql_3307]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is not running
[root@db-node1 mysql_3307]# mysqld_multi start 3306,3307
[root@db-node1 mysql_3307]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
[root@db-node1 mysql_3307]# netstat -nlutp|grep mysql
tcp6 0 0 :::3306 :::* LISTEN 44150/mysqld
tcp6 0 0 :::3307 :::* LISTEN 44153/mysqld
停止数据库
1
2
3
4
5
6
[root@db-node1 mysql_3307]# mysqld_multi stop 3306,3307
[root@db-node1 mysql_3307]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is not running
[root@db-node1 mysql_3307]# netstat -nlutp|grep mysql
到此使用mysqld_multi部署MySQL多实例已经部署完成啦
本文作者： 凌云本文链接： https://www.devopssec.cn/2018/08/26/MySQL数据库-mysqld_multi部署多实例/版权声明： 本作品采用 知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议 进行许可。转载请注明出处！
扫一扫
674
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
