mysql數(shù)據(jù)庫集群架構(gòu)主從、雙主架構(gòu)部署及數(shù)據(jù)備份

1.mysql主從與雙主結(jié)構(gòu)的優(yōu)缺點：

1.1主從結(jié)構(gòu)：

1.1.1 數(shù)據(jù)存在鏡像和數(shù)據(jù)冗余，可以防止單一主機(jī)的數(shù)據(jù)丟失，提高數(shù)據(jù)的安全性。

1.1.2在從服務(wù)器上做數(shù)據(jù)備份，這樣不影響主服務(wù)器的正常運行。如mysqldump備份時會加表所。

1.1.3在從服務(wù)器上做數(shù)據(jù)報表和數(shù)據(jù)統(tǒng)計，這樣可以避免生產(chǎn)服務(wù)器的訪問壓力過大；

1.1.4如果使用mysql proxy，在業(yè)務(wù)上可以實現(xiàn)讀寫分離。讓讀操作在從服務(wù)器上執(zhí)行，減小主服務(wù)器的負(fù)擔(dān)。不錯的項目，可惜官方半途夭折了，不建議用，后期維護(hù)不便；可以考慮JDBC文件中配置，實現(xiàn)讀寫分離；

1.2 雙主結(jié)構(gòu)

優(yōu)點：高可用、并發(fā)能力大

缺點：同步數(shù)據(jù)時容易出錯，不易維護(hù)（slave-skip-errors=all）

2配置文件

2.1主從配置

主：

log-bin-trust-function-creators=1

log-bin=mysql-bin

server-id=1

sync-binlog=1

log-bin-index=/home/mysql/mysql-log/mysql-bin.index

log-bin=/home/mysql/mysql-log/mysql-bin

binlog_format=mixed

從：

relay-log=mysql-relay

server-id=2

read-only=TRUE

replicate-ignore-db=mysql???//屏蔽對mysql庫的同步

replicate-do-db=node1???????//同步數(shù)據(jù)庫名稱

2.2雙主配置

主1：

log-bin=mysql-bin

relay-log=mysql-relay

server-id=1

sync-binlog=1

auto-increment-increment=1

auto-increment-offset=2

主2:

log-bin=mysql-bin

relay-log=mysql-relay

server-id=2

sync-binlog=1

auto-increment-increment=2

auto-increment-offset=2

2.3 參數(shù)優(yōu)化

1. innodb_buffer_pool_size

這個參數(shù)和MyISAM的key_buffer_size有相似之處，但也是有差別的。這個參數(shù)主要緩存innodb表的索引，數(shù)據(jù)，插入數(shù)據(jù)時的緩 沖。為Innodb加速優(yōu)化首要參數(shù)。

show engine innodb status \G;

數(shù)據(jù)大小＋１０％左右做為這個參數(shù)的值。

另外一個和此相關(guān)的參數(shù)：innodb_additional_mem_pool_size，來存放 Innodb的內(nèi)部目錄

這個值不用分配太大，系統(tǒng)可以自動調(diào)。如果表比較多，可以適當(dāng)?shù)脑龃蟆?/p>

show global variables like ? 'innodb_additional_mem_pool_size';

2. innodb_flush_log_at_trx_commit（日志刷新設(shè)置）

show global variables like ? 'innodb_flush_log_at_trx_commit';

0:每秒同步，并執(zhí)行磁盤flush操作

1：每事物同步，并執(zhí)行磁盤flush操作

2：每事物同步，但不執(zhí)行磁盤的flush操作

1最安全0最保守2最性能

普通硬盤情況下，innodb_flush_log_at_trx_commit參數(shù)分別是1和2的情況，兩者差別巨大，差異幾乎到了9倍。

在做了raid的情況下，innodb_flush_log_at_trx_commit參數(shù)分別是1和2的情況，兩者有10%左右的差異。

3.wait-timeout和interactive_timeout

