MySQL (MARIADB) Multi-instance application and multi-instance master-slave replication

MySQL Multi-instance

MySQL multi-instance, simple to understand is on a server, the MySQL service to open a number of different ports (such as 3306, 3307,3308), running multiple service processes. These MySQL service processes use different sockets to listen to different data ports, thus providing their own services independently of each other.

On the same server, MySQL multi-instance will go to share a MySQL application, so when you deploy MySQL only need to deploy a MySQL program, no multiple deployment. However, MySQL multiple instances will each use different MY.CNF configuration files, startup programs, and data files. In the provision of services, MySQL multi-instances logically appear to be independent, non-interfering, and multiple instances are based on the configuration file set values, to obtain the hardware resources of the relevant server.

Multi-instance application scenarios

1. When a company's business visits are not very large, but also want to save costs, and it is hoped that the different business database services can be as independent as possible, the provision of services can be mutually unaffected. In addition, we need to apply the master-slave synchronization technology to provide database backup or read/write separation service, as well as the extension and migration of database schema when the later business volume increases.

2. When the company's business visits are not too large, the resources of the server are basically surplus. This is a good fit for MySQL multi-instance applications. If you do better with SQL statement optimization, MySQL multi-instance is a technology that is worth using.

3. Test environments where a database environment is required to test the business, and multiple instances can be deployed on a single machine to save costs

MySQL multi-Instance implementation method

MySQL multi-instance routine, there are three kinds of programs can be implemented, these three kinds of scenarios have advantages and disadvantages, as follows:

• Multiple instances are implemented by using multiple profiles to launch different processes based on multiple profiles.

Advantages: Simple logic, simple configuration

Cons: Not easy to manage.

• Use a separate configuration file to implement multiple instances based on Mysqld_multi with the official Mysqld_multi tool

Advantages: Easy to centralize management management

Cons: Inconvenient to customize for each instance configuration

• Based on im using the MySQL instance Manager (Mysqlmanager), this method seems to be better, but it's a little complicated.

Advantages: Easy to centralize management

Disadvantage: High coupling degree. Im a hang, the instance is all hung

Not easy to customize for each instance configuration

This chapter is implemented in the first way, (personal preference in this way), the database uses mariadb instead of MySQL, 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 MySQL installation

• Install dependent packages

Create a MySQL user group

Useradd-r MySQL

Create an Instance database directory
Each instance of the database directory, the configuration file directory, the startup program directory are stored in a separate directory location, the structure is as follows:

[[email protected] ~]# mkdir -p /data/mysql/{3306,3307,3308}/{data,etc,log,socket,bin}[[email protected] ~]# 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

Unzip the installation source package

[[email protected] ~]# tar zxvf mariadb-10.2.15[[email protected] ~]# cd mariadb-10.2.15#编译cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql  -DCMAKE-USER=mysql  -DCMAKE-GROUP=mysql -DMYSQL_DATADIR=/data/mysql -DWITHOUT_TOKUDB=1

If you do CMake prompt: CMake Error at storage/tokudb/perconaft/cmake_modules/tokusetupcompiler.cmake:183 (message):
Add compilation parameters:-dwithout_tokudb=1

make  -j  4make  install

After the installation succeeds, the configuration file, the startup script file are configured, and then the initialization is performed.

Copy the configuration file to each instance under the ETC directory

Configuration file in the source package decompression Directory

[[email protected] support-files]# cp my-huge.cnf /data/mysql/3306/etc/my.cnf[[email protected] support-files]# cp my-huge.cnf /data/mysql/3307/etc/my.cnf[[email protected] support-files]# cp my-huge.cnf /data/mysql/3308/etc/my.cnf

Modify configuration file contents, 3306,3307,3308 each instance configuration file modify MY.CNF content, modify port, socket path, data storage directory, etc.

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

Similarly, the 3307,3308 instance will have to modify the same configuration

Writing service startup scripts

#!/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 prin TF "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}/mysqla Dmin-u ${mysql_user}-S ${mysql_sock} shutdown ${cmd_path}/mysqladmin-u ${mysql_user}-p${mysql_pwd}-S ${mysql_so CK} shutdown fi}function_restart_mysql () {printf "restarting mysql...\n" Function_stop_mysql sleep 2 functio N_start_mysql}case $ instart) function_start_mysql;; stop) Function_stop_mysql;; restart) Function_restart_mysql;; *) printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n "Esac 

The above script to prepare three copies, because the MySQL instance is independently managed, the above script parameter port variable is modified to 3307,3308, placed in the bin directory of their respective instances, the service starts from the bin directory of the respective instance to execute the script

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

Initializing the database

[[email protected] mysql]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql/3306/data[[email protected] mysql]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql/3307/data[[email protected] mysql]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql/3308/data

Modify MySQL Instance directory owner

