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_safe

mysqladmin= /usr/local/mysql/bin/mysqladmin

user=mt_user

password=abcd123

[mysqld3307]#tee=/data/mysql/mysql_3307/data/query.log

datadir = /data/mysql/mysql_3307/data

socket= /tmp/mysql3307.sock

tmpdir=/data/mysql/mysql_3307/tmp

server-id = 123307log-bin = /data/mysql/mysql_3307/logs/mysql-bin

innodb_data_home_dir= /data/mysql/mysql_3307/data

innodb_log_group_home_dir= /data/mysql/mysql_3307/logs

[mysqld3308]

port= 3308

#tee=/data/mysql/mysql_3308/data/query.log

datadir = /data/mysql/mysql_3308/data

socket= /tmp/mysql3308.sock

tmpdir=/data/mysql/mysql_3308/tmp

server-id = 123308log-bin = /data/mysql/mysql_3308/logs/mysql-bin

innodb_data_home_dir= /data/mysql/mysql_3308/data

innodb_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 mysql

tcp 0 0 :::3308 :::* LISTEN 9247/mysqld

tcp 0 0 :::3376 :::* LISTEN 9231/mysqld

unix2 [ ACC ] STREAM LISTENING 21545 9231/mysqld /tmp/mysql3307.sock

unix2 [ 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.00sec)

(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]#