Mysql DBA 高級運維學習之路-MySQL多實例安裝

1.什麼是MySQL多實例？

簡單的說，就是一臺機器上開啟多個不同的服務端口（如3306,3307），運行多個MySQL服務進程，這些進程通過不同的socket監聽不同的服務端口來提供各自的服務。

這些MySQL多實例共用一套MySQL安裝程序，使用不同（也可以相同）的my.cnf配置文件、啟動程序文件，數據文件。本次的案例採用共用一套安裝程序，使用不同的配置文件、不同的啟動程序、不同的數據文件的mysql多實例。在提供服務的時候，多實例MySQL在邏輯上看來是各自獨立的，多個實例的自身是根據配置文件對應的設置值，來取得服務器的相關硬件資源多少的。

做一個形象的比喻，MySQL多實例相當於房子的多個臥室一樣，每個實例可以看作是一個臥室，整個服務器就是一套房子，服務器的硬件資源可以看作是房子的衛生間、廚房一樣是房子的公共資源。就像是北漂住在一套房子裡，肯定要共享資源。其實很多服務都可以有多實例如nginx,apache,redias等。

2.MySQL多實例的作用與存在的問題

(1) 有效利用服務器資源

當單個服務器資源有剩餘時，可以充分利用剩餘的資源提供更多的服務。一般服務器資源佔用率應該到60%-70%。

(2) 節約服務器資源

當公司資金比較緊張，但是數據庫又需要各自盡量獨立提供服務，而且需要主從同步技術時多實例就再好不過了。

(2)資源互相搶佔問題

當某個實例併發很高或者慢查詢時，整個實例會消耗整個服務器更多的內存、cpu、磁盤IO資源，導致服務器上的其他實例提供服務的質量下降，這就相當於大家住在一個房子的不同臥室一樣，早晨起來要上班，都要刷牙、洗臉等等。這樣衛生間就會長期佔用，其他人要等待一樣的道理。

3.MySQL多實例常見配置方案

在配置mysql多實例之前，先了解一下mysql多實例配置方案。mysql多實例的配置原則是：降低耦合性

 [root@mysql ~] tree -L 2 /data/
 /data/
 ├── 3306
 │ ├── data
 │ ├── my.cnf
 │ └── mysql
 └── 3307
 ├── data
 ├── my.cnf
 └── mysql

4.安裝多實例MySQL數據庫

4.1 創建MySQL多實例的數據文件目錄

MySQL的安裝上篇已經寫了，這裡就不再安裝了。我們的數據文件目錄採用的是/data目錄作為mysql多實例總的根目錄，然後規劃不同的MySQL實例端口號來作為/data下面的二級目錄，不同的端口號就是不同的實例目錄以區別不同的實例，二級目錄下包含mysql數據文件、配置文件以及啟動文件的目錄。

（1）創建相關mysql多實例的目錄如下

我們先把安裝的單實例mysql停掉，要不然會衝突

 [root@mysql ~] pkill mysqld

然後把之前安裝的單實例mysql的啟動文件刪掉，以後就不用這個方法啟動了

 [root@mysql ~] rm -f /etc/init.d/mysqld

提示：多實例mysql的安裝和單實例mysql的安裝在make && make install之後初始化數據庫就不同了。

創建多實例目錄，我們這裡創建兩個實例，如果想創建3個、4個同理往下增加即可。

 [root@mysql ~] mkdir -p /data/{3306,33307}/data
 [root@mysql ~] tree /data/
 /data/ 總的多實例根目錄
 ├── 3306 3306實例的目錄
 │ └── data 3306實例的數據文件目錄
 └── 3307 3307實例的目錄
 └── data 3307實例的數據文件目錄
 4 directories, 0 files

4.2 創建MySQL多實例的配置文件