Chown-r Mysql:mysql/data/mysql

Start each instance service

[[email protected] ~]# /data/mysql/3306/bin/mysqld start[[email protected] ~]# /data/mysql/3307/bin/mysqld start[[email protected] ~]# /data/mysql/3308/bin/mysqld start

[[email protected] 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

[[email protected] ~]# mysql -S /data/mysql/3306/socket/mysql.sockWelcome to the MariaDB monitor.  

At this time, the MySQL multi-instance installation, in this multi-instance environment, you can use a separate instance for data storage, you can also configure each instance as a master-slave architecture for use, the following by the way MySQL master and slave also introduced to realize, about MySQL master-slave architecture, will be specialized in the future of various functions, This is a simple implementation that can help clarify the process of work and configuration of master-slave replication.

MySQL multi-instance master-slave replication

Before starting the master-slave configuration, let's briefly introduce MySQL's master-slave replication principle,
The MySQL replication principle is as follows:
1,mysql Primary Database The data changes as events are recorded in the binary log file Bin-log, and the Sync_binlog parameter on the MySQL main library controls the Bin-log log flush to disk when the transaction is committed.

2, the main library pushes the events in the binary log file Bin-log to the trunk log from the library Relay log, after which the data is written from the library according to the trunk log Relay log rewrite data from the library to achieve the same data from the main library and from the library

The copy operation is done through 3 threads during the MySQL replication process: Where the Binlog dump thread is on the main library, I/O threads and SQL threads are on the slave library, and when replication starts from the library (start SLAVE), the I/O thread first connects to the main library, which is created by the main library user. The main library then creates a binlog dump thread that reads the database event (Binlog log) and sends it to the I/O thread, and I/O gets to binlog log data and updates to the trunk log relay log from the library, reading the relay log from the SQL thread on the library relay log The data is written to the local database and the master-slave replication is completed.

The realization of a master multi-slave

This article implements a master multiple way, that is, the above 3 MySQL instances, 3306 instances as the main library, 3307 and 3308 as the slave library, the most critical of the implementation of primary and secondary replication is the log file settings, the main library must open the binary log, from the library to turn off the binary, and turn on the trunk log relay log , the main points are as follows:

• Main Library creation provides synchronized users from the library
• Open binary log in main library
• Turn on the trunk log relay log from the library and turn off the binary logging feature
• The Server-id between master and slave cannot be the same
• Master-Slave time synchronization (due to the same host, step over)

Configure configuration files for master and slave library instances

See if MySQL turns on binary and trunk 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)

MySQL installation will enable binary logging by default, but there is no need to set up the relay log on the main library, the trunk log should be turned on from the library:
Edit instance 3307,3308 my.cnf close Bin-log, turn on relay log, 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

To restart the 3307,3308 instance after modification

[[email protected] ~]# /data/mysql/3307/bin/mysqld restartRestarting MySQL...Stoping MySQL...Enter password: Starting MySQL...

[[email protected] ~]# /data/mysql/3308/bin/mysqld restartRestarting MySQL...Stoping MySQL...Enter password:    #这里提示要输入密码，因为中定义了要输入密码Starting MySQL...

Check log enablement after restarting from a 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)

The main library creates a user who synchronizes replication

MariaDB [(none)]> grant replication slave on *.* to [email protected]‘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 for the main library
The Bin-log Logging location node of the main library is specified when connecting to the main library when using the change master to directive from the library.

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

Connecting the main library from the library

Log on to the instance from library 3307:

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 on to the instance from library 3308:

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)

To start the Copy from library feature

Launch 3307 instance and view replication status

MariaDB [(none)]> START SLAVE;      #Query OK, 0 rows affected (0.80 sec)MariaDB [(none)]> show slave status\G*************************** 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         #同时为yes才能成功同步数据            Slave_SQL_Running: Yes

Launch 3308 instance and view replication status

MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> show slave status\G*************************** 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, the configuration starts without problems, the following from the main library instance 3306 write some data to see if the 3307,3308 instance can synchronize the data properly

Test synchronization

Log in to the main library 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.84 sec)MariaDB [yufu]> insert into test values (‘2323‘,‘yufuname‘);Query OK, 1 row affected (0.00 sec)

Log in to view data from a library 3307 instance:

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

Log in to view data from a library 3308 instance:

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

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

1. The main library must have the Bin-log binary log turned on
2. From the library must turn on the trunk log relay-log, close Bin-log
3. Master-Slave Server-id must not be the same
4. Create a sync user for the main library
5. After making sure the above settings, log in from the library to perform the change master to instruction
6. Master-Slave time synchronization

In a later article, you'll get a more detailed introduction to the master-slave replication feature using MySQL

Personal blog site www.gudaoyufu.com

MySQL (MARIADB) Multi-instance application and multi-instance master-slave replication