當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

MySQL主从复制能完美解决数据库单点问题吗？

發(fā)布時(shí)間：2024/2/28 数据库 25 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL主从复制能完美解决数据库单点问题吗？小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

一、單個(gè)數(shù)據(jù)庫(kù)服務(wù)器的缺點(diǎn)

數(shù)據(jù)庫(kù)服務(wù)器存在單點(diǎn)問題；

?
數(shù)據(jù)庫(kù)服務(wù)器資源無法滿足增長(zhǎng)的讀寫請(qǐng)求；

?
高峰時(shí)數(shù)據(jù)庫(kù)連接數(shù)經(jīng)常超過上限。

二、如何解決單點(diǎn)問題

增加額外的數(shù)據(jù)庫(kù)服務(wù)器，組建數(shù)據(jù)庫(kù)集群；

?
同一集群中的數(shù)據(jù)庫(kù)服務(wù)器需要具有相同的數(shù)據(jù)；

?
集群中的任一服務(wù)器宕機(jī)后，其它服務(wù)器可以取代宕機(jī)服務(wù)器。

三、MySQL主從復(fù)制架構(gòu)

1、主庫(kù)將變更寫入到主庫(kù)的binlog中

一些MySQL版本并不會(huì)開啟二進(jìn)制日志，所以一定要檢查是否開啟；

?
如果剛開始沒有開啟，后面再進(jìn)行開啟的話，需要重啟數(shù)據(jù)庫(kù)才能生效，而且數(shù)據(jù)庫(kù)的重啟往往會(huì)對(duì)業(yè)務(wù)造成很大的影響；

?
盡管二進(jìn)制日志對(duì)性能有稍許的影響，所以還是建議大家無論是否使用復(fù)制功能，都要開啟MySQL二進(jìn)制日志，因?yàn)樵隽總浞菀残枰M(jìn)制日志。

2、從庫(kù)的IO線程在指定位置讀取主庫(kù)binlog內(nèi)容存儲(chǔ)到本地的中繼日志（Relay Log）中

要完成二進(jìn)制日志的傳輸過程，MySQL會(huì)在從服務(wù)器上啟動(dòng)一個(gè)工作線程，稱為IO線程

這個(gè)IO線程會(huì)跟主數(shù)據(jù)庫(kù)建立一個(gè)普通的客戶端連接，然后在主服務(wù)器上啟動(dòng)一個(gè)特殊的二進(jìn)制轉(zhuǎn)儲(chǔ)線程稱為binlogdown線程。

從庫(kù)上的IO線程通過這個(gè)二進(jìn)制轉(zhuǎn)儲(chǔ)線程來讀取主庫(kù)上的二進(jìn)制事件，如果該事件追趕上主庫(kù)，則會(huì)進(jìn)入sleep狀態(tài)，直到主庫(kù)發(fā)起信號(hào)通知有新事件產(chǎn)生時(shí)，才會(huì)被喚醒，relay log的格式和binlog格式是完全相同的，

可以使用mysqlbinlog來讀取relay log中的內(nèi)容。

3、從庫(kù)的SQL線程讀取Relay Log日志中的內(nèi)容，并在從庫(kù)中重放

SQL線程所執(zhí)行的事件，我們可以通過配置選項(xiàng)來決定是否要寫入到從服務(wù)器的二進(jìn)制日志中。

目前MySQL支持兩種復(fù)制類型：

基于二進(jìn)制日志點(diǎn)的復(fù)制
基于GTID的復(fù)制（MySQL>=5.7推薦使用）

四、MySQL主從配置步驟

1、配置主從數(shù)據(jù)庫(kù)服務(wù)器參數(shù)

有些參數(shù)配置后需要數(shù)據(jù)庫(kù)重啟才能生效，為了不影響數(shù)據(jù)庫(kù)的正常使用，我們最好在服務(wù)器上線的同時(shí)就把參數(shù)都配置好。

特別是master服務(wù)器的參數(shù)，更應(yīng)該作為服務(wù)器初始參數(shù)來進(jìn)行配置。

