How to install multiple instances of MySQL on the same machine

Recently, due to the need for work, two MySQL instances need to be built on the same machine (note: A 3306 instance of MySQL already exists).

First of all, what is a multi-instance of MySQL, simply speaking, is a machine installed on a number of MySQL services, through different ports (such as 3307,3308) to provide services to the outside world, these processes through different sockets to listen to different service ports to provide a word service.

To make a metaphor: MySQL multi-instance equivalent to the House of the equivalent of multiple bedrooms, each instance can be regarded as a bedroom, the entire server is a house, the server hardware resources (CPU,MEM,DISK) is a shared resource, the equivalent of the house bathroom, kitchen, living room, is the public resources of the house, the North drift dwelling together, can be decided to be common above resources.

　　Benefits:

　　　　When the company funds tight, can save the resources of the server.

　　Harm:

　　　　When a service is in high concurrency or has slow queries, the entire instance consumes more memory, CPU, and resources from the entire server, resulting in degraded service quality from other instances on the server. This is equivalent to everyone living in a house in the different bedroom, morning to brush teeth, wash face, etc., so that the bathroom will be long-term occupancy, others need to wait for the same reason.

Below is a record of the steps to install a 3307 MySQL instance:

I. Basic needs and emerging issues

When MySQL fails or you need to add a new instance of MySQL, you need to reinitialize the MySQL database, which is the file that needs to regenerate the new database.

Linux environment, after the installation of MySQL, or can not directly start MySQL, need to initialize some basic MySQL things, including:

Initialize the table log, basic database information and some basic necessary files.

Two. Steps to install:

(1). Assuming that an instance of MySQL already exists with a port number of 3306, you now need to re-add an instance of MySQL with port number 3307.

Assuming that the path to MySQL installation is/usr/local/mysql/below, find a data disk comparison, such as the/data1/directory, under this directory to create a data folder:

1  mkdir  -p/data1/mysql_3307/data2  chown-  R mysql::mysql/data1/mysql_3307

(2). Copy the previous MySQL configuration file to the/data1/mysql_3307/directory and edit it:

Vim/data1/mysql_3307/my.cnf

The basic configuration is as follows:

　　　　

Note: The above path may be a bit out of the storage path of the database file above, but this is not the point, you can replace it yourself!

(3). Make sure the configuration file is correct, and then start initializing the database:

/usr/local/mysql/scripts/mysql_install_db--defaults-file=/data1/mysql_3307/my.cnf--datadir=/data1/mysql/-- After the completion of the Basedir=/usr/lcoal/mysql, the new MySQL 3307 database has been initialized, if there is an error, then look at the log error, generally due to the MySQL configuration file caused, modify it!

(4). Start the new MySQL instance service:

In the case where the initialization configuration above is guaranteed to be correct, you can start another instance of MySQL.

1 /usr/local/mysql/bin/mysqld_safe--defaults-file=/data1/mysql_3308/my.cnf &2  Check if start:  3     ps-aux | grep mysql4  If 3308 of the ports   indicate success

(5). The new MySQL does not have the root password set, which can be set by the following:

/usr/local/mysl/bin/mysqladmin     'new-paswd';

As the above steps, you can complete the installation and operation of multiple MySQL instances on a machine.

Of course, the Mysql_server script can be placed under the/rt/rc.local directory, as the server starts.

(6). If you want to log in, you need to specify the port number of MySQL, using-p3307 to specify which database to log on to.

Summarize:

In the installation of this database, I think it is necessary to download a new database of the source package, but in fact, the use of a MySQL source package, you can install multiple instances of MySQL.

How to install multiple instances of MySQL on the same machine (GO)