Slime: mysql5.5 Multi-instance deployment

This article by show according to Lin Feng to provide friendship sponsorship, starting in the mud row world.

mysql5.5 database Multi-instance deployment, we can do this in several steps.

1, MySQL multi-instance principle

2, MySQL multi-instance features

3. mysql Multi-instance application scenario

4. mysql5.5 Multi-Instance deployment method

First, the principle of MySQL multi-instance

MySQL multi-instance, simply put, is to open a number of different MySQL service ports (such as 3306,3307) on a single server, running multiple MySQL service processes. These service processes use different sockets to listen on different service ports to provide their own services.

These MySQL instances share a common set of MySQL installers that use different my.cnf configuration files, startup programs, and data files. When providing services, MySQL multi-instance logically appears to be independent, each instance according to the configuration file settings, to obtain the relevant hardware resources of the server.

Ii. features of MySQL multi-instance

2.1 Efficient use of server resources

When a single server resource is left, you can take advantage of the remaining server resources to provide more services.

2.2 Conserve server resources

When the company funds tight, but the database needs to provide separate services, and the need for master-slave synchronization and other technologies, the use of multi-instance is best.

2.3 Resource-to- preemption issues arise

When an instance service is in high concurrency or has slow queries, it consumes more resources such as memory, CPU, disk IO, and so on, which causes the quality of access to other instances on the server to degrade, and server resources preempt each other.

Three, MySQL multi-instance application scenario

3.1 choice of capital-intensive companies

When the company's business visits are not too large, but also reluctant to spend money, but at the same time hope that the different services of the database service independent, but also require master-slave synchronization and other technologies to provide backup or read-write separation services, using multi-instance is the best.

3.2 concurrent access is not a particularly large business

When the company's business visits are not too large, the server resources are basically idle more, this is very suitable for multi-instance applications. If the SQL statement is optimized, multiple instances are a good technique to use. Even if the concurrency is very large, so long as the reasonable allocation of system resources, there will not be too much problem.

Four, mysql5.5 multi-Instance deployment method

4.1 mysql5.5 Multi-Instance deployment method

mysql5.5 Multi-Instance deployment method One is to start multiple different processes through multiple profiles, and the second is to use the official own mysqld_multi.

The first method we can separate the configuration files of each instance, the management is more convenient. The second method is to put multiple instances into a configuration file, this management is not very convenient. So here we choose the first method, and the following experiments are all done under this method.

4.2 mysql5.5 the installation and configuration

To configure mysql5.5 multi-instance, we first want to install mysql5.5, about mysql5.5 installation can see "Slime: mysql5.5 database cmake Source Compilation Installation" This article.

mysql5.5 after installation, we do not start MySQL because MySQL is a single instance at this time.

4.3 Create a MySQL multi-instance Data directory

Now let's create a data directory with multiple instances of mysql5.5, where we create two MySQL instances 3306 and 3307. Create your own data directory, as follows:

Mkdir-p/data/{3306,3307}/data

Tree-l 2/data/

4.4 Modify mysql5.5 Multi-instance my.cnf file

After the data Catalog for instance 3306 and 3307 is created, we configure the MY.CNF configuration file for instance 3306 and 3307.

Copy the mysql5.5 installation directory support-files under MY-MEDIUM.CNF as MY.CNF, and modify the contents to the next. Now take the example of the 3306 example, as follows:

[Client]

Port = 3306

Socket =/data/3306/mysql.sock

[Mysqld]

Port = 3306

Socket =/data/3306/mysql.sock

Basedir =/usr/local/mysql

DataDir =/data/3306/data

Skip-external-locking

Key_buffer_size = 16M

Max_allowed_packet = 1M

Table_open_cache = 64

Sort_buffer_size = 512K

Net_buffer_length = 8K

Read_buffer_size = 256K

Read_rnd_buffer_size = 512K

Myisam_sort_buffer_size = 8M

Skip-name-resolve