master服務(wù)器：

log_bin??=?/data/mysql/sql_log/mysql-bin????#?指定mysql的binlog的存放路徑?/data/mysql/sql_log，以及日志文件名前綴mysql-bin?,#?如果只是為了啟用binlog，可以不指定存放路徑，默認(rèn)會(huì)存放到mysql的data目錄下，也就是會(huì)把日志和數(shù)據(jù)文件存放在一起#?之所以指定路徑分開存放，是為了提高IO性能，所以還是建議日志文件和數(shù)據(jù)文件分開存放 server_id?=?100????#?mysql的復(fù)制集群中通過server_id的值區(qū)分不同的服務(wù)器，建議使用服務(wù)器ip的后一段或后兩段的值進(jìn)行配置，比如192.168.3.100,就設(shè)置為100或2100

slave 服務(wù)器：

log_bin??=?/data/mysql/sql_log/mysql-bin? server_id?=?101relay_log?=?/data/mysql/sql_log/relay-bin????#?指定relay_log日志的存放路徑和文件前綴?，不指定的話默認(rèn)以主機(jī)名作為前綴read_only?=?on????#????使所有沒有server權(quán)限的用戶，在從服務(wù)器上不能執(zhí)行寫操作，不論這個(gè)用戶是否擁有寫權(quán)限?（mysql5.7?可以使用?super_read_only?=?on?，限制super用戶也不能在從服務(wù)器上執(zhí)行寫操作）skip_slave_start?=?on????#?在slave服務(wù)器重啟時(shí)，不會(huì)自動(dòng)啟動(dòng)復(fù)制鏈路。默認(rèn)情況下slave服務(wù)器重啟后，mysql會(huì)自動(dòng)啟動(dòng)復(fù)制鏈路，如果這個(gè)時(shí)候存在問題，則主從鏈路會(huì)中斷，所以正常情況下，我們應(yīng)該在服務(wù)器重啟后檢查是否存在問題，然后再手動(dòng)啟動(dòng)復(fù)制鏈路#?下面兩個(gè)參數(shù)是把主從復(fù)制信息存儲(chǔ)到innodb表中，默認(rèn)情況下主從復(fù)制信息是存儲(chǔ)到文件系統(tǒng)中的，如果從服務(wù)器宕機(jī)，很容易出現(xiàn)文件記錄和實(shí)際同步信息不同的情況，存儲(chǔ)到表中則可以通過innodb的崩潰恢復(fù)機(jī)制來保證數(shù)據(jù)記錄的一致性 master_info_repository?=?TABLE relay_log_info_repository?=?TABLE

2、在master服務(wù)器上創(chuàng)建用于復(fù)制的數(shù)據(jù)庫(kù)賬號(hào)

用于IO線程連接master服務(wù)器獲取binlog日志，需要* REPLICATION SLAVE** 權(quán)限：

create user 'repl'@'ip段' identified by 'password';

? ? grant replication slave on *.* to 'repl'@'ip段';

3、備份master服務(wù)器上的數(shù)據(jù)并初始化slave服務(wù)器數(shù)據(jù)

建議主從數(shù)據(jù)庫(kù)服務(wù)器采用相同的MySQL版本；
建議使用全庫(kù)備份的方式初始化slave數(shù)據(jù)。

采用相同版本的好處：

我們可以使用全備的方式來初始化slave數(shù)據(jù)，還可以避免不同版本之間的差異造成數(shù)據(jù)庫(kù)同步失敗的問題。

?
如果我們使用的主從復(fù)制的服務(wù)器MySQL版本不同，則一定要注意master上的版本一定要低于slave服務(wù)器，不然同步的時(shí)候就可能出現(xiàn)錯(cuò)誤。

由于我們演示過程中的MySQL服務(wù)器都是使用的MySQL5.7，所以我們可以使用全備的方式進(jìn)行：

mysqldump --master-data=2 -uroot -p -A --single-transaction -R --triggers

