MySQL 5.5.35 单机多实例配置详解

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL 5.5.35 单机多实例配置详解

技术小美 2017-11-12 16:41:00 浏览920
展开阅读全文

一、前言

二、概述

三、环境准备

四、安装MySQL 5.5.35

五、新建支持多实例的配置文件(我这里配置的是四个实例)

六、初始化多实例数据库

七、提供管理脚本 mysqld_multi.server

八、整体备份方便后续迁移

九、管理MySQL多实例

十、登录MySQL多实例

十一、其它管理配置

十二、总结

注,测试环境 CentOS 6.4 x86_64,软件版本 MySQL 5.5.35,软件下载地址:http://dev.mysql.com/downloads/mysql/5.1.html#downloads

1.应用场景

  • 采用了数据伪分布式架构的原因,而项目启动初期又不一定有那多的用户量,为此先一组物理数据库服务器,但部署多个实例,方便后续迁移;

  • 为规避mysql对SMP架构不支持的缺陷,使用多实例绑定处理器的办法,把不同的数据库分配到不同的实例上提供数据服务;

  • 一台物理数据库服务器支撑多个数据库的数据服务,为提高mysql复制的从机的恢复效率,采用多实例部署;

  • 已经为双主复制的mysql数据库服务器架构,想部分重要业务的数据多一份异地机房的热备份,而mysql复制暂不支持多主的复制模式,且不给用户提供服务,为有效控制成本,会考虑异地机房部署一台性能超好的物理服务器,甚至外加磁盘柜的方式,为此也会部署多实例;

  • 传统游戏行业的MMO/MMORPG,以及Web Game,每一个服都对应一个数据库,而可能要做很多数据查询和数据订正的工作,为减少维护而出错的概率,也可能采用多实例部署的方式,按区的概念分配数据库;

上面的应用场景介绍主要参考这篇文章:http://www.zhdba.com/mysqlops/2011/07/30/multi-mysqld/,我们这里应用主要是基于前面三种场景。下面我们来说一下要注意的问题……

2.背景/需求、注意事项

(1).背景与需求

  • 将所有的安装文件、配置文件、数据目录全部放存/data/mysql目录中,便于今后实现快速迁移、整体备份和快速复制;

  • 在一台服务器上运行四个MySQL实例,分别绑定在3306、3307、3308、3309端口上;

  • 四个实例都开启binlog日志,数据目录分别存放在/data/mysql/data、/data/mysql/data2、/data/mysql/data3、/data/mysql/data4

  • 四个实例均采用InnoDB作为默认的存储引擎,字符编码采用UTF-8;

  • 四个实例均采用相同的性能优化配置参数;

(2).注意事项

  • 在编译安装时,将数据库的配置文件my.cnf以及data目录等均指向到/data/mysql目录中;

  • 通过mysqld_multi的方式来管理四个不同的实例,采用相同的配置文件共享性能优化配置参数;

  • 在同一个配置文件中,利用[mysqld1]、[mysqld2]、[mysqld3]、[mysqld4]标签实现不同实例的差异化配置;

 

三、环境准备

1.安装yum源

1
2
[root@node1 src]# wget http://mirrors.hustunique.com/epel/6/x86_64/epel-release-6-8.noarch.rpm
[root@node1 src]# rpm -ivh epel-release-6-8.noarch.rpm

2.同步时间

1
2
3
[root@node1 src]# yum install -y ntp
[root@node1 src]# ntpdate 202.120.2.101
[root@node1 src]# hwclock –w

3.安装mysql5.5依赖包

1
[root@node1 ~]# yum install -y autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool* openssl*

4.安装cmake

1
[root@node1 ~]# yum install -y cmake

 

四、安装MySQL 5.5.35

1.创建安装目录与数据存放目录

1
2
[root@node1 ~]# mkdir /data/mysql
[root@node1 ~]# mkdir /data/mysql/data

2.创建mysql用户与组

1
2
3
[root@node1 ~]# useradd mysql
[root@node1 ~]# id mysql 
uid=500(mysql) gid=500(mysql) 组=500(mysql)

3.授权安装目录与数据目录

1
2
[root@node1 ~]# chown -R mysql.mysql /data/mysql/ 
[root@node1 ~]# chown -R mysql.mysql /data/mysql/data

4.安装mysql

1
2
3
4
5
[root@node1 ~]# cd src/ 
[root@node1 src]# tar xf mysql-5.5.35.tar.gz   
[root@node1 src]# cd mysql-5.5.35
[root@node1 mysql-5.5.35]# cmake -DCMAKE_INSTALL_PREFIX=/data/mysql -DSYSCONFDIR=/data/mysql/etc -DMYSQL_DATADIR=/data/mysql/data -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DMYSQL_USER=mysql -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1
[root@node1 mysql-5.5.35]# make && make install

