MySQL5.5 Multi-instance compilation installation--mysqld_multi

One, MySQL multi-instance introduction
MySQL multi-instance, simply, on a single server to open a number of different service ports (such as: 3306, 3307), while running multiple MySQL service processes, These service processes are serviced by different sockets listening from different ports, and the
Multi-instance not only saves the physical host cost, but also improves the CPU and disk I/O efficiency of a single physical host, and can also be used to deploy the database ha scheme between multiple instances.
as the number of instances increases, we face a unified management problem, so we need to use the MySQL Mysqld_multi management program to manage ...
Second, MySQL START process
650) this.width=650; "Src=" http://s3.51cto.com/wyfs02/M02/8B/60/ Wkiol1hlzdixcxwmaabafhn0v18168.png "title=" MySQL boot process. png "alt=" Wkiol1hlzdixcxwmaabafhn0v18168.png "/>mysqld_ Multi #多实例管理程序
mysqld          #MySQL最主要的启动方式, there are many parameters Now, using multiple instances requires a new mysql_safe to start the MySQL
mysql_safe    #实则还是调用mysqld, And will read the MY.CNF configuration parameters in Mysqld to start the Mysql,mysql_safe itself also has many parameters, but these parameters will take precedence over MY.CNF
my.cnf           #mysql的配置文件
my.sock        #mysql创建的sock文件, open, stop, Login and manage MySQL all through this interface file
Three, next based on the mysql5.5.52 version, installation method see MySQL5.5.52 compiled installation, using Mysqld_multi configuration A multi-instance
1, stop single instance MySQL database

[[email protected] ~]#/etc/init.d/mysqld stopshutting down MySQL. success!

2. No boot from boot

[[email protected] ~]# chkconfig mysqld off[[email protected] ~]# chkconfig--list mysqldmysqld 0: Off 1: off 2: Off 3: Close 4: Close 5: Close 6: Off

3. Create a multi-instance root directory/data/Directory

[Email protected] ~]# mkdir-p/data/{3306,3307}/data

4. Copy Mysqld_multi Program Files

[Email protected] ~]# Cp/application/mysql/support-files/mysqld_multi.server/etc/init.d/mysqld_multi.server

1) Modify the Mysqld_multi.server path configuration

[Email protected] ~]# sed-i ' s#basedir=/usr/local/mysql#basedir=/application/mysql#g '/etc/init.d/mysqld_ Multi.server[[email protected] ~]# sed-i ' s#bindir=/usr/local/mysql/bin#bindir=/application/mysql/bin#g '/etc/ Init.d/mysqld_multi.server

2) Add the/ETC/MYSQLD_MULTI.CNF configuration file used by Mysqld_multi
#这个模板文件可以用命令mysqld_multi--example Export to

[[email protected] ~]# vim /etc/mysqld_multi.cnf[mysqld_multi]mysqld      = /application/mysql/bin/mysqld_safemysqladmin = /application/mysql/bin/mysqladmin# user       = multi_admin#password   = my_password[ mysqld1]socket     = /data/3306/mysql.sockport        = 3306pid-file   = /data/3306/mysql.piddatadir    =  /data/3306/data#language  = /application/mysql/share/mysql/englishuser        = mysql[mysqld2]socket     = /data/3307/ mysql.sockport       = 3307pid-file   = /data/3307 /mysql.piddatadir    = /data/3307/data#language  = /application/mysql/ Share/mysql/englishuser &nbSp;     = mysql 

5. Configure MySQL Multi-instance file permissions
Use the following command to authorize the MySQL user and user group to manage the root directory of the entire multi-instance/data

[Email protected] ~]# chown-r mysql.mysql/data

6. Initializing MySQL multi-instance database file
(1) Initializing MySQL database
cd/application/mysql/scripts/<== Note that unlike the MySQL5.1 path, MySQL5.1 is not in the MySQL bin path.
3306 Example
/application/mysql/scripts/mysql_install_db \
--basedir=/application/mysql \
--datadir=/data/3306/data \
--user=mysql
3307 Example
/application/mysql/scripts/mysql_install_db \
--basedir=/application/mysql \
--datadir=/data/3307/data \
--user=mysql
Tip:--basedir=/application/mysql is the installation path for MySQL,--datadir for different instance data directories
Operation Process:

