MySQL database multi-instance installation configuration Mysql_multi Start-stop combat

One, multiple instances of the application scenario:

1, the choice of capital-intensive companies

When the company's business visits are not too large, but also reluctant to spend money, but at the same time hope that the different services of the database service independent, but also require master-slave synchronization and other technologies to provide backup or read-write separation services, using multi-instance is the best.

2, concurrent access is not a particularly large business

When the company's business visits are not too large, the server resources are basically idle more, this is very suitable for multi-instance applications. If the SQL statement is optimized, multiple instances are a good technique to use. Even if the concurrency is very large, so long as the reasonable allocation of system resources, there will not be too much problem.

Second, the principle of MySQL multi-instance

MySQL multi-instance, simply put, is to open a number of different MySQL service ports (such as 3306,3307) on a single server, running multiple MySQL service processes. These service processes use different sockets to listen on different service ports to provide their own services.

These MySQL instances share a common set of MySQL installers that use different my.cnf configuration files, startup programs, and data files. When providing services, MySQL multi-instance logically appears to be independent, each instance according to the configuration file settings, to obtain the relevant hardware resources of the server.

Three, multi-instance deployment steps

In this case, the compilation of the database installation will not be discussed, if not clear, please refer to other documents.

Port used by experiment: 33064,33065

1. Create Data Catalog

Create a data file to store the directory and modify the folder's owning user group

Mkdir-p/home/mysqldata/33064

Mkdir-p/home/mysqldata/33065

Chown Mysql.mysql 33064/33065/

2. Initialize the database:

/usr/bin/mysql_install_db\

--datadir=/home/mysqldata/33064/\

--user=mysql

/usr/bin/mysql_install_db\

--datadir=/home/mysqldata/33065/\

--user=mysql

3. configuring the /etc/my.cnf configuration file

Each instance has a Msyqld module that belongs to the instance itself, which is the initialization parameter corresponding to the instance, for example [mysqld33064],[mysqld33065]

[Email protected]/]# CAT/ETC/MY.CNF

[Mysqld_multi]

Mysqld=/usr/bin/mysqld_safe

Mysqladmin=/usr/bin/mysqladmin

User=mysql

Password=mysql

[mysqld33064]

datadir=/home/mysqldata/33064

port=33064

Socket=/home/mysqldata/33064/mysql33064.sock

User=mysql

Symbolic-links=0

Default-storage-engine=innodb

Log-bin = Mysql-bin

Binlog_format = Mixed

Expire_logs_days = 7

# # UTF8

#default-character-set = UTF8

#character_set_connection = UTF8

#character_set_results = UTF8

Character-set-server = UTF8

Collation-server = Utf8_general_ci

Init_connect = ' SET collation_connection =utf8_general_ci '

Init_connect = ' SET NAMES UTF8 '

Server-id = 1

[mysqld33065]

datadir=/home/mysqldata/33065

port=33065

Socket=/home/mysqldata/33065/mysql33065.sock

User=mysql

Symbolic-links=0

Default-storage-engine=innodb

Log-bin = Mysql-bin

Binlog_format = Mixed

Expire_logs_days = 7

# # UTF8

#default-character-set = UTF8

#character_set_connection = UTF8

#character_set_results = UTF8

Character-set-server = UTF8

Collation-server = Utf8_general_ci

Init_connect = ' SET collation_connection =utf8_general_ci '

Init_connect = ' SET NAMES UTF8 '

Server-id = 1

[Mysqld_safe]

#log =/var/log/mysqldquery.log

# # error Log

Log-error=/var/log/mysqld.log

#pid-file=/var/run/mysqld/mysqld.pid

4. Multi-instance start-up and stop

Start with the Mysqld_multi command instead of the mysqld command, and the command launches the corresponding amount DB instance by passing different port parameters.

/usr/bin/mysqld_multi--defaults-file=/etc/my.cnf start 33064

/usr/bin/mysqld_multi--defaults-file=/etc/my.cnf Start 33065

