使用innobackupex基于從庫(kù)搭建mysql主從架構(gòu)

?????? mysql的主從搭建大家有很多種方式,傳統(tǒng)的mysqldump方式是很多人的選擇之一。但對(duì)于較大的數(shù)據(jù)庫(kù)則該方式并非理想的選擇。使用xtrabackup可以快速輕松的構(gòu)建或修復(fù)mysql主從架構(gòu)。本文描述了基于現(xiàn)有的從庫(kù)來(lái)快速搭建主從,即作為原主庫(kù)的一個(gè)新從庫(kù)。該方式的好處是對(duì)主庫(kù)無(wú)需備份期間導(dǎo)致的相關(guān)性能壓力。搭建過(guò)程中使用了快速流備份方式來(lái)加速主從構(gòu)建以及描述了加速流式備份的幾個(gè)參數(shù),供大家參考。


??? 有關(guān)流式備份可以參考:Xtrabackup 流備份與恢復(fù)

1、備份從庫(kù)
###遠(yuǎn)程備份期間使用了等效性驗(yàn)證,因此應(yīng)先作相應(yīng)配置,這里我們使用的是mysql用戶

$?innobackupex?--user=root?--password=xxx?--slave-info?--safe-slave-backup?  --compress-threads=3?--parallel=3?--stream=xbstream?--compress?/log?|?ssh?-p50021?mysql@172.16.16.10?"xbstream?-x?-C?/log/recover"


###備份期間使用了safe-slave-backup參數(shù),可以看到SQL thread被停止,完成后被啟動(dòng)

$?mysql?-uroot?-p?-e?"show?slave?status?G"|egrep?'Slave_IO_Running|Slave_SQL_Running'  Enter?password:?  ?????????????Slave_IO_Running:?Yes  ????????????Slave_SQL_Running:?No  ??????Slave_SQL_Running_State:?Slave?has?read?all?relay?log;?waiting?for?the?slave?I/O?thread?to?update?it


###復(fù)制my.cnf文件到新從庫(kù)

$?scp?-P50021?/etc/my.cnf?mysql@172.16.16.10:/log/recover


2、主庫(kù)授予新從庫(kù)復(fù)制賬戶

master@MySQL>?grant?replication?slave,replication?client?on?*.*?to?repl@'172.16.%.%'?identified?by?'repl';


3、新從庫(kù)prepare????????
###由于使用了流式壓縮備份,因此需要先解壓
###下載地址?
http://www.php.cn/

#?tar?-xvf?qpress-11-linux-x64.tar?qpress#?cp?qpress?/usr/bin/  $?innobackupex?--decompress?/log/recover???????????????????????????????  ###解壓$?innobackupex?--apply-log?--use-memory=2G?/log/recover????  ###prepare備份


4、準(zhǔn)備從庫(kù)配置文件my.cnf
###根據(jù)需要修改相應(yīng)參數(shù),這里的修改如下,

skip-slave-start  datadir?=?/log/recover  port?=?3307  server_id?=?24?????????????????????  socket?=?/tmp/mysql3307.sock  pid-file=/log/recover/mysql3307.pid  log_error=/log/recover/recover.err


5、啟動(dòng)從庫(kù)及修改change master
# chown -R mysql:mysql /log/recover
# /app/soft/mysql/bin/mysqld_safe –defaults-file=/log/recover/my.cnf &

mysql>?system?more?/log/recover/xtrabackup_slave_info  CHANGE?MASTER?TO?MASTER_LOG_FILE='mysql-bin.000658',?MASTER_LOG_POS=925384099  mysql>?CHANGE?MASTER?TO  ????->?MASTER_HOST='172.16.16.10',???????  ###?Author:?Leshami  ????->?MASTER_USER='repl',????????????????????  ?###?Blog??:?  http://www.php.cn/  ????->?MASTER_PASSWORD='repl',  ????->?MASTER_PORT=3306,  ????->?MASTER_LOG_FILE='mysql-bin.000658',  ????->?MASTER_LOG_POS=925384099;  Query?OK,?0?rows?affected,?2?warnings?(0.31?sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)


6、基于從庫(kù)備份相關(guān)參數(shù)及加速流備份參數(shù)

The?--slave-info?option?This?option?is?useful?when?backing?up?a?replication?slave?server.?It?prints?the?binary  log?position?and?name?of?the?master?server.?It?also?writes?this?information?to?the?xtrabackup_slave_info??le  as?a?CHANGE?MASTER?statement.  This?is?useful?for?setting?up?a?new?slave?for?this?master?can?be?set?up?by?starting?a?slave?server?on?this?backup?and  issuing?the?statement?saved?in?the?xtrabackup_slave_info??le.


The?--safe-slave-backup?option?In?order?to?assure?a?consistent?replication?state,?this?option?stops?the?slave  SQL?thread?and?wait?to?start?backing?up?until?Slave_open_temp_tables?in?SHOW?STATUS?is?zero.?If?there?are  no?open?temporary?tables,?the?backup?will?take?place,?otherwise?the?SQL?thread?will?be?started?and?stopped?until?there  are?no?open?temporary?tables.?The?backup?will?fail?if?Slave_open_temp_tables?does?not?become?zero?after  --safe-slave-backup-timeout?seconds?(defaults?to?300?seconds).?The?slave?SQL?thread?will?be?restarted  when?the?backup??nishes.  Using?this?option?is?always?recommended?when?taking?backups?from?a?slave?server.


Warning: Make sure your slave is a true replica of the master before using it as a source for backup. A good tool
to validate a slave is pt-table-checksum.


–compress
????

????This?option?instructs?xtrabackup?to?compress?backup?copies?of?InnoDB  ????????data?files.?It?is?passed?directly?to?the?xtrabackup?child?process.

??????? ###注compress方式是一種相對(duì)粗糙的壓縮方式,壓縮為.gp文件,沒(méi)有g(shù)zip壓縮比高


–compress-threads
?????

???This?option?specifies?the?number?of?worker?threads?that?will?be?used  ????????for?parallel?compression.?It?is?passed?directly?to?the?xtrabackup  ????????child?process.?Try?'xtrabackup?--help'?for?more?details.


–decompress
?????

???Decompresses?all?files?with?the?.qp?extension?in?a?backup?previously  ????????made?with?the?--compress?option.


?–parallel=NUMBER-OF-THREADS
?????

???On?backup,?this?option?specifies?the?number?of?threads?the????????  ???xtrabackup?child?process?should?use?to?back?up?files?concurrently.????????  ???The?option?accepts?an?integer?argument.?It?is?passed?directly?to????????  ???xtrabackup's?--parallel?option.?See?the?xtrabackup?documentation?for????????  ???details.  ????????On?--decrypt?or?--decompress?it?specifies?the?number?of?parallel???????  ?????????forks?that?should?be?used?to?process?the?backup?files.

以上就是使用innobackupex基于從庫(kù)搭建mysql主從架構(gòu)的內(nèi)容,更多相關(guān)內(nèi)容請(qǐng)關(guān)注PHP中文網(wǎng)(www.php.cn)!

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點(diǎn)贊10 分享