4、啟動(dòng)基于日志點(diǎn)的復(fù)制鏈路

在slave服務(wù)器上運(yùn)行，MySQL命令：

CHANGE MASTER TO

MASTER_HOST= 'master_host_ip',

MASTER_USER= 'repl',

MASTER_PASSWORD = 'password',

MASTER_LOG_FILE='mysql_log_file_name',

MASTER_LOG_POS=xxxxxx;

5、啟動(dòng)基于GTID的復(fù)制鏈路

GTID：全局事務(wù)ID，GTID可以保證每一個(gè)在主上提交的事務(wù)，在復(fù)制集群中可以生成一個(gè)唯一的ID值，要使用基于GTID的復(fù)制，我們要在主從復(fù)制的配置文件中同時(shí)加入以下配置項(xiàng)。

MySQL配置：

gtid_mode=on # 是否啟動(dòng)gtid模式，啟動(dòng)了此模式會(huì)在二進(jìn)制日志中會(huì)額外記錄每個(gè)事務(wù)的GTID標(biāo)識(shí)符

enforce-gtid-consistency? ? # 強(qiáng)制gtid一致性，用于保證啟動(dòng)gtid后事務(wù)的安全

log-slave-updates = on? ? # mysql5.6一定要啟用參數(shù)，5.7可以不啟用

MySQL命令：

CHANGE MASTER TO

MASTER_HOST= 'master_host_ip',

MASTER_USER= 'repl',

MASTER_PASSWORD = 'password',

MASTER_AUTO_POSITION=1;

GTID復(fù)制的限制：

無法再使用create table ... select語(yǔ)句建立表，只能先create表，再insert數(shù)據(jù)；

?
無法在事務(wù)中使用create temporary table建立臨時(shí)表；

?
無法使用關(guān)聯(lián)更新同時(shí)更新事務(wù)表和非事務(wù)表。

4和5中選一個(gè)執(zhí)行即可。

五. MySQL主從復(fù)制演示

1. 先對(duì)主服務(wù)器進(jìn)行配置

[client] port??=?3306?????#?客戶端端口號(hào)為3306 socket?=?/home/mysql/data/mysql.sock[mysqld]#?skip?# skip_name_resolve?=?1 skip-external-locking?=1#?GENERAL?# user?=?mysql???#?MySQL啟動(dòng)用戶 default_storage_engine?=?InnoDB??#?新數(shù)據(jù)表的默認(rèn)數(shù)據(jù)表類型 character-set-server?=?utf8??????#?????#服務(wù)端默認(rèn)編碼（數(shù)據(jù)庫(kù)級(jí)別） socket?=?/home/mysql/data/mysql.sock pid_file?=??/home/mysql/data/mysqld.pid basedir?=?/home/mysql ???#使用該目錄作為根目錄（Mysql安裝目錄）；port?=?3306 bind-address?=?0.0.0.0 log_error_verbosity?=?3 explicit_defaults_for_timestamp?=?off #sql_mode?=?NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #sql_mode?=?NO_ENGINE_SUBSTITUTION#?undo?log #?innodb_undo_directory?=?/home.mysql/undo #?innodb_undo_tablespaces?=?32#?MyISAM?# key_buffer_size?=32M#?SAFETY?# max_allowed_packet????=?100M max_connect_errors????=?1000000 sysdate_is_now????=1 #innodb?=?FORCE #innodb_strict_mode?=?1??#?Replice?# server-id?=?100 relay_log?=??/home/mysql/sql_log/mysqld-relay-bin?#plugin-load?=?semisync_master.so log_slave_updates?=?on master_info_repository?=?TABLE relay_log_info_repository?=TABLE #?gtid_mode?=?on #?enforce_gtid_consistency?=on #?skip-slave-start?=1 #rpl_semi_sync_master_enabled?=?1 #rpl_semi_sync_master_timeout=200????#?0.2?second master_info_respository?=?TABLE #?gtid_mode=?on #?enforce_gtid_consistency?=?on #?skip-slave-start?=?1#?DATA?STORAGE?#datadir?=?/home/mysql/data?????#mysql?數(shù)據(jù)文件存放的目錄 tmpdir?=?/tmp????#?MySQL存放臨時(shí)文件的目錄#?BINARY?LOGGING?#log_bin?=?/home/mysql/sql_log/mysql-bin max_binlog_size??=?1000M binlog_format?=?row expire_log_days?=?7 sync_binlog?=?1#?CACHES?AND?LIMITS?#tmp_table_size?=?32M max_heap_table_size?=?32M query_cache_type?=?0??