好了,到这里我们的mysql就安装完成了,下面我们为mysql提供多实例配置文件。

 

五、新建支持多实例的配置文件(我这里配置的是四个实例)

1.删除默认的数据目录

1
2
[root@node1 ~]# cd /data/mysql/ 
[root@node1 mysql]# rm -rf data

2.创建多实例配置需要的目录

1
2
[root@node1 mysql]# mkdir etc tmp run log binlogs data data2 data3 data4
[root@node1 mysql]# chown -R mysql.mysql tmp run log binlogs data data2 data3 data4

3.提供配置文件

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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
[root@node1 ~]# cd src/ 
[root@node1 src]# cd mysql-5.5.35
[root@node1 mysql-5.5.35]# cp support-files/my-small.cnf /data/mysql/etc/my.cnf
[root@node1 ~]# cd /data/mysql/etc/ 
[root@node1 etc]# vim my.cnf  
# This server may run 4+ separate instances. So we use mysqld_multi to manage their services.
[client] 
default-character-set = utf8
[mysqld_multi] 
mysqld = /data/mysql/bin/mysqld_safe  
mysqladmin = /data/mysql/bin/mysqladmin  
log = /data/mysql/log/mysqld_multi.log  
user = root   
#password =
                                                                                                     
# This is the general purpose database. 
# The locations are default.  
# They are left in [mysqld] in case the server is started normally instead of by mysqld_multi.
[mysqld1] 
socket = /data/mysql/run/mysqld.sock  
port = 3306  
pid-file /data/mysql/run/mysqld.pid  
datadir = /data/mysql/data  
lc-messages-dir /data/mysql/share/english  
                                                                                                      
# These support master - master replication  
#auto-increment-increment = 4  
#auto-increment-offset = 1  # Since it is master 1  
log-bin = /data/mysql/binlogs/bin-log-mysqld1  
log-bin-index = /data/mysql/binlogs/bin-log-mysqld1.index  
#binlog-do-db = # Leave this blank if you want to control it on slave  
max_binlog_size = 1024M  
                                                                                                      
# This is exlusively for mysqld2  
# It is on 3307 with data directory /data/mysqld/data2
[mysqld2] 
socket = /data/mysql/run/mysqld.sock2  
port = 3307  
pid-file /data/mysql/run/mysqld.pid2  
datadir = /data/mysql/data2  
lc-messages-dir /data/mysql/share/english  
                                                                                                      
# Disable DNS lookups  
#skip-name-resolve  
                                                                                                      
# These support master - slave replication  
log-bin = /data/mysql/binlogs/bin-log-mysqld2  
log-bin-index = /data/mysql/binlogs/bin-log-mysqld2.index  
#binlog-do-db =  # Leave this blank if you want to control it on slave  
max_binlog_size = 1024M
 # Relay log settings
#relay-log = /data/mysql/log/relay-log-mysqld2
#relay-log-index = /data/mysql/log/relay-log-mysqld2.index
#relay-log-space-limit = 4G
                                                                                                      
# Slow query log settings
#log-slow-queries = /data/mysql/log/slow-log-mysqld2
#long_query_time = 2
#log-queries-not-using-indexes
                                                                                                      
# This is exlusively for mysqld3 
# It is on 3308 with data directory /data/mysqld/data3
[mysqld3] 
socket = /data/mysql/run/mysqld.sock3  
port = 3308  
pid-file /data/mysql/run/mysqld.pid3  
datadir = /data/mysql/data3  
lc-messages-dir /data/mysql/share/english
#Disable DNS lookups 
#skip-name-resolve
# These support master - slave replication 
log-bin = /data/mysql/binlogs/bin-log-mysqld3  
log-bin-index = /data/mysql/binlogs/bin-log-mysqld3.index  
#binlog-do-db =  # Leave this blank if you want to control it on slave  
 max_binlog_size = 1024M
 # This is exlusively for mysqld4
# It is on 3309 with data directory /data/mysqld/data4
[mysqld4] 
socket = /data/mysql/run/mysqld.sock4  
port = 3309  
pid-file /data/mysql/run/mysqld.pid4  
datadir = /data/mysql/data4  
lc-messages-dir /data/mysql/share/english
# Disable DNS lookups 
#skip-name-resolve
# These support master - slave replication 
log-bin = /data/mysql/binlogs/bin-log-mysqld4  
log-bin-index = /data/mysql/binlogs/bin-log-mysqld4.index  
#binlog-do-db =  # Leave this blank if you want to control it on slave  
max_binlog_size = 1024M
 # The rest of the my.cnf is shared
