分庫分表就是把數據打散，減小單庫的壓力 主從為了讀寫分離，大部分讀多寫少，單純的主從的單一從多， 分庫分表 主多從多 show slave status;為空 意味著不是從庫，是不是主庫不知道 show master status; 例如 91：3301 和 91：3308作為主庫 #無限擴充的架構(數據庫) ##大網站數據庫會遇到什么問題 **大網站的大指什么?** 數據量大 訪問量高 **兩者必然有聯系嗎?** 博客、新聞 搶購、社交 **如何處理才能無限擴充?** 只需要運維添加機器和設備,軟件架構不用修改 **單機性能** 提高機器性能 換用更強大的db類型 **無限擴充** 分表:縱向與橫向分表 分表:業務分庫與數據分庫 擴充:只需要添加數據庫與表的數量 討論:如何無縫過渡擴充? 分散訪問:多庫讀取 集中寫入:數據一致性 多主庫:寫入比訪問多的場景 擴充:只需要添加從庫機器 討論:寫入過多在業務上如何處理? #MySql的binlog與備份還原 ##多實例運行 多個端口 mysqld_multi 步驟: 配置 mysqld_multi.cnf ~~~ /var/lib/mysql56/bin/mysqld_multi ‐‐defaults‐extra‐file=/etc/my_multi.cnf report /var/lib/mysql56/bin/mysqld_multi ‐‐defaults‐extra‐file=/etc/my_multi.cnf start ‐‐log=./test.log ~~~ ##binlog作用 **數據恢復** **主從同步** **log-bin log-slave-updates** ##binlog限制 性能慢1% loaddata不能恢復 ##MySQL啟用binlog ##啟用binlog my.cnf 添加 log_bin = mysql_bin 其他參數: max_binlog_size 分割文件,但一個事物不會寫入2個日志文件 binlog-do-db、binlog-ignore-db 選擇特定數據庫寫入binlog ##查看是否啟用 a) 查看數據文件,mysql-bin.index b) mysql> show global variables like 'sql_log_bin'; ##MySQL處理binlog ###sql查看 ~~~ mysql> show binlog events in ‘mysql-bin.000002’; ~~~ ###mysqlbinlog查看 ~~~ mysqlbinlog mysql-bin.000006 --start-position=120 --stop-position=280 mysqlbinlog mysql-bin.000006 --start-datetime='2014-7-2 8:10:00' --stop- datetime='2014-07-03 00:00:00’ ~~~ ###處理binlog ~~~ mysql> flush logs; 產生新的binlog文件,什么情況下使用? mysql> reset master; 重置binlog,會刪除所有的binlog mysql> PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 2 DAY); 刪除指定 ~~~ ##利用binlog恢復數據庫 ###恢復流程 禁用binlog(為什么) -> 利用mysqlbinlog導出恢復(管道或文件) -> 啟用binlog mysql> set global sql_log_bin=0 在新的會話中,才生效,當前會話不生效 ###增量恢復場景 恢復全備 -> 確定恢復點 -> 導入binlog到指定時間或位置 ###刪除數據恢復場景 恢復全備 -> 查找刪除位置 -> 導入binlog到刪除位置前 -> 從刪除位置后恢復余下的 ###亂碼問題及解決方案 大多數情況下,binlog 直接導出成 SQL 語句文件,再入庫即可 但是在有中文字符的情況下,會出錯。 辦法: 1、set global sql_log_bin=0 先關閉 binlog 寫入。 2、得到想要用的 SQL 語句 mysql -uroot -S /tmp/mysql_3303.sock -e "show binlog events in 'mysql- bin.000002'"|cut -f 6 > quanzhan.sql 3、去掉前面兩行,Info 和 Server ver: 5.6.20-log, Binlog ver: 4 4、再 cat quanzhan.sql |awk '{printf "%s;\n",$0}' > quanzhan.sql.ok 這樣為每個 SQL 語句最后都加上了 ; 號 5、再導入 mysql -uroot -S /tmp/mysql_3303.sock --default-character-set=utf8 < quanzhan.sql.ok 這樣可以完成恢復。 ###通過 mysqldump 導出備份 注意 mysqldump 會鎖表,當然可以加 參數消除鎖 mysqldump備份不鎖表:加上--lock-tables=false參數,如果是innodb,則加上--single- transcation比較好。 但是? 在備份時,不僅僅備份,留下當時的時間點和狀態點 ~~~ mysql -uroot -S /tmp/mysql_3303.sock -e "stop slave; show slave status\G" > status.txt mysqldump --single-transaction --flush-logs --master-data=2 -uroot -S /var/lib/mysql56/data/mysql.sock -d quanzhan -p ~~~ ###數據恢復場景 恢復適合的地方,出了問題重建 還原做擴容 做切庫 #單主多從架構實踐 ##主從配置步驟 ###所有服務器啟用binlog,配置server-id, 在my.cnf 中開啟 log-bin ###主庫創建同步賬號,配置同步位置 ~~~ grant ALL PRIVILEGES on db.* to ‘user’@‘ip' identified by ‘ pass' with grant option; ~~~ ###從庫設置同步信息,啟用從庫,并查看同步狀態 ~~~ CHANGE MASTER TO XXX ~~~ ###測試數據庫,注意防火墻 ###程序需要處理的問題:數據同步有時間差!!! ##主從配置 ###所有服務器 啟用binlog,配置文件設置log-bin=mysql_bin 每臺服務器設置server-id,所有機器不能重復 配置文件里設置server-id=1,2,3.... ###主庫 創建同步賬號: `GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* to repl@'%' IDENTIFIED by 'replpass';` 記錄同步位置: `show master status;` 記下File和Position,從庫從這個位置開始同步 ###創建同步賬號: GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* to repl@'%' IDENTIFIED by 'replpass'; 記錄同步位置: show master status; 記下File和Position,從庫從這個位置開始同步 ###每臺從庫 設置同步信息: `change master to master_host='192.168.2.110', master_user='repl', master_password='replpass',MASTER_LOG_FILE='mysql- bin.000003',MASTER_LOG_POS=338;` 啟用同步: `start slave;` 查看同步狀態(Slave_IO_Running和Slave_SQL_Running都為yes即正常): `show slave status\G` ###從庫再做主庫 1、從庫再做主庫,可以做擴容 2、可以更大規模地擴充節點 3、開啟 log_slave_updates ###切庫擴容 1、第一步,規劃好擴容,要分成幾組 2、第二步,通過備份,恢復數據庫 3、第三步,導入備份的修改時間節點,導入數據 4、第四步,同步數據,跟上數據庫 5、第五步,修改配置庫 6、第六步,重新加載配置 ###數據測試 在主庫寫入數據,查看每個從庫的數據有沒有同步過來 如果同步狀態有問題,注意防火墻,打開3306等端口 注意通過show slave status 、show master status 查看狀態 #多主多從  ##環形多點配置(node A)  ~~~ # node A - water [mysqld] server-id = 10 log-bin = mysql-bin log-slave-updates replicate-same-server-id auto_increment_increment auto_increment_offset master-host master-user master-password report-host = 0 = 10 = 1 = earth.stardata.it = nodeAuser # node D = nodeApass = nodeA ~~~ ##環形多點配置(node B) ~~~ # Node B - air [mysqld] server-id = 20 log-bin = mysql-bin log-slave-updates replicate-same-server-id auto_increment_increment auto_increment_offset master-host master-user master-password report-host = 0 = 10 = 2 = water.stardata.it = nodeBuser # node A = nodeBpass = nodeB ~~~ ##環形多點特點 ###優點: 適合寫入量特別大的情況 服務器均衡壓力 故障處理簡單,只需要當機的下一個環點切換到上一個環點,不影響其他機器 ###缺點 機器多時,同步時間慢 只適合少部分情況,大部分網站都是讀取為主的