MySQL multi-instance configuration details

MySQL multi-instance configuration details

For centralized O & M of MySQL databases, you can deploy multiple MySQL instances on one MySQL database server. This function is implemented through mysqld_multi. Mysqld_multi is used to manage multiple mysqld service processes. These mysqld service process programs can use different sockets or listen to different ports, and distribute data files to different disks to distribute IO. Mysqld_multi provides simple commands for starting, disabling, and reporting the status of the managed server. This reduces the maintenance cost of the production environment, facilitates subsequent migration and cleaning, and improves the overall resource utilization of the server by binding multiple instances. There are two ways to configure multiple instances. One is to configure all instances in my. cnf, and the other is to use a configuration file for each instance. This article describes the first method.

For more information about how to configure multiple instances, see MySQL multi-instance configuration (2)
For installation of a single MySQL instance, see install the full version of MySQL source code in Linux.
 

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

1. Differences between multiple databases and Instances
MSSQL
An instance in MSSQL indicates that there is only one default instance on an SQL server. The default Instance name is ServerName (or IP ).
If you install SQL server on the same machine, you can name the instance as ServerName/InstanceName.
That is, multiple different instances can exist on an SQL server. A single instance can have multiple different databases.
You can use ServerName/InstanceName: PortNo to access databases under different instances. The default instance is ServerName: PortNo.
Configure IP addresses, related access protocols, and ports for different instances.
To enable the instance's accessibility, you must start the corresponding services of the instance. Note that the Instance name and service name of the instance are not the same.
The service name of the default instance is MSSQLSERVER, and the service name of the instance is MSSQL $ INSTANCE_NAME.

Oracle
An Oracle Server consists of an Oracle instance and an Oracle database. That is, Oracle Server = Oracle Instance + Oracle Database
Oracle instances are mainly composed of SGA, PGA, and a bunch of background processes, which are called instances.
A collection of physical files includes control files, data files, online log files, parameter files, and password files.
One instance can only access one database, and one database can be accessed by multiple instances.

MySQL
The concept of a MySQL instance is similar to that of MSSQL. A MySQL instance can have or access N databases.
Different instances can be distinguished by different port numbers, and the data of each instance can use different disk directories.
MySQL multi-instance management through mysqld_multi tool.

 

2. Existing Environment
Mysql installation path:/u01/app/mysql
Mysql DATA path:/u01/app/mysqldata/data3306
Mysql port: 3306
Mysql version: 5.6.12 Source distribution
OS environment: SUSE Linux Enterprise Server 11 SP3 (x86_64)
 

3. initialize the instance
# Create a data directory for the new instance and grant permissions
Suse11 :~ # Mkdir-p/u01/app/mysqldata/data3406
Suse11 :~ # Mkdir-p/u01/app/mysqldata/data3506
Suse11 :~ # Chown mysql: mysql-R/u01/app/mysqldata/data3406
Suse11 :~ # Chown mysql: mysql-R/u01/app/mysqldata/data3506

# Initializing an instance
Suse11 :~ # Cd/u01/app/mysql
Suse11:/u01/app/mysql #./scripts/mysql_install_db -- user = mysql -- ldata =/u01/app/mysqldata/data3406/
Suse11:/u01/app/mysql #./scripts/mysql_install_db -- user = mysql -- ldata =/u01/app/mysqldata/data3506/
# Author: Leshami
# Blog:

1 2 Next Page