Log-bin=mysql-bin

Binlog_format=mixed

Max_binlog_size = 500M

Server-id = 1

[Mysqld_safe]

Log-error=/data/3306/ilanni.err

Pid-file=/data/3306/ilanni.pid

[Mysqldump]

Quick

Max_allowed_packet = 16M

[MySQL]

No-auto-rehash

[Myisamchk]

Key_buffer_size = 20M

Sort_buffer_size = 20M

Read_buffer = 2M

Write_buffer = 2M

[Mysqlhotcopy]

Interactive-timeout

Note that the yellow mark in the figure is the part we mainly modify, the other default remains unchanged.

The above is the MY.CNF configuration file for instance 3306, now let's configure MY.CNF for instance 3307. The configuration file for instance 3307 my.cnf us to copy the my.cnf file of instance 3306 directly and then change the 3306 in the file to 3307 by using the SED command. As follows:

Cp/data/3306/my.cnf/data/3307/my.cnf

Sed-i ' s/3306/3307/g '/data/3307/my.cnf

Or

Sed-e ' s/3306/3307/g '/data/3306/my.cnf >/data/3307/my.cnf

4.5 initializing MySQL multi-instance

Once the MY.CNF configuration files for instances 3306 and 3307 have been modified, we need to initialize the two instances using the mysql_install_db command. As follows:

/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql--datadir=/data/3306/data--user=mysql

/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql--datadir=/data/3307/data--user=mysql

Note that the mysql_install_db of mysql5.5 is under the/usr/local/mysql/scripts/mysql_install_db directory of mysql5.5.

Look at the situation after the instance is initialized, as follows:

Tree-l 3/data/

We can see that the MySQL instance creates a basic database after it is initialized.

Now take a look at initializing the properties of the created file as follows:

The files created by the initialization can be seen to belong to the MySQL user.

Why would that be?

This is because we initialized the option to join--user=mysql. This, of course, is what we need because it increases the security of MySQL.

4.6 Modify database directory permissions for a MySQL instance

After the MySQL instance initialization is complete, we now re-assign the data directory permissions for instance 3306 and instance 3307 to the MySQL user. As follows:

Chown-r mysql:mysql/data/3306

Chown-r mysql:mysql/data/3307

This place is recommended to be operated on again, or an error will be prompted when starting the MySQL instance. Causes the MySQL instance to fail to start.

4.7 start mysql5.5 Multi-instance

Let's start the instance now. Use the following command:

/usr/local/mysql/bin/mysqld_safe--DEFAULTS-FILE=/DATA/3306/MY.CNF &

/usr/local/mysql/bin/mysqld_safe--DEFAULTS-FILE=/DATA/3307/MY.CNF &

PS aux |grep mysqld

Through, we can see that both instances 3306 and 3307 have started normally. It also shows that our mysql5.5 multi-instance has been successfully configured.

In fact, single-instance MySQL startup is also initiated by Mysqld_safe command. It will load the/etc/my.cnf file by default.

4.8 Login mysql5.5 Multi-instance

When logging into a multi-instance database, we need to join the socket file for that instance to log in properly. Now take 3306 instances as an example.

Local login 3306 instance, as follows:

Mysql-uroot-p-s/data/3306/mysql.sock

After logging in locally, we create a ilanni3306 database on instance 3306. As follows:

Create Database ilanni3306;

show databases;

Now we telnet to instance 3306 and look at the newly created database. As follows:

Mysql-h192.168.1.213-uroot-p-s/data/3306/mysql.sock

Through, we can see that the remote is also able to connect 3306 instances.

4.9 Modifying the mysql5.5 multi-instance root password

To modify the root password for instance 3306, use the Mysqladmin command. As follows:

Mysqladmin-uroot-p Password 123456-s/data/3306/mysql.sock

This is where our MySQL multi-instance deployment is completed.

Slime: mysql5.5 Multi-instance deployment