MySQL 多執行個體管理

標籤：tmp   default   建立   creat   interface   admin   direct   tcp   lis   

根據設定檔建立目錄

[[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‘

修改設定檔(注意sock datadir  server_id  pid port的修改)

[[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]# sed -i ‘s/3376/3307/g‘ /data/mysql/mysql_3307/my.cnf [[email protected] redis]# sed -i ‘s/3376/3308/g‘ /data/mysql/mysql_3308/my.cnf 

建立多執行個體

# ./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/

多執行個體啟動方式

多執行個體用一個設定檔可以用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  &

連續啟動

#mysqld_mulit start 1-3

#mysqld_mulit start 1,3,4,6

# mysql -S /tmp/mysql3307.sock

# mysql -S /tmp/mysql3308.sock

多執行個體關閉

 

[[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 

多執行個體共用一個設定檔可以用下面方法關閉

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

多執行個體使用一個設定檔

[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 mysqltcp        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

 建立關閉資料庫使用者

(product)root@localhost [(none)]> create user ‘mt_user‘@‘localhost‘ identified by ‘abc123‘;Query OK, 0 rows affected (0.00 sec)(product)root@localhost [(none)]> GRANT SHUTDOWN ON *.* TO ‘mt_user‘@‘localhost‘; Query OK, 0 rows affected (0.00 sec)

[[email protected] mysql_3307]# /usr/local/mysql/bin/mysqladmin -h localhost -S /tmp/mysql3307.sock -umt_user -pabc123 shutdown Warning: Using a password on the command line interface can be insecure.[[email protected] mysql_3307]# netstat -nalp | grep 3307[[email protected] mysql_3307]# /usr/local/mysql/bin/mysqladmin -h localhost -S /tmp/mysql3308.sock -umt_user -pabc123 shutdown Warning: Using a password on the command line interface can be insecure.[[email protected] mysql_3307]# 

 

 

MySQL 多執行個體管理