mysql5.1启动_mysql-5.1.73多实例安装启动

一、源码包下载：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登录