由于主服務(wù)器一直在運(yùn)行著，在生產(chǎn)環(huán)境中主服務(wù)器是很少會(huì)重啟的，如果主服務(wù)器重啟，會(huì)造成正常的業(yè)務(wù)訪問的中斷，所以在服務(wù)器啟動(dòng)之前就啟動(dòng)了二進(jìn)制日志。

這里不需要重啟主服務(wù)器了，由于主服務(wù)器的默認(rèn)server_id=1，我們雖然在配置文件中更改了它的值，但實(shí)際運(yùn)行環(huán)境中并沒有改變。

我們可以查看一下當(dāng)前server_id：

mysql> show variables like '%server_id%';

可以通過以下命令動(dòng)態(tài)的進(jìn)行修改：

mysql> set global server_id = 100;

2. 再對(duì)從服務(wù)器進(jìn)行配置

[client] port??=?3306?????#?客戶端端口號(hào)為3306 socket?=?/home/mysql/data/mysql.sock[mysqld]#?skip?# skip_name_resolve?=?1 skip-external-locking?=1#?GENERAL?# user?=?mysql???#?MySQL啟動(dòng)用戶 default_storage_engine?=?InnoDB??#?新數(shù)據(jù)表的默認(rèn)數(shù)據(jù)表類型 character-set-server?=?utf8??????#?????#服務(wù)端默認(rèn)編碼（數(shù)據(jù)庫(kù)級(jí)別） socket?=?/home/mysql/data/mysql.sock pid_file?=??/home/mysql/data/mysqld.pid basedir?=?/home/mysql ???#使用該目錄作為根目錄（Mysql安裝目錄）；port?=?3306 bind-address?=?0.0.0.0 log_error_verbosity?=?3 explicit_defaults_for_timestamp?=?off #sql_mode?=?NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #sql_mode?=?NO_ENGINE_SUBSTITUTION read_only?=?on#?undo?log #?innodb_undo_directory?=?/home.mysql/undo #?innodb_undo_tablespaces?=?32#?MyISAM?# key_buffer_size?=32M#?SAFETY?# max_allowed_packet????=?100M max_connect_errors????=?1000000 sysdate_is_now????=1 #innodb?=?FORCE #innodb_strict_mode?=?1??#?Replice?# server-id?=?101 relay_log?=??/home/mysql/sql_log/mysqld-relay-bin?#plugin-load?=?semisync_master.so log_slave_updates?=?on master_info_repository?=?TABLE relay_log_info_repository?=TABLE #?gtid_mode?=?on #?enforce_gtid_consistency?=on #?skip-slave-start?=1 #rpl_semi_sync_master_enabled?=?1 #rpl_semi_sync_master_timeout=200????#?0.2?second master_info_respository?=?TABLE #?gtid_mode=?on #?enforce_gtid_consistency?=?on #?skip-slave-start?=?1#?DATA?STORAGE?#datadir?=?/home/mysql/data?????#mysql?數(shù)據(jù)文件存放的目錄 tmpdir?=?/tmp????#?MySQL存放臨時(shí)文件的目錄#?BINARY?LOGGING?#log_bin?=?/home/mysql/sql_log/mysql-bin max_binlog_size??=?1000M binlog_format?=?row expire_log_days?=?7 sync_binlog?=?1#?CACHES?AND?LIMITS?#tmp_table_size?=?32M max_heap_table_size?=?32M query_cache_type?=?0??

