Shulou(Shulou.com)06/01 Report--

The following is about the installation and configuration of MySQL multi-instance database. The secret of the text lies in being close to the topic. So, no gossip, let's read the following directly, I believe you will benefit from reading this article on the installation and configuration of MySQL multi-instance database.

1. Introduction of multiple examples of MySQL

1. What is mysql multi-instance?

Mysql multi-instance means that multiple CVM ports (such as 3306 and 3307) are opened on one CVM at the same time, and multiple mysql service processes are run at the same time. These service processes provide services through different socket listening on different CVM ports.

These mysql multiple instances share a set of mysql installers, using different my.cnf (or the same) configuration files, startup commands (or the same), and data files. When providing services, multi-instance mysql appears to be logically independent, and they obtain the corresponding number of hardware resources of the CVM according to the corresponding settings of the configuration file.

For example, multiple instances of mysql are equivalent to multiple bedrooms of a house. Each instance can be regarded as a bedroom, and the entire CVM is a house. The hardware resources (CPU, Mem, Disk) and software resources (centos operating system) of the CVM can be regarded as the bathroom, kitchen and living room of the house, and they are the shared resources of the house.

2. The function and problems of mysql multi-instance.

Function:

2.1. Effective use of CVM resources

When there is a surplus of a single CVM resource, you can make full use of the remaining resources to provide more services, and you can achieve logical isolation of resources.

2.2. Save CVM resources

When the company is cash-strapped, but the database needs to provide services as independently as possible, and when technologies such as master-slave replication are needed, multiple instances are better.

Question:

There is the problem of mutual preemption of resources.

When the concurrency of a database instance is very high or has SQL slow query, the whole instance will consume a lot of resources such as system CPU and disk ID O, resulting in a decline in the service quality provided by other database instances on the CVM.

3. Mysql multi-instance production application scenario

3.1. The choice of cash-strapped companies

If the company is short of funds and the number of visits to the company's business is not too large, but wants the database services of different businesses to provide services independently as far as possible without affecting each other, and at the same time, technologies such as master-slave replication are required to provide backup or read-write separation services, then, multiple instances couldn't be better. For example, you can deploy 915 instances through 3 CVM, cross-master slave replication, data backup and read-write separation, thus achieving the effect that only one database can be installed on each of the 915 CVM. Emphasize: the so-called as independent as possible is relative.

3.2. Concurrent access is not a very large business

When the business visit volume of the company is not too large, the resources of the CVM are basically wasted, which is suitable for multi-instance applications. If the optimization of SQL statements is done well, mysql multi-instances will be a technology worth using. Even if the concurrency is very large, it will not be a big problem to allocate system resources and match services properly.

3.3.The portal application mysql multi-instance scenario

Portals usually use multiple instances, because a CVM with good hardware can save IDC cabinet space. At the same time, running multiple instances will also reduce the waste of hardware resources.

Install and configure multi-instance mysql database

1. Create a data file directory for multiple instances of mysql

Take the / data directory as the total root directory of mysql multi-instance, and then plan different numbers (that is, the port number of mysql instance) as the secondary directory under / data, and the corresponding numbers of different secondary directories will be used as the port numbers of mysql instances to distinguish different instances. The secondary directory corresponding to numbers contains mysql data files, configuration files and startup files.

[root@mysql-server tools] # mkdir-p / data/ {3306 root@mysql-server tools 3307} / data/ [root@mysql-server tools] # tree / data/data ├── 3306 │ └── data ├── 3307 │ └── data

2. Create a configuration file for multiple instances of mysql (data.zip takes the already configured template and uploads it to the relevant directory through rz)

To make mysql multiple instances independent of each other, create a my.cnf configuration file and a startup file mysql for each instance to correspond to their own data file directory data.

[root@mysql-server tools] # unzip data.zip Archive: data.zip creating: data/ creating: data/3306/ inflating: data/3306/my.cnf inflating: data/3306/mysql creating: data/3307/ inflating: data/3307/my.cnf inflating: data/3307/mysql [root@mysql-server tools] # cp data/3306/my.cnf / data/3306/ [root@mysql-server tools] # cp Data/3307/my.cnf / data/3307/ [root@mysql-server tools] # tree / data/data ├── 3306 │ ├── data │ └── my.cnf └── 3307 ├── data └── my.cnf4 directories 2 files

