Shulou(Shulou.com)05/31 Report--

This article is about how to use mysqld_multi to configure stand-alone multiple instances. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Environment: redhat6.5+mysql5.6.21

[root@linfytest2 bin] # cat / etc/redhat-release

Red Hat Enterprise Linux Server release 6.5 (Santiago)

[root@linfytest2 bin] #. / mysqld-version

. / mysqld Ver 5.6.21 for Linux on x861664 (Source distribution)

Example of mysqld_multi:

[root@linfytest2 bin] #. / 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 / usr/local/mysql/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 = / usr/local/mysql/bin/mysqld_safe

Mysqladmin = / usr/local/mysql/bin/mysqladmin

User = multi_admin

Password = my_password

[mysqld2]

Socket = / tmp/mysql.sock2

Port = 3307

Pid-file = / usr/local/mysql/data2/hostname.pid2

Datadir = / usr/local/mysql/data2

Language = / usr/local/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 = / usr/local/mysql/data3/hostname.pid3

Datadir = / usr/local/mysql/data3

Language = / usr/local/mysql/share/mysql/swedish

User = unix_user2

[mysqld4]

Socket = / tmp/mysql.sock4

Port = 3309

Pid-file = / usr/local/mysql/data4/hostname.pid4

Datadir = / usr/local/mysql/data4

Language = / usr/local/mysql/share/mysql/estonia

User = unix_user3

[mysqld6]

Socket = / tmp/mysql.sock6

Port = 3311

Pid-file = / usr/local/mysql/data6/hostname.pid6

Datadir = / usr/local/mysql/data6

Language = / usr/local/mysql/share/mysql/japanese

User = unix_user4

1. Create databases with ports 3306 and 3307, respectively.

2. Create a parameter file my_multi.cnf

[mysqld_multi]

Mysqld = / usr/local/mysql/bin/mysqld_safe

Mysqladmin = / usr/local/mysql/bin/mysqladmin

Log = / var/lib/mysql/mysqld_multi.log

User = multi_admin

Password = 111111

[mysqld6]

Datadir = / var/lib/mysql3306

Pid-file = / var/lib/mysql3306/mysqld.pid

Socket = / tmp/mysql3306.sock

Port = 3306

[mysqld7]

Datadir = / var/lib/mysql3307

Socket = / tmp/mysql3307.sock

Pid-file = / var/lib/mysql3307/mysqld.pid

Port = 3307

3. Start

[root@linfytest2 bin] #. / mysqld_multi-defaults-extra-file=/etc/my_multi.cnf report

Reporting MySQL servers

MySQL server from group: mysqld6 is not running

MySQL server from group: mysqld7 is not running

[root@linfytest2 bin] #. / mysqld_multi-defaults-extra-file=/etc/my_multi.cnf start

[root@linfytest2 bin] #. / mysqld_multi-defaults-extra-file=/etc/my_multi.cnf report

Reporting MySQL servers

MySQL server from group: mysqld6 is running

MySQL server from group: mysqld7 is running

[root@linfytest2 bin] # netstat-tunlp | grep mysql

Tcp 0 0: 3306: * LISTEN 17982/mysqld

Tcp 0 0: 3307: * LISTEN 17983/mysqld

4. Stop

[root@linfytest2 bin] #. / mysqld_multi-defaults-extra-file=/etc/my_multi.cnf stop

[root@linfytest2 bin] #. / mysqld_multi-defaults-extra-file=/etc/my_multi.cnf report

Reporting MySQL servers

MySQL server from group: mysqld6 is not running

MySQL server from group: mysqld7 is not running

Thank you for reading! This is the end of this article on "how to use mysqld_multi to configure stand-alone multiple instances". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.