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

MySQL multiple instances

Mysql multi-instance means that on one server, mysql service opens multiple different ports (such as 3306, 3307, 3308) and runs multiple service processes. These mysql service processes listen to different data ports through different socket, and then provide their own services without interference.

Mysql multiple instances share a set of mysql applications on the same server, so you only need to deploy the mysql program once when deploying mysql, rather than multiple deployments. However, different my.cnf configuration files, launchers, and data files are used among multiple instances of mysql. In terms of providing services, mysql multi-instances logically appear to be independent and independent of each other, and multiple instances obtain the hardware resources of the relevant servers according to the set values of the configuration file.

Multi-instance application scenario

When a company does not have too many visits to its business, it wants to save costs, and it also hopes that the database services of different businesses can be independent as far as possible, so that the provision of services will not be affected by each other. In addition, we also need to apply master-slave synchronization and other technologies to provide database backup or read-write separation services, and to facilitate the expansion and migration of database architecture when the volume of business increases.

When the business traffic of the company is not too large, the resources of the server are basically in a state of surplus. This is a good time for mysql multi-instance applications. If you do a good job of optimizing SQL statements, mysql multi-instance is a technique worth using.

In the test environment, the company will need a database environment to test the business. Deploying multiple instances on a single machine can also save the cost of MySQL multi-instance implementation.

Generally speaking, mysql multi-instance can be implemented in three ways, each of which has its own advantages and disadvantages, as follows:

Based on multiple profiles, multiple instances are implemented by using multiple profiles to start different processes.

Advantages: simple logic and simple configuration

Disadvantages: it is not convenient to manage

Based on mysqld_multi, multiple instances are implemented using separate configuration files through the official mysqld_multi tool.

Advantages: convenient for centralized management

Disadvantages: it is not convenient to customize for each instance configuration

Using MySQL instance Manager (MYSQLMANAGER) based on IM, this method seems better, but it is also a bit complicated.

Advantages: convenient for centralized management

Disadvantages: high coupling. As soon as IM is hung up, all instances will be hung up.

It is not convenient to customize for each instance configuration

The content of this chapter is implemented in the first way (individuals prefer this way), and the database uses mariadb instead of mysql, and the process is the same.

System and installation version information

Operating system: Centos6.9

Database: mariadb-10.2.15.tar.gz

Three examples: 3306, 3307, 3308.

MySQL multi-instance installation installation mysql installation dependency package yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake libevent-devel gnutls-devel libaio-devel openssl-devel ncurses-devel libxml2-devel

Create a mysql user group

Useradd-r mysql

Create an instance database directory

The database directory, configuration file directory and startup program directory of each instance are all stored in a separate directory location, with the following structure:

[root@localhost] # mkdir-p / data/mysql/ {3306, 3307, 3308} / {data,etc,log,socket Bin} [root@localhost ~] # tree / data/data ├── lost+found └── mysql ├── 3306 │ ├── bin │ ├── data │ ├── etc │ ├── log │ └── socket ├── 3307 │ ├── bin │ ├── data │ etc log └── socket ├── 3308 │ ├── bin │ ├── data │ ├── etc │ ├── log │ └── socket └── mysqld

Decompress and install the source package

[root@localhost ~] # tar zxvf mariadb-10.2.15 [root@localhost ~] # cd mariadb-10.2.15# compile cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-DCMAKE-USER=mysql-DCMAKE-GROUP=mysql-DMYSQL_DATADIR=/data/mysql-DWITHOUT_TOKUDB=1

If you execute the cmake prompt: CMake Error at storage/tokudb/PerconaFT/cmake_modules/TokuSetupCompiler.cmake:183 (message):

Add compilation parameter:-DWITHOUT_TOKUDB=1

Make-j 4make install

After the installation is successful, do not initialize first. Configure the configuration file and startup script file before initializing.

Copy the configuration file to the etc directory of each instance

The configuration file is under the decompression directory of the source code package.

[root@localhost support-files] # cp my-huge.cnf / data/mysql/3306/etc/my.cnf [root@localhost support-files] # cp my-huge.cnf / data/mysql/3307/etc/my.cnf [root@localhost support-files] # cp my-huge.cnf / data/mysql/3308/etc/my.cnf

Modify the content of the configuration file, modify the my.cnf content, modify the port, socket path, data storage directory, etc.

[client] # password = your_passwordport = 3306socket = / data/mysql/3306/socket/ mysql.sock [mysqld] port = 3306socket = / data/mysql/3306/socket/mysql.sockdatadir=/data/mysql/3306

Similarly, the 3307 and 3308 instances need to modify the same configuration.

Write a service startup script

