MySQL Multi-instance management

To create a directory from a configuration file

[[email protected] redis]#Mkdir-pv/data/mysql/mysql_3307/{data,logs,tmp}mkdir:created directory '/data/mysql/mysql_3307'mkdir:created directory '/data/mysql/mysql_3307/data'mkdir:created directory '/data/mysql/mysql_3307/logs'mkdir:created directory '/data/mysql/mysql_3307/tmp'[[email protected] redis]#Mkdir-pv/data/mysql/mysql_3308/{data,logs,tmp}mkdir:created directory '/data/mysql/mysql_3308'mkdir:created directory '/data/mysql/mysql_3308/data'mkdir:created directory '/data/mysql/mysql_3308/logs'mkdir:created directory '/data/mysql/mysql_3308/tmp'

Modify configuration file (Note sock datadir server_id PID Port modification)

[[email protected] redis] # cp/etc/my.cnf  /data/mysql/mysql_3307/[[email protected] Redis]#  cp/etc/my.cnf  /data /mysql/mysql_3308/  [[email protected] Redis]#[[email protected] Redis]#

Create multi-instance

#./scripts/mysql_install_db--user=mysql--defaults-file=/data/mysql/mysql_3307/my.cnf--datadir=/data/mysql/ mysql_3307/data/

#./scripts/mysql_install_db--user=mysql--defaults-file=/data/mysql/mysql_3308/my.cnf--datadir=/data/mysql/ mysql_3308/data/

Multi-instance Boot mode

Multiple instances with a configuration file can be started with Mysqld_multi

# Mysqld_multi Start 3307

# Mysqld_multi Start 3308

Mysqld--DEFAULTS-FILE=/DATA/MYSQL/MYSQL_3307/MY.CNF &

Mysqld--DEFAULTS-FILE=/DATA/MYSQL/MYSQL_3308/MY.CNF &

Mysqld_safe--DEFAULTS-FILE=/DATA/MYSQL/MYSQL_3307/MY.CNF &

Mysqld_safe--DEFAULTS-FILE=/DATA/MYSQL/MYSQL_3308/MY.CNF &

Continuous Start

#mysqld_mulit Start 1-3

#mysqld_mulit Start 1,3,4,6

# mysql-s/tmp/mysql3307.sock

# mysql-s/tmp/mysql3308.sock

Multi-instance shutdown

[[email protected] data] # mysqladmin  --defaults-file=/data/mysql/mysql_3307/my.cnf  shutdown[1]-  done                    mysqld-- defaults-file=/data/mysql/mysql_3307/My.cnf[[email protected] data]#  Netstat-nalp | grep 3307 

Multiple instances sharing a profile can be turned off in the following way

#mysqladmin-S/tmp/mysql3308.sock shutdown
#mysqladmin-S/tmp/mysql3307.sock shutdown

Multi-instance using one configuration file

[Mysqld_multi]mysqld=/usr/local/mysql/bin/Mysqld_safemysqladmin=/usr/local/mysql/bin/Mysqladminuser=Mt_userpassword=abcd123 [mysqld3307]#Tee=/data/mysql/mysql_3307/data/query.logDataDir =/data/mysql/mysql_3307/Datasocket=/tmp/Mysql3307.socktmpdir=/data/mysql/mysql_3307/Tmpserver-id = 123307Log-bin =/data/mysql/mysql_3307/logs/mysql-Bininnodb_data_home_dir=/data/mysql/mysql_3307/Datainnodb_log_group_home_dir=/data/mysql/mysql_3307/logs [Mysqld3308]port= 3308#Tee=/data/mysql/mysql_3308/data/query.logDataDir =/data/mysql/mysql_3308/Datasocket=/tmp/Mysql3308.socktmpdir=/data/mysql/mysql_3308/Tmpserver-id = 123308Log-bin =/data/mysql/mysql_3308/logs/mysql-Bininnodb_data_home_dir=/data/mysql/mysql_3308/Datainnodb_log_group_home_dir=/data/mysql/mysql_3308/logs

[[email protected] mysql_3307] # pkill MySQL [[email protected] mysql_3307] # mysqld_multi  start 3307,3308[[email protected] mysql_3307]#  netstat-nalp | grep mysql TCP        0      0::: 3308                     :::*                        LISTEN      9247/mysqld         tcp        0      0::: 3376                     ::: *                        LISTEN      9231/mysqld         Unix  2      [ACC]     STREAM     LISTENING     21545  9231/mysqld         /tmp/mysql3307.sockunix  2      [ACC]     STREAM     LISTENING     21554  9247/mysqld         /tmp/mysql3308.sock

Create a shutdown database user

(product) Root@localhost [(None)]> Create User 'Mt_user'@'localhost'Identified by 'abc123'; Query OK,0Rows Affected (0.00sec) (product) Root@localhost [(None)]> GRANT SHUTDOWN  on *.*  to 'Mt_user'@'localhost'; Query OK,0Rows Affected (0.00Sec

[[email protected] mysql_3307] #  warning:using a password on the command line interface can be insecure.[ [email protected] mysql_3307]#  Netstat-nalp | grep 3307[[email protected] mysql_3307]#  warning:using a password on the command line interface can be insecure.[ [email protected] mysql_3307]#

MySQL Multi-instance management