MySQL數(shù)據(jù)庫主從復(fù)制原理及作用分析
1.數(shù)據(jù)庫主從分類:
主從分為倆種:傳統(tǒng)主從/GTID主從
2.mysql主從介紹由來
現(xiàn)實生活中,數(shù)據(jù)極其重要,存儲數(shù)據(jù)庫的方式很多,但是數(shù)據(jù)庫存在著一種隱患。
隱患:
用一臺數(shù)據(jù)庫存放數(shù)據(jù),若數(shù)據(jù)庫服務(wù)器宕機了導(dǎo)致數(shù)據(jù)丟失數(shù)據(jù)多了,訪問量大了,一臺服務(wù)器無法保證服務(wù)質(zhì)量
因此數(shù)據(jù)庫主從誕生
3.主從作用
故障切換,實現(xiàn)預(yù)備讀寫分離,提供查詢服務(wù)數(shù)據(jù)庫管理系統(tǒng)備份(DBSM),避免影響業(yè)務(wù)
4.主從復(fù)制原理
bin log:二進制日志,記錄寫操作(增刪改查)
Relay log:中繼日志
- 主庫會將所有的寫操作記錄到binlog日志下生成一個log dump線程,將binlog日志傳給從庫的I/O線程。
- 從庫有倆個線程:
I/O線程
sql線程 - 從庫的I/O線程會請求主庫得到binlog日志寫到relay log(中繼日志)中
- sql線程,會讀取relay log日志文件中的日志,并解析具體操作,來實現(xiàn)主從的操作一樣,達到數(shù)據(jù)一致
5.主從復(fù)制配置(數(shù)據(jù)一致時)
步驟:
- 確保主數(shù)據(jù)庫與從數(shù)據(jù)的數(shù)據(jù)一樣
- 主數(shù)據(jù)庫里創(chuàng)建一個同步賬號授權(quán)給從數(shù)據(jù)庫使用
- 配置主數(shù)據(jù)庫(修改配置文件)
- 配置從數(shù)據(jù)庫(修改配置文件)
環(huán)境需求:
倆臺mysql服務(wù)器,一臺主服務(wù)器(寫功能),一臺從服務(wù)器(讀功能)
主數(shù)據(jù)庫(centos8) ip地址:192.168.136.145 centos8.0/mysql5.7 相同數(shù)據(jù)
第六節(jié):數(shù)據(jù)不相同 (可能在公司之前有數(shù)據(jù)的情況)
從數(shù)據(jù)庫(centos8) ip地址:192.168.136.191 centos7.0/mysql5.7 相同數(shù)據(jù)
5.1主從服務(wù)器分別安裝mysql5.7
可看相關(guān)教程教程(超詳細(xì)):http://www.dbjr.com.cn/article/221946.htm
#二進制安裝:https://blog.csdn.net/qq_47945825/article/details/116848970?spm=1001.2014.3001.5501 #或者網(wǎng)絡(luò)倉庫安裝:(一般二進制安裝) https://blog.csdn.net/qq_47945825/article/details/116245442?spm=1001.2014.3001.5501
5.2主數(shù)據(jù)庫與從數(shù)據(jù)庫數(shù)據(jù)一致
[root@mysql01 ~]# mysql -uroot -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ [root@mysql02 ~]# mysql -uroot -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
5.3在主數(shù)據(jù)庫里創(chuàng)建一個同步賬號授權(quán)給從數(shù)據(jù)庫使用
replication:復(fù)制 slave:從 192.168.136.191:從數(shù)據(jù)庫ip地址
mysql> create user 'vvv'@'192.168.136.191' identified by 'vvv0917'; Query OK, 0 rows affected (0.00 sec) mysql> grant replication slave on *.*to 'vvv'@'192.168.136.191'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
5.4在從庫上測試連接
[root@mysql02 ~]# mysql -uvvv -vvv0917 -h192.168.136.145 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec)
5.5配置主數(shù)據(jù)庫
[root@mysql01 ~]# cat /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 pid-file = /opt/data/mysql.pid user = mysql skip-name-resolve log-bin=mysql_bin #啟動binlog日志 server-id=10 #數(shù)據(jù)庫服務(wù)器唯一標(biāo)識,id必須比從數(shù)據(jù)庫小 #重啟服務(wù) (此重啟方式,前提已配置mysqld.service文件) [root@mysql01 ~]# systemctl restart mysqld 觀察主數(shù)據(jù)庫狀態(tài): mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql_bin.000004 | 962 | | | | +------------------+----------+--------------+------------------+---
5.6配置從數(shù)據(jù)庫
[root@mysql02 ~]# cat /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3307 user = mysql pid-file = /opt/data/mysql.pid skip-name-resolve #skip-grant-tables server-id=20 #服務(wù)器id,大于主數(shù)據(jù)庫id relay-log=mysql_relay_log #啟動中繼日志 #log-bin=mysql-bin #重啟服務(wù): [root@mysql02 ~]# systemctl restart mysqld
5.7配置并啟動主從復(fù)制的功能(mysql02從數(shù)據(jù)庫上)
[root@slave02 ~]# mysql -uroot -p mysql> change master to -> master_host='192.168.136.145', -> master_user='vvv', -> master_password='vvv0917', -> master_log_file='mysql_bin.000004', -> master_log_pos=962; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; #stop slave為關(guān)閉 Query OK, 0 rows affected (0.01 sec) #查看配置狀態(tài): mysql> show slave status\G; Slave_IO_State: Waiting for master to send event Master_Host: 192.168.136.145 Master_User: vvv Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin.000004 Read_Master_Log_Pos: 962 Relay_Log_File: mysql_relay_log.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql_bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes #此處必須倆個都是yes,就是配置成功,否則失敗
5.8測試:
主庫:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
從庫:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
主庫創(chuàng)建數(shù)據(jù)庫clq并且加入數(shù)據(jù):
mysql> create database clq; Query OK, 1 row affected (0.00 sec) mysql> create table clq01(id int(11)not null primary key auto_increment,name varchar(100)not null,age tinyint(4)); mysql> insert clq01(name,age) values('A',20),('B',21),('C',22); Query OK, 3 rows affected (0.00 sec)
從庫中查看:
mysql> select * from clq01; +----+------+------+s | id | name | age | +----+------+------s+ | 1 | A | 20 | | 2 | B | 21 | | 3 | C | 22 | +----+------+------+ #主從復(fù)制完成!
6.主從配置(數(shù)據(jù)不一致時)
6.1一般全備主庫需要另開一個終端,給數(shù)據(jù)庫加上讀鎖(只讀不寫)
避免其他人在寫入數(shù)據(jù)導(dǎo)致不一樣
flush tables with read lock: quit:退出即可為解鎖(備份完之后才能解鎖)
6.2確保主主數(shù)據(jù)庫與從數(shù)據(jù)庫的數(shù)據(jù)一樣
#先對主庫進行全備 [root@mysql01 ~]# mysqldump -uroot -A > all-databases.sql #拷貝數(shù)據(jù)到從數(shù)據(jù)庫上 [root@mysql01 ~]# ls /clq all-databases.sql [root@mysql01 ~]# scp /clq/all-databases.sql root@192.168.136.193:/clq/ The authenticity of host '192.168.136.193 (192.168.136.193)' can't be established. ECDSA key fingerprint is SHA256:XIAQEoJ+M0vOHmCwQvhUdw12u5s2nvkN0A4TMKLaFiY. Are you sure you want to continue connecting (yes/no/[fingerprint])yes root@192.168.136.193's password: all-databases.sql 100% 853KB 115.4MB/s 00:00 [root@mysql02 clq]# ll 總用量 896 #從庫上查看 -rw-r--r--. 1 root root 873266 5月 17 19:36 all-databases.sql
6.3在從庫上查看主庫有哪些庫,確保一致
[root@mysql02 clq]# mysql -uroot -pHuawei0917@ < all-databases.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@mysql02 clq]# mysql -uroot -pHuawei0917@ -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | clq | | mysql | | performance_schema | | sys | +--------------------+ 主庫: mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | clq | | mysql | | performance_schema | | sys | +--------------------+
6.4確保倆庫的配置文件已經(jīng)配置了相應(yīng)的文件
[root@mysql01 ~]# cat /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 pid-file = /opt/data/mysql.pid user = mysql skip-name-resolve log-bin=mysql_bin #日志文件 server-id=10 #唯一標(biāo)識服務(wù)id [root@mysql02 ~]# cat /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3307 user = mysql pid-file = /opt/data/mysql.pid skip-name-resolve #skip-grant-tables server-id=20 #唯一標(biāo)識服務(wù)id(大于主庫) relay-log=mysql_relay_log #中繼日志 #log-bin=mysql-bin
此后步驟和5.5之后一模一樣!
小結(jié):
主庫修改數(shù)據(jù),從庫的數(shù)據(jù)隨之改變!
反之,從庫修改數(shù)據(jù),主庫的數(shù)據(jù)不會發(fā)生改變
查看數(shù)據(jù)庫運行的命令進程
mysql> show processlist; +----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+ | 5 | repl | 192.168.136.219:39788 | NULL | Binlog Dump | 1575 | Master has sent all binlog to slave; waiting for more updates | NULL | | 7 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+ 2 rows in set (0.00 sec)
以上就是MySQL數(shù)據(jù)庫主從復(fù)制原理及作用分析的詳細(xì)內(nèi)容,更多關(guān)于MySQL數(shù)據(jù)庫主從復(fù)制的資料請關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
MySQL動態(tài)字符串處理DYNAMIC_STRING
本文主要給大家簡單講解了mysql如何使用DYNAMIC_STRING來進行動態(tài)字符串的保存,非常的實用,有需要的小伙伴可以參考下2016-10-10mysql常用日期時間/數(shù)值函數(shù)詳解(必看)
下面小編就為大家?guī)硪黄猰ysql常用日期時間/數(shù)值函數(shù)詳解(必看)。小編覺得挺不錯的,現(xiàn)在就分享給大家,也給大家做個參考。一起跟隨小編過來看看吧2016-06-06MySQL錯誤“Data?too?long”的原因、解決方案與優(yōu)化策略
MySQL作為重要的數(shù)據(jù)庫系統(tǒng),在數(shù)據(jù)插入時可能遇到“Data?too?long?for?column”錯誤,本文探討了該錯誤的原因、解決方案及預(yù)防措施,如調(diào)整字段長度、使用TEXT類型等,旨在優(yōu)化數(shù)據(jù)庫設(shè)計,提升性能和用戶體驗,需要的朋友可以參考下2024-09-09