#! / bin/bashport=3306mysql_user= "root" mysql_pwd= "cmd_path=" / usr/local/mysql/bin "mysql_basedir=" / data/mysql "mysql_sock=" ${mysql_basedir} / ${port} / socket/mysql.sock "function_start_mysql () {if [!-e" $mysql_sock "] Then printf "Starting MySQL...\ n" ${cmd_path} / mysqld_safe-- defaults-file=$ {mysql_basedir} / ${port} / etc/my.cnf & > / dev/null & else printf "MySQL is running...\ n" exit fi} function_stop_mysql () {if [!-e "$mysql_sock"] Then printf "MySQL is stopped...\ n" exit else printf "Stoping MySQL...\ n" # ${cmd_path} / mysqladmin-u ${mysql_user}-S ${mysql_sock} shutdown ${cmd_path} / mysqladmin-u ${mysql_user}-p ${mysql_pwd}-S ${mysql_sock} shutdown fi} function_restart_mysql () {printf "Restarting MySQL.. .\ n "function_stop_mysql sleep 2 function_start_mysql} case $1 instart) function_start_mysql ; stop) function_stop_mysql;;restart) printf "Usage: ${mysql_basedir} / ${port} / bin/mysqld {start | stop | restart}\ n" esac

You need to prepare three copies of the above script, because the mysql instances are managed independently. Modify the above script parameter port variable to 3307 port and place it in the bin directory of each instance. The script is executed from the bin directory of the respective instance when the service is started.

Port=3307mysql_user= "root" mysql_pwd= "cmd_path=" / usr/local/mysql/bin "mysql_basedir=" / data/mysql "mysql_sock=" ${mysql_basedir} / ${port} / socket/mysql.sock "

Initialize the database

[root@localhost mysql] # scripts/mysql_install_db-user=mysql-basedir=/usr/local/mysql/-datadir=/data/mysql/3306/data [root@localhost mysql] # scripts/mysql_install_db-user=mysql-basedir=/usr/local/mysql/-datadir=/data/mysql/3307/data [root@localhost mysql] # scripts/mysql_install_db-user=mysql-basedir=/usr/local/mysql/-datadir=/data/mysql/3308/data

Modify the mysql instance directory owner

Chown-R mysql:mysql / data/mysql

Start each instance service

[root@localhost ~] # / data/mysql/3306/bin/mysqld start [root@localhost ~] # / data/mysql/3307/bin/mysqld start [root@localhost ~] # / data/mysql/3308/bin/mysqld start [root@localhost mysql] # ss-tnlState Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 80:: 3306: * LISTEN 0 80: 3307:: * LISTEN 0 80: 3308: * LISTEN 0 128: 22:: *

Test login

[root@localhost] # mysql-S / data/mysql/3306/socket/mysql.sockWelcome to the MariaDB monitor. Commands end with; or\ g.Your MariaDB connection id is 9Server version: 10.2.15-MariaDB-log Source distributionCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] >

At this point, the multi-instance installation of mysql is completed. In this multi-instance environment, you can use a single instance for data storage, or you can configure each instance into a master-slave architecture for use. Here, by the way, the master-slave architecture of mysql is also introduced to achieve. With regard to the master-slave architecture of mysql, we will specifically study various functions in the future. It is only simple to implement here, which can help the assistant clear the work process and configuration process of master-slave replication.

MySQL multi-instance master-slave replication

Before starting the master-slave configuration, let's briefly introduce the master-slave replication principle of mysql.

The principle of mysql replication is roughly as follows:

1when a transaction is submitted, the mysql master database records the data changes as an event events in the binary log file bin-log, and the sync_binlog parameter on the main mysql library controls the bin-log log to be flushed to disk.

2. The master library pushes the events in the binary log file bin-log to the relay log Relay Log of the slave library, and then the slave library writes the data to the slave database according to the relay log Relay log rewrite data operation, so as to achieve the consistency of the data between the master library and the slave database.

In the process of mysql replication, there are three threads to complete the replication operation: the binlog dump thread is on the master database, and the I / O thread and SQL thread are on the slave database. When the replication (START SLAVE) is started on the slave library, the START SLAVE thread will first connect to the master library (the user who connects the master library will create it), and the master library will then create the binlog dump thread to read the database events (SQL logs) and send them to the I / O thread. The binlog log data is updated to the relay log Relay log of the slave database, and the data in the relay log Relay log is read from the SQL thread on the database and written to the local database. Finally, the master-slave replication is completed.

The realization of one master and multiple followers

This article implements one master and multiple mysql instances, with 3306 instances as master libraries and 3307 and 3308 as slave libraries. The most important thing to achieve master-slave replication is the setting of log files. The master database must open binary logs, slave libraries must turn off binary, and enable relay log relay log. The key points are as follows:

Create master library to provide synchronization of slave library user master library open binary log slave library enable relay log relay log and turn off binary log function server-id between master and slave cannot be synchronized with the same master-slave time (because on the same host, step skip) configure the configuration file of master-slave library instance