3. Create a startup file for multiple instances of mysql (data.zip takes the already configured template and uploads it to the relevant directory through rz)

[root@mysql-server tools] # cp data/3306/mysql / data/3306/ [root@mysql-server tools] # cp data/3307/mysql / data/3307/ [root@mysql-server tools] # tree / data/data ├── 3306 │ ├── data │ ├── my.cnf │ └── mysql └── 3307 ├── data ├── my.cnf └── mysql4 directories, 4 files

4. Configure file permissions for multiple instances of mysql

4.1.Authorize mysql users and groups to manage the root / data of the entire multi-instance through the following command

[root@mysql-server tools] # chown-R mysql.mysql / data [root@mysql-server tools] # find / data-type f-name "mysql" | xargs ls-1 mysql mysql 1307 April 8 21:07 / data/3306/mysql-rw-r--r-- 1 mysql mysql 1307 April 8 21:07 / data/3307/mysql

4.2.Use the following command to authorize the mysql executable of all startup files of mysql multi-instance. It is best to set 700permissions. Be careful not to use 755permissions, because there is a database administrator password in the startup file, which will be read.

[root@mysql-server tools] # find / data-type f-name "mysql" | xargs chmod 700 [root@mysql-server tools] # find / data-type f-name "mysql" | xargs ls-RWX-1 mysql mysql 1307 April 8 21:07 / data/3306/mysql-rwx- 1 mysql mysql 1307 April 8 21:07 / data/3307/mysql

5. Add mysql related commands to the configuration of the global path

5.1. The significance of configuring the global path

If you don't configure a global path for mysql commands, you can't enter commands like mysql directly on the command line. You can only use full-path commands (/ application/mysql/bin/mysql), which is troublesome to enter commands with paths.

5.2.The method of configuring the global path of mysql

5.2.1. Confirm the path where the mysql command is located, as follows:

[root@mysql-server tools] # ls / application/mysql/bin/mysql/application/mysql/bin/mysql

5.2.2. Add the / application/mysql/bin path before the PATH variable and append it to the / etc/profile file. The command is as follows:

[root@mysql-server tools] # echo 'export PATH=/application/mysql/bin:$PATH' > > / etc/profile [root@mysql-server tools] # tail-1 / etc/profilePATH= "/ application/mysql/bin:$PATH" [root@mysql-server tools] # source / etc/profile [root@mysql-server tools] # echo $PATH/application/mysql/bin:/usr/local/java/jdk1.8.0_60/bin:/usr/local/java/jdk1.8.0_60/jre/bin : / application/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/bin:/root/bin

6. Initialize the database file of multiple instances of mysql

Initialize the mysql database with the following commands:

Cd / application/mysql/scripts/./mysql_install_db-- basedir=/application/mysql-- datadir=/data/3306/data-- user=mysql./mysql_install_db-- basedir=/application/mysql-- datadir=/data/3307/data-- user=mysql#-- basedir=/application/mysql is the mysql installation path, and-- datadir is a different instance data directory.

6.2. The principle and result description of initializing database.

The essence of initializing the database is to create the library files of the basic database system, such as generating mysql library tables and so on.

After initializing the database, look at the data directory of the corresponding instance, and you can see the following files:

[root@mysql-server scripts] # tree / data/data ├── 3306 │ ├── data │ │ ├── mysql │ ├── columns_priv.frm │ ├── columns_priv.MYD │ ├── columns_priv.MYI │ ├── db.frm... Omit the part.

7. Start mysql multi-instance database

The startup command for the first instance 3306 is as follows:

[root@mysql-server scripts] # / data/3306/mysql startStarting MySQL...

The startup command for the second instance 3307 is as follows:

[root@mysql-server scripts] # / data/3307/mysql start Starting MySQL...

Check whether the mysql multi-instance database is started successfully, with the following command:

[root@mysql-server scripts] # ss-lntup | grep 330tcp LISTEN 0 128 *: 3306 *: * users: (("mysqld", 2804 mysqld 12)) tcp LISTEN 0 128 *: 3307 *: * users: ("mysqld", 3522prime11)

