MySQL Multi-instance configuration

This configuration uses cmake compiled installation of MySQL for multi-instance configuration, using a different configuration file configuration, the general steps are divided into the following:

installing MySQL Software

Create a multi-instance data file directory

Create a multi-instance configuration file

Modify file permissions for multiple instances

Initializing MySQL multi-instance

MySQL multi-instance connection and password addition

MySQL Multi-instance startup and shutdown

Open Remote connection Access

Add to boot Boot

Operating system version

CentOS Release 6.5 (Final) x86_64

MySQL version

5.5.45

1. Installing the MySQL Software

Installing MySQL with reference to CMake

2. Create a multi-instance data file directory

[Email protected] ~]# pkill mysqld[[email protected] ~]# rm/etc/init.d/mysqld [[email protected] ~]# mkdir-p/data/{330 6,3307}/data[[email protected] ~]# tree/data/[[email protected] ~]# rm/etc/my.cnf

3. Create a multi-instance configuration file

[Email protected] ~]#/bin/cp/soft/mysql-5.5.45/support-files/my-small.cnf/data/3306/my.cnf[[email protected] ~]#/ Bin/cp/soft/mysql-5.5.45/support-files/my-small.cnf/data/3307/my.cnf

Edit Modify configuration file

Note the parameters are the port number, socket, the file location associated with each instance, and the Server-id

Vi/data/3306/my.cnf

[Client] #password        = your_passwordport             = 3306socket           = /data/3306/mysql.sock# The MySQL server[mysqld]port             = 3306socket           = /data/3306/mysql.sockskip-external-lockingkey_buffer_size = 16kmax_ Allowed_packet = 1mtable_open_cache = 4sort_buffer_size = 64kread_buffer_size  = 256kread_rnd_buffer_size = 256knet_buffer_length = 2kthread_stack =  128kdatadir = /data/3306/datalog-error = /data/3306/mysql_3306.errlog-slow-queries  = /data/3306/slow.logpid-file = /data/3306/mysql.pidlog-bin = /data/3306/ Mysql-binrelay-log = /data/3306/relay-binrelay-log-info-file = /data/3306/relay-log.info#skip-networkingserver-id        = 1[mysqldump]quickmax_allowed_packet = 16m[mysql]no-auto-rehash[ Myisamchk]key_buffer_size = 8msort_buffer_size = 8m[mysqlhotcopy]interactive-timeout

Vi/data/3307/my.cnf

[Client] #password        = your_passwordport             = 3307socket           = /data/3307/mysql.sock# The MySQL server[mysqld]port             = 3307socket           = /data/3307/mysql.sockskip-external-lockingkey_buffer_size = 16kmax_ Allowed_packet = 1mtable_open_cache = 4sort_buffer_size = 64kread_buffer_size  = 256kread_rnd_buffer_size = 256knet_buffer_length = 2kthread_stack =  128kdatadir = /data/3307/datalog-error = /data/3307/mysql_3307.errlog-slow-queries  = /data/3307/slow.logpid-file = /data/3307/mysql.pidlog-bin = /data/3307/ Mysql-binrelay-log = /data/3307/relay-binrelay-log-info-file = /data/3307/relay-log.info#skip-networkingserver-id        = 3[mysqldump]quickmax_allowed_packet = 16m[mysql]no-auto-rehash[ Myisamchk]key_buffer_size = 8msort_buffer_size = 8m[mysqlhotcopy]interactive-timeout

4. modify file permissions for multiple instances

[[email protected] ~]# chown-r mysql.mysql/data/[[email protected] ~]# find/data/-name mysql[[email protected] ~]# fin d/data/-name mysql-exec chmod 700 {} \; [Email protected] ~]# find/data-name mysql-exec ls-l {} \;

5. Initializing the database

[Email protected] ~]# Cd/app/mysql/scripts[[email protected] scripts]#./mysql_install_db--basedir=/app/mysql-- Datadir=/data/3306/data--user=mysql[[email protected] scripts]#./mysql_install_db--basedir=/app/mysql--datadir=/ Data/3307/data--user=mysql[[email protected] ~]$ tree/data/

6.mysql multi-instance connection and password addition

MySQL multi-instance connection requires the SID specified when sock,oracle multi-instance connection is specified

[Email protected] ~]# mysql-s/data/3306/mysql.sock [[email protected] ~]# mysql-s/data/3307/mysql.sock[[email Protect Ed] ~]# mysqladmin-u root-s/data/3307/mysql.sock password ' 123456 ' [[email protected] ~]# mysqladmin-u root-s/data/33 06/mysql.sock password ' 123456 '

7.MySQL Multi-instance startup, connection, and shutdown

start multiple instances, you can start with a separate script, or you can use Mysqld_safe to specify a configuration file to start, or you can use Mysqld_multi is started, but this is not a separate configuration file for each instance, this time using commands and individual scripts. In fact, the essence is mysql_safe and mysqladmin operation, just use the script convenient.

7.1 mysql_safe command starts and shuts down

[[Email protected] ~]# mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1  > /dev/null &[[email protected] ~]# mysqld_safe --defaults-file=/ data/3306/my.cnf 2>&1 > /dev/null &[[email protected] ~]#  netstat -lntup | grep 330tcp        0       0 0.0.0.0:3307                 0.0.0.0:*                    LISTEN      36549/mysqld         tcp        0       0 0.0.0.0:3306                 0.0.0.0:*                    LISTEN      35835/mysqld[[email protected] ~]#  Mysqladmin -uroot -p123456 -s /data/3307/mysql.sock shutdown[[email protected]  ~]# mysqladmin -uroot -p123456 -s /data/3306/mysql.sock shutdown[[email  protected] ~]# netstat -lntup | grep 330

