MySQL 5.5.35 单机多实例配置详解-阿里云开发者社区

一、前言

二、概述

三、环境准备

四、安装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源

[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.同步时间

[root@node1 src]# yum install -y ntp

[root@node1 src]# ntpdate 202.120.2.101

[root@node1 src]# hwclock –w

3.安装mysql5.5依赖包

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

4.安装cmake

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

四、安装MySQL 5.5.35

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

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

2.创建mysql用户与组

[root@node1 ~]# useradd mysql

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

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

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

4.安装mysql

[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.删除默认的数据目录

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

[root@node1 mysql]# rm -rf data

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.提供配置文件

[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读写权限

[root@node1 etc]# chown -R root.root /data/mysql/etc

[root@node1 etc]# chmod 600 /data/mysql/etc/my.cnf

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

六、初始化多实例数据库

1.切换到mysql的安装目录

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

2.初始化实例[mysqld1]

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

3.初始化实例[mysqld2]

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

4.初始化实例[mysqld3]

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

5.初始化实例[mysqld4]

[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 ，如需转载请自行联系原作者