首页 > 代码库 > MySQL 多实例管理
MySQL 多实例管理
根据配置文件创建目录
[root@localhost 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‘[root@localhost 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的修改)
[root@localhost redis]# cp /etc/my.cnf /data/mysql/mysql_3307/[root@localhost redis]# cp /etc/my.cnf /data/mysql/mysql_3308/ [root@localhost redis]# sed -i ‘s/3376/3307/g‘ /data/mysql/mysql_3307/my.cnf [root@localhost 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
多实例关闭
[root@localhost data]# mysqladmin --defaults-file=/data/mysql/mysql_3307/my.cnf shutdown[1]- Done mysqld --defaults-file=/data/mysql/mysql_3307/my.cnf[root@localhost 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
[root@localhost mysql_3307]# pkill mysql[root@localhost mysql_3307]# mysqld_multi start 3307,3308[root@localhost 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)
[root@localhost 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.[root@localhost mysql_3307]# netstat -nalp | grep 3307[root@localhost 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.[root@localhost mysql_3307]#
MySQL 多实例管理