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

In this issue, the editor will bring you about how to achieve multi-instance deployment in mysql5.5. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

First, the principle of mysql multi-instance.

Mysql multi-instance, to put it simply, is to open multiple different mysql service ports (such as 3306 and 3307) on a server and run multiple mysql service processes. These service processes provide their own services by listening to different service ports through different socket.

These mysql instances share a set of mysql installers, using different my.cnf configuration files, launchers, and data files. When providing services, mysql multiple instances are logically independent, and each instance obtains the relevant hardware resources of the server according to the set value of the configuration file.

Second, the characteristics of mysql multi-instance.

2.1 effective use of server resources

When a single server resource is left, we can make full use of the remaining server resources to provide more services.

2.2 Save server resources

When the company is cash-strapped, but the database needs to provide independent services, and requires technologies such as master-slave synchronization, it is best to use multiple instances.

2.3 there is a problem of mutual preemption of resources.

When the concurrency of an instance service is very high or there are slow queries, it will consume more memory, CPU, disk IO and other resources of the server, which will lead to a decline in the quality of access provided by other instances on the server, and the server resources will preempt each other.

Third, mysql multi-instance application scenarios

3.1 Choice of cash-strapped companies

When the company does not have too much business visits and is reluctant to spend money, but at the same time wants the database services of different businesses to be independent, and requires technologies such as master-slave synchronization to provide backup or read-write separation services, it is best to use multiple instances.

3.2 concurrent access is not a very large business

When the business visit volume of the company is not too large, the server resources are basically idle, which is very suitable for multi-instance applications. If the SQL statement is well optimized, multi-instance is a technique worth using. Even if the concurrency is very large, as long as the reasonable allocation of system resources, there will be no big problem.

4. Mysql5.5 multi-instance deployment method

4.1 mysql5.5 multi-instance deployment method

The mysql5.5 multi-instance deployment method one is to start multiple different processes through multiple configuration files, and the second is to use the official native mysqld_multi to implement.

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

4.2 installation and configuration of mysql5.5

To configure multiple instances of mysql5.5, we first need to install mysql5.5, which can be found in the article "Mud: mysql5.5 Database cmake source code compilation and installation" for mysql5.5 installation.

After mysql5.5 is installed, let's not start mysql because mysql is a single instance at this time.

4.3 create a data directory for multiple instances of mysql

Now let's create a data directory for multiple instances of mysql5.5, where we create two mysql instances 3306 and 3307. Create their own data directories, 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 directories for instances 3306 and 3307 are created, let's configure the my.cnf configuration files for instances 3306 and 3307.

Copy the my-medium.cnf under the mysql5.5 installation directory support-files to my.cnf, and modify the contents below. Now take 3306 as an 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 part marked by * * in the figure is our main modification, and the rest remains the same by default.

The above is the my.cnf configuration file for instance 3306. Now let's configure the my.cnf for instance 3307. The configuration file my.cnf of instance 3307 Let's directly copy the my.cnf file of instance 3306, and then change 3306 of the file to 3307 through the sed command. As follows:

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

Sed-I's Universe 3306 + 3307 / data/3307/my.cnf

Or

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

4.5 initialize mysql multiple instances

After 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

Notice that the mysql_install_db of mysql5.5 is in the / usr/local/mysql/scripts/mysql_install_db directory of mysql5.5.

View the initialization of the instance, as follows:

Tree-L 3 / data/

From the figure above, we can see that the mysql instance creates a basic database after initialization.

Now let's take a look at the properties of the initialization creation file, as follows:

From the figure above, you can see that the files created by initialization belong to the user mysql.

Why is this the case?

This is because we initialize the option of adding-- user=mysql. Of course, this is also what we need, because it increases the security of mysql.

4.6 modify database directory permissions of mysql instance

After the mysql instance is initialized, we now re-grant the data directory permissions of instance 3306 and instance 3307 to the mysql user. As follows:

Chown-R mysql:mysql / data/3306

Chown-R mysql:mysql / data/3307

It is recommended that you do it again, otherwise you will get an error when you start the mysql instance. Causes the mysql instance to fail to start.

4.7 launch multiple instances of mysql5.5

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

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

In fact, the startup of a single instance mysql is also started through the mysqld_safe command. It loads the / etc/my.cnf file by default.

4.8 Log in to mysql5.5 multiple instances

When logging in to a multi-instance database, we need to add the socket file of the instance before we can log in normally. Now take 3306 as an example.

Log in to 3306 instance locally, as follows:

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

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

Create database ilanni3306

Show databases

Now let's remotely log in to instance 3306 and view the database we just created. As follows:

Mysql-h292.168.1.213-uroot-p-S / data/3306/mysql.sock

Or: mysql-h292.168.1.213-uroot-p-P

3306

From the figure above, we can see that 3306 instances can also be connected remotely.

4.9 modify the mysql5.5 multi-instance root password

Change the root password of instance 3306 and use the mysqladmin command. As follows:

Mysqladmin-uroot-p password 123456-S / data/3306/mysql.sock

The above is how to achieve multi-instance deployment in the mysql5.5 shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.