修改完從服務(wù)器配置后，重啟MySQL服務(wù)器。如果使用的是MySQL5.7版本的需要注意：

MySQL5.7增加了server-uuid值，默認(rèn)情況下載auto.cnf文件中

?

如果是使用的鏡像的方式安裝，可能大家的uuid一樣，所以需要把a(bǔ)uto.cnf文件刪除掉。

?

MySQL重啟后會(huì)自動(dòng)重新生成uuid的值，這樣就可以保證不同服務(wù)器上的MySQL實(shí)例的uuid的值是不一樣的；

?
如果server-uuid的值相同，主從復(fù)制會(huì)出現(xiàn)問題。

以上我們就完成了主從復(fù)制的配置，接下來我們要在主服務(wù)器上建立復(fù)制賬號(hào)。

3. 在MySQL主服務(wù)器上建立MySQL復(fù)制賬號(hào)

mysql> create user 'dba_repl'@'192.168.3.%' identified by '123456';

mysql> grant replication slave on *.* to 'dba_repl'@'192.168.3.%';

4. 建立好復(fù)制賬號(hào)以后，通過mysql主服務(wù)器上的全備初始化從服務(wù)器上數(shù)據(jù)

進(jìn)行全備：

[root@localhost data]# cd /data/db_backup/

[root@localhost db_backup]#? mysqldump -uroot -p --master-data=1 --single-transaction --routines --triggers --events? --all-databases > all.sql

Enter password:?

將其拷貝到從服務(wù)器上：

[root@localhost db_backup]# scp all.sql root@192.168.3.101:/root

在從服務(wù)器上恢復(fù)備份進(jìn)行初始化：

[root@Node2 ~]# mysql -uroot -p < all.sql

初始化完成后，準(zhǔn)備。

5. 從服務(wù)器進(jìn)行基于日志點(diǎn)的復(fù)制鏈路的配置

mysql> change master to master_host='192.168.3.100',

? ? ? ? -> master_user='dba_repl',

? ? ? ? -> master_password='123456',

? ? ? ? ->MASTER_LOG_FILE='mysql-bin.000017',MASTER_LOG_POS=663;

MASTER_LOG_FILE和MASTER_LOG_POS的值從全備文件中的CHANGE MASTER中獲取

以上復(fù)制鏈路的配置完成。

啟動(dòng)slave：

mysql> start slave;

檢查是否啟動(dòng)成功狀態(tài)：

mysql> show slave status \G

顯示：

Relay_Master_Log_File: mysql-bin.000017

Slave_IO_Running：Yes

Slave_SQL_Running: Yes

說明啟動(dòng)成功了，可以在主服務(wù)器上插入數(shù)據(jù)，在從服務(wù)上查看數(shù)據(jù)是否同步過來了。

六. 主從復(fù)制的一些缺點(diǎn)

雖然主從復(fù)制增加了一個(gè)數(shù)據(jù)庫(kù)副本，但從數(shù)據(jù)庫(kù)和主數(shù)據(jù)庫(kù)的數(shù)據(jù)最終會(huì)是一致的。

之所以說是最終一致，因?yàn)镸ySQL復(fù)制是異步的，正常情況下主從復(fù)制數(shù)據(jù)之間會(huì)有一個(gè)微小的延遲。

通過這個(gè)數(shù)據(jù)庫(kù)副本看似解決了數(shù)據(jù)庫(kù)單點(diǎn)問題，但并不完美：

因?yàn)檫@種架構(gòu)下，如果主服務(wù)器宕機(jī)，需要手動(dòng)切換從服務(wù)器，業(yè)務(wù)中斷不能忍受，不能滿足應(yīng)用高可用的要求。

End

作者：聽風(fēng)

來源：

https://www.cnblogs.com/huchong/p/10253522.html

本文版權(quán)歸作者所有

總結(jié)

以上是生活随笔為你收集整理的MySQL主从复制能完美解决数据库单点问题吗？的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： Hive _偏门常用查询函数（一）附带实
下一篇： HQL中的substring