MySQL數(shù)據(jù)庫的多實(shí)例安裝

0. summary

1. 多實(shí)例介紹
2. 參數(shù)要求
3. 安裝和管理多實(shí)例
.   3.1 初始化數(shù)據(jù)庫目錄
.   3.2 啟動和停止多實(shí)例
.   3.3 多實(shí)例的連接
.   3.4 拷貝啟動腳本，方便自啟
.   3.5 mysqld_multi命令的實(shí)質(zhì)
.   3.6 mysqld_mutli管理多版本的mysql

1. 多實(shí)例介紹

  • 一臺服務(wù)器上安裝多個MySQL數(shù)據(jù)庫實(shí)例
  • 可以充分利用服務(wù)器的硬件資源
  • 通過mysqld_multi進(jìn)行管理

2. 參數(shù)要求

[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe                 ---- 也可以通過mysqld來啟動，是否需要看應(yī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ù)庫目錄

[root@lab11g mysql]# bin/mysqld --initialize --user=mysql --datadir=/mdata/data1
[root@lab11g mysql]# bin/mysqld --initialize --user=mysql --datadir=/mdata/data2

初始化完成后檢查error.log, 并記下臨時密碼

3.2 啟動和停止多實(shí)例

#### 啟動單個實(shí)例 ####

[root@lab11g mysql]# mysqld_multi start 1

#### 啟動所有 ####

[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

#### 停止到單個實(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 拷貝啟動腳本，方便自啟

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就是通過mysqladmin來管理實(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

管理多個實(shí)例實(shí)際上還可以用

mysqld_safe --defaults-file=/etc/my2.cnf

但是這樣很麻煩，而且mysqld_multi還可以管理不同版本的mysql, 如下：

[root@lab11g local]# ls -ltr
總計 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

#### 測試連接 ####

[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>
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容