MySQL主從配置及mysqldump備份實(shí)戰(zhàn)

1. Mysql主從概念：

mysql的主從復(fù)制，是用來建立一個(gè)和主數(shù)據(jù)庫完全一樣的數(shù)據(jù)庫環(huán)境僧凤，從庫會(huì)同步主庫的所有數(shù)據(jù)，可輕松實(shí)現(xiàn)故障轉(zhuǎn)移。

1.1 mysql主從主要作用：

在業(yè)務(wù)復(fù)雜的系統(tǒng)中沫浆，有這么一個(gè)情景，有一句sql語句需要鎖表滚秩，導(dǎo)致暫時(shí)不能使用讀的服務(wù)件缸，那么就很影響運(yùn)行中的業(yè)務(wù)，使用主從復(fù)制叔遂，讓主庫負(fù)責(zé)寫他炊，從庫負(fù)責(zé)讀，這樣已艰，即使主庫出現(xiàn)了鎖表的情景痊末，通過讀從庫也可以保證業(yè)務(wù)的正常運(yùn)行。

做數(shù)據(jù)的熱備哩掺，主庫宕機(jī)后能夠及時(shí)替換主庫凿叠，保證業(yè)務(wù)可用性。

架構(gòu)的擴(kuò)展嚼吞。業(yè)務(wù)量越來越大盒件，I/O訪問頻率過高，單機(jī)無法滿足舱禽，此時(shí)做多庫的存儲(chǔ)炒刁，降低磁盤I/O訪問的頻率，提高單個(gè)機(jī)器的I/O性能誊稚。

1.2 常見mysql主從架構(gòu)：

1.2.1MySQL主從復(fù)制的流程

主庫的更新事件(update翔始、insert罗心、delete)被寫到binlog；

從庫啟動(dòng)并發(fā)起連接城瞎，連接到主庫渤闷；

主庫創(chuàng)建一個(gè)binlog dump thread，把binlog的內(nèi)容發(fā)送到從庫脖镀；

從庫啟動(dòng)之后飒箭，創(chuàng)建一個(gè)I/O線程，讀取主庫傳過來的binlog內(nèi)容并寫入到relay log蜒灰；

從庫啟動(dòng)之后补憾，創(chuàng)建一個(gè)SQL線程，從relay log里面讀取內(nèi)容卷员，從Exec_Master_Log_Pos位置開始執(zhí)行讀取到的更新事件盈匾，將更新內(nèi)容寫入到slave的數(shù)據(jù)庫。

1.2.2 MySQL主從復(fù)制的原理

MySQL主從復(fù)制是一個(gè)異步的復(fù)制過程毕骡，主庫發(fā)送更新事件到從庫削饵，從庫讀取更新記錄，并執(zhí)行更新記錄未巫，使得從庫的內(nèi)容與主庫保持一致窿撬。

binlog：binary log，主庫中保存所有更新事件日志的二進(jìn)制文件叙凡。binary log是從數(shù)據(jù)庫服務(wù)啟動(dòng)的一刻起劈伴，保存數(shù)據(jù)庫所有變更記錄（數(shù)據(jù)庫結(jié)構(gòu)和內(nèi)容）的文件。在主庫中握爷，只要有更新事件出現(xiàn)跛璧，就會(huì)被依次地寫入到binary log中，之后會(huì)推送到從庫中作為從庫進(jìn)行復(fù)制的數(shù)據(jù)源新啼。

binlog輸出線程：每當(dāng)有從庫連接到主庫的時(shí)候追城，主庫都會(huì)創(chuàng)建一個(gè)線程然后發(fā)送binlog內(nèi)容到從庫。 對(duì)于每一個(gè)即將發(fā)送給從庫的sql事件燥撞，binlog輸出線程會(huì)將其鎖住座柱。一旦該事件被線程讀取完之后，該鎖會(huì)被釋放物舒，即使在該事件完全發(fā)送到從庫的時(shí)候色洞，該鎖也會(huì)被釋放。

在從庫中冠胯，當(dāng)復(fù)制開始時(shí)火诸，從庫就會(huì)創(chuàng)建從庫I/O線程和從庫的SQL線程進(jìn)行復(fù)制處理。

