MySQL使用mysqld_multi部署单机多实例

随着硬件的性能越来越好，往往我们的数据库服务器若只开了一个单实例回有点浪费硬件资源的，这一点就不解释了哈。
参考： http://blog.itpub.net/26230597/viewspace-1659537/ 大神的解释

源码安装mysql参考blog：http://blog.youkuaiyun.com/mchdba/article/details/35994251，源码包下载地址：
http://download.youkuaiyun.com/detail/mchdba/7545037
http://download.youkuaiyun.com/detail/jyf0412/8726723   percona-server-5.5.41-37.0
http://download.youkuaiyun.com/detail/jyf0412/8807893   percona-server-5.6.23-72.1


下载好源码包后，先自己准备好数据库账号，安装目录

创建好安装目录
mkdir-p /usr/local/mysql
创建好数据库存放目录，推荐大空间磁盘，可以再挂载一个数据目录/data

编译安装的过程有点麻烦，但是大家不要怕，其实编译安装也很简单的
如果大家不想cmake的时候一哈一哈的warning或者error
那就来个简单的：
#Set timezone(设置服务器的时区：亚洲上海)
rm -rf /etc/localtime
ln -s /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

#Remove MySQL if exists mysql (删除服务器上已存在MySQL,切记数据库备份)
rpm -qa|grep mysql
rpm -e mysql
yum -y remove mysql-server mysql mysql-libs

#常用依赖安装
centos:
yum -y install patch make cmake gcc gcc-c++ gcc-g77 flex bison file libtool libtool-libs autoconf kernel-devel libjpeg libjpeg-devel libpng libpng-devel libpng10 libpng10-devel gd gd-devel freetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glib2 glib2-devel bzip2 bzip2-devel libevent libevent-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel openssl openssl-devel vim-minimal nano fonts-chinese gettext gettext-devel ncurses-devel gmp-devel pspell-devel unzip libcap diffutils
debian:
yum -y install build-essential gcc g++ make cmake autoconf automake re2c wget cron bzip2 libzip-dev libc6-dev file rcconf flex vim nano bison m4 gawk less make cpp binutils diffutils unzip tar bzip2 libbz2-dev libncurses5 libncurses5-dev libtool libevent-dev libpcre3 libpcre3-dev libpcrecpp0 libssl-dev zlibc openssl libsasl2-dev libxml2 libxml2-dev libltdl3-dev libltdl-dev libmcrypt-dev zlib1g zlib1g-dev libbz2-1.0 libbz2-dev libglib2.0-0 libglib2.0-dev libpng3 libfreetype6 libfreetype6-dev libjpeg62 libjpeg62-dev libjpeg-dev libpng-dev libpng12-0 libpng12-dev curl libcurl3 libmhash2 libmhash-dev libpq-dev libpq5 gettext libncurses5-dev libcurl4-gnutls-dev libjpeg-dev libpng12-dev libxml2-dev zlib1g-dev libfreetype6 libfreetype6-dev libssl-dev libcurl3 libcurl4-openssl-dev libcurl4-gnutls-dev mcrypt libcap-dev diffutils ca-certificates debian-keyring debian-archive-keyring
ubuntu:
build-essential gcc g++ make cmake automake autoconf re2c wget cron bzip2 libzip-dev libc6-dev file rcconf flex vim nano bison m4 gawk less make cpp binutils diffutils unzip tar bzip2 libbz2-dev unrar p7zip libncurses5-dev libncurses5 libncurses5-dev libncurses5-dev libtool libevent-dev libpcre3 libpcre3-dev libpcrecpp0  libssl-dev zlibc openssl libsasl2-dev libltdl3-dev libltdl-dev libmcrypt-dev zlib1g zlib1g-dev libbz2-1.0 libbz2-dev libglib2.0-0 libglib2.0-dev libpng3 libjpeg62 libjpeg62-dev libjpeg-dev libpng-dev libpng12-0 libpng12-dev curl libcurl3 libmhash2 libmhash-dev libpq-dev libpq5 gettext libncurses5-dev libcurl4-gnutls-dev libjpeg-dev libpng12-dev libxml2-dev zlib1g-dev libfreetype6 libfreetype6-dev libssl-dev libcurl3 libcurl4-openssl-dev libcurl4-gnutls-dev mcrypt libcap-dev diffutils ca-certificates debian-keyring debian-archive-keyring

删除原来数据库配置文件，如果有的话

rm -f /etc/my.cnf

tar -zxvf percona-server-5.6.23-72.1.tar.gz
cd percona-server-5.6.23-72.1

编译
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1

make && make install

添加数据库用户
groupadd mysql
useradd -s /sbin/nologin -M -g mysql mysql

我这里为了凸显鲜果是开了7个实例
mkdir -p /data/dbdata_3306
mkdir -p /data/dbdata_3307
mkdir -p /data/dbdata_3308
mkdir -p /data/dbdata_3309
mkdir -p /data/dbdata_3310
mkdir -p /data/dbdata_3311
mkdir -p /data/dbdata_3312
chown -R mysql:mysql *

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3306 --user=mysql
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3307 --user=mysql
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3308 --user=mysql
.....

mysqladmin -uroot -S /data/dbdata_3306/mysql.sock password 123456
mysqladmin -uroot -S /data/dbdata_3307/mysql.sock password 123456
mysqladmin -uroot -S /data/dbdata_3308/mysql.sock password 123456
......

创建一个管理这些实例的日志，这个日志是配置在配置文件的
touch /data/my_multi.log 

配置文件：
# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The MySQL server
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = root
password   = 123456
log=/data/my_multi.log


[mysqld1]
socket = /data/dbdata_3306/mysql.sock
port = 3306
pid-file = /data/dbdata_3306/3306.pid
datadir = /data/dbdata_3306
user = mysql
skip-name-resolve
innodb_file_per_table=1
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin=mysql-bin
binlog_format=mixed
expire_logs_days=7
slow_query_log
long_query_time = 3
server-id = 1
innodb_data_home_dir = /data/dbdata_3306
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/dbdata_3306
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50


[mysqld2]
socket = /data/dbdata_3307/mysql.sock
port = 3307
pid-file = /data/dbdata_3307/3307.pid
datadir = /data/dbdata_3307
user = mysql
skip-name-resolve
innodb_file_per_table=1
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin=mysql-bin
binlog_format=mixed
expire_logs_days=7
slow_query_log
long_query_time = 3
server-id = 1
innodb_data_home_dir = /data/dbdata_3307
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/dbdata_3307
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
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 = 256M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

可以自己DIY配置文件，我这个是小网站配置，也是lnmp的默认配置
看看效果吧



 mysqld_multi --defaults-extra-file=/etc/my_multi.cnf start
 mysqld_multi --defaults-extra-file=/etc/my_multi.cnf report

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30290247/viewspace-1700037/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30290247/viewspace-1700037/