一、源码包下载:http://download.softagency.net/MySQL/Downloads/MySQL-5.1/
二、编译安装
groupadd mysql
useradd-r -g mysql mysqlmkdir /data/mysql_multi/mysql_exa1/cd/data/mysql_multi/mysql_exa1/
mkdirdata binlog logmkdir /data/mysql_multi/mysql_exa2/cd/data/mysql_multi/mysql_exa2/
mkdirdata binlog logchown -R mysql:mysql /data/mysql_multi/mysql_exa2/ /data/mysql_multi/mysql_exa1/cd/data/installs/mysql-5.1.73./configure --prefix=/usr/local/mysql5.1 --with-plugins=innobase,innodb_plugin,myisam #编译参数参考:http://dev.mysql.com/doc/refman/5.1/en/source-configuration-options.html
make
make installcd/usr/local/mysql5.1/bin/./mysql_install_db --user=mysql --basedir=/usr/local/mysql5.1 --datadir=/data/mysql_multi/mysql_exa1/data/ ./mysql_install_db --user=mysql --basedir=/usr/local/mysql5.1 --datadir=/data/mysql_multi/mysql_exa2/data/
cp -rp ../share/mysql/mysqld_multi.server /etc/init.d/mysql_multi #复制启动文件
vim/etc/init.d/mysql_multi #修改basedir 和 datadir
三、配置多实例
[mysqld_multi]mysqld= /usr/local/mysql5.1/bin/mysqld_safe
mysqladmin= /usr/local/mysql5.1/bin/mysqladmin
user=root #管理实例时指定的用户名和密码
password= 1234
[client]#port= 3306#socket=/data/mysql_multi/mysql_exa1/mysql.sock[mysqld3307]server-id= 3307port= 3307socket=/data/mysql_multi/mysql_exa1/mysql.sock
pid-file=/data/mysql_multi/mysql_exa1/mysql.pid
datadir=/data/mysql_multi/mysql_exa1/data
log-error=/data/mysql_multi/mysql_exa1/log/error.log
long_query_time= 1slow_query_log_file=/data/mysql_multi/mysql_exa1/log/slow-query.log
log-bin=/data/mysql_multi/mysql_exa1/binlog/mysql-bin
log-bin-index=/data/mysql_multi/mysql_exa1/binlog/mysql-bin.index
expire_logs_days= 2binlog_format=mixed
skip-locking
key_buffer_size=16M
max_allowed_packet=1M
table_open_cache= 64sort_buffer_size=512K
net_buffer_length=8K
read_buffer_size=256K
read_rnd_buffer_size=512K
myisam_sort_buffer_size=8M
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 2M
#Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 80M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_file_io_threads = 4
[mysqld3308]server-id= 3308port= 3308socket=/data/mysql_multi/mysql_exa2/mysql.sock
pid-file=/data/mysql_multi/mysql_exa2/mysql.pid
datadir=/data/mysql_multi/mysql_exa2/data
log-error=/data/mysql_multi/mysql_exa2/log/error.log
long_query_time= 1slow_query_log_file=/data/mysql_multi/mysql_exa2/log/slow-query.log
log-bin=/data/mysql_multi/mysql_exa2/binlog/mysql-bin
log-bin-index=/data/mysql_multi/mysql_exa2/binlog/mysql-bin.index
expire_logs_days= 2binlog_format=mixed
skip-locking
key_buffer_size=16M
max_allowed_packet=1M
table_open_cache= 64sort_buffer_size=512K
net_buffer_length=8K
read_buffer_size=256K
read_rnd_buffer_size=512K
myisam_sort_buffer_size=8M
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 2M
#Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 80M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_file_io_threads = 4
[mysqldump]quick
max_allowed_packet=16M[mysql]no-auto-rehash[myisamchk]key_buffer_size=20M
sort_buffer_size=20M
read_buffer=2M
write_buffer=2M[mysqlhotcopy]interactive-timeout
四、启动多实例
root@192.168.100.246:/usr/local/mysql5.1/bin# /etc/init.d/mysqld_multi start 3307root@192.168.100.246:/usr/local/mysql5.1/bin# /etc/init.d/mysqld_multi start 3308root@192.168.100.246:/usr/local/mysql5.1/bin# netstat -lntp | grepmysql
tcp0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 26218/mysqld
tcp0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 31910/mysqld
tcp0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 32154/mysqld
五、关闭多实例
5.1 分别设置密码
root@192.168.100.246:/usr/local/mysql5.1/bin# mysql -uroot -p -S /data/mysql_multi/mysql_exa1/mysql.sock #指定sock文件路径登录
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connectionid is 1Server version:5.1.73-log Source distribution
Copyright (c)2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This isfreesoftware,
and you are welcome to modify and redistribute it under the GPL v2 license
Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.
mysql> set password=password('1234');
Query OK,0 rows affected (0.00sec)
mysql>flush privileges;
Query OK,0 rows affected (0.00sec)
root@192.168.100.246:/usr/local/mysql5.1/bin# mysql -uroot -p -S /data/mysql_multi/mysql_exa2/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connectionid is 1Server version:5.1.73-log Source distribution
Copyright (c)2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This isfreesoftware,
and you are welcome to modify and redistribute it under the GPL v2 license
Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.
mysql> set password=password('1234');
Query OK,0 rows affected (0.00sec)
mysql>flush privileges;
Query OK,0 rows affected (0.00 sec)
5.2 关闭实例
root@192.168.100.246:/usr/local/mysql5.1/bin# /etc/init.d/mysqld_multi stop 3307root@192.168.100.246:/usr/local/mysql5.1/bin# /etc/init.d/mysqld_multi stop 3308root@192.168.100.246:/usr/local/mysql5.1/bin# netstat -lntp | grepmysql
tcp0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 26218/mysqld
六、注意事项
a. mysql_multi.log默认路径:/usr/local/mysql5.1/share/mysqld_multi.log
b. 新加实例时,只需要在配置文件加上实例相关配置,启动实例即可,停止实例也类似
c. 多实例mysql数据库在本机登录必须指定socket登录,而在其他机器上,可以通过指定port登录