7.2 Start and close using their own scripts

Vi/data/3306/mysql

Note that the full path to the Mysqld_safe, mysqladmin command

#!/bin/bash#### #mysql_port =3306mysql_username= "root" mysql_password= "123456" Function_start_mysql () {printf  "Starting mysql...\n"/bin/sh /app/mysql/bin/mysqld_safe --defaults-file=/data/${mysql_port}/ My.cnf 2>&1 > /dev/null &}function_stop_mysql () {printf  "Stoping  mysql...\n "/app/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -s / Data/${mysql_port}/mysql.sock shutdown}function_restart_mysql () {printf  "Restarting MySQL...\n" Function_stop_mysqlfunction_start_mysql}function_kill_mysql () {kill -9 $ (ps -ef | grep   ' Bin/mysqld_safe '  | grep ${mysql_port} | awk  ' {printf $2} ') kill  -9 $ (ps -ef | grep  ' libexec/mysqld '  | grep ${mysql_port} |  awk  ' {printf $2} ')}case $1 instart) function_start_mysql;; stop) Function_stop_mysql;; Kill) Function_kill_mysql;; REstart) Function_stop_mysqlfunction_start_mysql;; *) echo  "Usage: /data/${mysql_port}/mysqld {start|stop|restart|kill}";; Esac

vi/data/3307/mysql

#!/bin/bash#### #mysql_port =3307mysql_username= "root" mysql_password= "123456" Function_start_mysql () {printf  "Starting mysql...\n"/bin/sh /app/mysql/bin/mysqld_safe --defaults-file=/data/${mysql_port}/ My.cnf 2>&1 > /dev/null &}function_stop_mysql () {printf  "Stoping  mysql...\n "/app/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -s / Data/${mysql_port}/mysql.sock shutdown}function_restart_mysql () {printf  "Restarting MySQL...\n" Function_stop_mysqlfunction_start_mysql}function_kill_mysql () {kill -9 $ (ps -ef | grep   ' Bin/mysqld_safe '  | grep ${mysql_port} | awk  ' {printf $2} ') kill  -9 $ (ps -ef | grep  ' libexec/mysqld '  | grep ${mysql_port} |  awk  ' {printf $2} ')}case $1 instart) function_start_mysql;; stop) Function_stop_mysql;; Kill) Function_kill_mysql;; REstart) Function_stop_mysqlfunction_start_mysql;; *) echo  "Usage: /data/${mysql_port}/mysqld {start|stop|restart|kill}";; Esac

Add executable permissions

chmod +x/data/3306/mysqlchmod +x/data/3307/mysql

Using scripts to start, close MySQL multi-instance

[[Email protected] ~]# /data/3306/mysql startstarting mysql ... [[email protected] ~]# lsof -i:3306command  pid  user    fd   type device size/off node namemysqld  4617 mysql    13u  ipv4  14657      0t0  tcp  *:mysql  (LISTEN) [[email protected] ~]# [[email protected] ~]# /data/ 3307/mysql startstarting mysql ... [[email protected] ~]# lsof -i:3307command  pid  user    fd   type device size/off node namemysqld  4937 mysql    13u  ipv4  14933      0t0  tcp  *:opsession-prxy  (LISTEN) [[email protected] ~]# /data/3307/mysql stopstoping  mysqL ... [[Email protected] ~]# lsof -i:3307[[email protected] ~]# /data/3306/mysql  stopstoping mysql ... [[email protected] ~]# lsof -i:3306

8. Open remote connection access

[[email protected] ~]# mysql -uroot -p123456 -s /data/3306/mysql.sock  Welcome to the mysql monitor.  commands end with ; or \ g.your mysql connection id is 1server version: 5.5.45-log source  distributioncopyright  (c)  2000, 2015, Oracle and/or its affiliates.  All rights reserved. oracle is a registered trademark of oracle corporation and/or  Itsaffiliates. other names may be trademarks of their respectiveowners . type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql> GRANT ALL PRIVILEGES ON *.* TO  ' root ' @ ' 192.168.10.% '  IDENTIFIED BY  ' 123456 '  WITH GRANT OPTION ; query ok, 0 rows affected  (0.00 sec) mysql> flush privileges ; query ok, 0 rows affected  (0.00 sec) mysql> exitbye[[email protected ] ~]# mysql -uroot -p123456 -s /data/3307/mysql.sock welcome to  The mysql monitor.  commands end with ; or \g.your mysql  connection id is 2Server version: 5.5.45-log Source  distributioncopyright  (c)  2000, 2015, Oracle and/or its affiliates.  All rights reserved. oracle is a registered trademark of oracle corporation and/or  Itsaffiliates. other names may be trademarks of their respectiveowners . type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input statement.mysql> grant all privileges on *.* to  ' Root ' @ ' 192.168.10.% '  IDENTIFIED BY  ' 123456 '  WITH GRANT OPTION ; query ok, 0 rows affected  (0.00 sec) mysql> flush privileges ; query ok, 0 rows affected  (0.00 sec) mysql>

9. Add to boot boot

Vi/etc/rc.d/rc.local/data/3306/mysql Start/data/3307/mysql Start

This article is from "The girl said" blog, please be sure to keep this source http://sugarlovecxq.blog.51cto.com/6707742/1695946

MySQL Multi-instance configuration