# Here follows entries for some specific programs
# The MySQL server
[mysqld] 
basedir = /data/mysql  
tmpdir = /data/mysql/tmp  
socket = /data/mysql/run/mysqld.sock  
port = 3306  
pid-file /data/mysql/run/mysqld.pid  
datadir = /data/mysql/data  
lc-messages-dir /data/mysql/share/english  
                                                                                                      
skip-external-locking  
key_buffer_size = 16K  
max_allowed_packet = 1M  
table_open_cache = 4  
sort_buffer_size = 64K  
read_buffer_size = 256K  
read_rnd_buffer_size = 256K  
net_buffer_length = 2K  
thread_stack = 128K  
                                                                                                      
# Increase the max connections  
max_connections = 2  
                                                                                                      
# The expiration time for logs, including binlogs  
expire_logs_days = 14  
                                                                                                      
# Set the character as utf8  
character-set-server = utf8  
collation-server = utf8_unicode_ci  
                                                                                                      
# This is usually only needed when setting up chained replication  
#log-slave-updates  
                                                                                                      
# Enable this to make replication more resilient against server crashes and restarts  
# but can cause higher I/O on the server  
#sync_binlog = 1  
                                                                                                      
# The server id, should be unique in same network  
server-id = 1  
                                                                                                      
# Set this to force MySQL to use a particular engine/table-type for new tables  
# This setting can still be overridden by specifying the engine explicitly  
# in the CREATE TABLE statement  
default-storage-engine = INNODB  
                                                                                                      
# Enable Per Table Data for InnoDB to shrink ibdata1  
innodb_file_per_table = 1  
                                                                                                      
# Uncomment the following if you are using InnoDB tables  
#innodb_data_home_dir = /data/mysql/data  
#innodb_data_file_path = ibdata1:10M:autoextend  
#innodb_log_group_home_dir = /data/mysql/data  
# You can set .._buffer_pool_size up to 50 - 80 % of RAM  
# but beware of setting memory usage too high  
innodb_buffer_pool_size = 16M  
innodb_additional_mem_pool_size = 2M  
# Set .._log_file_size to 25 % of buffer pool size  
innodb_log_file_size = 5M  
innodb_log_buffer_size = 8M  
innodb_flush_log_at_trx_commit = 1  
innodb_lock_wait_timeout = 50  
                                                                                                      
[mysqldump]  
quick  
max_allowed_packet = 16M  
                                                                                                      
[mysql]  
no-auto-rehash  
                                                                                                      
[myisamchk]  
key_buffer_size = 8M  
sort_buffer_size = 8M  
                                                                                                      
[mysqlhotcopy]  
interactive-timeout  
                                                                                                      
[mysql.server]  
user = mysql  
                                                                                                      
[mysqld_safe]  
log-error = /data/mysql/log/mysqld.log  
pid-file /data/mysql/run/mysqld.pid  
open-files-limit = 8192

注,MySQL自带了几个不同的配置文件,放置在/data/mysql/support-files目录下,分别是my-huge.cnf,my-innodb-heavy-4G.cnf,my-large.cnf,my-medium.cnf,my-small.cnf,通过名称我们可以很直观的了解到他们是针对不同的服务器配置的,本文的配置文件是来自于my-small.cnf的,因为我是在虚拟机上进行的设置;在生产环境中,我们可以通过参考my-huge.cnf或my-innodb-heavy-4G.cnf中的部分参数配置,来对服务器进行优化;

4.修改my.cnf读写权限

1
2
[root@node1 etc]# chown -R root.root /data/mysql/etc
[root@node1 etc]# chmod 600 /data/mysql/etc/my.cnf

好了,到这里我们的配置文件就设置完成了,下面我们来初始化一下数据库。

 

六、初始化多实例数据库

1.切换到mysql的安装目录

1
[root@node1 ~]# cd /data/mysql/

2.初始化实例[mysqld1]

1
[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data --user=mysql

3.初始化实例[mysqld2]

1
[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data2 --user=mysql

4.初始化实例[mysqld3]

1
[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data3 --user=mysql

5.初始化实例[mysqld4]

1
[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data4 --user=mysql

好了,到这里我们初始化工作就完成了,下面我们来提供一下多实例的管理脚本。

 


本文转自 张冲andy 博客园博客,原文链接:http://www.cnblogs.com/andy6/p/6171859.html   ,如需转载请自行联系原作者



网友评论

登录后评论
0/500
评论
技术小美
+ 关注