Run multiple instances on the same Mysql 5

 

 

I. Preface:

 

There is a mysqld_multi command in Mysql that can be used to run multiple Mysql services on one physical server. Refer to some documents for testing and passing the command today. Now we will share the operation process with you!

 

Operating System: Redhat Enterprise AS 3. Other versions of Linux should be similar.

Database: Mysql 5.0.22 (RPM installation)

 

Plan: run two instances on one Mysql instance

 

For example, the running ports are 3307 and 3308 respectively.

 

2. Install Mysql 5 in Linux

 

Check whether Mysql is installed in the system. If yes, delete it first:

 

Check whether mysql is installed in the system.

Rpm-qa | grep mysql

 

Uninstall

Rpm-e -- nodeps mysqlxxxx

 

Add the option -- nodeps to ignore errors.

 

 

Mysql is officially recommended to use the. rpm file to install Mysql. The two rpm files, Server and Client, are officially downloaded for installation:

 

Rpm-ivh MySQL-server-standard-5.0.22-0.rhel3.i386.rpm

Rpm-ivh MySQL-client-standard-5.0.22-0.rhel3.i386.rpm

 

Iii. Introduction to mysqld_multi:

 

According to the Mysql Management Manual, each Mysql service can be independent, so it calls. different startup options in cnf-the GNR value mentioned in the following section, which uses different ports to generate their own socket files, the Service database is independent (for more information, see the mysql official website's English Management Manual ).

 

Mysqld_multi is a service process that manages multiple mysqld. These service process programs have different unix sockets or listen on different ports. It can start, stop, and monitor the current service status.

 

The program searches for [mysqld #] segments in my. cnf (or in the custom configuration file -- config-file). "#" can be any positive integer. This positive integer is the segment sequence mentioned below, I .e., GNR. The serial number of a segment is used as the parameter of mysqld_multi to distinguish different segments, so that you can control the startup, stop, or obtain report information of a specific mysqld process. The parameters in these groups are the same as those in the group required to start a mysqld. However, if you use multiple services, you must specify a unix socket or port for each service (from using the mysqld_multi program in the http://mifor.4dian.org to manage multiple MySQL services ).

 

Iv. Example of mysql_multi_exam.cnf file:

 

[Mysqld_multi]

Mysqld =/usr/bin/mysqld_safe

Mysqladmin =/usr/bin/mysqladmin

 

# Use an account to start all mysql servers, because the same account is used. Which account must be used by every mysql service? It is best to manage the account. The password below is the same

# It is a mysql user, not a linux User

# This mysql instance must be granted permissions through root to grant it the shutdown and start mysql permissions.

# Grant shutdown on *. * to 'boss' @ '%' identified by 'boss'

 

User = boss

Password = boss

 

[Mysqld1]

Socket =/data/mysqldata/master/mysql. sock

Port = 3307

Pid-file =/data/mysqldata/master/mysql. pid

Datadir =/data/mysqldata/master

User = boss

 

Skip-locking

Key_buffer = 16 K

Max_allowed_packet = 1 M

Table_cache = 4

Sort_buffer_size = 64 K

Read_buffer_size = 256 K

Read_rnd_buffer_size = 256 K

Net_buffer_length = 2 K

Thread_stack = 64 K

 

# Don't listen on a TCP/IP port at all. This can be a security enhancement,

# If all processes that need to connect to mysqld run on the same host.

# All interaction with mysqld must be made via Unix sockets or named pipes.

# Note that using this option without enabling named pipes on Windows

# (Using the "enable-named-pipe" option) will render mysqld useless!

#

# Skip-networking

Server-id = 1

 

# Uncomment the following if you want to log updates

# Log-bin = mysql-bin

 

# Uncomment the following if you are NOT using BDB tables

# Skip-bdb

 

# Uncomment the following if you are using InnoDB tables

# Innodb_data_home_dir =/var/lib/mysql/

# Innodb_data_file_path = ibdata1: 10 M: autoextend

# Innodb_log_group_home_dir =/var/lib/mysql/

# Innodb_log_arch_dir =/var/lib/mysql/

# You can set .. _ buffer_pool_size up to 50-80%

# Of RAM but beware of setting memory usage too high

# Innodb_buffer_pool_size = 16 M

# Innodb_additional_mem_pool_size = 2 M

# Set .. _ log_file_size to 25% of buffer pool size

# Innodb_log_file_size = 5 M

# Innodb_log_buffer_size = 8 M

# Innodb_flush_log_at_trx_commit = 1

# Innodb_lock_wait_timeout = 50

 

[Mysqld2]

Socket =/data/mysqldata/slave/mysql. sock

Port = 3308

Pid-file =/data/mysqldata/slave/mysql. pid

Datadir =/data/mysqldata/slave

User = boss

 

Skip-locking

Key_buffer = 16 K

Max_allowed_packet = 1 M

Table_cache = 4

Sort_buffer_size = 64 K

Read_buffer_size = 256 K

Read_rnd_buffer_size = 256 K

Net_buffer_length = 2 K

Thread_stack = 64 K

 

# Don't listen on a TCP/IP port at all. This can be a security enhancement,

# If all processes that need to connect to mysqld run on the same host.

# All interaction with mysqld must be made via Unix sockets or named pipes.

# Note that using this option without enabling named pipes on Windows

# (Using the "enable-named-pipe" option) will render mysqld useless!

#

# Skip-networking

Server-id = 1

 

# Uncomment the following if you want to log updates

# Log-bin = mysql-bin

 

# Uncomment the following if you are NOT using BDB tables

# Skip-bdb

 

# Uncomment the following if you are using InnoDB tables

# Innodb_data_home_dir =/var/lib/mysql/

# Innodb_data_file_path = ibdata1: 10 M: autoextend

# Innodb_log_group_home_dir =/var/lib/mysql/

# Innodb_log_arch_dir =/var/lib/mysql/

# You can set .. _ buffer_pool_size up to 50-80%

# Of RAM but beware of setting memory usage too high

# Innodb_buffer_pool_size = 16 M

# Innodb_additional_mem_pool_size = 2 M

# Set .. _ log_file_size to 25% of buffer pool size

# Innodb_log_file_size = 5 M

# Innodb_log_buffer_size = 8 M

# Innodb_flush_log_at_trx_commit = 1

# Innodb_lock_wait_timeout = 50

 

[Mysqldump]

Quick

Max_allowed_packet = 16 M

 

[Mysql]

No-auto-rehash

# Remove the next comment character if you are not familiar with SQL

# Safe-updates

 

[Isamchk]

Key_buffer = 8 M

Sort_buffer_size = 8 M

 

[Myisamchk]

Key_buffer = 8 M

Sort_buffer_size = 8 M

 

[Mysqlhotcopy]

Interactive-timeout

 

From the above configuration, we can see that my configuration file contains two instances: mysqld1 and mysqld2. That is to say, I will start two mysql services on different ports of the same server -- 3307 and 3308. The database file paths specified by each datadir are different.

 

5. Use Mysqld_multi to start/stop multiple Mysql instances

 

Note:

Use the following parameters to start mysqld_multi: (Note: This command is in the bin directory of mysql, as mentioned above. /configure -- prefix =/usr/local/mysql, so the file should be in/usr/local/mysq/bin, which depends on the path specified during installation)

Db-app:/# mysqld_multi [options] {start | stop | report} [GNR [, GNR]...]

 

Start, stop, and report are the operations you want to perform. You can specify an operation on a separate service or multiple services, which is different from the GNR list after the option. If the GNR list is not specified, mysqld_multi operates on all services according to the option file.

 

The value of each GNR is the serial number of a group or the serial number range of a group. The value of this item must be the last number of the group name. For example, if the group name is mysqld17, the value of this item is 17. if a range is specified, use "-" to connect two numbers. If the value of GNR is 10-13, it means that the group mysqld10 is in the group mysqld13. You can specify multiple groups or group ranges in the command line and separate them with commas. Blank characters (such as spaces or tabs) are not allowed. parameters after blank characters are ignored. (Note: The GNR value is the value defined in mysqld # In my. cnf. I only have 1-4 here ).

 

Start mysql

Mysqld_multi -- config-file =/usr/share/mysql/mysql_multi_exam.cnf start 1-2

 

Stop mysql

Mysqld_multi -- config-file =/usr/share/mysql/mysql_multi_exam.cnf stop 1-2

 

 

Vi. Client Access

 

Note:

The access port must be specified for any client access. To enter the specified database service. Otherwise, the default Mysql port (3306) will be used, for example:

 

Mysql-uboss-pboss-P3307-h127.0.0.1

Mysql-uboss-pboss-P3308-h127.0.0.1

 

VII. FAQs:

 

A,/usr/bin/mysqld_multi startup Error

 

 

 

1. WARNING! My_print_defaults command not found!

....

2. fatal error: Tried to use mysqladmin in group [mysqld1],

But no mysqladmin binary was found. Please add "mysqladmin = ..."

In group [mysqld_multi], or in group [mysqld1].

....

 

Solution: # export PATH = $ PATH:/usr/bin

 

 

B. When the Mysql client is started, the error "/var/lib/mysql/temp. sock" is reported.

 

 

The/var/lib/mysql/temp. sock error is returned. Remember to add-P and-h to the error. For example:

 

Mysql-uboss-pboss-P3310-h10.10.12.43

Mysql-uboss-pboss-P3311-h10.10.12.43

 

-H: with ip address. For example, the local host is 127.0.0.1.

-P: mysql instance port, for example, 3307

From: xgbjmxn