.jpg)
?????? 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）！
.png)