wait-timeout?=?100?指的是MySQL在關(guān)閉一個非交互的連接之前所要等待的秒數(shù)interactive_timeout?=?100指的是mysql在關(guān)閉一個交互的連接之前所要等待的秒數(shù)nteractive_timeout和wait_timeout的默認(rèn)值都是2880（8小時）。當(dāng)這兩個參數(shù)同時出現(xiàn)在[mysqld]里時，會以interactive_timeout的值為準(zhǔn)。也就是說不管wait_timeout的值是多少，用show variables like ? '%timeout%';查看時顯示的兩個值都是一樣的，并且都是interactive_timeout的值。

這里設(shè)置innodb_lock_wait_timeout = 120? ? 就可以了

實際報錯：

mysql> set ? global wait_timeout=600;

Query OK, 0 rows affected ? (0.00 sec)

mysql> set ? global interactive_timeout=600;

Query OK, 0 rows ? affected (0.00 sec)

4.慢查詢?nèi)罩?/p>

show variables like "%slow%";

log-slow-queries=/home/mysql/slowlog/slowquery.log

(指定日志文件存放位置，可以為空，系統(tǒng)會給一個缺省的文件host_name-slow.log)

long_query_time=2

(記錄超過的時間，默認(rèn)為10s)

log-queries-not-using-indexes

(log下來沒有使用索引的query,可以根據(jù)情況決定是否開啟)

log-long-format

(如果設(shè)置了，所有沒有使用索引的查詢也將被記錄)

5、二進(jìn)制日志

binlog-format=mixed

1）statement:修改數(shù)據(jù)的sql

優(yōu)點：減少了bin-log日志量，節(jié)約了I/O性能

缺點：為了這些語句能在slave上正確運行。因此還必須記錄每條在執(zhí)行的時候的一些相關(guān)信息，mysql的復(fù)制像一些特定函數(shù)功能slave可與master上要保持一致會有很多相關(guān)問題（如sleep（）函數(shù)，last_insert_id(),以及user-defind function（udf)會出現(xiàn)的問題。

2）row：記錄那一條記錄被修改成什么了

優(yōu)點：解決無法被正確復(fù)制的問題

缺點：產(chǎn)生大量的日志內(nèi)容。

3）mixed：是以上level的混合使用。

查看二進(jìn)制日志（要在目錄binlog目錄下）

mysqlbinlog? -u root -proot--start-position=106 ? --stop-position=187 mysql-bin.000001

滾動：

service MySQld start

flush logs

刪除：

show binary logs；

purge binary logs to 'mysql-bin-000003'

6. innodb_thread_concurrency

最大的線程數(shù)

1) 最少設(shè)置為(num_disks+num_cpus)*2。

2) 可以通過設(shè)置成1000來禁止這個限制

7. innodb_flush_method

設(shè)置InnoDB同步IO的方式：

1) Default – 使用fsync（）。

2) O_SYNC 以sync模式打開文件，通常比較慢。

3) O_DIRECT，在Linux上使用Direct IO。可以顯著提高速度，特別是在RAID系統(tǒng)

上。避免額外的數(shù)據(jù)復(fù)制和double buffering（mysql buffering 和OS buffering）。

設(shè)置：

innodb_flush_method=O_DIRECT

innodb_max_dirty_pages_pct＝75

8. innodb_log_buffer_size：

磁盤速度是很慢的，直接將log寫道磁盤會影響InnoDB的性能，該參數(shù)設(shè)定了log buffer的大小，一般4M。如果有大的blob操作，可以適當(dāng)增大。

3.主從同步

第一步master將改變記錄到二進(jìn)制日志(binary log)中（這些記錄叫做二進(jìn)制日志事件，log-bin）；

第二步slave將master的binary log events拷貝到它的中繼日志(relay log)；

第三步slave重放中繼日志中的事件，將改變反映它自己的數(shù)據(jù)。

具體過程如圖所示：

4.備份與還原

4.1壓縮工具選擇

工具

壓縮前

壓縮后

用時

tar –zcf

150M

9.1M

3秒

tar –Jcf

150M

7.2M

25秒

zip

150M

9.2M

3秒

gzip

150M

