mysql多執行個體安裝

標籤：cti   初始   lis   反向   pass   集中化   info   sock   exp   

1. 背景MySQL資料庫的集中化營運，可以通過在一台伺服器上，部署運行多個MySQL服務進程，通過不同的socket監聽不同的服務連接埠來提供各自的服務。各個執行個體之間是相互獨立的，每個執行個體的datadir, port, socket, pid都是不同的。2. 多執行個體特點* 有效利用伺服器資源，當單個伺服器資源有剩餘時，可以充分利用剩餘的資源提供更多的服務。* 資源互相搶佔問題，當某個服務執行個體服務並發很高時或者開啟慢查詢時，會消耗更多的記憶體、CPU、磁碟IO資源，導致伺服器上的其他執行個體提供服務的品質下降。 3. 環境 [ 關閉SeLinux ][[email protected] ~]# cat /etc/RedHat-releaseCentOS release 6.9 (Final) [[email protected] ~]# uname -r2.6.32-696.3.2.el6.x86_64 [[email protected] ~]# getenforceDisabled4. 部署 [ 4個執行個體 ]* 下載 MySQL 5.7 二制包 [ 推薦官方下載 ]　此下載版本大於5.7.5[[email protected] ~]# wget wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz* 解壓 MySQL 5.7 二進位包到指定目錄[[email protected] ~]# tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/* 建立 MySQL 軟連結[[email protected] ~]# ln -s /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64 /usr/local/mysql* 建立 MySQL 使用者[[email protected] ~]# groupadd -r mysql[[email protected] ~]# useradd -r  -g mysql -s /bin/false mysql* 在 MySQL 二進位包目錄中建立 mysql-files 目錄 [MySQL 資料匯入/匯出資料專放目錄][[email protected] ~]# mkdir -v /usr/local/mysql/mysql-filesmkdir: created directory `/usr/local/mysql/mysql-files‘* 建立多執行個體資料目錄[[email protected] ~]# mkdir -vp /data/mysql_data{1..4}mkdir: created directory `/data‘mkdir: created directory `/data/mysql_data1‘mkdir: created directory `/data/mysql_data2‘mkdir: created directory `/data/mysql_data3‘mkdir: created directory `/data/mysql_data4‘* 修改 MySQL 二進位包目錄的所屬使用者與所屬組1 [[email protected] ~]# chown mysql:mysql -R /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64* 修改 MySQL 多執行個體資料目錄與 資料匯入/匯出專放目錄的所屬使用者與所屬組[[email protected] ~]# chown mysql:mysql -R /usr/local/mysql/mysql-files /data/mysql_data{1..4}* 配置 MySQL 設定檔 /etc/my.cnf[mysqld][mysqld_multi]mysqld = /usr/local/mysql/bin/mysqldmysqladmin = /usr/local/mysql/bin/mysqladminlog = /tmp/mysql_multi.log [mysqld1]# 設定資料目錄　[多執行個體中一定要不同]datadir = /data/mysql_data1# 設定sock存放檔案名稱　[多執行個體中一定要不同]socket = /tmp/mysql.sock1# 設定監聽開放連接埠　[多執行個體中一定要不同]port = 3306# 設定運行使用者user = mysql# 關閉監控performance_schema = off# 設定innodb 緩衝大小innodb_buffer_pool_size = 32M# 設定監聽IP地址bind_address = 0.0.0.0# 關閉DNS 反向解析skip-name-resolve = 0 [mysqld2]datadir = /data/mysql_data2socket = /tmp/mysql.sock2port = 3307user = mysqlperformance_schema = offinnodb_buffer_pool_size = 32Mbind_address = 0.0.0.0skip-name-resolve = 0 [mysqld3]datadir = /data/mysql_data3socket = /tmp/mysql.sock3port = 3308user = mysqlperformance_schema = offinnodb_buffer_pool_size = 32Mbind_address = 0.0.0.0skip-name-resolve = 0 [mysqld4]datadir = /data/mysql_data4socket = /tmp/mysql.sock4port = 3309user = mysqlperformance_schema = offinnodb_buffer_pool_size = 32Mbind_address = 0.0.0.0skip-name-resolve = 0* 初始化各個執行個體 [ 初始化完成後會內建隨機密碼在輸出日誌中 ][[email protected] ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1[[email protected] ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data2[[email protected] ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data3[[email protected] ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data4* 各執行個體開啟 SSL 串連[[email protected] ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1[[email protected] ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data2[[email protected] ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data3[[email protected] ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data4* 複製多執行個體指令碼到服務管理目錄下 [ /etc/init.d/ ][[email protected] ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi* 添加指令碼執行許可權[[email protected] ~]# chmod +x /etc/init.d/mysqld_multi* 添加進service服務管理[[email protected] ~]# chkconfig --add mysqld_multi5. 啟動測試 (失敗的話加變數  export PATH=$PATH:/usr/local/mysql/bin)* 查個多執行個體狀態[[email protected] ~]# /etc/init.d/mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld1 is not runningMySQL server from group: mysqld2 is not runningMySQL server from group: mysqld3 is not runningMySQL server from group: mysqld4 is not running* 啟動多執行個體[[email protected] ~]#/etc/init.d/mysqld_multi start 啟動(或者)　　　　　　　 　service mysqld_multi start* 查看多執行個體狀態 : /etc/init.d/mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld1 is runningMySQL server from group: mysqld2 is runningMySQL server from group: mysqld3 is runningMySQL server from group: mysqld4 is running* 查看執行個體監聽連接埠[[email protected] ~]# netstat -lntp | grep mysqldtcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2673/mysqldtcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 2676/mysqldtcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 2679/mysqldtcp 0 0 0.0.0.0:3309 0.0.0.0:* LISTEN 2682/mysqld6. 串連測試  (後有圖片)　 * 執行個體1[[email protected] ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql.sock1 -p‘z+Ilo*>s:3kw‘mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.7.18 Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> set password = ‘123456‘;Query OK, 0 rows affected (0.00 sec)　 * 執行個體2[[email protected] ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql.sock2 -p‘b*AHUrTgu1rl‘mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 7Server version: 5.7.18 Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> set password = ‘123456‘;Query OK, 0 rows affected (0.00 sec)                   

mysql多執行個體安裝