從庫I/O線程：當(dāng)start slave語句在從庫開始執(zhí)行之后涵叮，從庫創(chuàng)建一個(gè)I/O線程惭蹂，該線程連接到主庫并請(qǐng)求主庫發(fā)送binlog里面的更新記錄到從庫上。 從庫I/O線程讀取主庫的binlog輸出線程發(fā)送的更新并拷貝這些更新到本地文件割粮，其中包括relay log文件盾碗。

從庫的SQL線程：從庫創(chuàng)建一個(gè)SQL線程，這個(gè)線程讀取從庫I/O線程寫到relay log的更新事件并執(zhí)行舀瓢。

綜上所述廷雅，可知：

對(duì)于每一個(gè)主從復(fù)制的連接，都有三個(gè)線程京髓。擁有多個(gè)從庫的主庫為每一個(gè)連接到主庫的從庫創(chuàng)建一個(gè)binlog輸出線程航缀，每一個(gè)從庫都有它自己的I/O線程和SQL線程。

從庫通過創(chuàng)建兩個(gè)獨(dú)立的線程堰怨，使得在進(jìn)行復(fù)制時(shí)芥玉，從庫的讀和寫進(jìn)行了分離。因此备图，即使負(fù)責(zé)執(zhí)行的線程運(yùn)行較慢灿巧，負(fù)責(zé)讀取更新語句的線程并不會(huì)因此變得緩慢。比如說揽涮，如果從庫有一段時(shí)間沒運(yùn)行了抠藕，當(dāng)它在此啟動(dòng)的時(shí)候，盡管它的SQL線程執(zhí)行比較慢蒋困，它的I/O線程可以快速地從主庫里讀取所有的binlog內(nèi)容盾似。這樣一來，即使從庫在SQL線程執(zhí)行完所有讀取到的語句前停止運(yùn)行了雪标，I/O線程也至少完全讀取了所有的內(nèi)容零院，并將其安全地備份在從庫本地的relay log，隨時(shí)準(zhǔn)備在從庫下一次啟動(dòng)的時(shí)候執(zhí)行語句村刨。

1.3 MySQL主從部署：

node3：master门粪，192.168.48.183

node4：slave， 192.168.48.184

1.3.2 master端配置：

xxxxxxxxxx

# 安裝好mysql/mariadb數(shù)據(jù)庫：

[root@node03 ~]# yum install mariadb mariadb-server -y

# 修改/etc/my.cnf配置文件烹困，在[mysqld]指令段添加以下行：

log-bin=node3-bin

server-id=1

?

# 啟動(dòng)數(shù)據(jù)庫服務(wù)：

[root@node03 ~]# systemctl start mariadb

[root@node03 ~]#

# 查看mysql進(jìn)程：

[root@node03 ~]# ps -ef|grep mysqld

mysql ? ?? 6130 ? ?? 1? 0 20:37 ? ? ? ?? 00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr

mysql ? ?? 6316 ? 6130? 0 20:37 ? ? ? ?? 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock

root ? ? ? 6365 ? 5819? 0 20:38 pts/0 ?? 00:00:00 grep --color=auto mysqld

[root@node03 ~]#

# 查看mysql端口：

[root@node03 ~]# netstat -ntlp|grep 3306

tcp ? ? ?? 0 ? ?? 0 0.0.0.0:3306 ? ? ? ? ?? 0.0.0.0:* ? ? ? ? ? ? ? LISTEN ? ?? 6316/mysqld ? ? ? ?

[root@node03 ~]#

1.3.3 查看配置是否生效：

xxxxxxxxxx

# 通過mysql直接進(jìn)入數(shù)據(jù)庫：

[root@node03 ~]# mysql

Welcome to the MariaDB monitor.? Commands end with ; or \g.

Your MariaDB connection id is 2

Server version: 5.5.65-MariaDB MariaDB Server

?

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

?

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

?

MariaDB [(none)]>

# 查看log_bin和sql_log_bin是否均為on;

MariaDB [(none)]> show variables like "%log_bin";

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin ? ? ? | ON ?? |

| sql_log_bin ? | ON ?? |

+---------------+-------+

2 rows in set (0.00 sec)

?

MariaDB [(none)]>

1.3.4 授權(quán)從庫：

xxxxxxxxxx