不支持壓縮目錄

bzip2

150M

不支持壓縮目錄

xz

150M

不支持壓縮目錄

經(jīng)過在tar –zcf 、tar –Jcf、zip、gzip、bzip2、xz6款壓縮工具測試對比，選擇了tar –zcf為壓縮工具，把tar –zcf壓縮后的包再用xz壓縮包大小為9.0M；

4.2 備份數(shù)據(jù)庫

完全備份腳本：

Vi mysql-1dayback.sh

#/bin/bash

bktime=$(date +%F)

cd /home/mysql-data-bak/

#打包壓縮文件

tar -zcf? mysqlback_$(date +%F-%H-%M-%S).tar.xz? day-*

#壓縮daybak/目櫸有文

xz day-*

#將打包文倀°allbk目

mv?? *.xz? ? allbak/

#刪除原

rm -rf ? /home/mysql-data-bak/day-wanquan/*?? ? /home/mysql-data-bak/day-zengliang/*

#每日完全備份一次

/usr/bin/innobackupex ? --user=root --password=root /home/mysql-data-bak/day-wanquan/

增量備份：

Vi mysql-1hourback.sh

#!/bin/bash

# This is a backup script ? for mysql' backup

name=`ls ? /home/mysql-data-bak/day-wanquan/`

/usr/bin/innobackupex ? --user=root --password=root? ? --incremental /home/mysql-data-bak/day-zengliang? ? --incremental-basedir=/home/mysql-data-bak/day-wanquan/$name

添加計劃任務(wù)：

0 0 * * *? sh?? ? /home/mysql-1dayback.sh

15 */1 * * *? sh?? ? /home/mysql-1hourback.sh

4.3 還原數(shù)據(jù)庫

恢復(fù)完全備份數(shù)據(jù)

[root@localhost data]# ? innobackupex --apply-log? --redo-only /backup/2016-05-26_15-37-02/

--apply-log? ? 回滾日志

--redo-only? ? 回滾合并

合并第一個增量備份到完全備份里面

[root@localhost data]# ? innobackupex --apply-log? --redo-only /backup/2013-09-06_15-37-02/? ? --incremental-dir=/backup/2016-05-26_16-15-17/

執(zhí)行完結(jié)尾部分出現(xiàn)下面的消息表示成功了

innobackupex: completed OK!

恢復(fù)全部數(shù)據(jù)庫

[root@localhost data]# innobackupex --copy-back? /backup/2016-05-26_15-37-02/

修改恢復(fù)數(shù)據(jù)庫目錄中文件的權(quán)限

[root@localhost data]# chown? -R? mysql:mysql *

service mysqld start

5.計劃

5.1 目前正式和驗證環(huán)境都采用的是主從結(jié)構(gòu)，確保主服務(wù)器正常使用；

5.2從服務(wù)器與主服務(wù)器做數(shù)據(jù)同步，觀察三周時間，此期間解決數(shù)據(jù)同步時發(fā)生的問題；

5.3到6月13日確保從服務(wù)器正常同步數(shù)據(jù)，可以讓從服務(wù)器做讀操作，分擔(dān)主服務(wù)器壓力；

5.4今后根據(jù)業(yè)務(wù)需求考慮雙主。

附：

1.解鎖表命令：

flush ? table with read lock 鎖表，只讀不改

unlock ? tables 解鎖

2.備份

溫備步驟：

備份所有庫：mysqldump -uroot-lock-all-tables --flush-logs --master-date=2 ? --events ?-B --all-databases ? >all-'date +%F-$H-%M-%s'.sql

備份二進(jìn)制：mysqlbinlog mysql-bin.000004>/opt/binlog-bak-'date ? +%F-%H-%M-%S'.sql

3、xtrabackup

安裝

rpm ? -ivh percona-xtrabackup-2.3.3-1.el6.x86_64.rpm

附件： MySql數(shù)據(jù)庫集群架構(gòu)方案.pdf 1005.83KB 下載次數(shù)：1次

MySQL 數(shù)據(jù)庫

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。