/usr/bin/mysqld_multi--defaults-file=/etc/my.cnf Stop 33064

/usr/bin/mysqld_multi--defaults-file=/etc/my.cnf Stop 33065

Review the database process, if there are two database processes that indicate that the database is installed properly, or fail, for specific reasons, you need to review the log log for processing.

[[email protected] mysql]# ps-ef|grep MySQL

Root      3990     1  0 11:18 pts/0    00:00:00/bin/sh/usr/bin/mysqld_safe-- datadir=/home/mysqldata/33065--port=33065--socket=/home/mysqldata/33065/mysql33065.sock--user=mysql-- Symbolic-links=0--default-storage-engine=innodb--log-bin=mysql-bin--binlog_format=mixed--expire_logs_days=7-- Character-set-server=utf8--collation-server=utf8_general_ci--init_connect=set collation_connection = utf8_general _ci  --init_connect=set NAMES UTF8--server-id=1

MySQL     4132  3990  0 11:18 pts/0    00:00:01/usr/libexec/mysqld--basedir=/usr--data dir=/home/mysqldata/33065--user=mysql--symbolic-links=0--default-storage-engine=innodb--log-bin=mysql-bin-- binlog_format=mixed--expire_logs_days=7--character-set-server=utf8--collation-server=utf8_general_ci--init_ Connect=set collation_connection = utf8_general_ci  --init_connect=set NAMES UTF8--server-id=1--log-error=/var/ Log/mysqld.log--pid-file=/home/mysqldata/33065/model.pid--socket=/home/mysqldata/33065/mysql33065.sock--port= 33065

Root      4155     1  0 11:18 pts/0    00:00:00/bin/sh/usr/bin/mysqld_safe-- datadir=/home/mysqldata/33064--port=33064--socket=/home/mysqldata/33064/mysql33064.sock--user=mysql-- Symbolic-links=0--default-storage-engine=innodb--log-bin=mysql-bin--binlog_format=mixed--expire_logs_days=7-- Character-set-server=utf8--collation-server=utf8_general_ci--init_connect=set collation_connection = utf8_general _ci  --init_connect=set NAMES UTF8--server-id=1

MySQL 4297 4155 0 11:18 pts/0 00:00:01/usr/libexec/mysqld--basedir=/usr--datadir=/home/mysqldata/33064--user= MySQL--symbolic-links=0--default-storage-engine=innodb--log-bin=mysql-bin--binlog_format=mixed--expire_logs_ days=7--character-set-server=utf8--collation-server=utf8_general_ci--init_connect=set collation_connection = UTF8 _general_ci--init_connect=set NAMES UTF8--server-id=1--log-error=/var/log/mysqld.log--pid-file=/home/mysqldata/ 33064/model.pid--socket=/home/mysqldata/33064/mysql33064.sock--port=33064

5. Multi-Instance Connection login

Mysql-s/home/mysqldata/33064/mysql33064.sock

Mysql-s/home/mysqldata/33065/mysql33065.sock

6. Assigning Permissions

The newly installed database root user does not have the localhost login permission, so you need to assign permissions to log in with the user name and password.

Mysql-s/home/mysqldata/33064/mysql33064.sock

Mysql>grant Allon *. * to ' root ' @ '% ' identified by ' root ';

Mysql>grant Allon *. * to ' root ' @ ' localhost ' identified by ' root ';

Mysql> flushprivileges;

7. Login with user name and password

Mysql-uroot-proot-s/home/mysqldata/33064/mysql33064.sock

8, multi-instance Change password

To modify the root password for instance 33064, use the Mysqladmin command. As follows:

mysqladmin-uroot-p password root-s /home/mysqldata/33064/mysql33064.sock

This is where our MySQL multi-instance deployment is completed.

This article is from the "Silent Dialogue" blog, please be sure to keep this source http://chbinmile.blog.51cto.com/6085145/1872975

MySQL database multi-instance installation configuration Mysql_multi Start-stop combat