MariaDB [(none)]> grant replication slave on *.* to "superman"@"192.168.48.184" identified by "123456";

Query OK, 0 rows affected (0.00 sec)

?

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

?

MariaDB [(none)]>

1.3.5 查看master狀態(tài)：

xxxxxxxxxx

MariaDB [(none)]> show master status;

+------------------+----------+--------------+------------------+

| File ? ? ? ? ? ? | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| node3-bin.000004 | ? ?? 479 | ? ? ? ? ? ?? | ? ? ? ? ? ? ? ?? |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

?

MariaDB [(none)]>

1.3.6 slave端配置：

xxxxxxxxxx

# 修改/etc/my.cnf配置文件玄妈，在[mysqld]指令塊下添加如下行：

server-id=2

1.3.7 啟動(dòng)slave數(shù)據(jù)庫服務(wù)：

xxxxxxxxxx

[root@node04 ~]# systemctl start mariadb

1.3.8 在slave數(shù)據(jù)庫上指定master:

xxxxxxxxxx

[root@node04 ~]# mysql

Welcome to the MariaDB monitor.? Commands end with ; or \g.

Your MariaDB connection id is 2

Server version: 5.5.65-MariaDB MariaDB Server

?

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

?

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

?

MariaDB [(none)]>

MariaDB [(none)]> change master to

?? -> master_host="192.168.48.183",

?? -> master_user="superman",

?? -> master_password="123456",

?? -> master_log_file="node3-bin.000004",

?? -> master_log_pos=479;

Query OK, 0 rows affected (0.02 sec)

?

MariaDB [(none)]>

1.3.9 查看slave狀態(tài)：

xxxxxxxxxx

MariaDB [(none)]> slave start;

Query OK, 0 rows affected (0.00 sec)

?

MariaDB [(none)]>

MariaDB [(none)]> show slave status\G

*************************** 1. row ***************************

? ? ? ? ? ? ? Slave_IO_State: Waiting for master to send event

? ? ? ? ? ? ? ?? Master_Host: 192.168.48.183

? ? ? ? ? ? ? ?? Master_User: superman

? ? ? ? ? ? ? ?? Master_Port: 3306

? ? ? ? ? ? ?? Connect_Retry: 60

? ? ? ? ? ?? Master_Log_File: node3-bin.000004

? ? ? ?? Read_Master_Log_Pos: 479

? ? ? ? ? ? ? Relay_Log_File: mariadb-relay-bin.000002

? ? ? ? ? ? ?? Relay_Log_Pos: 529

? ? ?? Relay_Master_Log_File: node3-bin.000004

? ? ? ? ? ? Slave_IO_Running: Yes

? ? ? ? ?? Slave_SQL_Running: Yes

? ? ? ? ? ?? Replicate_Do_DB:

? ? ? ?? Replicate_Ignore_DB:

? ? ? ? ? Replicate_Do_Table:

? ? ? Replicate_Ignore_Table:

? ?? Replicate_Wild_Do_Table:

? Replicate_Wild_Ignore_Table:

? ? ? ? ? ? ? ? ? Last_Errno: 0

? ? ? ? ? ? ? ? ? Last_Error:

? ? ? ? ? ? ? ? Skip_Counter: 0

? ? ? ?? Exec_Master_Log_Pos: 479

? ? ? ? ? ?? Relay_Log_Space: 825

? ? ? ? ? ?? Until_Condition: None

? ? ? ? ? ? ? Until_Log_File:

? ? ? ? ? ? ?? Until_Log_Pos: 0

? ? ? ? ? Master_SSL_Allowed: No

? ? ? ? ? Master_SSL_CA_File:

? ? ? ? ? Master_SSL_CA_Path:

? ? ? ? ? ?? Master_SSL_Cert:

? ? ? ? ?? Master_SSL_Cipher:

? ? ? ? ? ? ? Master_SSL_Key:

? ? ?? Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

? ? ? ? ? ? ?? Last_IO_Errno: 0

? ? ? ? ? ? ?? Last_IO_Error:

? ? ? ? ? ? ? Last_SQL_Errno: 0

? ? ? ? ? ? ? Last_SQL_Error:

? Replicate_Ignore_Server_Ids:

? ? ? ? ? ? Master_Server_Id: 1

