0. summary
1. 多實(shí)例介紹
2. 參數(shù)要求
3. 安裝和管理多實(shí)例
. 3.1 初始化數(shù)據(jù)庫(kù)目錄
. 3.2 啟動(dòng)和停止多實(shí)例
. 3.3 多實(shí)例的連接
. 3.4 拷貝啟動(dòng)腳本，方便自啟
. 3.5 mysqld_multi命令的實(shí)質(zhì)
. 3.6 mysqld_mutli管理多版本的mysql
1. 多實(shí)例介紹
- 一臺(tái)服務(wù)器上安裝多個(gè)MySQL數(shù)據(jù)庫(kù)實(shí)例
- 可以充分利用服務(wù)器的硬件資源
- 通過(guò)mysqld_multi進(jìn)行管理
2. 參數(shù)要求
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe ---- 也可以通過(guò)mysqld來(lái)啟動(dòng)苔可，是否需要看應(yīng)用場(chǎng)景
mysqladmin=/usr/local/mysql/bin/mysqladmin ---- 進(jìn)行關(guān)閉的
log=/usr/local/mysql/mysqld_multi.log ---- 日志位置，要在mysql組下面
[mysqld1]
#slave-parallel-type=LOGICAL-CLOCK
#slave-parallel-workers=4
server-id=110
innodb_page_size=16384
innodb_buffer_pool_size=500M
basedir=/usr/local/mysql/
datadir=/mdata/data1 ---- 這是需要配置的
socket=/tmp/mysql.sock1 ---- 這是需要配置的
port=3307 ---- 這是需要配置的
[mysqld2]
#slave-parallel-type=LOGICAL-CLOCK
#slave-parallel-workers=4
server-id=120
innodb_page_size=16384
innodb_buffer_pool_size=500M
basedir=/usr/local/mysql/
datadir=/mdata/data2 ---- 這是需要配置的
socket=/tmp/mysql.sock2 ---- 這是需要配置的
port=3308 ---- 這是需要配置的
[mysqld3]
plugin_dir=/usr/local/mysql56/lib/plugin
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
server-id=130
innodb_buffer_pool_size=500M
basedir=/usr/local/mysql56/
datadir=/mdata/data3
socket=/tmp/mysql.sock3
port=3309
3. 安裝和管理多實(shí)例
3.1 初始化數(shù)據(jù)庫(kù)目錄
[root@lab11g mysql]# bin/mysqld --initialize --user=mysql --datadir=/mdata/data1
[root@lab11g mysql]# bin/mysqld --initialize --user=mysql --datadir=/mdata/data2
初始化完成后檢查error.log, 并記下臨時(shí)密碼
3.2 啟動(dòng)和停止多實(shí)例
#### 啟動(dòng)單個(gè)實(shí)例 ####
[root@lab11g mysql]# mysqld_multi start 1
#### 啟動(dòng)所有 ####
[root@lab11g mysql]# mysqld_multi start
#### 查看實(shí)例狀態(tài) ####
[root@lab11g mysql]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
#### 停止到單個(gè)實(shí)例 ####
[root@lab11g mysql]# mysqld_multi stop 1
#### 停止所有 ####
[root@lab11g mysql]# mysqld_multi stop
3.3 多實(shí)例的連接
[root@lab11g mysql]# mysql -u root -S /tmp/mysql.sock1 -P 3307 -p
[root@lab11g mysql]# mysql -u root -S /tmp/mysql.sock2 -P 3308 -p
3.4 拷貝啟動(dòng)腳本求晶，方便自啟
cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multid
chkconfig --add mysqld_multi.server
chkconfig mysqld_multid.server on
3.5 mysqld_multi命令的實(shí)質(zhì)
# 1.COMMON USER
#
# Make sure that the MySQL user, who is stopping the mysqld services, has
# the same password to all MySQL servers being accessed by $my_progname.
# This user needs to have the 'Shutdown_priv' -privilege, but for security
# reasons should have no other privileges. It is advised that you create a
# common 'multi_admin' user for all MySQL servers being controlled by
# $my_progname. Here is an example how to do it:
#
# GRANT SHUTDOWN ON *.* TO multi_admin\@localhost IDENTIFIED BY 'password'
#
# You will need to apply the above to all MySQL servers that are being
# controlled by $my_progname. 'multi_admin' will shutdown the servers
# using 'mysqladmin' -binary, when '$my_progname stop' is being called.
......
實(shí)際上mysqld_multi就是通過(guò)mysqladmin來(lái)管理實(shí)例的崖蜜，所以配置文件之前配上了mysqladmin的路徑浊仆。
[root@lab11g ~]# mysqladmin -u root shutdown
[root@lab11g ~]# netstat -ntl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 127.0.0.1:2208 0.0.0.0:* LISTEN
tcp 0 0 192.168.1.21:3307 0.0.0.0:* LISTEN
tcp 0 0 192.168.1.21:3308 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:21 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:733 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:2207 0.0.0.0:* LISTEN
[root@lab11g ~]# mysqladmin -u root shutdown -S /tmp/mysql.sock1
[root@lab11g ~]# netstat -ntl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 127.0.0.1:2208 0.0.0.0:* LISTEN
tcp 0 0 192.168.1.21:3308 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:21 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:733 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:2207 0.0.0.0:* LISTEN
3.6 mysqld_mutli管理多版本的mysql
管理多個(gè)實(shí)例實(shí)際上還可以用
mysqld_safe --defaults-file=/etc/my2.cnf
但是這樣很麻煩，而且mysqld_multi還可以管理不同版本的mysql, 如下：
[root@lab11g local]# ls -ltr
總計(jì) 88
drwxr-xr-x 2 root root 4096 2009-10-01 src
drwxr-xr-x 2 root root 4096 2009-10-01 sbin
drwxr-xr-x 2 root root 4096 2009-10-01 libexec
drwxr-xr-x 2 root root 4096 2009-10-01 lib64
drwxr-xr-x 2 root root 4096 2009-10-01 lib
drwxr-xr-x 2 root root 4096 2009-10-01 include
drwxr-xr-x 2 root root 4096 2009-10-01 games
drwxr-xr-x 2 root root 4096 2009-10-01 etc
drwxr-xr-x 5 root root 4096 2015-04-18 share
drwxr-xr-x 2 root root 4096 2015-04-18 bin
drwxr-xr-x 13 root mysql 4096 06-25 23:48 mysql-5.6.31-linux-glibc2.5-x86_64
lrwxrwxrwx 1 root root 34 07-06 20:34 mysql -> mysql-5.7.13-linux-glibc2.5-x86_64
drwxr-xr-x 11 root mysql 4096 07-08 15:02 mysql-5.7.13-linux-glibc2.5-x86_64
lrwxrwxrwx 1 root root 45 07-08 17:23 mysql56 -> /usr/local/mysql-5.6.31-linux-glibc2.5-x86_64
[root@lab11g local]# cd mysql56/
[root@lab11g mysql56]# chown -R mysql .
[root@lab11g mysql56]# chgrp -R mysql .
[root@lab11g mysql56]# vi /etc/my.cnf ---- 修改autocommit
[root@lab11g mysql56]# scripts/mysql_install_db --user=mysql --datadir=/mdata/data3 --basedir=/usr/local/mysql56
Installing MySQL system tables...2016-07-12 16:14:28 0 [Warning] 'ERROR_FOR_DIVISION_BY_ZERO' is deprecated and will be removed in a future release.
2016-07-12 16:14:28 0 [Warning] 'NO_ZERO_DATE' is deprecated and will be removed in a future release.
2016-07-12 16:14:28 0 [Warning] 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release.
2016-07-12 16:14:28 0 [Note] /usr/local/mysql56/bin/mysqld (mysqld 5.6.31-log) starting as process 18654 ...
OK
Filling help tables...2016-07-12 16:14:37 0 [Warning] 'ERROR_FOR_DIVISION_BY_ZERO' is deprecated and will be removed in a future release.
2016-07-12 16:14:37 0 [Warning] 'NO_ZERO_DATE' is deprecated and will be removed in a future release.
2016-07-12 16:14:37 0 [Warning] 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release.
2016-07-12 16:14:37 0 [Note] /usr/local/mysql56/bin/mysqld (mysqld 5.6.31-log) starting as process 18682 ...
OK
......
[root@lab11g mysql56]# mysqld_multi start
[root@lab11g data3]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
#### 測(cè)試連接 ####
[root@lab11g data3]# mysql -u root -S /tmp/mysql.sock3 -P 3309
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@lab11g data3]# mysql -u root -S /tmp/mysql.sock3 -P 3309 -p
Enter password:
......
mysql> set password=password('mysql');
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
[root@lab11g data3]# mysql -u root -S /tmp/mysql.sock3 -P 3309
......
mysql>
