本連結轉載自：https://www.cnblogs.com/chensiqiqi/p/9162921.html
僅供自學使用。
一，概述
1.1 MySQL介紹
MySQL屬于傳統關系型資料庫産品，它開放式的架構使得使用者選擇性很強，同時社群開發與維護人數衆多。其功能穩定，性能卓越，且在遵守GPL協定的前提下，可以免費使用與修改，也為MySQL的推廣與使用帶來了更多的利好。在MySQL成長與發展過程中，支援的功能逐漸增多，性能也不斷提高，對平台的支援也越來越多。
MySQL是一種關系型資料庫管理系統，關系型資料庫的特點是将資料儲存在不同的表中，再将這些表放入不同的資料庫中，而不是将所有資料統一放在一個大倉庫裡，這樣的設計增加了MySQL的讀取速度，而且靈活性和可管理性也得到了很大提高。通路及管理MySQL資料庫的最常用标準化語言為SQL結構化查詢語言。
1.2 MariaDB 資料庫的誕生背景介紹
自甲骨文公司收購MySQL後，其在商業資料庫與開源資料庫領域市場的占有份額都躍居第一，這樣的格局引起了業内很多的人士的擔憂，因為商業資料庫的老大有可能将MySQL閉源。為了避免Oracle将MySQL閉源，而無開源的類MySQL資料庫可用，MySQL社群采用分支的方式來避開這個風險。MariaDB資料庫就這樣誕生了，MariaDB是一個向後相容，可能在以後替代MySQL的資料庫産品，其官方位址為：https://mariadb.org/ 。不過，這裡還是建議大家選擇更穩定，使用更廣泛的MySQL資料庫，可以先測試MariaDB資料庫，等使用的人員更多一些，社群更活躍後再考慮使用為好。
二，MySQL多執行個體介紹
在之前LNMP的講解中，已經針對MySQL資料庫進行了介紹，并說明了為什麼要選擇MySQL資料庫，以及MySQL資料庫在Linux系統下的多種安裝方式，同時講解了MySQL的二進制方式單執行個體安裝，基礎優化等内容，本節将為同學們講解更為實用的MySQL多執行個體安裝，主從複制叢集等重要應用實踐。
2.1 什麼是MySQL多執行個體
- 簡單的說，MySQL多執行個體就是在一台伺服器上同時開啟多個不同的伺服器端口（如：3306，3307），同時運作多個MySQL服務程序，這些服務程序通過不同的socket監聽不同的伺服器端口來提供服務。
- 這些MySQL多執行個體共用一套MySQL安裝程式，使用不同的my.cnf（也可以相同）配置檔案，啟動程式（也可以相同）和資料檔案。在提供服務時，多執行個體MySQL在邏輯上看起來是各自獨立的，它們根據配置檔案的對應設定值，獲得伺服器相應數量的硬體資源。
- 打個比方吧，MySQL多執行個體就相當于房子的多個卧室，每個執行個體可以看作一間卧室，整個伺服器就是一套房子，伺服器的硬體資源（CPU，Mem，Disk），軟體資源（Centos作業系統）可以看作房子的衛生間，廚房，客廳，是房子的公用資源。
- 其實很多網絡服務都是可以配置多執行個體的，例如Nginx，Apache，Haproxy，Redis，Memcache等。這在門戶網站使用得很廣泛。
2.2 MySQL多執行個體的作用與問題
MySQL多執行個體的作用如下：
（1）有效利用伺服器資源
當單個伺服器資源有剩餘時，可以充分利用剩餘的資源提供更多的服務，且可以實作資源的邏輯隔離。
（2）節約伺服器資源
當公司資金緊張，但是資料庫又需要各自盡量獨立地提供服務，而且，需要主從複制等技術時，多執行個體就再好不過了。
MySQL多執行個體有它的好處，但也有其弊端，比如，會存在資源互相搶占的問題。
當某個資料庫執行個體并發很高或有SQL慢查詢時，整個執行個體會消耗大量的系統CPU，磁盤I/O等資源，導緻伺服器上的其他資料庫執行個體提供服務的品質一起下降。這就相當于大家住在一個房子的不同卧室一樣，早晨起來上班，都要刷牙，洗臉等，這樣衛生間就會長期占用，其他人要等待一樣。不同執行個體擷取的資源是相對獨立的，無法像虛拟化一樣完全隔離。
三， MySQL多執行個體的生産應用場景
3.1 資金緊張型公司的選擇
若公司資金緊張，公司業務通路量不太大，但又希望不同業務的資料庫服務各自盡量獨立地提供服務而互相不受影響，同時，還需要主從複制等技術提供備份或讀寫分離服務，那麼多執行個體就再好不過了。例如：可以通過3台伺服器部署9～15個執行個體，交叉做主從複制，資料備份及讀寫分離，這樣就可達到9～15台伺服器每個隻裝一個資料庫才有的效果。這裡要強調的是，所謂的盡量獨立是相對的。
3.2 并發通路不是特别大的業務
當公司業務通路量不太大的時候，伺服器的資源基本上都浪費了，這時就很适合多執行個體的應用，如果對SQL語句的優化做得比較好，MySQL多執行個體會是一個很值得使用的技術，即使并發很大，合理配置設定好系統資源，搭配好服務，也不會有太大問題。
3.3 門戶網站應用MySQL多執行個體場景
門戶網站通常都會使用多執行個體，因為配置硬體好的伺服器，可節省IDC機櫃空間，同時，跑多執行個體也會減少硬體資源跑不滿的浪費。比如，百度公司的很多資料庫都是多執行個體，不過，一般是從庫多執行個體，例如某部門中使用的IBM伺服器為48核CPU，記憶體96GB，一台伺服器跑3～4個執行個體；此外，新浪網使用的也是多執行個體，記憶體48GB左右。
說明：
據調查，新浪網的資料庫單機1～4個資料庫執行個體的居多，其中又數1～2個的最多，因為大業務占用的機器比較多。伺服器是DELL R510的居多，CPU是E5210，48GB記憶體，磁盤12×300G SAS，做RAID10，此為門戶網站的伺服器配置參考，希望能給同學們的面試帶來一些啟迪。
另外，新浪網站安裝資料庫時，一般采用編譯安裝的方式，并且會在優化之後做成rpm包，以便統一使用。
四， MySQL多執行個體常見的配置方案
4.1 單一配置檔案，單一啟動程式的多執行個體部署方案
下面是MySQL官方文檔提到的單一配置檔案，單一啟動程式多執行個體部署方案，但不推薦此方案，這裡僅作為知識點提及，後文不再涉及此方案的說明。my.cnf配置檔案示例（MySQL手冊裡提到的方法）如下：
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = mysql
[mysqld1]
socket = /var/lib/mysql/mysql.sock
port = 3306
pid-file = /var/lib/mysql/mysql.pid
datadir = /var/lib/mysql/
user = mysql
[mysqld2]
socket = /mnt/data/db1/mysql.sock
port = 3302
pid-file = /mnt/data/db1/mysql.pid
datadir = /mnt/data/db1/
user = mysql
skip-name-resolv
server-id=10
default-storage-engine=innodb
innodb_buffer_pool_size=512M
innodb_additional_mem_pool=10M
default_character_set=utf8
character_set_server=utf8
#read-only
relay-log-space-limit=3G
expire_logs_day=20
啟動程式的指令如下：
mysqld_multi --config-file=/data/mysql/my_multi.cnf start 1,2
該方案的缺點是耦合度太高，一個配置檔案，不好管理。工作開發和運維的統一原則為降低耦合度。
4.2 多配置檔案，多啟動程式的部署方案
多配置檔案，多啟動程式部署方案，是本文主要講解的方案，也是非常常用并極力推薦的多執行個體方案。下面來看配置示例。
[[email protected] /]# tree /data
/data
├──
提示：
這裡的配置檔案my.cnf,啟動程式mysql都是獨立的檔案，資料檔案data目錄也是獨立的。
多執行個體MySQL資料庫的安裝和之前講解的單執行個體沒有任何差別，是以，同學們如果有前文單執行個體的安裝環境，那麼可以直接略過5.1節的内容。
五，安裝并配置多執行個體MySQL資料庫
5.1 安裝MySQL多執行個體
1，安裝MySQL需要的依賴包和編譯軟體
（1）安裝MySQL需要的依賴包
安裝MySQL之前，最好先安裝MySQL需要的依賴包，不然後面會出現很多報錯資訊，到那時還得再回來安裝MySQL的依賴包。安裝指令如下：
（2）安裝編譯MySQL需要的軟體
首先通過網絡獲得cmake軟體，然後進行如下操作：
[[email protected] ~]# ls -lh cmake-2.8.6.tar.gz
-rw-r--r--
2，開始安裝MySQL
為了讓同學們學習更多的MySQL技術，接下來會以相對複雜的源代碼安裝來講解MySQL多執行個體的安裝。大型公司一般都會将MySQL軟體定制成rpm包，然後放到yum倉庫裡，使用yum安裝，中小企業裡的二進制和編譯安裝的差別不大。
（1）建立MySQL使用者賬号
首先以root身份登入到Linux系統中，然後執行如下指令建立mysql使用者賬号：
[[email protected] ~]# useradd -s /sbin/nologin -M mysql
[[email protected] ~]# id mysql
uid=
（2）擷取MySQL軟體包
MySQL軟體包的下載下傳位址為：https://dev.mysql.com/downloads/mysql/
提示：
本例以MySQL編譯的方式來講解，之前已經示範過二進制方式安裝了。在生産場景中，二進制和源碼包兩種安裝方法都是可以用的，其應用場景一般沒什麼差别。不同之處在于，二進制的安裝包較大，名字和源碼包也有些差別，二進制安裝過程比源碼更快。
MySQL源碼包和二進制安裝包的名稱見下圖
（3）采用編譯方式安裝MySQL
配置及編譯安裝的步驟如下：
[[email protected] ~]# tar xf mysql-5.5.22.tar.gz -C /usr/src/
[[email protected] ~]# cd /usr/src/mysql-5.5.22/
[[email protected] mysql-5.5.22]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.22 \
> -DMYSQL_DATADIR=/usr/local/mysql-5.5.22/data \ #資料存放目錄
> -DMYSQL_UNIX_ADDR=/usr/local/mysql-5.5.22/tmp/mysql.sock \ #MySQL程序間通信的套接字位置 > -DDEFAULT_CHARSET=utf8 \ #預設字元集為utf8 > -DDEFAULT_COLLATION=utf8_general_ci \ #預設字元集排序規則 > -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \ #額外的字元集支援 > -DENABLED_LOCAL_INFILE=ON \ #是否啟用加載本地資料 > -DWITH_INNOBASE_STORAGE_ENGINE=1 \ #靜态編譯innodb存儲引擎到資料庫 > -DWITH_FEDERATED_STORAGE_ENGINE=1 \ #靜态編譯FEDERATED存儲引擎到資料庫 > -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ #靜态編譯blackhole存儲引擎到資料庫 > -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \ #不編譯EXAMPLE存儲引擎到資料庫 > -DWITHOUT_PARTITION_STORAGE_ENGINE=1 \ #不支援資料庫分區 > -DWITH_FAST_MUTEXES=1 \ > -DWITH_ZLIB=bundled \ #zlib壓縮模式 > -DENABLED_LOCAL_INFILE=1 \ #是否啟用本地的LOCAL_INFILE > -DWITH_READLINE=1 \ #使用捆綁的readline > -DWITH_EMBEDDED_SERVER=1 \ #是否要建立嵌入式伺服器 > -DWITH_DEBUG=0 #禁用DEBUG（開啟影響性能） # 提示：編譯時可配置的選項很多，具體可參考官方文檔 [[email protected] mysql-5.5.22]# make && make install
下面設定不帶版本号的軟連結/usr/local/mysql,操作步驟如下：
如果上述操作未出現錯誤，檢視/usr/local/mysql目錄下有内容，則MySQL5.5.22源代碼包采用cmake方式的安裝就算成功了。
5.2 建立MySQL多執行個體的資料檔案目錄
在企業中，通常以/data目錄作為MySQL多執行個體總的根目錄，然後規劃不同的數字（即MySQL執行個體端口号）作為/data下面的二級目錄，不同的二級目錄對應的數字就作為MySQL執行個體的端口号，以差別不同的執行個體，數字對應的二級目錄下包含MySQL的資料檔案，配置檔案及啟動檔案等。
下面以配置3306,3307兩個執行個體為例進行講解。建立MySQL多執行個體的目錄如下：
[[email protected] ~]# mkdir -p /data/{3306,3307}/data
[[email protected] ~]# tree /data/ /data/ ├──
提示：
（1）mkdir -p /data/{3306,3307}/data相當于mkdir -p /data/3306/data;mkdir -p /data/3307/data兩條指令
（2）如果是建立多個目錄，可以增加如3308,3309這樣的目錄名，在生産環境中，一般為3~4個執行個體為佳。
5.3 建立MySQL多執行個體的配置檔案
MySQL資料庫預設為使用者提供了多個配置檔案模闆，使用者可以根據伺服器硬體配置的大小來選擇。
[[email protected] mysql]# ls -l support-files/my*.cnf
-rw-r--r--
注意：
這些配置檔案裡的注釋非常詳細，不過是英文的。。。
上面是單執行個體的預設配置檔案模闆，如果配置多執行個體，和單執行個體會有不同。為了讓MySQL多執行個體之間彼此獨立，要為每一個執行個體建立一個my.cnf配置檔案和一個啟動檔案MySQL，讓他們分别對應自己的資料檔案目錄data。
首先，通過vim指令添加配置檔案内容，指令如下：
vim /data/3306/my.cnf
vim /data/3307/my.cnf
不同的執行個體需要添加的my.cnf内容會有差別,其中的配置由官方的配置模闆修改而來。當然，在實際工作中，我們是拿早已配置好的模闆來進行修改的，可以通過rz等方式上傳配置檔案模闆my.cnf檔案到相關目錄下。
MySQL3306,3307執行個體配置檔案如下
##執行個體3306配置檔案my.cnf
[[email protected] ~]# cat /data/3306/my.cnf
[client]
port = 3306
socket = /data/3306/mysql.sock
[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_open_cache = 614
external-locking = FALSE
max_allowed_packet = 8M
#binlog_cache_size = 1M
#max_heap_table_size = 64M
#read_buffer_size = 2M #read_rnd_buffer_size = 16M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k #ft_min_word_len = 4 #default-storage-engine = MYISAM thread_stack = 192K transaction_isolation = READ-COMMITTED tmp_table_size = 2M max_heap_table_size = 2M #log-bin=mysql-bin #binlog_format=mixed #slow_query_log long_query_time = 1 pid-file = /data/3306/mysql.pid relay-log = /data/3306/relay-bin relay-log-info-file = /data/3306/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db = mysql server-id = 1 #key_buffer_size = 32M #bulk_insert_buffer_size = 64M #myisam_sort_buffer_size = 128M #myisam_max_sort_file_size = 10G #myisam_repair_threads = 1 #myisam_recover innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:128M:autoextend innodb_file_io_threads = 4 #innodb_write_io_threads = 8 #innodb_read_io_threads = 8 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysql] no-auto-rehash #[myisamchk] #key_buffer_size = 512M #sort_buffer_size = 512M #read_buffer = 8M #write_buffer = 8M #[mysqlhotcopy] #interactive-timeout [mysqld_safe] log-error = /data/3306/mysql_yunjisuan3306.err pid-file = /data/3306/mysqld.pid
提示：執行個體3307的配置檔案隻需要将3306配置檔案裡的所有3306數字替換成3307(server-id換個數字)即可。
最終完成後的多執行個體根/data目錄結果如下：
[[email protected] ~]# tree /data
/data
├──
5.4 建立MySQL多執行個體的啟動檔案
MySQL多執行個體啟動檔案的建立和配置檔案的建立幾乎一樣，也可以通過vim指令來添加，如下：
vim /data/3306/mysql
vim /data/3307/mysql
需要添加的MySQL啟動檔案内容如下。（當然，在實際工作中我們是拿早已配置好的模闆來進行修改的，可以通過rz等方式上傳配置檔案模闆MySQL檔案到相關目錄下）
[[email protected] ~]# cat /data/3306/mysql
#!/bin/bash
###############################################
#this scripts is created by Mr.chen at 2016-06-25
port=3306
mysql_user="root" mysql_pwd="" #這裡需要修改為使用者的實際密碼 CmdPath="/usr/local/mysql/bin" mysql_sock="/data/${port}/mysql.sock" #startup function function_start_mysql(){ if [ ! -e "$mysql_sock" ];then printf "Starting MySQL....\n" /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null & else printf "MySQL is running...\n" exit fi } #stop function function_stop_mysql(){ if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped...\n" exit else printf "Stoping MySQL...\n" ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown fi } #restart function function_restart_mysql(){ printf "Restarting MySQL...\n" function_stop_mysql sleep 2 function_start_mysql } case $1 in start) function_start_mysql ;; stop) function_stop_mysql ;; restart) function_restart_mysql ;; *) printf "Usage: /data/${port}/mysql{start|stop|restart}\n" esac
3307執行個體的啟動檔案隻需修改3306啟動檔案的端口即可
最終完成後的多執行個體根/data目錄結果如下：
[[email protected] ~]# tree /data
/data
├──
需要特别說明一下，在多執行個體啟動檔案中，啟動MySQL不同執行個體服務，所執行的指令實質是有差別的，例如，啟動3306執行個體的指令如下：啟動3307執行個體的指令如下：mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &
mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 >/dev/null &
下面看看在多執行個體啟動檔案中，停止MySQL不同執行個體服務的實質指令。
停止3306執行個體的指令如下：
停止3307執行個體的指令如下：mysqladmin -uroot -pyunjisuan123 -S /data/3306/mysql.sock shutdown
mysqladmin -u root -pyunjisuan123 -S /data/3307/mysql.sock shutdown
5.5 配置MySQL多執行個體的檔案權限
1）通過下面的指令，授權mysql使用者群組管理整個多執行個體的根目錄/data
[[email protected] ~]# chown -R mysql.mysql /data
[[email protected] ~]# find /data -name "mysql" | xargs ls -l -rw-r--r--. 1 mysql mysql 1039 Jul 20 19:33 /data/3306/mysql -rw-r--r--. 1 mysql mysql 1039 Jul 20 19:34 /data/3307/mysql
2）通過下面的指令，授權MySQL多執行個體所有啟動檔案的mysql可執行，設定700權限最佳，注意不要用755權限，因為啟動檔案裡有資料庫管理者密碼，會被讀取到。
5.6 MySQL相關指令加入全局路徑的配置
（1）配置全局路徑的意義
如果不為MySQL的指令配置全局路徑，就無法直接在指令行輸入mysql這樣的指令，隻能用全路徑指令（/usr/local/mysql/bin/mysql）,這種帶着路徑輸入指令的方式很麻煩。
（2）配置MySQL全局路徑的方法
1）确認mysql指令所在路徑，指令如下：
[[email protected] ~]# ls /usr/local/mysql/bin/mysql
/usr/local/mysql/bin/mysql
2）在PATH變量前面增加/usr/local/mysql/bin路徑，并追加到/etc/profile檔案中，指令如下：
[[email protected] ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
#注意，echo後邊是單引号，雙引号的話變量内容會被解析掉。
[[email protected] ~]# tail -1 /etc/profile
export PATH=/usr/local/mysql/bin:$PATH [[email protected] ~]# source /etc/profile #執行source使上一行添加到/etc/profile中，内容直接生效 #以上指令的用途為定義mysql全局路徑，實作在任意路徑執行mysql指令
提示：
更簡單的設定方法為用下面指令做軟連結：ln -s /usr/local/mysql/bin/* /usr/local/sbin/,把mysql指令說在路徑連結到全局路徑/usr/local/sbin/的下面。
5.7 初始化MySQL多執行個體的資料庫檔案
上述步驟全都配置完畢後，就可以初始化資料庫檔案了，這個步驟其實也可以在編譯安裝MySQL之後就操作，隻不過放到這裡更合理一些。
（1）初始化MySQL資料庫
初始化指令如下：
[[email protected] scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3306/data --user=mysql
[[email protected] scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3307/data --user=mysql
提示：
--basedir=/usr/local/mysql為MySQL的安裝路徑，--datadir為不同的執行個體資料目錄
（2）初始化資料庫的原理及結果說明
初始化資料庫的實質就是建立基礎的資料庫系統的庫檔案，例如：生成MySQL庫表等。
初始化資料庫後檢視對應執行個體的資料目錄，可以看到多了如下檔案：
[[email protected] scripts]# tree /data
#以下省略若幹...
5.8 啟動MySQL多執行個體的指令
[[email protected] scripts]# /data/3306/mysql start
Starting MySQL....
[[email protected] scripts]# /data/3307/mysql start
Starting MySQL....
[email protected] scripts]# netstat -antup | grep 330
tcp
從輸出中可以看到，3306和3307執行個體均已正常啟動。
六，配置及管理MySQL多執行個體資料庫
6.1 配置MySQL多執行個體資料庫開機自啟動
服務的開機自啟動很關鍵，MySQL多執行個體的啟動也不例外，把MySQL多執行個體的啟動指令加入/etc/rc.local,實作開機自啟動，指令如下：
[[email protected] ~]# echo "#mysql multi instances" >> /etc/rc.local
[[email protected] ~]# echo "/data/3306/mysql start" >> /etc/rc.local
[[email protected] ~]# echo "/data/3307/mysql start" >> /etc/rc.local
[[email protected] ~]# tail -3 /etc/rc.local
#mysql multi instances /data/
6.2 登陸MySQL測試
6.3 MySQL多執行個體資料庫的管理方法
- MySQL安裝完成後，預設情況下，MySQl管理者的賬号root是無密碼的。登陸不同的執行個體需要指定不同執行個體的mysql.sock檔案路徑，這個mysql.sock是在my.cnf配置檔案裡指定的。
- 下面是無密碼情況下登陸資料庫的方法，關鍵點是-S參數及後面指定的/data/33306/mysql.sock,注意，不同執行個體的sock雖然名字相同，但是路徑是不同的，是以是不同的檔案。
mysql -S /data/3306/mysql.sock
mysql -S /sata/
下面是重新開機對應執行個體資料庫的指令
/data/3306/mysql stop
/data/3307/mysql start
6.4 MySQL安全配置
MySQL管理者的賬号root密碼預設為空，極不安全，可以通過mysqladmin指令為MySQL不同執行個體的資料庫設定獨立的密碼，指令如下：
帶密碼登陸不同執行個體資料庫的方法：
#登陸3306執行個體的指令如下：
mysql -uroot -p123123 -S /data/3306/mysql.sock #登陸3307執行個體的指令如下： mysql -uroot -p123123 -S /data/3307/mysql.sock
提示：
基礎弱的同學，在測試時盡量保證多執行個體的密碼相同，可以減少麻煩，後面還原資料庫時會覆寫密碼！
若要重新開機多執行個體資料庫，也需要進行相應的如下配置。再重新開機資料庫前，需要調整不同執行個體啟動檔案裡對應的資料庫密碼。
[[email protected] ~]# vim /data/3306/mysql
[[email protected] ~]# sed -n '7p' /data/3306/mysql #這是之前mysql多執行個體啟動腳本裡mysql的登陸密碼變量
mysql_pwd="" [[email protected] ~]# sed -i '7 s#""#"123123"#' /data/3306/mysql [[email protected] ~]# sed -n '7p' /data/3306/mysql mysql_pwd="123123" #修改成實際的登入密碼 [[email protected] ~]# sed -n '7p' /data/3307/mysql mysql_pwd="" [[email protected] ~]# sed -i '7 s#""#"123123"#' /data/3307/mysql [[email protected] ~]# sed -n '7p' /data/3307/mysql mysql_pwd="123123"
多執行個體下正常停止資料庫的指令如下：
/data/3306/mysql stop
由于選擇了mysqladmin shutdown的停止方式，是以停止資料庫時需要在啟動檔案裡配置資料庫的密碼
/data/3306/mysql start
重點提示：
禁止使用pkill，kill -9，killall -9等指令強制殺死資料庫，這會引起資料庫無法啟動等故障的發生。
6.5 如何再增加一個MySQL的執行個體
若再3306和3307執行個體的基礎上，再增加一個MySQL執行個體，該怎麼辦？下面給出增加一個MySQL3308端口執行個體的指令集合：
mkdir -p /data/3308/data
\cp /data/3306/my.cnf /data/3308/ \cp /data/3306/mysql /data/3308/ sed -i 's#
如果配置以後，服務啟動後卻沒有運作起來，别忘了一定要看MySQL錯誤日志，在/data/3308/my.cnf最下面有錯誤日志路徑位址。
6.6 多執行個體MySQL登陸問題分析
（1）多執行個體本地登入MySQL
多執行個體本地登入一般通過socket檔案來指定具體登陸到哪個執行個體，此檔案的具體位置是在MySQL編譯過程或my.cnf檔案中指定的。在本地登陸資料庫時，登陸程式會通過socket檔案來判斷登陸的是哪個資料庫執行個體。
例如：通過mysql -uroot -p '123123' -S /data/3307/mysql.sock可知，登陸的是3307這個執行個體。
mysql.sock檔案是MySQL伺服器端與本地MySQL用戶端進行通信的UNIX套接字檔案。
（2）遠端連接配接登陸MySQL多執行個體
遠端登陸MySQL多執行個體中的一個執行個體時，通過TCP端口（port）來指定說要登陸的MySQL執行個體，此端口的配置是在MySQL配置檔案my.cnf中指定的。
例如：在mysql -uyunjisuan -p '123123' -h 192.168.200.101 -P 3307中，-P為端口參數，後面接具體的執行個體端口，端口是一種“邏輯連接配接位置”，是用戶端程式被分派到計算機上特殊服務程式的一種方式，強調提前在192.168.200.101上對yunjisuan使用者做了授權。
七， MySQL主從複制介紹
MySQL資料庫的主從複制方案，與使用scp/rsync等指令進行的檔案級别複制類似，都是資料的遠端傳輸，隻不過MySQL的主從複制是其自帶的功能，無需借助第三方工具，而且，MySQL的主從複制并不是資料庫磁盤上的檔案直接拷貝，而是通過邏輯的binlog日志複制到要同步的伺服器本地，然後由本地的線程讀取日志裡面的SQL語句，重新應用到MySQL資料庫中。
7.1 概述
- MySQL資料庫支援單向，雙向，鍊式級聯，環狀等不同業務場景的複制。在複制過程中，一台伺服器充當主伺服器（Master），接收來自使用者的内容更新，而一個或多個其他的伺服器充當從伺服器（Slave），接收來自主伺服器binlog檔案的日志内容，解析出SQL，重新更新到從伺服器，使得主從伺服器資料達到一緻。
- 如果設定了鍊式級聯複制，那麼，從伺服器（Slave）本身除了充當從伺服器外，也會同時充當其下面從伺服器的主伺服器。鍊式級聯複制類似A-->B-->C的複制形式。
下圖為單向主從複制架構邏輯圖，此架構隻能在Master端進行資料寫入
下圖為雙向主主複制邏輯架構圖，此架構可以在Master1端或Master2端進行資料寫入，或者兩端同時寫入資料（需要特殊設定）
下圖為線性級聯單向雙主複制邏輯架構圖，此架構隻能在Master1端進行資料寫入，工作場景中，Master1和master2作為主主互備，Slave1作為從庫，中間的Master2需要做特殊的設定。
下圖為環狀級聯單向多主同步邏輯架構圖，任意一個點都可以寫入資料，此架構比較複雜，屬于極端環境下的“成品”，一般場景慎用
在目前的生産工作中，MySQL主從複制都是異步的複制方式，既不是嚴格實時的資料同步，但是正常情況下給使用者的體驗是真實的。
7.2 MySQL主從複制的企業應用場景
MySQL主從複制叢集功能使得MySQL資料庫支援大規模高并發讀寫成為可能，同時有效地保護了實體伺服器當機場景的資料備份。
應用場景1：從伺服器作為主伺服器的實時資料備份
- 主從伺服器架構的設定可以大大加強MySQL資料庫架構的健壯性。例如：當主伺服器出現問題時，我們可以人工或設定自動切換到從伺服器繼續提供服務，此時從伺服器的資料與當機時的主資料庫幾乎是一緻的。
- 這類似NFS存儲資料通過inotify + rsync同步到備份的NFS伺服器，隻不過MySQL的複制方案是其自帶的工具。
- 利用MySQL的複制功能進行資料備份時，在硬體故障，軟體故障的場景下，該資料備份是有效的，但對于人為地執行drop，delete等語句删除資料的情況，從庫的備份功能就沒用了，因為從伺服器也會執行删除的語句。
應用場景2：主從伺服器實作讀寫分離，從伺服器實作負載均衡
- 主從伺服器架構可通過程式（PHP，java等）或代理軟體（mysql-proxy，Amoeba）實作對使用者（用戶端）的請求讀寫分離，即讓從伺服器僅僅處理使用者的select查詢請求，降低使用者查詢響應時間，以及同時讀寫在主伺服器上帶來的通路壓力。對于更新的資料（例如：update，insert，delete語句），則仍然交給主伺服器處理，確定主伺服器和從伺服器保持實時同步。
- 百度，淘寶，新浪等絕大多數的網站都是使用者浏覽頁面多于使用者釋出内容，是以通過在從伺服器上接收隻讀請求，就可以很好地減輕主庫的讀壓力，且從伺服器可以很容易地擴充為多台，使用LVS做負載均衡效果就非常棒了，這就是傳說中的資料庫讀寫分離架構。邏輯架構圖如下所示：
應用場景3：把多個從伺服器根據業務重要性進行拆分通路
可以把幾個不同的從伺服器，根據公司的業務進行拆分。例如：有為外部使用者提供查詢服務的從伺服器，有内部DBA用來資料備份的從伺服器，還有為公司内部人員提供通路的背景，腳本，日志分析及供開發人員查詢使用的從伺服器。這樣的拆分除了減輕主伺服器的壓力外，還可以使資料庫對外部使用者浏覽，内部使用者業務處理及DBA人員的備份等互不影響。
7.3 實作MySQL主從讀寫分離的方案
（1）通過程式實作讀寫分離（性能和效率最佳，推薦）
PHP和Java程式都可以通過設定多個連接配接檔案輕松地實作對資料庫的讀寫分離，即當語句關鍵字為select時，就去連接配接讀庫的連接配接檔案，若為update，insert，delete時，則連接配接寫庫的連接配接檔案。
通過程式實作讀寫分離的缺點就是需要開發人員對程式進行改造，使其對下層不透明，但這種方式更容易開發和實作，适合網際網路業務場景。
根據業務重要性拆分從庫方案
（2）通過開源的軟體實作讀寫分離
MySQL-proxy，Amoeba等代理軟體也可以實作讀寫分離功能，這些軟體的穩定性和功能一般，不建議生産使用。絕大多數公司常用的還是在應用端發程式實作讀寫分離。
（3）大型門戶獨立開發DAL層綜合軟體
百度，阿裡等大型門戶都有開發牛人，會花大力氣開發适合自己業務的讀寫分離，負載均衡，監控報警，自動擴容，自動收縮等一系列功能的DAL層軟體。
7.4 MySQL主從複制原理介紹
- MySQL的主從複制是一個異步的複制過程（雖然一般情況下感覺是實時的），資料将從一個MySQL資料庫（我們稱之為Master）複制到另一個MySQL資料庫（我們稱之為Slave），在Master與Slave之間實作整個主從複制的過程是由三個線程參與完成的。其中有兩個線程（SQL線程和I/O線程）在Slave端，另外一個線程（I/O線程）在Master端。
- 要實作MySQL的主從複制，首先必須打開Master端的binlog記錄功能，否則就無法實作。因為整個複制過程實際上就是Slave從Master端擷取binlog日志，然後再在Slave上以相同順序執行擷取的binlog日志中所記錄的各種SQL操作。
- 要打開MySQL的binlog記錄功能，可通過在MySQL的配置檔案my.cnf中的mysqld子產品（[mysqld]辨別後的參數部分）增加“log-bin”參數選項來實作，具體資訊如下。
[mysqld]
log-bin=/data/
提示：
有些同學把log-bin放在了配置檔案結尾，而不是[mysqld]辨別後，進而導緻配置複制不成功。
7.5 MySQL主從複制原理過程較長的描述
下面簡單描述MySQL Replication的複制原理過程
1）在Slave伺服器上執行start slave指令開啟主從複制開關，開始進行主從複制
2）此時，Slave伺服器的I/O線程會通過在Master上已經授權的複制使用者權限請求連接配接Master伺服器，并請求從指定binlog日志檔案的指定位置（日志檔案名和位置就是在配置主從複制服務時執行change master指令指定的）之後開始發送binlog日志内容。
3）Master伺服器接收到來自Slave伺服器的I/O線程的請求後，其上負責複制的I/O線程會根據Slave伺服器的I/O線程請求的資訊分批讀取指定binlog日志檔案指定位置之後的binlog日志資訊，然後傳回給Slave端的I/O線程。傳回的資訊中除了binlog日志内容外，還有在Master伺服器端記錄的新的binlog檔案名稱，以及在新的binlog中的下一個指定更新位置。
4）當Slave伺服器的I/O線程擷取到Master伺服器上I/O線程發送的日志内容，日志檔案及位置點後，會将binlog日志内容依次寫到Slave端自身的Relay Log（即中繼日志）檔案（MySQL-relay-bin.xxxx）的最末端，并将新的binlog檔案名和位置記錄到master-info檔案中，以便下一次讀取Master端新binlog日志時能夠告訴Master伺服器從新binlog日志的指定檔案及位置開始請求新的binlog日志内容。
5）Slave伺服器端的SQL線程會實時檢測本地Relay Log中I/O線程新增加的日志内容，然後及時地把Relay Log檔案中的内容解析成SQL語句，并在自身Slave伺服器上按解析SQL語句的位置順序執行應用這些SQL語句，并在relay-log.info中記錄目前應用中繼日志的檔案名及位置點。
經過了上面的過程，就可以確定在Master端和Slave端執行了同樣的SQL語句。當複制狀态正常時，Master端和Slave端的資料是完全一樣的。當然，MySQL的複制機制也有一些特殊情況，具體請參考官方的說明，大多數情況下，同學們不用擔心。
MySQL Replication的複制原理邏輯圖
特别說明：
當企業面試MySQL主從複制原理時，不管是面試還是筆試，都要盡量畫圖表達，而不是口頭講或文字描述，面試時可以找黑闆或拿出紙來給面試官詳細講解。
下面針對MySQL主從複制原理的重點進行小結：
- 主從複制是異步的邏輯的SQL語句級的複制
- 複制時，主庫有一個I/O線程，從庫有兩個線程，即I/O和SQL線程
- 實作主從複制的必要條件是主庫要開啟記錄binlog功能
- 作為複制的所有MySQL節點的server-id都不能相同。
- binlog檔案隻記錄對資料庫有更改的SQL語句（來自主資料庫内容的變更），不記錄任何查詢（如select，show）語句。
八，MySQL主從複制實踐
8.1 主從複制實踐準備
8.1.1 主從複制資料庫實戰環境準備
MySQL主從複制實踐對環境的要求比較簡單，可以是單機單資料庫多執行個體的環境，也可以是兩台伺服器，每個機器一個獨立資料庫的環境。本文以單機資料庫多執行個體的環境為例講解。執行個體端口資訊檢視如下：
[[email protected] ~]# ss -antup | grep 330
tcp LISTEN
提示：
這裡把3306執行個體作為主庫，3307執行個體作為從庫，如果根據前面的内容配置了MySQL多執行個體環境，直接開啟多執行個體環境使用即可。
8.1.2 定義主從複制需要的伺服器角色
這裡的主從複制技術是針對前面的内容以單機資料庫多執行個體環境來講的。一般情況下，小企業在做正常的主從複制時，主從伺服器多數在不同的機器上，并且監聽的端口均為預設的3306.雖然不在同一個機器上，但是步驟和過程卻是一樣的。
同學們在掌握了但資料庫多執行個體的同步方法後，可以自己适當擴充，完成異機相同端口之間的主從複制。
8.1.3 資料庫中英文名稱約定
8.2 在主庫Master上執行操作配置
8.2.1 設定server-id值并開啟binlog功能參數
根據之前介紹的MySQL主從複制原理我們知道，要實作主從複制，關鍵是要開啟binlog日志功能，是以，首先來打開主庫的binlog日志參數。
1）修改主庫的配置檔案。執行vi /data/3306/my.cnf,編輯多執行個體3006的my.cnf配置檔案，按如下内容修改兩個參數：
[mysqld]
server-id =
提示：
上面的兩個參數要放在my.cnf中的[mysqld]子產品下，否則會出錯。
不同執行個體間server-id的值不可以重複
要先在my.cnf配置檔案中查找相關參數，并按要求修改。若發現不存在，再添加參數，切記，參數不能重複。
修改my.cnf配置後，需要重新開機動資料庫，指令為：/data/3306/mysql restart ,注意要确認真正重新開機了。
2）檢查配置參數後的結果，如下：
[email protected] ~]# egrep "server-id|log-bin" /data/3306/my.cnf
server-id =
3）重新開機主庫MySQL服務，指令如下：
[[email protected] ~]# /data/
4）登陸資料庫，檢查參數的更改情況，如下：
8.2.2 在主庫上建立用于主從複制的賬号
根據主從複制的原理，從庫要想和主庫同步，必須有一個可以連接配接主庫的賬号，并且這個賬号是主庫上建立的，權限是允許主庫的從庫連接配接并同步資料。
1）登陸MySQL3306執行個體主資料庫，指令如下：
[r[email protected] ~]# mysql -uroot -p123123 -S /data/3306/mysql.sock
2)建立用于從庫複制的賬号yunjisuan，指令如下：
mysql> grant replication slave on *.* to 'yunjisuan'@'192.168.0.%' identified by 'yunjisuan123';
Query OK, 0 rows affected (0.00 sec)
#語句說明：
1）replication slave為mysql同步的必須權限，此處不要授權all權限
2）*.* 表示所有庫所有表，也可以指定具體的庫和表進行複制。例如yunjisuan.test中，yunjisuan為庫名，test為表名 3）'yunjisuan'@'192.168.0.%' yunjisuan為同步賬号。192.168.0.%為授權主機網段，使用了%表示允許整個192.168.0.0網段可以用yunjisuan這個使用者通路資料庫 4）identified by 'yunjisuan123'; yunjisuan123為密碼，實際環境下設定的複雜些為好
建立完賬号并授權後，需要重新整理權限，使授權的權限生效
mysql> flush privileges; #重新整理權限
Query OK, 0 rows affected (0.00 sec)
3）檢查主庫建立的yunjisuan複制賬号指令及結果如下：
mysql> select user,host from mysql.user;
+-----------+-------------+
| user | host |
+-----------+-------------+
| root |
mysql> select user,host from mysql.user where user='yunjisuan'; #where是SQL查詢語句的條件
+-----------+-------------+
| user | host |
+-----------+-------------+
| yunjisuan |
8.2.3 實作對主資料庫鎖表隻讀
1）對主資料庫鎖表隻讀（目前視窗不要關掉）的指令如下：
mysql> flush table with read lock;
Query OK,
提示：
在引擎不同的情況下，這個鎖表指令的時間會受下面參數的控制。鎖表時，如果超過設定時間不操作會自動解鎖。
預設情況下自動解鎖的時長參數值如下：
mysql> show variables like '%timeout%';
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout |
2）鎖表後檢視主庫狀态。可通過目前binlog日志檔案名和二進制binlog日志偏移量來檢視，結果如下：
注意，show master status;指令顯示的資訊要記錄在案，後面的從庫導入全備後，繼續和主庫複制時就是要從這個位置開始。
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.
或者新開一個指令行視窗，用如下指令檢視鎖表後的主庫binlog位置點資訊：
[[email protected] ~]# mysql -uroot -p123123 -S /data/3306/mysql.sock -e "show master status"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.
3）鎖表後，一定要單開一個新的SSH視窗，導出資料庫的所有資料，如果資料量很大（50GB以上），并且允許停機，可以停庫直接打包資料檔案進行遷移，那樣更快。
[[email protected] ~]# mkdir -p /server/backup
[[email protected] ~]# mysqldump -uroot -p123123 -S /data/3306/mysql.sock --events -A -B | gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
#注意：-A表示備份所有庫；-B表示增加use DB和 drop 等（導庫時會直接覆寫原有的）
[[email protected] ~]# ll /server/backup/mysql_bak.2017-07-21.sql.gz
-rw-r--r--.
8.2.4 把主庫導出的MySQL資料遷移到從庫
下面主要講解單資料庫多執行個體的主從配置，也就是說，mysqldump備份的3306執行個體的資料和要恢複的3307執行個體在一台機器上，是以無需異地複制拷貝。想檢視主庫導出的資料，如下：
8.3 在MySQL從庫上執行的操作過程
8.3.1 設定server-id值并關閉binlog功能參數
- 資料庫的server-id一般在一套主從複制體系内是唯一的，這裡從庫的server-id要和主庫及其他從庫的不同，并且要注釋掉從庫的binlog參數配置，如果從庫不做級聯複制，并且不作為備份用，就不要開啟binlog，開啟了反而會增加從庫磁盤I/O等的壓力。
- 但是，有以下兩種情況需要打開從庫的binlog記錄功能，記錄資料庫更新的SQL語句：
- 級聯同步A-->B-->C中間的B時，就要開啟binlog記錄功能。
- 在從庫做資料庫備份，資料庫備份必須要有全備和binlog日志，才是完整的備份。
（1）修改配置檔案，配置從庫1的相關參數
執行vi /data/3307/my.cnf,編輯my.cnf配置檔案，按如下内容修改兩個參數：
[mysqld]
server-id =
提示：
上面兩參數要放在my.cnf中的[mysqld]子產品下，否則會出錯。
server-id的值不能和任何MySQL執行個體重複。
要先在檔案中查找相關參數按要求修改。若發現不存在，再添加參數，切記，參數不能重複。
修改my.cnf配置後需要重新開機資料庫，指令為：/data/3307/mysql restart,注意要确認真正重新開機了。
（2）檢查配置參數後的結果
指令如下：
[[email protected] ~]# egrep "server-id|log-bin" /data/3307/my.cnf
server-id =
（3）重新開機3307的從資料庫
指令如下：
[[email protected] ~]# /data/3307/mysql restart
Restarting MySQL...
Stoping MySQL...
Starting MySQL....
[[email protected] ~]# ss -antup | grep 3307
tcp LISTEN
（4）登陸資料庫檢查參數的改變情況
指令如下：
8.3.2 把從主庫mysqldump導出的資料恢複到從庫
操作指令如下：
[[email protected] ~]# cd /server/backup/
[[email protected] backup]# ls -l
total
8.3.3 登陸3307從庫，配置複制參數
（1）MySQL從庫連接配接主庫的配置資訊如下：
CHANGE MASTER TO
MASTER_HOST='192.168.0.200', #這裡是主庫的IP
MASTER_PORT=3306, #這裡是主庫的端口，從庫端口可以和主庫不同
MASTER_USER='yunjisuan', #這裡是主庫上建立的用于複制的使用者yunjisuan
MASTER_PASSWORD='yunjisuan123', #這裡是yunjisuan使用者的密碼
MASTER_LOG_FILE='mysql-bin.000001', #這裡是show master status時檢視到的二進制日志檔案名稱，注意不能多空格 MASTER_LOG_POS=533; #這裡是show master status時檢視到的二進制日志偏移量，注意不能多空格 #提示：字元串用單引号括起來，數值不用引号，注意内容前後不能有空格。
（2）登陸資料庫後，去掉上述語句中的注釋，執行如下：
上述操作的原理實際上是把使用者密碼等資訊寫入從庫新的master.info檔案中
8.4 啟動從庫同步開關，測試主從複制配置情況
（1）啟動從庫主從複制開關，并檢視複制狀态
相關語句如下：
主從同步是否成功，最關鍵的為下面的3項狀态參數：
- [x] :Slave_IO_Running: Yes,這個時I/O線程狀态，I/O線程負責從從庫到主庫讀取binlog日志，并寫入從庫的中繼日志，狀态為Yes表示I/O線程工作正常。
- [x] :Slave_SQL_Running: Yes,這個是SQL線程狀态，SQL線程負責讀取中繼日志（relay-log）中的資料并轉換為SQL語句應用到從資料庫中，狀态為Yes表示I/O線程工作正常。
- [x] :Seconds_Behind_Master:0,這個是複制過程中從庫比主庫延遲的秒數，這個參數極度重要，但企業裡更準确地判斷主從延遲的方法為：在主庫寫時間戳，然後從庫讀取時間戳，和目前資料庫時間進行比較，進而認定是否延遲。
（2）測試主從複制結果
在主庫上寫入資料，然後觀察從庫的資料狀況。
8.5 MySQL主從複制配置步驟小結
MySQL主從複制配置完整步驟如下：
- 準備兩台資料庫環境或單台多執行個體環境，确定能正常啟動和登陸
- 配置my.cnf檔案：主庫配置log-bin和server-id參數；從庫配置server-id，該值不能和主庫及其他從庫一樣，一般不開啟從庫log-bin功能。注意，配置參數後要重新開機才能生效。
- 登陸主庫，增加從庫連接配接主庫同步的賬戶，例如：yunjisuan，并授權replication slave同步的權限。
- 登陸主庫，整庫鎖表flush table with read lock（視窗關閉後即失效，逾時參數設定的時間到了，鎖表也失效），然後show master status檢視binlog的位置狀态。
- 新開視窗，在Linux指令行備份導出原有的資料庫資料，并拷貝到從庫所在的伺服器目錄。如果資料庫資料量很大，并且允許停機，可以停機打包，而不用mysqldump。
- 導出主庫資料後，執行unlock tables解鎖主庫。
- 把主庫導出的資料恢複到從庫
- 根據主庫的show master status檢視到的binlog的位置狀态，在從庫執行change master to....語句。
- 從庫開啟複制開關，即執行start slave；。
- 從庫show slave status\G,檢查同步狀态，并在主庫進行更新測試。
8.6 MySQL主從複制線程狀态說明及用途
8.6.1 MySQL主從複制主庫I/O線程狀态說明
（1）登陸主資料庫檢視MySQL線程的同步狀态
mysql> show processlist\G
***************************
下圖中列出了主伺服器binlog Dump線程中State列的最常見狀态。如果你沒有在主伺服器上看見任何binlog Dump線程，則說明複制沒有運作，二進制binlog日志由各種事件組成，事件通常會為更新添加資訊。
（2）登陸從資料庫檢視MySQL線程工作狀态
從庫有兩個線程，即I/O和SQL線程。從庫I/O線程的狀态如下：
mysql> show processlist\G
***************************
下圖列出了從伺服器的I/O線程的State列的最常見的狀态。該狀态也出現在Slave_IO_State列，由SHOW SLAVE STATUS顯示。
下圖列出了從伺服器的SQL線程的State列的最常見狀态
8.6.2 檢視MySQL線程同步狀态的用途
- 通過MySQL線程同步狀态可以看到同步是否正常進行，故障的位置是什麼，另外還可檢視資料庫同步是否完成，可用于主庫當機切換資料庫或人工資料庫主從切換遷移等。
- 例如：主庫當機，要選擇最快的從庫将其提升為主庫，就需要檢視主從庫的線程狀态，如果主從複制在正常情況下進行角色切換，也需要檢視主從庫的線程狀态，根據複制狀态确定更新是否完成。
8.7 MySQL主從複制更多應用技巧實踐
8.7.1 工作中MySQL從庫停止複制故障案例
模拟重制故障的能力是運維人員最重要的能力。下面就來次模拟操作。先在從庫建立一個庫，然後去主庫建立同名的庫來模拟資料沖突，指令如下：
mysql> show slave status\G
***************************
對于該沖突，解決辦法如下
辦法一：關閉從同步，調動sql_slave指針
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> set global sql_slave_skip_counter=1; #将sql線程同步指針向下移動一個，如果多次不同步，可以重複操作 Query OK, 0 rows affected (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)
對于普通的網際網路業務，上述的移動指針的操作帶來的問題不是很大。當然，要在确認不影響公司業務的前提下。
若是在企業場景下，對目前業務來說，解決主從同步比主從不一緻更重要，如果主從資料一緻也是很重要的，那就再找個時間恢複這個從庫。
是主從資料不一緻更重要，還是保持主從同步持續狀态更重要，要根據業務選擇。這樣Slave就會與Master同步了，主要關鍵點如下：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master:
辦法二：根據可以忽略的錯誤号事先在配置檔案中配置，跳過指定的不影響業務資料的錯誤，例如：
[[email protected] ~]# grep slave-skip /data/3306/my.cnf
slave-skip-errors =
其他可能引起複制故障的原因：
- MySQL自身的原因及人為重複插入資料。
- 不同的資料庫版本會引起不同步，低版本到高版本可以，但是高版本不能往低版本同步。
- MySQL的運作錯誤或程式bug
- binlog記錄模式，例如：row level模式就比預設的語句模式要好。
8.7.2 讓MySQL從庫記錄binlog日志的方法
從庫需要記錄binlog的應用場景：目前的從庫還要作為其他從庫的主庫，例如級聯複制或雙主互為主從場景的情況下。下面介紹從庫記錄binlog日志的方法。
在從庫的my.cnf中加入如下參數，然後重新開機服務生效即可。
log-slave-updates #必須要有這個參數
log-bin = /data/
8.7.3 MySQL主從複制叢集架構的資料備份政策
-
有主從複制了，還需要做定時全量加增量備份麼？答案是肯定的！
因為，如果主庫有語句級誤操作（例如：drop database yunjisuan；），從庫也會執行drop database yunjisuan；，這樣MySQL主從庫就都删除了該資料。
-
把從庫作為資料庫備份伺服器時，備份政策如下：
高并發業務場景備份時，可以選擇在一台從庫上備份（Slave5），把從庫作為資料庫備份伺服器時需要在從庫開啟binlog功能，其邏輯圖如下所示：
步驟如下：
1）選擇一個不對外提供服務的從庫，這樣可以確定和主庫更新最接近，專門用于做資料備份。
2）開啟從庫的binlog功能
備份時可以選擇隻停止SQL線程，停止應用SQL語句到資料庫，I/O線程保留工作狀态，執行指令為stop slave sql_thread;,備份方式可以采取mysqldump邏輯備份或直接實體備份，例如：使用cp，tar（針對/data目錄）工具或xtrabackup（第三方的實體備份軟體）進行備份，則邏輯備份和實體備份的選擇，一般是根據總的備份資料量的多少進行選擇的，資料量低于30G，建議選擇mysqldump邏輯備份方法，安全穩定，最後把全備和binlog資料發送到備份伺服器上留存。
8.7.4 MySQL主從複制延遲問題的原因及解決方案
問題一：主庫的從庫太多，導緻複制延遲
從庫數量以3~5個為宜，要複制的從節點數量過多，會導緻複制延遲。
問題二：從庫硬體比主庫差，導緻複制延遲。
檢視Master和Slave的系統配置，可能會因為機器配置不當，包括磁盤I/O，CPU，記憶體等各方面因素造成複制的延遲。這一般發生在高并發大資料量寫入場景中。
問題三：慢SQL語句太多
假如一條SQL語句執行時間是20秒，那麼從執行完畢到從庫上能查到資料至少需要20秒，這樣就延遲20秒了。
一般要把SQL語句的優化作為正常工作，不斷的進行監控和優化，如果單個SQL的寫入時間長，可以修改後分多次寫入。通過檢視慢查詢日志或show full processlist指令，找出執行時間長的查詢語句或大的事務。
問題四：主從複制的設計問題
例如，主從複制單線程，如果主庫寫并發太大，來不及傳送到從庫，就會導緻延遲。
更高版本的MySQL可以支援多線程複制，門戶網站則會自己開發多線程同步功能。
問題五：主從庫之間的網絡延遲
主從庫的網卡，網線，連接配接的交換機等網絡裝置都可能成為複制的瓶頸，導緻複制延遲，另外，跨公網主從複制很容易導緻主從複制延遲。
問題六：主庫讀寫壓力大，導緻複制延遲。
主庫硬體要搞好一點，架構的前端要加buffer及緩存層。
8.7.5 通過read-only參數讓從庫隻讀通路
read-only參數選項可以讓從伺服器隻允許來自從伺服器線程或具有SUPER權限的資料庫使用者進行更新，確定從伺服器不接受來自使用者端的非法使用者更新。
read-only參數允許資料庫更新的條件為：
- 具有SUPER權限的使用者可以更新，不受read-only參數影響，例如：管理者root。
- 來自從伺服器線程可以更新，不受read-only參數影響，例如：前文的yunjisuan使用者。
- 再生産環境中，可以在從庫Slave中使用read-only參數，確定從庫資料不被非法更新。
read-only參數的配置方法如下：
方法一：直接帶 --read-only參數啟動或重新開機資料庫，
使用
killall mysqld
或
mysqladmin -uroot -p123123 -S /data/3307/mysql.sock shutdown
mysqld_safe --defaults-file=/data/3307/my.cnf --read-only &
方法二：在my.cnf裡[mysqld]子產品下加read-only參數重新開機資料庫，配置如下：
[mysqld]
read-only
九，本節重點回顧
- MySQL多執行個體的實作原理及實戰部署
- MySQL主從複制的原理（面試常問）
- MySQL主從複制的實踐
- MySQL主從複制故障解決思路
- MySQL主從複制延遲原因及解決思路
- MySQL主從複制叢集，從庫備份的思想和思路
轉載于:https://www.cnblogs.com/fengxingzhe/p/11032867.html