1 row in set (0.00 sec)

?

MariaDB [(none)]>

1.3.10 驗(yàn)證數(shù)據(jù)同步：

xxxxxxxxxx

# 在主庫創(chuàng)建一個(gè)數(shù)據(jù)庫：

MariaDB [(none)]> create database zabbix charset=utf8;

Query OK, 1 row affected (0.00 sec)

?

MariaDB [(none)]>

MariaDB [(none)]> show databases;

+--------------------+

| Database ? ? ? ? ? |

+--------------------+

| information_schema |

| mysql ? ? ? ? ? ?? |

| performance_schema |

| test ? ? ? ? ? ? ? |

| zabbix ? ? ? ? ? ? |

+--------------------+

5 rows in set (0.00 sec)

?

MariaDB [(none)]>

?

# 在從庫查看：

MariaDB [(none)]> show databases;

+--------------------+

| Database ? ? ? ? ? |

+--------------------+

| information_schema |

| mysql ? ? ? ? ? ?? |

| performance_schema |

| test ? ? ? ? ? ? ? |

| zabbix ? ? ? ? ? ? |

+--------------------+

5 rows in set (0.00 sec)

?

MariaDB [(none)]>

1.3.11 同步錯(cuò)誤分析:

xxxxxxxxxx

Slave_IO_Running: Connecting

# 第一種：主庫宕機(jī)

# 第二種：從庫指定的用戶名與密碼錯(cuò)誤（與主庫授權(quán)的用戶名和密碼不一致）

# 第三種：關(guān)閉防火墻

Slave_IO_Running: No

# 從庫指定的二進(jìn)制文件有誤

Slave_SQL_Running: No

# pos點(diǎn)問題

1.4 主從復(fù)制延遲問題及解決方法：

1.4.1 從庫過多：

建議從庫數(shù)量3-5 為宜，要復(fù)制的從節(jié)點(diǎn)數(shù)量過多髓梅，會(huì)導(dǎo)致復(fù)制延遲拟蜻。

1.4.2 從庫硬件差：

從庫硬件比主庫差，導(dǎo)致復(fù)制延遲枯饿，查看master和slave的系統(tǒng)配置酝锅，可能會(huì)因?yàn)闄C(jī)器配置的問題，包括磁盤IO奢方、CPU搔扁、內(nèi)存等各方面因素造成復(fù)制的延遲爸舒，一般發(fā)生在高并發(fā)大數(shù)據(jù)量寫入場景。

1.4.3 網(wǎng)絡(luò)問題：

主從庫之間的網(wǎng)絡(luò)延遲稿蹲，主庫的網(wǎng)卡扭勉、網(wǎng)線、連接的交換機(jī)等網(wǎng)絡(luò)設(shè)備都可能成為復(fù)制的瓶頸苛聘，導(dǎo)致復(fù)制延遲涂炎。

1.5? Mysqldump備份：

1.5.1 只備份表，不備份數(shù)據(jù)本身：

xxxxxxxxxx

# 備份zabbix數(shù)據(jù)庫中的所有表设哗，但是不會(huì)自動(dòng)生成創(chuàng)建zabbix數(shù)據(jù)庫的語句：

[root@node03 ~]# mysqldump -uroot -p*** zabbix > zabbix_tables.sql

[root@node03 ~]#

1.5.2 備份zabbix數(shù)據(jù)庫與表：

xxxxxxxxxx

備份zabbix數(shù)據(jù)庫中的所有表唱捣，并且會(huì)生成創(chuàng)建zabbix數(shù)據(jù)庫的SQL語句，也就是導(dǎo)入時(shí)不需要先創(chuàng)建數(shù)據(jù)庫：

[root@node03 ~]# mysqldump -uroot -p*** --databases zabbix > zabbix_database.sql

[root@node03 ~]#

1.5.3 備份多個(gè)數(shù)據(jù)庫：

xxxxxxxxxx

[root@node03 ~]# mysqldump -uroot -p*** --databases zabbix mysql > zabbix_mysql_database.sql

[root@node03 ~]#

1.5.4 備份所有數(shù)據(jù)庫：

xxxxxxxxxx

[root@node03 ~]# mysqldump -uroot -p*** --all-databases > all_databases.sql

[root@node03 ~]#