Check to see if mysql enables binary and relay logs

MariaDB [(none)] > show variables like 'log_bin';+-+-+ | Variable_name | Value | +-+-+ | log_bin | ON | +-+-+ 1 row in set (0.00 sec) MariaDB [(none)] > show variables like' relay_log' +-+-+ | Variable_name | Value | +-+-+ | relay_log | | +-+-+ 1 row in set (0.00 sec)

Binary logging is enabled by default when mysql is installed, but relay log is not started, so there is no need to set it on the master library, and relay logs need to be enabled in the slave library:

Edit the my.cnf of instance 3307 and 3308 to close bin-log, open relay log, and modify server-id.

3307 examples:

# log-bin=mysql-binrelay-log=relay-logserver-id = 2

3308 examples:

# log-bin=mysql-binrelay-log=relay-logserver-id = 3

Restart the 3307pint 3308 instance after modification

[root@localhost ~] # / data/mysql/3307/bin/mysqld restartRestarting MySQL...Stoping MySQL...Enter password: Starting MySQL... [root@localhost ~] # / data/mysql/3308/bin/mysqld restartRestarting MySQL...Stoping MySQL...Enter password: # prompt for password here, because password Starting MySQL... is defined in

Check whether the log is enabled after restarting the slave library instance

MariaDB [(none)] > show variables like 'relay_log' +-+-+ | Variable_name | Value | +-+-+ | relay_log | relay-log | +-+-+ 1 row in set (0.00 sec) MariaDB [(none)] > show variables like 'log_bin' +-+-+ | Variable_name | Value | +-+-+ | log_bin | OFF | +-+-+ 1 row in set (0.00 sec) main library creates synchronous replicated user MariaDB [(none)] > grant replication slave on *. * to yufu@'192.168.%.%' identified by '123456' Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > flush privileges;Query OK, 0 rows affected (0.00 sec)

View the bin-log logging location node of the main library

Because when using the change master to instruction to connect to the master library from the slave library, you need to specify the bin-log logging location node of the master library when you connect.

MariaDB [(none)] > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+-+ | mysql-bin.000001 | 645 | +- + 1 row in set (0.00 sec) connect to the master library from the slave library

Log in to the slave library 3307 instance:

MariaDB [(none)] > change master to master_host='192.168.214.141',master_user='yufu',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=645;Query OK, 0 rows affected (0.11 sec) MariaDB [(none)] > flush privileges;Query OK, 0 rows affected (0.01 sec)

Log in to the slave library 3308 instance:

MariaDB [(none)] > change master to master_host='192.168.214.141',master_user='yufu',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=645;Query OK, 0 rows affected (0.11 sec) MariaDB [(none)] > flush privileges;Query OK, 0 rows affected (0.00 sec) starts replication from the library

Start the 3307 instance and view the replication status

MariaDB [(none)] > START SLAVE # Query OK 0 rows affected (0.80 sec) MariaDB [(none)] > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.214.141 Master_User: yufu Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 645 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: mysql-bin.000001 Slave_ IO_Running: Yes # is also yes to successfully synchronize data Slave_SQL_Running: Yes

Start the 3308 instance and view the replication status

MariaDB [(none)] > start slave Query OK 0 rows affected (0.01 sec) MariaDB [(none)] > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.214.141 Master_User: yufu Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 645 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: mysql-bin.000001 Slave_ IO_Running: Yes Slave_SQL_Running: Yes

At this point, there is no problem with configuration startup. Let's write some data from the main library instance 3306 to see if the 3307 and 3308 instances can synchronize data normally.

Test synchronization

Log in to the main database 3306 instance to write some data:

MariaDB [(none)] > create database yufu;Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > use yufu;Database changedMariaDB [yufu] > create table test (- > id varchar (10),-> name varchar (20)->); Query OK, 0 rows affected (0.84sec) MariaDB [yufu] > insert into test values

Log in to view the data from the instance of library 3307:

MariaDB [(none)] > select * from yufu.test;+-+-+ | id | name | +-+-+ | 2323 | yufuname | +-+-+ 1 row in set (sec)

Log in to view the data from the instance of library 3308:

MariaDB [(none)] > select * from yufu.test;+-+-+ | id | name | +-+-+ | 2323 | yufuname | +-+-+ 1 row in set (0.01 sec)

After testing that the synchronization function is normal, the article finally summarizes the main points of mysql master-slave replication:

1. Bin-log binary log must be enabled for the main library

two。 The slave library must enable relay log Relay-log and close bin-log.

3. Master-slave server-id must not be the same

4. The main library creates synchronous users

5. Make sure that after the above setting, log in to execute the change master to instruction from the slave library

6. Master-slave time synchronization

In later articles, we will introduce the master-slave replication function of mysql in more detail.

Personal blog site www.gudaoyufu.com

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.