MySQL database builds a series of multiple instances

MySQL database builds a series of multiple instances

The so-called multi-instance is to build and run multiple MySQL instances on a server. Each instance uses different service ports and listens through different sockets. Physically, each instance has an independent parameter configuration file and database.

In general, it is not recommended to run multiple MySQL instances on a server, especially in the production environment, because this will cause a high proportion of resources. However, in some high-availability environments, there is such a need. For example, in a Failover Cluster Environment built using Heartbeat, if the master and slave servers only run one MySQL instance, the standby server is usually idle, causing waste of resources. If one MySQL instance runs separately, the other party is required to take over the task when one party fails, that is, two MySQL instances are run simultaneously. Based on this requirement, the MySQL server should be reasonably configured so that it can run multiple instances at the same time.

The key to how to build and run multiple MySQL instances on a server is how to assign independent parameter files to each instance. Below are several common multi-instance solutions, learn and familiarize yourself with each other.

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

System Environment

OS: CentOS 5.8 (x86_64) kernel: 2.6.18-308. el5 DB: MySQL 5.5.17

I. Use the source code package to install MySQL for multiple instances

The source package is the most flexible installation package. You can customize some path parameters and install them in any path. Therefore, you can build multiple MySQL instances on one server. In this example, install two MySQL instances as follows:

1. Preparations

Starting from MySQL 5.5, use the cmake tool to compile the source code package. Therefore, you must install it first. For more information, see other documents. In addition, the ncurses-devel and bison dependency packages must be installed before compilation. Otherwise, the compilation will fail.

-Install the dependency package

# Rpm-ivh ncurses-devel-5.5-24.20060715.x86_64.rpm

# Rpm-ivh bison-2.3-2.1.x86_64.rpm

-Create a mysql system group and user

# Groupadd-g 497 mysql

# Useradd-u 499-g mysql

-- Unzip the installation package

# Tar-zxvf mysql-5.5.17.tar.gz

# Cd mysql-5.5.17

2. Compile and install

L MySQL instance 1:

(Port: 3306, installation directory:/usr/local/mysqla, data file directory:/data/lib/mysqla)

-Custom Compilation

# Cmake-DCMAKE_INSTALL_PREFIX =/usr/local/mysqla-DMYSQL_TCP_PORT = 3306-DMYSQL_DATADIR =/data/lib/mysqla-DMYSQL_UNIX_ADDR =/data/lib/mysqla/mysql. sock-DSYSCONFDIR =/usr/local/mysqla-DWITH_MYISAM_STORAGE_ENGINE = 1-rows = bytes = 1-rows = 1-rows = 1-DWITH_READLINE = 1-DWITH_SSL = yes-DDEFAULT_CHARSET = utf8-DDEFAULT_COLLATION = utf8_general_ci-DEXTRA_CHARSETS = all

# Make

# Make install

-Create a system database and a system table

# Cd/usr/local/mysqla/

# Scripts/mysql_install_db -- user = mysql -- basedir =/usr/local/mysqla -- datadir =/data/lib/mysqla

L MySQL instance 2:

(Port: 3307, installation directory:/usr/local/mysqlb, data file directory:/data/lib/mysqlb)

-- Delete old cache and object files

Before re-compilation, clear the original Cache Information and object files as follows:

# Cd mysql-5.5.17

# Make clean

# Rm-rf CMakeCache.txt

-Custom Compilation

# Cmake-DCMAKE_INSTALL_PREFIX =/usr/local/mysqlb-DMYSQL_TCP_PORT = 3307-DMYSQL_DATADIR =/data/lib/mysqlb-DMYSQL_UNIX_ADDR =/data/lib/mysqlb/mysql. sock-DSYSCONFDIR =/usr/local/mysqlb-DWITH_MYISAM_STORAGE_ENGINE = 1-rows = bytes = 1-rows = 1-rows = 1-DWITH_READLINE = 1-DWITH_SSL = yes-DDEFAULT_CHARSET = utf8-DDEFAULT_COLLATION = utf8_general_ci-DEXTRA_CHARSETS = all

# Make

# Make install

-Create a system database and a system table

# Cd/usr/local/mysqlb/

# Scripts/mysql_install_db -- user = mysql -- datadir =/data/lib/mysqlb

For more details, please continue to read the highlights on the next page:

1 2 3 4 Next Page