使用mysqld_multi 来管理多个数据库实例

先建立一个config配置文件

[mysqld_multi]

mysqld = /usr/local/mysql-5.6/bin/mysqld_safe #根据自己的mysql目录配置

mysqladmin = /usr/local/mysql-5.6/bin/mysqladmin

[mysqld1]

port                   = 3306
pid-file                 = /home/mysql/mysql/mysql/mysqld.pid
socket                  =  /home/mysql/mysql/mysql/mysqld.sock
basedir                 = /usr/local/mysql-5.6
datadir                 =  /home/mysql/mysql/mysql/
innodb_data_home_dir    =  /home/mysql/mysql/mysql/
innodb_log_group_home_dir = /home/mysql/mysql/mysql/
tmpdir                  = /home/mysql/mysql/mysql/
log-error               = /home/mysql/mysql/mysql/mysqld.log
slow_query_log          =1
slow_query_log_file     = /home/mysql/mysql/mysql/mysql-slow.log

log_bin                 = /home/mysql/mysql/mysql/mysql-bin.log
server-id=1
log_slave_updates   =  1
skip-slave-start
skip-name-resolve
user                    = mysql
#language                = /usr/local/mysql/share/mysql/english
#table_cache             = 512
long_query_time         = 1
max_connections         = 600
query_cache_type        = 0
#default-character-set      = utf8
default-storage-engine     = innodb
skip-external-locking
expire_logs_days        = 7

lower_case_table_names  = 1

max_binlog_size         = 100M
innodb_buffer_pool_size = 512M
innodb_data_file_path   = ibdata1:1024M:autoextend
innodb_autoextend_increment     = 128
innodb_log_files_in_group       = 2
innodb_log_file_size            = 512M
innodb_lock_wait_timeout        = 5
innodb_flush_method            = O_DIRECT
innodb_flush_log_at_trx_commit   = 2
innodb_file_per_table   = 1

innodb_file_format         =Barracuda
innodb_file_format_check   =Barracuda
#innodb_adaptive_checkpoint  =estimate
#innodb_expand_import       = 1
innodb_read_io_threads      = 1
innodb_stats_method        = nulls_unequal
innodb_thread_concurrency  = 12
innodb_write_io_threads    = 1
innodb_io_capacity         =800

#read_only                  = on 

[mysqld2]
port                   = 3310
pid-file                 = /home/mysql/mysql/mysql-3310/mysqld.pid
socket                  =  /home/mysql/mysql/mysql-3310/mysqld.sock
basedir                 = /usr/local/mysql-5.6
datadir                 =  /home/mysql/mysql/mysql-3310/
innodb_data_home_dir    =  /home/mysql/mysql/mysql-3310/
innodb_log_group_home_dir = /home/mysql/mysql/mysql-3310/
tmpdir                  = /home/mysql/mysql/mysql-3310/
log-error               = /home/mysql/mysql/mysql-3310/mysqld.log
slow_query_log          =1
slow_query_log_file     = /home/mysql/mysql/mysql-3310/mysql-slow.log
log_bin                 = /home/mysql/mysql/mysql-3310/mysql-bin.log
server-id=2
log_slave_updates   =  1
skip-slave-start
skip-name-resolve
user                    = mysql
#language                = /usr/local/mysql/share/mysql/english
#table_cache             = 512
long_query_time         = 1
max_connections         = 600
query_cache_type        = 0
#default-character-set      = utf8
default-storage-engine     = innodb
skip-external-locking
expire_logs_days        = 7

lower_case_table_names  = 1

max_binlog_size         = 100M
innodb_buffer_pool_size = 512M
innodb_data_file_path   = ibdata1:1024M:autoextend
innodb_autoextend_increment     = 128
innodb_log_files_in_group       = 2
innodb_log_file_size            = 512M
innodb_lock_wait_timeout        = 5
innodb_flush_method            = O_DIRECT
innodb_flush_log_at_trx_commit   = 2
innodb_file_per_table   = 1

innodb_file_format         =Barracuda
innodb_file_format_check   =Barracuda
#innodb_adaptive_checkpoint  =estimate
#innodb_expand_import       = 1
innodb_read_io_threads      = 1
innodb_stats_method        = nulls_unequal
innodb_thread_concurrency  = 12
innodb_write_io_threads    = 1
innodb_io_capacity         =800

#read_only                  = on 

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M
default-character-set       = utf8

[mysql]
default-character-set = utf8

mysqld_multi 的相关参数说明：

Usage: mysqld_multi [OPTIONS] {start|reload|stop|report} [GNR,GNR,GNR...]
or     mysqld_multi [OPTIONS] {start|reload|stop|report} [GNR-GNR,GNR,GNR-GNR,...]


The GNR means the group number. You can start, reload, stop or report any GNR,
or several of them at the same time. (See --example) The GNRs list can
be comma separated or a dash combined. The latter means that all the
GNRs between GNR1-GNR2 will be affected. Without GNR argument all the
groups found will either be started, reloaded, stopped, or reported. Note that
syntax for specifying GNRs must appear without spaces.


