MySQL/MariaDB单机多实例配置

在同一台服务器上可以配置mysql多实例，提高服务器的使用率

这里mysql的目录为/usr/local/mysql/，开两个实例，存放目录分别为

/data/db1
/data/db2

分别监听3306, 3307

接下来写好/etc/my.cnf内容，主要注意sock, pid, log

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
log = /var/log/mysqld_multi.log

[mysqld3306]
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /var/run/mysql/mysql3306.pid
datadir = /data/innodb
user=mysql
log-error=/var/log/mysql_error3306.log
default-storage-engine = INNODB
innodb_read_io_threads = 16
innodb_write_io_threads = 4
innodb_io_capacity = 4000
back_log = 50
max_connections = 2000
max_prepared_stmt_count=500000
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 16M
max_heap_table_size = 64M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 1000
query_cache_size = 0
query_cache_type = 0
thread_stack = 192K
tmp_table_size = 64M
server-id = 10
key_buffer_size = 8M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

[mysqld3307]
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /var/run/mysql/mysql-3307.pid
datadir = /data/tokudb
user=mysql
log-error=/var/log/mysql_error3307.log
back_log = 50
max_connections = 2000
max_prepared_stmt_count=500000
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 16M
max_heap_table_size = 64M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 1000
query_cache_size = 0
query_cache_type = 0
ft_min_word_len = 4
thread_stack = 192K
tmp_table_size = 64M
server-id = 10
key_buffer_size = 8M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

安装数据文件

cd /usr/local/mysql/
./scripts/mysql_install_db --datadir=/data/db1/ --user=mysql
./scripts/mysql_install_db --datadir=/data/db2/ --user=mysql

创建启动脚本

cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi
chmod u+x /etc/init.d/mysqld_multi

为方便使用，将/usr/local/mysql/bin加入到PATH中

export PATH=/usr/local/mysql/bin:$PATH

启动实例

/etc/init.d/mysqld_multi start 3306
/etc/init.d/mysqld_multi start 3307

查看启动的情况

netstat -tnlp