有时在安装的mysql服务器上根据需要可能会想跑多个mysql服务器，比如当进行新版本测试，在一台主机上配置主从同步等操作时，这时重新编译安装mysql是完全没有必要的，因为mysql支持多实例管理。
一：根据mysql自带的mysqld_multi程序管理。
1.编译安装mysql
[root@zhu2 mysql-5.1.39]# ./configure --prefix=/opt/mysql --with-charset=utf8 --with-extra-charsets=all --with-big-tables --with-readline --with-ssl --with-embedded-server --enable-assembler --enable-thread-safe-client --enable-local-infile --with-plugins=all [root@zhu2 mysql-5.1.39]# make && make install
2.初始化数据库，指定不同的数据目录
[root@zhu2 ~]# /opt/mysql/bin/mysql_install_db --datadir=/opt/mysql/data3306 --user=mysql [root@zhu2 ~]# /opt/mysql/bin/mysql_install_db --datadir=/opt/mysql/data3307 --user=mysql [root@zhu2 ~]# /opt/mysql/bin/mysql_install_db --datadir=/opt/mysql/data3308 --user=mysql
3.配置mysql配置文件my.cnf
[root@zhu2 ~]# vim /etc/my.cnf [mysqld_multi] mysqld = /opt/mysql/bin/mysqld_safe mysqladmin = /opt/mysql/bin/mysqladmin [mysqld3306] bind-address=0.0.0.0 port = 3306 socket = /tmp/mysql3306.sock pid = /tmp/mysql3306.pid datadir = /opt/mysql/data3306 back_log = 50 skip-name-resolve max_connections = 4000 max_connect_errors = 100 table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 1M max_heap_table_size = 64M read_buffer_size = 2M read_rnd_buffer_size = 16M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 8 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default-storage-engine = InnoDB thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=mysql-bin binlog_format=mixed slow_query_log long_query_time = 2 server-id = 1 key_buffer_size = 32M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 1G innodb_data_file_path = ibdata1:200M:autoextend #innodb_write_io_threads = 8 #innodb_read_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqld3307] bind-address=0.0.0.0 port = 3307 socket = /tmp/mysql3307.sock pid = /tmp/mysql3307.pid datadir = /opt/mysql/data3307 back_log = 50 skip-name-resolve max_connections = 4000 max_connect_errors = 100 table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 1M max_heap_table_size = 64M read_buffer_size = 2M read_rnd_buffer_size = 16M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 8 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default-storage-engine = InnoDB thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=mysql-bin binlog_format=mixed slow_query_log long_query_time = 2 server-id = 1 key_buffer_size = 32M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 1G innodb_data_file_path = ibdata1:200M:autoextend innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqld3308] bind-address=0.0.0.0 port = 3308 socket = /tmp/mysql3308.sock pid = /tmp/mysql3308.pid datadir = /opt/mysql/data3308 back_log = 50 skip-name-resolve max_connections = 4000 max_connect_errors = 100 table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 1M max_heap_table_size = 64M read_buffer_size = 2M read_rnd_buffer_size = 16M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 8 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default-storage-engine = InnoDB thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=mysql-bin binlog_format=mixed slow_query_log long_query_time = 2 server-id = 1 key_buffer_size = 32M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 10M innodb_data_file_path = ibdata1:200M:autoextend innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 8192
4.启动多实例mysql
[root@zhu2 ~]# /opt/mysql/bin/mysqld_multi start 3306 [root@zhu2 ~]# /opt/mysql/bin/mysqld_multi start 3307 [root@zhu2 ~]# /opt/mysql/bin/mysqld_multi start 3308 [root@zhu2 ~]# /opt/mysql/bin/mysqld_multi start 3306，3307，,3308 [root@zhu2 ~]# /opt/mysql/bin/mysqld_multi start 3306-3308 #这几种启动方式都是可以的
5.查看是否启动
[root@zhu2 ~]# netstat -lntp | grep 330 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 31991/mysqld tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 2579/mysqld tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 2589/mysqld
6.连接
[root@zhu2 ~]# mysql -S /tmp/mysql3306.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.1.39-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | | zhu3306 | +--------------------+ 4 rows in set (0.01 sec) [root@zhu2 ~]# mysql -S /tmp/mysql3307.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.1.39-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | | zhu3307 | +--------------------+ 4 rows in set (0.00 sec) [root@zhu2 ~]# mysql -S /tmp/mysql3308.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.1.39-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysqld_multi详解：
# mysqld_multi可以从配置文件my.cnf中读取配置的组[mysqld_multi]，在该组中一般指定mysqld和mysqladmin的位置以便用来启动管理mysql，然而也可以在
[mysqldN]中指定mysqld和mysqladmin，不管是在[mysqld_multi]中配置默认的mysqld进程和mysqladmin管理还是在[mysqldN]中设置特定的mysqld进程和管理，我们必须选择一种配置，当两者都不配置时，启动将会失败。[mysqldN]是我们配置的实例数据库，N可以是任意的整数，本文中用的是3306,3307，3308；这些数字在以后的启动中会用到。
#mysqld_multi指令行管理
Usage: mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]
or mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]
GNR指的是group number，即是上文中配置文件中[mysqldN]中的N；常用参数
--help #显示帮助信息，很有用的参数 --example #多实例的配置例子，很有用的参数 --no-log #该参数可以使状态信息发送到终端显示，而不是发送到日志文件 --version #显示版本号 --password= --user= #这两项是指mysqladmin的用户和密码，当使用mysqladmin来管理时所使用的。 --tcp-ip # 通过tcp-ip的端口号连接mysql服务器，默认连接msyql服务器使用的是unix socket文件，这在stop和report操作时是非常有用的，适合的场景是socket文件丢失了，但是mysql任然在运行。 #举例： [root@zhu2 ~]# mysqld_multi start 3308 [root@zhu2 ~]# netstat -lntp | grep 3308 tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 5341/mysqld [root@zhu2 ~]# mv /tmp/mysql3308.sock . [root@zhu2 ~]# mysqld_multi --tcp-ip stop 3308 [root@zhu2 ~]# netstat -lntp | grep 3308
mysqld_multi--example和mysqld_multi--help输出
[root@zhu2 ~]# mysqld_multi --example # This is an example of a my.cnf file for mysqld_multi. # Usually this file is located in home dir ~/.my.cnf or /etc/my.cnf # # SOME IMPORTANT NOTES FOLLOW: # # 1.COMMON USER # # Make sure that the MySQL user, who is stopping the mysqld services, has # the same password to all MySQL servers being accessed by mysqld_multi. # This user needs to have the 'Shutdown_priv' -privilege, but for security # reasons should have no other privileges. It is advised that you create a # common 'multi_admin' user for all MySQL servers being controlled by # mysqld_multi. Here is an example how to do it: # # GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'password' # # You will need to apply the above to all MySQL servers that are being # controlled by mysqld_multi. 'multi_admin' will shutdown the servers # using 'mysqladmin' -binary, when 'mysqld_multi stop' is being called. # # 2.PID-FILE # # If you are using mysqld_safe to start mysqld, make sure that every # MySQL server has a separate pid-file. In order to use mysqld_safe # via mysqld_multi, you need to use two options: # # mysqld=/path/to/mysqld_safe # ledir=/path/to/mysqld-binary/ # # ledir (library executable directory), is an option that only mysqld_safe # accepts, so you will get an error if you try to pass it to mysqld directly. # For this reason you might want to use the above options within [mysqld#] # group directly. # # 3.DATA DIRECTORY # # It is NOT advised to run many MySQL servers within the same data directory. # You can do so, but please make sure to understand and deal with the # underlying caveats. In short they are: # - Speed penalty # - Risk of table/data corruption # - Data synchronising problems between the running servers # - Heavily media (disk) bound # - Relies on the system (external) file locking # - Is not applicable with all table types. (Such as InnoDB) # Trying so will end up with undesirable results. # # 4.TCP/IP Port # # Every server requires one and it must be unique. # # 5.[mysqld#] Groups # # In the example below the first and the fifth mysqld group was # intentionally left out. You may have 'gaps' in the config file. This # gives you more flexibility. # # 6.MySQL Server User # # You can pass the user=... option inside [mysqld#] groups. This # can be very handy in some cases, but then you need to run mysqld_multi # as UNIX root. # # 7.A Start-up Manage Script for mysqld_multi # # In the recent MySQL distributions you can find a file called # mysqld_multi.server.sh. It is a wrapper for mysqld_multi. This can # be used to start and stop multiple servers during boot and shutdown. # # You can place the file in /etc/init.d/mysqld_multi.server.sh and # make the needed symbolic links to it from various run levels # (as per Linux/Unix standard). You may even replace the # /etc/init.d/mysql.server script with it. # # Before using, you must create a my.cnf file either in /opt/mysql/etc/my.cnf # or /root/.my.cnf and add the [mysqld_multi] and [mysqld#] groups. # # The script can be found from support-files/mysqld_multi.server.sh # in MySQL distribution. (Verify the script before using) # [mysqld_multi] mysqld = /opt/mysql/bin/mysqld_safe mysqladmin = /opt/mysql/bin/mysqladmin user = multi_admin password = my_password [mysqld2] socket = /tmp/mysql.sock2 port = 3307 pid-file = /opt/mysql/var2/hostname.pid2 datadir = /opt/mysql/var2 language = /opt/mysql/share/mysql/english user = unix_user1 [mysqld3] mysqld = /path/to/mysqld_safe ledir = /path/to/mysqld-binary/ mysqladmin = /path/to/mysqladmin socket = /tmp/mysql.sock3 port = 3308 pid-file = /opt/mysql/var3/hostname.pid3 datadir = /opt/mysql/var3 language = /opt/mysql/share/mysql/swedish user = unix_user2 [mysqld4] socket = /tmp/mysql.sock4 port = 3309 pid-file = /opt/mysql/var4/hostname.pid4 datadir = /opt/mysql/var4 language = /opt/mysql/share/mysql/estonia user = unix_user3 [mysqld6] socket = /tmp/mysql.sock6 port = 3311 pid-file = /opt/mysql/var6/hostname.pid6 datadir = /opt/mysql/var6 language = /opt/mysql/share/mysql/japanese user = unix_user4 [root@zhu2 ~]#
[root@zhu2 ~]# mysqld_multi --help
mysqld_multi version 2.16 by Jani Tolonen
Description:
mysqld_multi can be used to start, or stop any number of separate
mysqld processes running in different TCP/IP ports and UNIX sockets.
mysqld_multi can read group [mysqld_multi] from my.cnf file. You may
want to put options mysqld=... and mysqladmin=... there. Since
version 2.10 these options can also be given under groups [mysqld#],
which gives more control over different versions. One can have the
default mysqld and mysqladmin under group [mysqld_multi], but this is
not mandatory. Please note that if mysqld or mysqladmin is missing
from both [mysqld_multi] and [mysqld#], a group that is tried to be
used, mysqld_multi will abort with an error.
mysqld_multi will search for groups named [mysqld#] from my.cnf (or
the given --config-file=...), where '#' can be any positive integer
starting from 1. These groups should be the same as the regular
[mysqld] group, but with those port, socket and any other options
that are to be used with each separate mysqld process. The number
in the group name has another function; it can be used for starting,
stopping, or reporting any specific mysqld server.
Usage: mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]
or mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]
The GNR means the group number. You can start, 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, 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:
--config-file=... Deprecated, please use --defaults-extra-file instead
--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:
--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.
[root@zhu2 ~]#
