溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本文主要給大家介紹MySQL 5.7 主從原理、配置及測試講義，希望可以給大家補充和更新些知識，如有其它問題需要了解的可以持續在億速云行業資訊里面關注我的更新文章的。
首先要準備兩臺云服務器，一臺主服務器(Master)，另一臺從服務器(Slave)，然后要保證Master與Slave的版本要相同且Master不能高于Slave的版本，一般穩健的做法都是使其版本相同，因為MySQL不同版本之間的binlog(二進制日志)格式可能會不一樣，最后會導致同步出現異常。
系統：CentOS 6. 或 7.
MySQL版本：5.7
| IP | 主機名 | 角色 |
|---|---|---|
| 192.168.1.101 | MySQL-001 | master |
| 192.168.1.102 | MySQL-002 | slave |
Mysql的主從同步就是當master（主庫）發生數據變化的時候，會實時同步到slave（從庫）。
主從復制可以水平擴展數據庫的負載能力，容錯，高可用，數據備份。
不管是delete、update、insert，還是創建函數、存儲過程，都是在master上，當master有操作的時候，slace會快速的接受到這些操作，從而做同步。
當master上的數據發生變化的時候，該事件變化會按照順序寫入binlog中。當slave鏈接到master的時候，master機器會為slave開啟binlog dunp線程。當master的binlog發生變化的時候，binlog dump線程會通知slave，并將相應的binlog內容發送給slave。
當主從同步開啟的時候，slave上會創建兩個線程：I\O線程。該線程連接到master機器，master機器上的binlog dump 線程會將binlog的內容發送給該I\O線程。該I/O線程接收到binlog內容后，再將內容寫入到本地的relay log；sql線程。該線程讀取到I/O線程寫入的ralay log。并且根據relay log。并且根據relay log 的內容對slave數據庫做相應的操作。
一般mysql配置文件在/etc/my.cnf
(如果找不到的話也有可能在這些目錄下：/etc/mysql/my.cnf，/usr/local/mysql/etc/my.cnf，~/.my.cnf)
[root@MySQL-001 ~]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysqldata
socket=/tmp/mysql.sock
user=mysql
port=3306
# master的配置
server-id=1 # 服務器id (設置唯一標識)
binlog-do-db=employees # 要給從機同步的庫
binlog-ignore-db=mysql # 不給從機同步的庫(多個寫多行)
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
log-bin=mysql-bin # 打開日志(主機需要打開)，可以指定絕對路徑；
expire_logs_days=90 # 自動清理 90 天前的log文件,可根據需要修改[root@MySQL-001 ~]# service mysqld restart
[root@MySQL-001 ~]# service mysqld status
SUCCESS! MySQL running (15607) [root@MySQL-001 ~]# systemctl restart mysqld.service
[root@MySQL-001 ~]# systemctl status mysqld.service
mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)
Active: active (running) since 四 2018-05-17 11:42:02 CST; 2h 5min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 29959 (mysqld)
CGroup: /system.slice/mysqld.service
└─29959 /opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf
5月 17 11:42:02 tcloud-118 systemd[1]: Started MySQL Server.[root@MySQL-001 ~]# mysql -uroot -p
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------+
| log_bin | ON |
| log_bin_basename | /opt/mysql/logs/mysql-bin |
| log_bin_index | /opt/mysql/logs/mysql-bin.index |
| log_bin_trust_function_creators | ON |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+---------------------------------+
6 rows in set (0.00 sec)
mysql>可以看到log_bin為ON；
backup為用戶名，192.168.1.%表示只允許192.168.1網段的客戶端連接，123456為密碼；
mysql> grant replication slave on *.* to 'backup'@'192.168.1.%' identified by '123456'; # 創建同步賬戶
mysql> flush privileges; # 刷新權限
mysql> select Host,User,authentication_string from mysql.user; # 檢查是否創建
+--------------+---------------+-------------------------------------------+
| Host | User | authentication_string |
+--------------+---------------+-------------------------------------------+
| localhost | root | *6C362347EBEAA7DF44F6D34884615A35095E80EB |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| 192.168.1.% | backup | *9BB58B7F11A03B83C396FF506F3DF45727E79614 |
+--------------+---------------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql>backup@192.168.1.%賬戶已建立；
重啟MySQL服務并設置讀取鎖定，讀取鎖定的意思是只能讀取，不能更新，以便獲得一個一致性的快照；
mysql> flush table with read lock; # 主庫鎖表；默認28800秒，即8小時自動解鎖；
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 1621
Binlog_Do_DB:
Binlog_Ignore_DB: mysql
Executed_Gtid_Set: 1d3d078c-59a7-11e8-9a08-00163e000b3f:1-7
1 row in set (0.00 sec)
mysql>查看主服務器上當前的二進制日志名和偏移量值這里的file 和position 要和上面的一致；
導出master(192.168.1.101)上的數據，然后導入slave 中
格式：mysqldump -uUSER -pPASSWORD DATABASE TABLE > NAME.sql
[root@MySQL-001 ~]# mysqldump -uroot -p employees > /opt/employees.sql # 假如employees為主庫已經存在的庫
[root@MySQL-001 ~]# yum install openssh-clients -y # 可選
[root@MySQL-001 ~]# scp /opt/employees.sql root@192.168.1.102:/opt/[root@MySQL-002 ~]# yum install openssh-clients -y # 可選[root@MySQL-002 ~]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql # mysql程序路徑
datadir=/data/mysqldata # mysql數據目錄
socket=/tmp/mysql.sock
user=mysql
port=3306
# slave配置
server-id=2 # MySQLid 后面2個從服務器需設置不同
skip_slave_start=1 # 復制進程不會隨著數據庫的啟動而啟動,重啟數據庫后需手動啟動；
#加上以下參數可以避免更新不及時，SLAVE 重啟后導致的主從復制出錯。
read_only = 1 # 從庫普通賬戶只讀；
master_info_repository=TABLE
relay_log_info_repository=TABLE
#relay_log_recovery=1 # 從機禁止寫
#super_read_only=1 # 從機禁止寫CentOS 6.*：
[root@MySQL-002 ~]# service mysqld restart
[root@MySQL-002 ~]# service mysqld status
SUCCESS! MySQL running (15604)CentOS 7.*：
[root@MySQL-002 ~]# systemctl restart mysqld.service
[root@MySQL-002 ~]# systemctl status mysqld.service
mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)
Active: active (running) since 四 2018-05-17 11:42:02 CST; 2h 5min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 29959 (mysqld)
CGroup: /system.slice/mysqld.service
└─29959 /opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf
5月 17 11:42:02 tcloud-118 systemd[1]: Started MySQL Server.然后導入到mysql數據庫中，slave上的employees數據庫不存在則先創建，然后再導入
[root@MySQL-002 ~]# mysql -uroot -p
mysql> create database employees; # 新建這個庫
[root@MySQL-002 ~]# mysql -uroot -p employees < /opt/employees.sql登錄slave數據庫，并做如下設置
[root@MySQL-002 ~]# mysql -uroot -p
mysql> stop slave; # 關閉slave同步，第一次可略過；
mysql> change master to
-> master_host='192.168.1.101', # master的ip
-> master_user='backup', # 備份用戶名
-> master_password='123456', # 密碼
-> master_log_file='mysql-bin.000002', # 三.3，且要與master的參數一致
-> master_log_pos=1621; # 三.3，且要與master的參數一致合寫為：
mysql> change master to master_host='192.168.1.101', master_user='backup', master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=1621;
mysql> start slave; # 啟動同步
mysql> show slave status \G # 查看slave從機的狀態
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1621
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 1621
Relay_Log_Space: 521
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: 101
Master_UUID: 1d3d078c-59a7-11e8-9a08-00163e000b3f
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 5037e479-59a7-11e8-a35b-00163e000402:1-3
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql>下面對應參數相同代表設置成功，0延時；
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0mysql> unlock tables;mysql> use employees;
mysql> create table test001(id int auto_increment primary key,name varchar(20) not null);
mysql> insert into test001 values(null,'will');
mysql> insert into test001 values(null,'jim');
mysql> insert into test001 values(null,'tom');
mysql> select * from employees.test001;
+----+------+
| id | name |
+----+------+
| 1 | will |
| 2 | jim |
| 3 | tom |
+----+------+
3 rows in set (0.01 sec)mysql> use employees;
mysql> select * from employees.test001;
+----+------+
| id | name |
+----+------+
| 1 | will |
| 2 | jim |
| 3 | tom |
+----+------+mysql> drop table employees.test001;mysql> use employees;
mysql> show tables;以上實驗證明主從同步成功！！！
1、show slave status 顯示參數 Seconds_Behind_Master不為0，這個數值可能會很大
2、show slave status 顯示參數 Relay_Master_Log_File和Master_Log_File顯示bin-log的編號相差很大，說明bin-log在從庫上沒有及時同步，所以近期執行的bin-log和當前IO線程所讀的bin-log相差很大
3、mysql的從庫數據目錄下存在大量mysql-relay-log日志，該日志同步完成之后就會被系統自動刪除，存在大量日志，說明主從同步延遲很厲害
mysql主從同步原理：
主庫針對寫操作，順序寫binlog，從庫單線程去主庫順序讀”寫操作的binlog”，從庫取到binlog在本地原樣執行（隨機寫），來保證主從數據邏輯上一致。
mysql的主從復制都是單線程的操作，主庫對所有DDL和DML產生binlog，binlog是順序寫，所以效率很高，slave的Slave_IO_Running線程到主庫取日志，效率比較高，下一步，問題來了，slave的Slave_SQL_Running線程將主庫的DDL和DML操作在slave實施。DML和DDL的IO操作是隨即的，不是順序的，成本高很多，還可能可slave上的其他查詢產生lock爭用，由于Slave_SQL_Running也是單線程的，所以一個DDL卡主了，需要執行10分鐘，那么所有之后的DDL會等待這個DDL執行完才會繼續執行，這就導致了延時。
有朋友會問：“主庫上那個相同的DDL也需要執行10分，為什么slave會延時？”，答案是master可以并發，Slave_SQL_Running線程卻不可以。
當主庫的TPS并發較高時，產生的DDL數量超過slave一個sql線程所能承受的范圍，那么延時就產生了，當然還有就是可能與slave的大型query語句產生了鎖等待。
首要原因：數據庫在業務上讀寫壓力太大，CPU計算負荷大，網卡負荷大，硬盤隨機IO太高
次要原因：讀寫binlog帶來的性能影響，網絡傳輸延遲。
mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS /usr/local/mysql/log/relay-bin.004764 --start-position=94813395 |more其中：
mysqlbinlog mysql-bin.001314 | awk '/Table_map/{print $11}'|sort |uniq -c|sort -n1.業務的持久化層的實現采用分庫架構，mysql服務可平行擴展，分散壓力。
2.單個庫讀寫分離，一主多從，主寫從讀，分散壓力。這樣從庫壓力比主庫高，保護主庫。
3.服務的基礎架構在業務和mysql之間加入memcache或者redis的cache層。降低mysql的讀壓力。
4.不同業務的mysql物理上放在不同機器，分散壓力。
5.使用比主庫更好的硬件設備作為slave
總結，mysql壓力小，延遲自然會變小。
1.采用好服務器，比如4u比2u性能明顯好，2u比1u性能明顯好。
2.存儲用ssd或者盤陣或者san，提升隨機寫的性能。
3.主從間保證處在同一個交換機下面，并且是萬兆環境。
總結，硬件強勁，延遲自然會變小。一句話，縮小延遲的解決方案就是花錢和花時間。
1、sync_binlog在slave端設置為0
2、–logs-slave-updates 從服務器從主服務器接收到的更新不記入它的二進制日志。
3、直接禁用slave端的binlog
4、slave端，如果使用的存儲引擎是innodb，innodb_flush_log_at_trx_commit =2
主庫是寫，對數據安全性較高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類的設置是需要的
而slave則不需要這么高的數據安全，完全可以講sync_binlog設置為0或者關閉binlog，innodb_flushlog也可以設置為0來提高sql的執行效率
MySQL提供一個sync_binlog參數來控制數據庫的binlog刷到磁盤上去。
默認，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系統自己控制它的緩存的刷新。這時候的性能是最好的，但是風險也是最大的。一旦系統Crash，在binlog_cache中的所有binlog信息都會被丟失。
如果sync_binlog>0，表示每sync_binlog次事務提交，MySQL調用文件系統的刷新操作將緩存刷下去。最安全的就是sync_binlog=1了，表示每次事務提交，MySQL都會把binlog刷下去，是最安全但是性能損耗最大的設置。這樣的話，在數據庫所在的主機操作系統損壞或者突然掉電的情況下，系統才有可能丟失1個事務的數據。
但是binlog雖然是順序IO，但是設置sync_binlog=1，多個事務同時提交，同樣很大的影響MySQL和IO性能。
雖然可以通過group commit的補丁緩解，但是刷新的頻率過高對IO的影響也非常大。對于高并發事務的系統來說，
“sync_binlog”設置為0和設置為1的系統寫入性能差距可能高達5倍甚至更多。
所以很多MySQL DBA設置的sync_binlog并不是最安全的1，而是2或者是0。這樣犧牲一定的一致性，可以獲得更高的并發和性能。
默認情況下，并不是每次寫入時都將binlog與硬盤同步。因此如果操作系統或機器(不僅僅是MySQL服務器)崩潰，有可能binlog中最后的語句丟失了。要想防止這種情況，你可以使用sync_binlog全局變量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog寫入后與硬盤同步。即使sync_binlog設置為1,出現崩潰時，也有可能表內容和binlog內容之間存在不一致性。
抱怨Innodb比MyISAM慢100倍？那么你大概是忘了調整這個值。
默認值1的意思是每一次事務提交或事務外的指令都需要把日志寫入（flush）硬盤，這是很費時的。特別是使用電池供電緩存（Battery backed up cache）時。
設成2對于很多運用，特別是從MyISAM表轉過來的是可以的，它的意思是不寫入硬盤而是寫入系統緩存。日志仍然會每秒flush到硬盤，所以你一般不會丟失超過1-2秒的更新。
設成0會更快一點，但安全方面比較差，即使MySQL掛了也可能會丟失事務的數據。而值2只會在整個操作系統 掛了時才可能丟數據。
master端
修改linux、Unix文件系統中文件的etime屬性， 由于每當讀文件時OS都會將讀取操作發生的時間回寫到磁盤上，對于讀操作頻繁的數據庫文件來說這是沒必要的，只會增加磁盤系統的負擔影響I/O性能。可以通過設置文件系統的mount屬性，組織操作系統寫atime信息，在linux上的操作為：
打開/etc/fstab，加上noatime參數
/dev/sdb1 /data reiserfs noatime 1 2然后重新mount文件系統
mount -oremount /data
看了以上關于MySQL 5.7 主從原理、配置及測試講義，希望能給大家在實際運用中帶來一定的幫助。本文由于篇幅有限，難免會有不足和需要補充的地方，如有需要更加專業的解答，可在官網聯系我們的24小時售前售后，隨時幫您解答問題的。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