Options:


These options must be given before any others:
--no-defaults      Do not read any defaults file
--defaults-file=...  Read only this configuration file, do not read the
                   standard system-wide and user-specific files
--defaults-extra-file=...  Read this configuration file in addition to the
                   standard system-wide and user-specific files
Using:  


--example          Give an example of a config file with extra information.
--help             Print this help and exit.
--log=...          Log file. Full path to and the name for the log file. NOTE:
                   If the file exists, everything will be appended.
                   Using: /var/lib/mysql/mysqld_multi.log
--mysqladmin=...   mysqladmin binary to be used for a server shutdown.
                   Since version 2.10 this can be given within groups [mysqld#]
                   Using: 
--mysqld=...       mysqld binary to be used. Note that you can give mysqld_safe
                   to this option also. The options are passed to mysqld. Just
                   make sure you have mysqld in your PATH or fix mysqld_safe.
                   Using: 
                   Please note: Since mysqld_multi version 2.3 you can also
                   give this option inside groups [mysqld#] in ~/.my.cnf,
                   where '#' stands for an integer (number) of the group in
                   question. This will be recognised as a special option and
                   will not be passed to the mysqld. This will allow one to
                   start different mysqld versions with mysqld_multi.
--no-log           Print to stdout instead of the log file. By default the log
                   file is turned on.
--password=...     Password for mysqladmin user.
--silent           Disable warnings.
--tcp-ip           Connect to the MySQL server(s) via the TCP/IP port instead
                   of the UNIX socket. This affects stopping and reporting.
                   If a socket file is missing, the server may still be
                   running, but can be accessed only via the TCP/IP port.
                   By default connecting is done via the UNIX socket.
--user=...         mysqladmin user. Using: root
--verbose          Be more verbose.
--version          Print the version number and exit.

使用下面的命令启动所有的数据库

[16:43:45 mysql@kiwi mysql]$ mysqld_multi --defaults-file=$MYSQL/my-multi.cnf start ;
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: Tue Feb 17 16:44:03 2015

Starting MySQL (Percona Server) servers
[16:44:03 mysql@kiwi mysql]$ 150217 16:44:04 mysqld_safe Logging to '/home/mysql/mysql/mysql-3310/mysqld.log'.
150217 16:44:04 mysqld_safe Logging to '/home/mysql/mysql/mysql/mysqld.log'.
150217 16:44:04 mysqld_safe Starting mysqld daemon with databases from /home/mysql/mysql/mysql-3310/
150217 16:44:04 mysqld_safe Starting mysqld daemon with databases from /home/mysql/mysql/mysql/

[17:10:09 mysql@kiwi mysql]$ mysqld_multi --defaults-file=$MYSQL/my-multi.cnf report
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: Tue Feb 17 17:10:46 2015
Reporting MySQL (Percona Server) servers
MySQL (Percona Server) from group: mysqld1 is running
MySQL (Percona Server) from group: mysqld2 is running

可以看到两个数据库实例已经起来了，当然，我们也可以单独的启动关闭数据库

[17:12:48 mysql@kiwi mysql]$ mysqld_multi --defaults-file=$MYSQL/my-multi.cnf -user root -password oracle stop 1;
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: Tue Feb 17 17:13:18 2015

Stopping MySQL (Percona Server) servers
[17:13:18 mysql@kiwi mysql]$ Warning: Using a password on the command line interface can be insecure.
150217 17:13:19 mysqld_safe mysqld from pid file /home/mysql/mysql/mysql/mysqld.pid ended

[17:13:21 mysql@kiwi mysql]$ 
[17:13:21 mysql@kiwi mysql]$ 
[17:13:21 mysql@kiwi mysql]$ mysqld_multi --defaults-file=$MYSQL/my-multi.cnf report
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: Tue Feb 17 17:13:35 2015
Reporting MySQL (Percona Server) servers
MySQL (Percona Server) from group: mysqld1 is not running
MySQL (Percona Server) from group: mysqld2 is running

可以看到1号实例已经关闭了，我们再单独的启动一号实例

[17:13:35 mysql@kiwi mysql]$ mysqld_multi --defaults-file=$MYSQL/my-multi.cnf start 1;
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: Tue Feb 17 17:15:12 2015

Starting MySQL (Percona Server) servers
[17:15:12 mysql@kiwi mysql]$ 150217 17:15:13 mysqld_safe Logging to '/home/mysql/mysql/mysql/mysqld.log'.
150217 17:15:13 mysqld_safe Starting mysqld daemon with databases from /home/mysql/mysql/mysql/

[17:15:15 mysql@kiwi mysql]$ mysqld_multi --defaults-file=$MYSQL/my-multi.cnf report;
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: Tue Feb 17 17:15:20 2015
Reporting MySQL (Percona Server) servers
MySQL (Percona Server) from group: mysqld1 is running
MySQL (Percona Server) from group: mysqld2 is running