或者：

[root@node03 ~]# mysqldump -uroot -p*** -A > all.sql

[root@node03 ~]#

1.5.5 備份zabbix數(shù)據(jù)庫网梢，并且記錄pos點(diǎn)：

xxxxxxxxxx

[root@node03 ~]# mysqldump -uroot -p*** --master-data zabbix > zabbix_pos.sql

[root@node03 ~]#

1.5.6? 備份數(shù)據(jù)庫震缭，并刷新日志：

xxxxxxxxxx

[root@node03 ~]# mysqldump -uroot -p*** --master-data --flush-logs zabbix > zabbix_pos_flush.sql

[root@node03 ~]#

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個(gè)濱河市战虏，隨后出現(xiàn)的幾起案子蛀序，更是在濱河造成了極大的恐慌，老刑警劉巖活烙，帶你破解...
    沈念sama閱讀 218,546評(píng)論 6贊 507
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件徐裸，死亡現(xiàn)場離奇詭異，居然都是意外死亡啸盏，警方通過查閱死者的電腦和手機(jī)重贺，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 93,224評(píng)論 3贊 395
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來回懦，“玉大人气笙，你說我怎么就攤上這事∏釉危” “怎么了潜圃？”我有些...
    開封第一講書人閱讀 164,911評(píng)論 0贊 354
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長舟茶。 經(jīng)常有香客問我谭期，道長，這世上最難降的妖魔是什么吧凉？ 我笑而不...
    開封第一講書人閱讀 58,737評(píng)論 1贊 294
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任隧出，我火速辦了婚禮，結(jié)果婚禮上阀捅，老公的妹妹穿的比我還像新娘胀瞪。我一直安慰自己，他們只是感情好饲鄙，可當(dāng)我...
    茶點(diǎn)故事閱讀 67,753評(píng)論 6贊 392
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布凄诞。 她就那樣靜靜地躺著圆雁，像睡著了一般。 火紅的嫁衣襯著肌膚如雪帆谍。 梳的紋絲不亂的頭發(fā)上伪朽，一...
    開封第一講書人閱讀 51,598評(píng)論 1贊 305
  • 城市分裂傳說
    那天，我揣著相機(jī)與錄音既忆，去河邊找鬼驱负。 笑死嗦玖，一個(gè)胖子當(dāng)著我的面吹牛患雇，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播宇挫，決...
    沈念sama閱讀 40,338評(píng)論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼苛吱，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了器瘪？” 一聲冷哼從身側(cè)響起翠储，我...
    開封第一講書人閱讀 39,249評(píng)論 0贊 276
  • 萬榮殺人案實(shí)錄
    序言：老撾萬榮一對(duì)情侶失蹤，失蹤者是張志新（化名）和其女友劉穎橡疼，沒想到半個(gè)月后援所，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 45,696評(píng)論 1贊 314
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡欣除，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,888評(píng)論 3贊 336
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年住拭，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片历帚。...
    茶點(diǎn)故事閱讀 40,013評(píng)論 1贊 348
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡滔岳，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出挽牢，到底是詐尸還是另有隱情谱煤，我是刑警寧澤，帶...
    沈念sama閱讀 35,731評(píng)論 5贊 346
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布禽拔，位于F島的核電站刘离，受9級(jí)特大地震影響，放射性物質(zhì)發(fā)生泄漏睹栖。R本人自食惡果不足惜寥闪，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,348評(píng)論 3贊 330
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望磨淌。 院中可真熱鬧疲憋，春花似錦、人聲如沸梁只。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,929評(píng)論 0贊 22
  • 一樁弒父案埃脏，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至秋忙，卻和暖如春彩掐，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背灰追。 一陣腳步聲響...
    開封第一講書人閱讀 33,048評(píng)論 1贊 270
  • 情欲美人皮
    我被黑心中介騙來泰國打工堵幽， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人弹澎。 一個(gè)月前我還...
    沈念sama閱讀 48,203評(píng)論 3贊 370
  • 代替公主和親
    正文 我出身青樓朴下，卻偏偏與公主長得像，于是被迫代替她去往敵國和親苦蒿。 傳聞我的和親對(duì)象是個(gè)殘疾皇子殴胧，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,960評(píng)論 2贊 355