Mysql开启多个端口

最近需要使用mysql的多个端口，现将步骤整理，以备后用。

mysql默认会从/etc/my.conf读取配置，然后生成数据文件。

1、/etc/my.conf

[mysqld]
port = 3310
datadir=/data1/var/lib/mysql_3310
socket=/data1/var/lib/mysql_3310/mysql_3310.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/data1/var/log/mysqld_3310.log
pid-file=/data1/var/run/mysqld/mysqld_3310.pid

datadir最好选个磁盘空间大的地方，防止数据文件过大，造成数据库无法使用的问题

mysql_3310的属主和属组改成mysql

2、service mysqld start

启动mysql服务

3、ln -s/data1/var/lib/mysql_3310/mysql_3310.sock /tmp/mysql.sock

mysql会默认去/tmp目录下找mysql.sock，所以，做一个软连接

4、mysql -uroot-h10.210.210.127 -P3310

启动mysql

5、update user set Host= '%' where Host = 'vm14080022' and User = 'root';

更改Host，以便其他的机器可以连接mysql

6、service mysqld stop

关闭mysql服务

重复以上步骤直到开启所有端口

7、. /etc/rc.local

/etc/rc.local

#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local

###Added by netsys cfengine for ntpdate ###
/usr/sbin/ntpdate tiger.sina.com.cn >/dev/null

/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/etc/mysql/ecommerce_db_3306.conf --user=mysql &

/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/etc/mysql/ecommerce_db_3307.conf --user=mysql &

/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/etc/mysql/ecommerce_db_3308.conf --user=mysql &

/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/etc/mysql/ecommerce_db_3309.conf --user=mysql &

/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/etc/mysql/ecommerce_db_3310.conf --user=mysql &

/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/etc/mysql/ecommerce_db_3311.conf --user=mysql &

/usr/local/etc/mysql/ecommerce_db_3310.conf

[mysqld]
server-id               = 2303310
port                    = 3310
bind-address            = 127.0.0.1
user                    = mysql
datadir                 = /data1/var/lib/mysql_3310
tmpdir                  = /dev/shm
slave-load-tmpdir       = /tmp
socket                  = /data1/var/run/mysql_3310.sock
character-set-server    = latin1
back_log                = 1024
skip-external-locking
default-storage-engine  = Myisam
key_buffer              = 1024M
max_allowed_packet      = 10M
table_cache             = 1024
sort_buffer_size        = 16M
read_buffer_size        = 16M
read_rnd_buffer_size    = 16M
myisam_sort_buffer_size = 1024M
query_cache_size        = 256M
query_cache_limit       = 2M
max_tmp_tables          = 1024
tmp_table_size          = 1024M
max_heap_table_size     = 32M
thread_cache            = 30
thread_concurrency      = 16
max_connections         = 100
max_user_connections    = 80
max_connect_errors      = 99999999
wait_timeout            = 604800
long_query_time         = 1
open_files_limit        = 10240
#table_open_cache        = 10240
#log-queries-not-using-indexes
#slow_query_log
#log-long-format
#log-slave-updates
log-bin                 = mysql_log_bin
relay-log               = mysql_relay_log
relay-log-index         = mysql_relay_log

#delay_key_write         = ALL
#low_priority_updates
#read_only
#old_passwords=1
skip-name-resolve
#skip-host-cache
#replicate-do-db=mysql
#replicate-do-db=esfang
#myisam-recover=BACKUP,FORCE
#skip_slave_start
#relay-log-purge=1

#skip-bdb
#skip-symlink

skip-innodb
innodb_buffer_pool_size = 128M
innodb_data_file_path = ibdata1:100M:autoextend
#innodb_data_home_dir = <directory>
innodb_file_io_threads = 4
#innodb_force_recovery=1
innodb_thread_concurrency = 4
innodb_flush_log_at_trx_commit = 1
#innodb_fast_shutdown
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct = 90
#innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 180