8. Troubleshooting instructions for mysql multi-instance startup

If the service for multiple instances of mysql is not started, the troubleshooting method is as follows:

1) if you find that the port of the corresponding instance of mysql is not displayed, please wait a few seconds before checking. The startup of mysql service is slower than that of web service.

2) if not, check the error log of the corresponding instance of the mysql service. The error log path is defined at the bottom of the my.cnf configuration. For example, the error log for a 3306 instance is:

[root@mysql-server 3306] # grep log-error my.cnf | tail-1 started; log sequence number errorspur tail-1 tail _ oldboy3306.err[ root @ root 3306] # started; log sequence number-10 / log 180408 22:14:09 InnoDB: 5.5.32 started; log sequence number 0180408 22:14:09 [Note] Server hostname (bind-address): '0.0.0.0' Port: 3306180408 22:14:09 [Note]-'0.0.0.0' resolves to' 0.0.0.0The Note: 180408 22:14:09 [Note] Server socket created on IP: '0.0.0.0The.... Omit the part. 3. Configure and manage mysql multi-instance database

1. Configure mysql multi-instance database to boot automatically

[root@mysql-server 3306] # echo "/ data/3306/mysql start" > > / etc/rc.local [root@mysql-server 3306] # echo "/ data/3307/mysql start" > > / etc/rc.local [root@mysql-server 3306] # tail-2 / etc/rc.local / data/3306/mysql start/data/3307/mysql start

2. Log in to mysql and its management method

After the mysql installation is complete, by default, the mysql administrator's account root is password-free. Logging in to different instances requires specifying the mysql.sock file path for different instances, and this mysql.sock is specified in the my.cnf configuration file.

The following is how to log in to the database without a password. The key points are the-S parameter and the / data/3306/mysql.sock specified later. Note that although the sock of different instances has the same name, the path is different, so it is a different file.

[root@mysql-server 3306] # mysql- S / data/3306/mysql.sock [root@mysql-server 3306] # mysql- S / data/3307/mysql.sock

The following is the command to restart the corresponding instance database:

/ data/3306/mysql stop/data/3306/mysql start

3. Mysql security configuration

The root password of the mysql administrator's account is empty by default, which is extremely insecure. You can set an independent password for the databases of different mysql instances through the mysqladmin command, as follows:

[root@mysql-server 3306] # mysqladmin-u root-S / data/3306/mysql.sock password '123456' [root@mysql-server 3306] # mysqladmin-u root-S / data/3307/mysql.sock password' 123456' Log in to 3306 instance:

[root@mysql-server 3306] # mysql- uroot-p-S / data/3306/mysql.sock

The command to log in to 3307 instance is as follows:

[root@mysql-server 3306] # mysql- uroot-p-S / data/3307/mysql.sock

To restart the multi-instance database, you also need to configure it as follows. Before restarting the database, you need to adjust the corresponding database password in different instance startup files.

[root@mysql-server 3306] # sed-n '13p' / data/3306/mysql / data/3307/mysqlmysql_pwd= "oldboy" [root@mysql-server 3306] # sed-I' 13 sroomoldboy`123456 / data/3306/mysql / data/3307/mysql [root@mysql-server 3306] # sed-n '13p' / data/3306/mysql / data/3307/mysqlmysql_pwd= "123456"

4. Analysis of multi-instance mysql login problem

1). Log in to mysql for multiple instances locally.

Multi-instance local login usually specifies which instance to log in to through a socket file, and the location of this file is specified in the mysql compilation process or the my.cnf file. When logging in to the database locally, the login program uses the socket file to determine which database instance is logged in.

2) remote connection to log in to multiple instances of mysql

When logging into one of the mysql multiple instances remotely, the mysql instance to be logged in is specified through the TCP port, whose configuration is specified in the mysql configuration file my.cnf.

For example, in mysql-uoldboy-palleoldboy'-h 10.0.0.7-P 3307,-P is the port parameter, followed by a specific instance port, which is a "logical connection location", which is a way in which client programs are assigned to special service programs on the computer, emphasizing that oldboy users are authorized on 10.0.0.7 in advance.

Is there anything you don't understand about the installation and configuration of the above MySQL multi-instance database? Or if you want to know more about it, you can continue to follow our industry information section.

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.