[[email protected] ~]# cd /application/mysql/scripts/ 3306 instances [[email protected]  scripts]# /application/mysql/scripts/mysql_install_db > --basedir=/application/mysql  > --datadir=/data/3306/data > --user=mysqlWARNING: The host  ' DB01 '  could not be looked up with resolveip. this probably means that your libc libraries are not 100 %  compatiblewith this binary MySQL version. The MySQL daemon,  mysqld, should worknormally with the exception that host name  Resolving will not work. this means that you should use ip addresses instead of  hostnameswhen specifying mysql privileges ! Installing mysql system tables ... 161117 14:14:14 [note] /application/mysql/bin/mysqld  (mysqld 5.5.52)  starting as process  46676&NBSP, ..... Okfilling help tables ... 161117 14:14:15 [note] /application/mysql/bin/mysqld  (mysqld 5.5.52)  starting  AS PROCESS 46683&NBSP, ..... OK if there are two ok, it means the initialization is successful 3307 instance [[Email protected] scripts]# /application/mysql/scripts/mysql_install_ Db > --basedir=/application/mysql > --datadir=/data/3307/data > --user= Mysqlinstalling mysql system tables ... 161117 14:18:20 [note] /application/mysql/bin/mysqld  (mysqld 5.5.52)  starting  AS PROCESS 46733&NBSP, ..... Okfilling help tables ... 161117 14:18:21 [note] /application/mysql/bin/mysqld  (mysqld 5.5.52)  starting  AS PROCESS 46740&NBSP, ..... OK if there are two ok, it means the initialization is successful.

7. Start multiple instances:
1) View database status

Mysqld_multi--defaults-extra-file=/etc/mysqld_multi.cnf report[[email protected] ~]# Mysqld_multi-- DEFAULTS-EXTRA-FILE=/ETC/MYSQLD_MULTI.CNF reportreporting MySQL serversmysql server from Group:mysqld1 are not Runningmysql server from Group:mysqld2 are not running

2) Start the database

[[email protected] ~]# mysqld_multi--defaults-extra-file=/etc/mysqld_multi.cnf start 1,2[[email protected] ~]# mysqld_ Multi--DEFAULTS-EXTRA-FILE=/ETC/MYSQLD_MULTI.CNF report Reporting MySQL Serversmysql server from Group:mysqld1 are Runn Ingmysql server from Group:mysqld2 is running

3) View Port

[Email protected] ~]# ss-nlutp|grep 330tcp LISTEN 0 *:3306 *:* Users: (("mysqld", 47045,10)) TCP LISTEN 0 *:3307 *:* users: ("MySQL D ", 47041,10))

8. Stop the database

[[email protected] ~]# mysqld_multi--defaults-extra-file=/etc/mysqld_multi.cnf stop 1,2[[email protected] ~]# mysqld_ Multi--defaults-extra-file=/etc/mysqld_multi.cnf reportreporting MySQL serversmysql server from Group:mysqld1 are not RU Nningmysql server from Group:mysqld2 are not running

9. Login Database
1) Start the database

[Email protected] ~]# Mysqld_multi--defaults-extra-file=/etc/mysqld_multi.cnf start

2) Log in to the database
Method One: Specify the port and host IP for remote connection

mysql-uroot-h127.0.0.1-p3306

Method Two: Specify the socket login, suitable for the local connection

Mysql-s/data/3307/mysql.sock

Operation Demo
Method One: Specify the port and host IP for remote connection

[Email protected] ~]# mysql-uroot-h127.0.0.1-p3306welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 1Server version:5.5.52 Source distributioncopyright (c) $, Oracle and/or its a Ffiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql>

Method Two: Specify the socket login, suitable for the local connection

[Email protected] ~]# mysql-s/data/3307/mysql.sockwelcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 1Server version:5.5.52 Source distributioncopyright (c) $, Oracle and/or its a Ffiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql>

Here MySQL multi-instance is configured to complete O (∩_∩) o~~!!!

This article is from the "Hyun-dimensional" blog, please be sure to keep this source http://xuanwei.blog.51cto.com/11489734/1881521

MySQL5.5 Multi-instance compilation installation--mysqld_multi