Mysql5.5 multi-instance deployment and mysql5.5 instance deployment

Mysql5.5 multi-instance deployment and mysql5.5 instance deployment

To deploy MySQL Databases on multiple instances, follow these steps.

1. mysql multi-instance principle

2. Features of mysql multi-instance

3. mysql multi-instance application scenarios

4. mysql5.5 multi-instance deployment Method

I. Principles of mysql multi-instance

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

These mysql instances share a set of mysql installation programs, using different my. cnf configuration files, startup programs, and data files. When providing services, mysql multiple instances are logically independent of each other. Each instance obtains related hardware resources of the server based on the value set in the configuration file.

Ii. Features of mysql multi-instance

2.1 effectively use server resources

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

2.2 saving server resources

When the company has a shortage of funds, but the database needs to provide independent services, and master-slave synchronization and other technologies, it is best to use multiple instances.

2.3 mutual resource Preemption

When the service concurrency of an instance is high or slow, more memory, CPU, disk IO, and other resources of the server are consumed, in this case, the quality of access provided by other instances on the server is reduced, and server resources are preemptible.

Iii. mysql multi-instance application scenarios

3.1 choice of capital shortage companies

When the company's business traffic is not large, and it is reluctant to spend money, but it also wants the database services of different businesses to be independent, and requires technologies such as master-slave synchronization to provide backup or read/write splitting services, it is best to use multiple instances.

3.2 concurrent access is not a particularly large business

When the company's business traffic is not large and the server resources are mostly idle, this is very suitable for applications with multiple instances. If you optimize SQL statements, multiple instances are a very useful technology. Even if the concurrency is large, as long as the system resources are properly allocated, there will be no major problems.

Iv. 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 configuration files, the second is to use the built-in mysqld_multi.

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

4.2 install and configure mysql5.5

To configure multiple instances of mysql5.5, we first need to install mysql5.5. for installation of mysql5.5, see the article "dirty mud: mysql5.5 database cmake source code compilation and installation.

After mysql5.5 is installed, do not start mysql because mysql is a single instance.

4.3 create a data directory for multiple mysql instances

Now let's create a data directory for multiple mysql 3306 instances. Here we create two mysql instances 3307 and. Create a data directory as follows:

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

Tree-L 2/data/

4.4 modify the mysql5.5 multi-instance my. cnf File

After the data directories of instances 3306 and 3307 are created, we will configure the my. cnf configuration file for instances 3306 and 3307.

Copy the my-medium.cnf under the mysql5.5 installation directory support-files to my. cnf and change the content. Take the instance 3306 as an example:

[Client]

Port = 3306

Socket =/data/3306/mysql. sock

[Mysqld]

Port = 3306

Socket =/data/3306/mysql. sock

Basedir =/usr/local/mysql

Data =/data/3306/data

Skip-external-locking

Key_buffer_size = 16 M

Max_allowed_packet = 1 M

Table_open_cache = 64

Sort_buffer_size = 512 K

Net_buffer_length = 8 K

Read_buffer_size = 256 K

Read_rnd_buffer_size = 512 K

Myisam_sort_buffer_size = 8 M

Skip-name-resolve

Log-bin = mysql-bin

Binlog_format = mixed

Max_binlog_size = 500 M

Server-id = 1

[Mysqld_safe]

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

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

[Mysqldump]

Quick

Max_allowed_packet = 16 M

[Mysql]

No-auto-rehash

[Myisamchk]

Key_buffer_size = 20 M

Sort_buffer_size = 20 M

Read_buffer = 2 M

Write_buffer = 2 M

[Mysqlhotcopy]

Interactive-timeout

Note that the ** marked part in the figure is the main modification, and the others remain unchanged by default.

The above is the my. cnf configuration file of instance 3306. Now we will configure my. cnf of instance 3307. In the configuration file my. cnf of instance 3307, we directly copy the my. cnf file of instance 3306, and then use the sed command to change 3306 in this file to 3307. As follows:

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

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

Or

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

4.5 initialize multiple mysql instances

After modifying the my. cnf configuration files of instances 3306 and 3307, We need to initialize these two instances and use 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 mysql_install_db of mysql5.5 is in the/usr/local/mysql/scripts/mysql_install_db directory of mysql5.5.

Check the status of the instance after initialization as follows:

Tree-L 3/data/

We can see that the mysql instance will create a basic database after initialization.

Now let's take a look at the attributes of the initial file creation, as shown below:

We can see that all the files created during initialization belong to the mysql user.

Why?

This is because the -- user = mysql option is added during initialization. Of course, this is what we need, because it increases the security of mysql.

4.6 modify the database directory permission of the mysql instance

After the initialization of the mysql instance, 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

We recommend that you perform this operation again. Otherwise, an error will be prompted when starting the mysql instance. The mysql instance cannot be started.

4.7 start multiple instances of mysql5.5

Let's start the instance now. Run 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

We can see that both instance 3306 and instance 3307 have started properly. It also indicates that our mysql5.5 multi-instance has been configured successfully.

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

4.8 log on to multiple instances of mysql5.5

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

Log on to the 3306 instance as follows:

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

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

Create database ilanni3306;

Show databases;

Now we remotely log on to instance 3306 and view the newly created database. As follows:

Mysql-h192.168.1.213-u root-p-S/data/3306/mysql. sock

Or: mysql-h192.168.1.213-uroot-p-P 3306

We can see that the remote connection to the 3306 instance is also possible.

4.9 modify mysql5.5 multi-instance root Password

Modify the root password of instance 3306 and run the mysqladmin command. As follows:

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

By now, the mysql multi-instance deployment has been completed.