MySQL數據庫默認為用戶提供了多個配置文件模板，用戶可以根據硬件配置大小來選擇

 [root@mysql mysql-5.5.32] ls -l support-files/*.cnf
 -rw-r--r--. 1 root root 4723 1月 11 05:31 support-files/my-huge.cnf
 -rw-r--r--. 1 root root 19791 1月 11 05:31 support-files/my-innodb-heavy-4G.cnf
 -rw-r--r--. 1 root root 4697 1月 11 05:31 support-files/my-large.cnf
 -rw-r--r--. 1 root root 4708 1月 11 05:31 support-files/my-medium.cnf
 -rw-r--r--. 1 root root 2872 1月 11 05:31 support-files/my-small.cnf

上面是單實例的配置文件的默認配置文件模板，配置多實例就不同了，為了保證多實例之間彼此獨立。因此，我們為每一個實例建立一個配置文件，一個啟動文件，對應自己的數據文件。

在實際工作中可以拿早已修改好的模板來進行修改，通過rz命令等方式上傳配置文件模板my.cnf文件。

我們在mysql安裝包目錄拷貝一個數據庫文件重命名為my.cnf，然後將my.cnf內容清空，添加下面內容。下面是3306的配置文件，3307的配置文件要把my.cnf中的3306替換成3307，注意3307中的server-id值一定要修改，不能3306中的server-id一樣。降配置文件放到/data/3306/目錄下。配置文件如下所示：沒有前面的數字，數字是行號！

[root@mysql ~] cp /home/zbf/mysql-5.5.32/support-files/my-small.cnf /data/3306/my.cnf
 [root@mysql 3306] > my.cnf
 [root@mysql 3306] vim my.cnf
 1 [client]
 2 port= 3306
 3 socket = /data/3306/mysql.sock
 4 [mysql]
 5 no-auto-rehash
 6 [mysqld]
 7 user= mysql
 8 port= 3306
 9 socket = /data/3306/mysql.sock 

 10 basedir = /usr/local/mysql
 11 datadir = /data/3306/data
 12 open_files_limit= 1024
 13 back_log = 600
 14 max_connections = 800
 15 max_connect_errors = 3000
 16 table_cache = 614
 17 external-locking = FALSE
 18 max_allowed_packet =8M
 19 sort_buffer_size = 1M
 20 join_buffer_size = 1M
 21 thread_cache_size = 100
 22 thread_concurrency = 2
 23 query_cache_size = 2M
 24 query_cache_limit = 1M
 25 query_cache_min_res_unit = 2k
 26 default_table_type = InnoDB
 27 thread_stack = 192K
 transaction_isolation = READ-COMMITTED
 29 tmp_table_size = 2M
 30 max_heap_table_size = 2M
 31 long_query_time = 1
 32 log_long_format
 33 log-error = /data/3306/error.log
 34 log-slow-queries = /data/3306/slow.log
 35 pid-file = /data/3306/mysql.pid
 36 log-bin = /data/3306/mysql-bin
 37 relay-log = /data/3306/relay-bin
 38 relay-log-info-file = /data/3306/relay-log.info
 39 binlog_cache_size = 1M
 40 max_binlog_cache_size = 1M
 41 max_binlog_size = 2M
 42 expire_logs_days = 7
 43 key_buffer_size = 16M
 44 read_buffer_size = 1M
 45 read_rnd_buffer_size = 1M
 46 bulk_insert_buffer_size = 1M
 47 InnoDB_sort_buffer_size = 1M
 48 InnoDB_max_sort_file_size = 10G
 49 InnoDB_max_extra_sort_file_size = 10G
 50 InnoDB_repair_threads = 1
 51 InnoDB_recover
 52 lower_case_table_names = 1
 53 skip-name-resolve
 54 slave-skip-errors = 1032,1062
 55 replicate-ignore-db=mysql
 56 server-id = 1
 57 innodb_additional_mem_pool_size = 4M
 58 innodb_buffer_pool_size = 32M
 59 innodb_data_file_path = ibdata1:128M:autoextend 

 60 innodb_file_io_threads = 4
 61 innodb_thread_concurrency = 8
 62 innodb_flush_log_at_trx_commit = 2
 63 innodb_log_buffer_size = 2M
 64 innodb_log_file_size = 4M
 65 innodb_log_files_in_group = 3
 66 innodb_max_dirty_pages_pct = 90
 67 innodb_lock_wait_timeout = 120
 68 innodb_file_per_table = 0
 69 [mysqldump]
 70 quick
 71 max_allowed_packet = 2M
 [mysqld_safe]
 73 log-error=/data/3306/mysql_linzhongniao3306.err
 74 pid-file=/data/3306/mysqld.pid

4.3 創建啟動文件

下面是3306實例的配置文件，配置3307實例的配置文件只需要把3306實例配置文件中的3306替換成3307。將啟動文件放到/data/3306/目錄下，啟動文件如下所示：

 !/bin/bash
 cmdPath="/usr/local/mysql/bin"
 myPath="/data/3306"
 softPath="/usr/local/mysql"
 socketfile="$myPath/mysql.sock"
 my_user="root"
 my_pass="123456" 數據庫設置的密碼是多少這裡就填多少
 start(){
 if [ ! -e "$socketfile" ];then
 printf "Starting MySQL......\\n"
 /bin/sh ${cmdPath}/mysqld_safe --defaults-file=${myPath}/my.cnf --user=mysql \\
 --basedir=${softPath} --datadir=${myPath}/data &>/dev/null &
 sleep 2
 else
 printf "Mysqld is running....\\n" && exit 1
 fi
 }
 stop(){
 if [ ! -e "$socketfile" ];then
 printf "MySQL is stopped...\\n"
 exit 1
 else 

 printf "Stoping MySQL....\\n"
 mysqladmin -u"${my_user}" -p"${my_pass}" \\
 -S "$socketfile" shutdown 
 fi
 }
 restart(){
 printf "Restarting MySQL...\\n"
 stop
 sleep 2
 start
 } 
 case "$1" in
 start)
 start
 ;;
 stop)
 stop
 ;;
 restart)
 restart
 ;;
 *)
 printf "Usage: $myPath/mysql {start|stop|restart}\\n"
 exit 1
 esac

最終配置效果如下所示：

 [root@mysql ~] tree /data
 /data
 ├── 3306
 │ ├── data
 │ ├── my.cnf 3306實例的配置文件
 │ └── mysql 3306實例的啟動文件
 └── 3307
 ├── data
 ├── my.cnf 3307實例的配置文件
 └── mysql 3307實例的啟動文件

多實例啟動文件啟動mysql服務實質

 [root@linzhongniao ~] /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data 2>&1 >/dev/null &
 

多實例啟動文件的平滑停止mysql服務實質

 [root@linzhongniao ~] /usr/local/mysql/bin/mysqladmin -uroot -p123456 -S /data/3306/mysql.sock shutdown

4.4 授權用戶和組管理整個多實例目錄/data

 [root@mysql ~] chown -R mysql.mysql /data
 [root@mysql ~] find /data/ -name mysql|xargs ls -l
 -rw-r--r--. 1 mysql mysql 1123 Oct 12 22:32 /data/3306/mysql
 -rw-r--r--. 1 mysql mysql 1252 Oct 12 22:19 /data/3307/mysql

保證所有文件mysql用戶能夠可執行

 [root@mysql ~] find /data -type f -name "mysql"|xargs ls -l
 -rw-r--r--. 1 mysql mysql 1604 1月 12 17:06 /data/3306/mysql
 -rw-r--r--. 1 mysql mysql 1604 1月 12 17:06 /data/3307/mysql
 [root@mysql ~] find /data -type f -name "mysql"|xargs chmod +x
 [root@mysql ~] find /data -type f -name "mysql"|xargs ls -l
 -rwxr-xr-x. 1 mysql mysql 1604 1月 12 17:06 /data/3306/mysql
 -rwxr-xr-x. 1 mysql mysql 1604 1月 12 17:06 /data/3307/mysql

4.6 配置MySQL命令全局使用路徑

 [root@mysql ~] echo "export PATH=/usr/local/mysql/bin:$PATH" >>/etc/profile
 [root@mysql ~] tail -1 /etc/profile
 export PATH=/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/bin/passwd:/usr/bin/passwd:/root/bin
 [root@mysql ~] source /etc/profile 

4.7 初始化MySQL多實例的數據庫文件

（1）初始化命令

mysql5.1.X初始化命令

 mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/3307/data/ --user=mysql

(2)mysql5.5.32初始化命令

 [root@linzhongniao ~] cd /usr/local/mysql/scripts/

要在scripts下執行，不在/usr/local/mysql/bin下。注意要在當前目錄下執行加 ./。

 [root@mysql/> /usr/local/mysql/scripts
 [root@mysql/> Installing MySQL system tables...
 OK
 Filling help tables...
 OK
 ..省略....
 [root@mysql/> Installing MySQL system tables...
 OK
 Filling help tables...
 OK
 ...省略....

（3）為什麼要初始化數據庫？

a.初始化的主要目的就是創建基礎的數據庫文件，例如生成mysql庫表。

b.初始化後可以查看對應的實例數據庫目錄，可以看到如下文件。

4.8 啟動MySQL服務

 /data/3306/mysql start
 /data/3307/mysql start

4.9 MySQL故障排錯多實例數據庫

如果mysql沒有起來排查方法如下

（1）如果發現沒有顯示MySQL的端口，請等待幾秒MySQL服務啟動會有點慢。

（2）如果還是不行，請查看錯誤日誌，錯誤日誌路徑在my.cnf配置文件路徑下面。

 grep log-error /data/3306/mysql_linzhongniao3306.err|tail -1

（3）細看所有執行命令返回的輸出，不要忽略關鍵的輸出結果。

（4）查看系統的/var/log/messages。

4.10 MySQL多實例登錄

mysql安裝完成後，默認情況下管理員賬號root是無密碼的。登錄不同的mysql數據庫需要指定不同庫的sock文件。如果是遠程的話就指定IP地址。

 [root@mysql ~] mysql -S /data/3306/mysql.sock 
 [root@mysql ~] mysql -S /data/3307/mysql.sock 

5. MySQL安全配置

5.1 為root增加密碼

 mysqladmin -u root -S /data/3306/mysql.sock password '123456'
 mysqladmin -u root -S /data/3307/mysql.sock password '123456'

設置完數據路密碼登錄數據庫的方法

 [root@mysql 3306] mysql -uroot -p123456 -S /data/3306/mysql.sock
 [root@mysql 3306] mysql -uroot -p123456 -S /data/3307/mysql.sock

5.2 為mysql多實例啟動腳本修改執行權限

只允許root用戶有執行權限

 [root@mysql ~] find /data -type f -name "mysql" -exec ls -l {} \\;
 -rwxr-xr-x. 1 mysql mysql 1267 Oct 12 23:01 /data/3307/mysql
 -rwxr-xr-x. 1 mysql mysql 1138 Oct 12 23:01 /data/3306/mysql
 [root@mysql ~] find /data -type f -name "mysql" -exec chown root.root {} \\; 
 [root@mysql ~] find /data -type f -name "mysql" -exec chmod 700 {} \\; 
 [root@mysql ~] find /data -type f -name "mysql" -exec ls -l {} \\; 
 -rwx------. 1 root root 1267 Oct 12 23:01 /data/3307/mysql
 -rwx------. 1 root root 1138 Oct 12 23:01 /data/3306/mysql

至此mysql多實例配置完成