MySQL 5.6.x多執行個體配置

MySQL 5.6.x多執行個體配置
前提準備

做測試還是採用二進位方式安裝，簡單方便快速，在原環境安裝好的基礎上。    
MySQL-5.6.x二進位版本安裝記錄   

個人不喜歡採用mysqlmulti方式配置多執行個體，還是採用多執行個體多進程方式配置。

1, 準備資料庫執行個體datadir目錄

# mkdir -p /usr/local/mysql/mysql3307    
# chown mysql:mysql /usr/local/mysql/mysql3307

初始化執行個體目錄    
# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/mysql3307

2. 準備設定檔

(1) 修改現有設定檔名my.cnf修為3306.cnf,並複製一個設定檔名為3307.cnf    
(2) 3306.cnf設定檔修改，連接埠,sock位置,pid位置,datadir位置等。

[client]    
port        = 3306    
socket      = /tmp/mysql3306.sock

[mysqld]    
port        = 3306    
socket      = /tmp/mysql3306.sock    
datadir    = /usr/local/mysql/data

(3) 3307.cnf設定檔修改

[client]    
port        = 3307    
socket      = /tmp/mysql3307.sock

[mysqld]    
port        = 3307    
socket      = /tmp/mysql3307.sock    
datadir    = /usr/local/mysql/mysql3307

 

3. 啟動多執行個體，啟動指令碼可以加入到開機自開機檔案中。

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/3306.cnf 2>&1 >/dev/null &    
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/3307.cnf 2>&1 >/dev/null &

 

4. 資料庫登入

# /usr/local/mysql/bin/mysql -u root -p -P 3306 -S /tmp/mysql3306.sock    
# /usr/local/mysql/bin/mysql -u root -p -P 3307 -S /tmp/mysql3307.sock

修改3307執行個體密碼:    
# /usr/local/mysql/bin/mysqladmin -u root password 'admin' -S /tmp/mysql3307.sock  #設定管理員密碼

 

3306登入樣本1:

[root@test ~]# mysql -uroot -p -S /tmp/mysql3306.sock    
Enter password:    
Welcome to the MySQL monitor.  Commands end with ; or \g.    
Your MySQL connection id is 14    
Server version: 5.6.24-log Source distribution

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its    
affiliates. Other names may be trademarks of their respective    
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>    
mysql> \q    
Bye

3307登入樣本:

[root@test ~]# mysql -uroot -p  -S /tmp/mysql3307.sock    
Enter password:    
Welcome to the MySQL monitor.  Commands end with ; or \g.    
Your MySQL connection id is 15    
Server version: 5.6.24-log Source distribution

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its    
affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

指定連接埠號碼也是可以訪問的：

mysql -uroot -p -h127.0.0.1 -P3307

5. 關閉mysql多執行個體方式

# /usr/local/mysql/bin/mysqladmin -u root -p -P 3306 -S /tmp/mysql3306.sock shutdown    
# /usr/local/mysql/bin/mysqladmin -u root -p -P 3307 -S /tmp/mysql3307.sock shutdown

或採用kill方式，或編寫啟動指令碼。

本文永久更新連結地址：