标签为 多实例 的文章

MySQL 5.5.x 单机多实例配置实践

背景/需求:
在一台服务器上通过源码编译安装一个版本为5.5以上的MySQL数据库;
将所有配置文件与数据等均存放在/opt/mysql,便于今后实现快速迁移、整体备份和快速复制;
在同一个MySQL中运行两个实例,一个绑定在端口3306,另一个绑定在端口3307;
绑定在3306端口的实例,不开启binlog,数据存放在/opt/mysql/data;
绑定在3307端口的实例,开启binlog,数据存放在/opt/mysql/data2;
两个实例均采用InnoDB作为默认的存储引擎,字符编码采用UTF-8;
两个实例均采用相同的性能优化配置参数;

实践/方案:
在编译安装时,将数据库的配置文件my.cnf以及data目录等均指向到/opt/mysql目录;
通过mysqld_multi的方式来管理两个不同的实例,采用相同的配置文件共享性能优化配置参数;
在同一个配置文件中,利用[mysqld1]与[mysqld2]标签实现不同实例的差异化配置;

配置步骤:
一、编译安装MySQL
1.安装cmake
MySQL从5.5版本开始,通过./configure进行编译配置方式已经被取消,取而代之的是cmake工具。
因此,我们首先要在系统中源码编译安装cmake工具。
# wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz

# tar zxvf cmake-2.8.4.tar.gz

# cd cmake-2.8.4

# ./configure
# make
# make install

2.确保以下所需系统软件包已经被安装
通过 rpm -qa | grep name 的方式验证以下软件包是否已全部安装。

gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool*

如果缺少相关的软件包,可通过yum -y install 的方式在线安装,或直接从系统安装光盘中找到并通过rpm -ivh 的方式安装。

3. 安装前的系统设置
建立mysql安装目录及数据存放目录
# mkdir /opt/mysql
# mkdir /opt/mysql/data

创建用户和用户组
# groupadd mysql
# useradd -g mysql mysql

赋予数据存放目录权限
# chown mysql:mysql -R /opt/mysql/data

4.开始编译安装 MySQL
通过http://www.mysql.com/downloads/mysql官方网址或国内的sohu镜像下载软件包,如目前最新的MySQL 5.5.20。
# wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.20.tar.gz

# tar zxvf mysql-5.5.20.tar.gz

# cd mysql-5.5.20
# cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql \
-DSYSCONFDIR=/opt/mysql/etc \
-DMYSQL_DATADIR=/opt/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

# make
# make install

在make与make install的时候可以看到进度百分比,感觉这一点要比configure方式要好。

二、创建支持多实例的配置文件
进入MySQL主目录
# cd /opt/mysql/

删除默认的data目录
# rm -rf data

创建需要的目录
# mkdir etc tmp run log binlogs data data2
# chown -R mysql:mysql tmp run log binlogs data data2

创建my.cnf配置文件
# vim etc/my.cnf

## This server may run 2+ separate instances
## So we use mysqld_multi to manage their services
[mysqld_multi]
mysqld = /opt/mysql/bin/mysqld_safe
mysqladmin = /opt/mysql/bin/mysqladmin
log = /opt/mysql/log/mysqld_multi.log
user = root ## Used for stopping the server via mysqladmin
#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 = /opt/mysql/run/mysqld.sock
port = 3306
pid-file = /opt/mysql/run/mysqld.pid
datadir = /opt/mysql/data
lc-messages-dir = /opt/mysql/share/english

## These support master - master replication
#auto-increment-increment = 4
#auto-increment-offset = 1  ## Since it is master 1
#log-bin = /opt/mysql/binlogs/bin-log-mysqld1
#log-bin-index = /opt/mysql/binlogs/bin-log-mysqld1.index
#binlog-do-db = ## Leave this blank if you want to control it on slave

## This is exlusively for mysqld2
## It is on 3307 with data directory /opt/mysqld/data2
[mysqld2]
socket = /opt/mysql/run/mysqld.sock2
port = 3307
pid-file = /opt/mysql/run/mysqld.pid2
datadir = /opt/mysql/data2
lc-messages-dir = /opt/mysql/share/english

## Disable DNS lookups
#skip-name-resolve

## These support master - slave replication
log-bin = /opt/mysql/binlogs/bin-log-mysqld2
log-bin-index = /opt/mysql/binlogs/bin-log-mysqld2.index
#binlog-do-db =  ## Leave this blank if you want to control it on slave

## Relay log settings
#relay-log = /opt/mysql/log/relay-log-mysqld2
#relay-log-index = /opt/mysql/log/relay-log-mysqld2.index
#relay-log-space-limit = 4G

## Slow query log settings
#log-slow-queries = /opt/mysql/log/slow-log-mysqld2
#long_query_time = 2
#log-queries-not-using-indexes

## The rest of the my.cnf is shared
## Here follows entries for some specific programs
## The MySQL server
[mysqld]
basedir = /opt/mysql
tmpdir = /opt/mysql/tmp
socket = /opt/mysql/run/mysqld.sock
port = 3306
pid-file = /opt/mysql/run/mysqld.pid
datadir = /opt/mysql/data
lc-messages-dir = /opt/mysql/share/english

skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 64M
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 = 1024

## Increase the max connect errors
max_connect_errors = 10000

## Increase the connect timeout
connect_timeout = 10

## Decrease the timeout and increase the retrieves of replication
slave-net-timeout = 60
master-connect-retry = 10

## 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

## Set the max size of each binlog file
max_binlog_size = 1024M

## Set the binlog format
binlog_format = ROW

## Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /opt/mysql/data
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /opt/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 = 64M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

[mysql.server]
user = mysql

[mysqld_safe]
log-error = /opt/mysql/log/mysqld.log
pid-file = /opt/mysql/run/mysqld.pid
open-files-limit = 8192

[client]
default-character-set = utf8

阅读全文 »

,

3 Comments