Mysql开启多端口

最近需要使用mysql 多端口，整理步骤，备用。

1.准备mysql配置文件

  /usr/local/etc/mysql/ecommerce_db_3306.conf

[mysqld]
server-id               = 463306
port                    = 3306
bind-address            = 10.210.241.46
user                    = mysql
datadir                 = /data1/var/lib/mysql_3306
#slow_query_log_file    = /data1/var/logs/mysql_3306_slow_query.dat
tmpdir                  = /dev/shm
slave-load-tmpdir       = /tmp
socket                  = /data1/var/run/mysql_3306.sock
character-set-server    = utf8
back_log                = 1024
skip-external-locking
default-storage-engine  = innodb
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

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

2. 创建好需要的数据文件夹

  datadir   /data1/var/lib/mysql_3306

  log-error  /data1/var/log/mysql_3306

  pid-file  /data1/var/run/mysqld_3306

3. 修改所有新建文件夹的属主和数组为mysql

  chown -R mysql  /data1/var

  chgrp -R mysql /data1/var

4. 生成数据库文件

/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --defaults-file=/usr/local/etc/mysql/ecommerce_db_3306.conf --datadir=/data1/var/lib/mysql_3306

如果出现错误：Could not find ./bin/my_print_defaults   添加：--basedir=/usr/local/mysql

5. 开启端口

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

6. 修改mysql的访问权限，以便其他机器访问

mysql -S /data1/var/run/mysql_3306.sock mysql;  ​ //连接数据库修改权限

update user set host = '%' where user ='root' limit 1; flush privileges;   //时数据库可以远程访问

mysql -h10.210.210.123 -P3311;  //验证是否可以用ip访问

7. 开机启动

修改 /etc/rc.local

/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 &