溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容主要講解“MySQL復制的原理和作用是什么”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MySQL復制的原理和作用是什么”吧!
一、MySQL復制相關概念
二、簡單的一主一從架構實現
1、新數據庫搭建主從架構
2、舊數據庫新加從服務器
三、級聯復制架構實現
四、主主復制架構
五、半同步復制的實現
六、加密傳輸復制的實現
七、MySQL復制的相關指令和變量總結
主從復制：主節點將數據同步到多個從節點
級聯復制：主節點將數據同步到一個從節點，其他的從節點在向從節點復制數據
同步復制：將數據從主節點全部同步到從節點時才返回給用戶的復制策略叫同步復制
異步復制：只要數據寫入到主節點就立即返回給用戶同步完成
讀寫分離：在前端加一個調度器，負責將改變數據的語句和查詢數據的語句分開調度，把寫操作調度到主節點，讀操作調度到從節點
主節點：
dump Thread：為每個Slave的I/O Thread啟動一個dump線程，用于向其發送binary log events
從節點：
I/O Thread：向Master請求二進制日志事件，并保存于中繼日志中
SQL Thread：從中繼日志中讀取日志事件，在本地完成重放
跟復制功能相關的文件：
master.info：用于保存slave連接至master時的相關信息，例如賬號、密碼、服務器地址等
relay-log.info：保存在當前slave節點上已經復制的當前二進制日志和本地replay log日志的對應關系
復制架構：
一主一從
一主多從
主主復制
環狀復制
級聯復制
多主一從
常見的架構有主從架構或者級聯架構
1）主服務器配置
~]# vim /etc/my.cnf [mysqld] log_bin binlog_format=ROW log-basename=master1 server_id=1 ~]# systemctl restart mariadb ~]# mysql MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO testuser@'192.168.0.8' IDENTIFIED BY 'testpass'; #授權同步賬戶 MariaDB [(none)]> SHOW MASTER LOGS; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | master1-bin.000001 | 26756 | | master1-bin.000002 | 921736 | | master1-bin.000003 | 401 | #記錄此位置，從服務器從這里開始同步 +--------------------+-----------+
2）從服務器配置
~]# vim /etc/my.cnf [mysqld] server_id=2 #服務器ID唯一 relay_log=relay-log relay_log_index=relay-log.index read_only=ON ~]# systemctl restart mariadb ~]# mysql MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.7', #指定主節點IP -> MASTER_USER='testuser', #同步用戶的用戶名 -> MASTER_PASSWORD='testpass', #密碼 -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master1-bin.000003', #以上記錄的文件 -> MASTER_LOG_POS=401, #位置 -> MASTER_CONNECT_RETRY=10; #重試時間10秒 MariaDB [(none)]> START SLAVE; #開始主從復制
3）測試
在主節點上生成一些數據：
MariaDB [(none)]> CREATE DATABASE testdb;
MariaDB [(none)]> use testdb
MariaDB [testdb]> create table testlog (id int auto_increment primary key,name char(30),age int default 20);
MariaDB [testdb]> delimiter $$
MariaDB [testdb]> create procedure pro_testlog()
-> begin
-> declare i int;
-> set i = 1;
-> while i < 100000
-> do insert into testlog(name,age) values (concat('testuser',i),i);
-> set i = i +1;
-> end while;
-> end$$
MariaDB [testdb]> delimiter ;
MariaDB [testdb]> START TRANSACTION;
MariaDB [testdb]> CALL pro_testlog;
MariaDB [testdb]> COMMIT;在從節點上查看同步情況： MariaDB [(none)]> SELECT COUNT(*) FROM testdb.testlog; +----------+ | COUNT(*) | +----------+ | 99999 | #同步成功 +----------+ MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row **************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.7 Master_User: testuser Master_Port: 3306 Connect_Retry: 10 Master_Log_File: master1-bin.000003 Read_Master_Log_Pos: 10389814 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 10389944 Relay_Master_Log_File: master1-bin.000003 Slave_IO_Running: Yes #IO線程已啟動 Slave_SQL_Running: Yes #SQL線程已啟動 Seconds_Behind_Master: 0 #主從復制的時間差 Master_Server_Id: 1
1）主服務器配置
~]# vim /etc/my.cnf [mysqld] log_bin binlog_format=ROW log-basename=master1 server_id=1 ~]# systemctl restart mariadb ~]# mysqldump -A -F --single-transaction --master-data=1 > full.sql ~]# scp full.sql root@192.168.0.8:/root/ ~]# mysql -e 'GRANT REPLICATION SLAVE ON *.* TO testuser@'192.168.0.8' IDENTIFIED BY 'testpass';'
2）從服務器配置
~]# vim /etc/my.cnf [mysqld] server_id=2 relay_log=relay-log relay_log_index=relay-log.index read_only=ON ~]# systemctl restart mariadb ~]# vim full.sql #在備份的SQL文件中加入以下信息 CHANGE MASTER TO MASTER_HOST='192.168.0.7', MASTER_USER='testuser', MASTER_PASSWORD='testpass', MASTER_PORT=3306, MASTER_LOG_FILE='master1-bin.000005', MASTER_LOG_POS=245, MASTER_CONNECT_RETRY=10; ~]# mysql < full.sql #導入SQL的同時配置已經完成 MariaDB [(none)]> SELECT COUNT(*) FROM testdb.testlog; +----------+ | COUNT(*) | +----------+ | 99999 | +----------+ MariaDB [(none)]> START SLAVE; #啟動復制
1）主節點
[root@master ~]# vim /etc/my.cnf [mysqld] log_bin binlog_format=ROW log-basename=master server_id=1 [root@master ~]# systemctl restart mariadb MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass'; MariaDB [(none)]> SHOW MASTER LOGS; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 26753 | | master-bin.000002 | 921736 | | master-bin.000003 | 401 | +-------------------+-----------+
2）從節點
[root@slave1 ~]# vim /etc/my.cnf [mysqld] log_bin #注意，級聯架構中中繼從節點一定得開二進制日志功能 binlog_format=ROW read_only=ON server_id=2 log_slave_updates #這項為關鍵，作用是將從服務的數據改變記錄到二進制日志文件中 relay_log=relay-log relay_log_index=relay-log.index [root@slave1 ~]# systemctl start mariadb MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.7', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master-bin.000003', -> MASTER_LOG_POS=401, -> MASTER_CONNECT_RETRY=10; MariaDB [(none)]> START SLAVE; MariaDB [(none)]> SHOW MASTER LOGS; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 245 | +--------------------+-----------+ MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';
3）從節點的從節點
[root@slave2 ~]# vim /etc/my.cnf [mysqld] read_only=ON server_id=3 relay_log=relay-log relay_log_index=relay-log.index [root@slave2 ~]# systemctl start mariadb MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.8', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000001', -> MASTER_LOG_POS=245, -> MASTER_CONNECT_RETRY=10; MariaDB [(none)]> START SLAVE;
4）從節點的從節點2
[root@slave3 ~]# vim /etc/my.cnf [mysqld] read_only=ON server_id=4 relay_log=relay-log relay_log_index=relay-log.index [root@slave3 ~]# systemctl start mariadb MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.8', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000001', -> MASTER_LOG_POS=245, -> MASTER_CONNECT_RETRY=10; MariaDB [(none)]> START SLAVE; 到此已經搭建好了級聯復制，接下來測試一下把~
容易產生的問題：數據不一致，因此慎用；考慮要點：自動增長id
配置一個節點使用奇數id
auto_increment_offset=1 開始點
auto_increment_increment=2 增長幅度
另一個節點使用偶數id
auto_increment_offset=2
auto_increment_increment=2
1）主1
[mysqld] log_bin binlog_format=ROW log-basename=master1 server_id=1 relay_log=relay-log relay_log_index=relay-log.index auto_increment_offset=1 #自增長字段從1開始 auto_increment_increment=2 #每次增長2，也就是說master1節點寫入的數據的id字段全部是奇數 [root@master ~]# systemctl start mariadb MariaDB [(none)]> SHOW MASTER LOGS; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | master1-bin.000001 | 27033 | | master1-bin.000002 | 942126 | | master1-bin.000003 | 245 | +--------------------+-----------+ MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass'; MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.8', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master2-bin.000003', -> MASTER_LOG_POS=245, -> MASTER_CONNECT_RETRY=10; MariaDB [(none)]> START SLAVE;
2）主2
[mysqld] log_bin binlog_format=ROW log-basename=master2 server_id=2 relay_log=relay-log relay_log_index=relay-log.index auto_increment_offset=2 #自增長字段從1開始 auto_increment_increment=2 #每次增長2，也就是說master1節點寫入的數據的id字段全部是偶數 [root@master2 ~]# systemctl start mariadb MariaDB [(none)]> SHOW MASTER LOGS; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | master2-bin.000001 | 27036 | | master2-bin.000002 | 942126 | | master2-bin.000003 | 245 | +--------------------+-----------+ MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass'; MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.7', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master1-bin.000003', -> MASTER_LOG_POS=245, -> MASTER_CONNECT_RETRY=10; MariaDB [(none)]> START SLAVE;
3）測試
在master1上創建表，增加數據
MariaDB [(none)]> CREATE DATABASE db1;
MariaDB [(none)]> use db1
MariaDB [db1]> CREATE TABLE t1(id INT(2) AUTO_INCREMENT PRIMARY KEY,name CHAR(30));
MariaDB [db1]> INSERT t1(name) VALUES ('tom');
MariaDB [db1]> INSERT t1(name) VALUES ('maria');
MariaDB [db1]> SELECT * FROM t1;
+----+-------+
| id | name |
+----+-------+
| 1 | tom |
| 3 | maria |
+----+-------+
在master2上增加數據
MariaDB [db1]> INSERT t1(name) VALUES ('jerry');
MariaDB [db1]> INSERT t1(name) VALUES ('tony');
MariaDB [db1]> SELECT * FROM t1;
+----+-------+
| id | name |
+----+-------+
| 1 | tom |
| 3 | maria |
| 4 | jerry |
| 6 | tony |默認情況下，MySQL的復制功能是異步的，異步復制可以提供最佳的性能，主庫把binlog日志發送給從庫即結束，并不驗證從庫是否接收完畢。這意味著當主服務器或從服務器端發生故障時，有可能從服務器沒有接收到主服務器發送過來的binlog日志，這就會造成主服務器和從服務器的數據不一致，甚至在恢復時造成數據的丟失；半同步復制的機制是只有當主節點和從節點同步完成，僅有一臺同步完成即可，返回寫入完成，這樣的機制保證了數據的安全性。
1）主節點
[root@master ~]# vim /etc/my.cnf [mysqld] log_bin binlog_format=ROW log-basename=master server_id=1 relay_log=relay-log relay_log_index=relay-log.index [root@master ~]# systemctl restart mariadb MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass'; MariaDB [(none)]> SHOW MASTER LOGS; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 26753 | | master-bin.000002 | 921736 | | master-bin.000003 | 401 | +-------------------+-----------+ MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #安裝模塊 MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1; #開啟半同步功能 MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | ON | #已開啟 | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+
2）從節點1
[root@slave1 ~]# vim /etc/my.cnf [mysqld] read_only=ON log_bin binlog_format=ROW log-basename=slave server_id=2 relay_log=relay-log relay_log_index=relay-log.index [root@slave1 ~]# systemctl restart mariadb MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.7', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master-bin.000003', -> MASTER_LOG_POS=401, -> MASTER_CONNECT_RETRY=10; MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1; MariaDB [(none)]> START SLAVE; MariaDB [(none)]> SHOW MASTER LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | slave-bin.000001 | 26753 | | slave-bin.000002 | 921736 | | slave-bin.000003 | 245 | +------------------+-----------+ MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';
3）從節點2
[root@slave2 ~]# vim /etc/my.cnf [mysqld] read_only=ON server_id=3 relay_log=relay-log relay_log_index=relay-log.index [root@slave2 ~]# systemctl restart mariadb MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.8', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='slave-bin.000003', -> MASTER_LOG_POS=245, -> MASTER_CONNECT_RETRY=10; MariaDB [(none)]> START SLAVE;
4）從節點3
[root@slave3 ~]# vim /etc/my.cnf [mysqld] read_only=ON server_id=4 relay_log=relay-log relay_log_index=relay-log.index [root@slave3 ~]# systemctl restart mariadb MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.8', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='slave-bin.000003', -> MASTER_LOG_POS=245, -> MASTER_CONNECT_RETRY=10; MariaDB [(none)]> START SLAVE;
在默認的主從復制過程或遠程連接到MySQL/MariaDB所有的鏈接通信中的數據都是明文的，外網里訪問數據或則復制，存在安全隱患。通過SSL/TLS加密的方式進行復制的方法，來進一步提高數據的安全性
主服務器開啟SSL：[mysqld] 加一行ssl
主服務器配置證書和私鑰；并且創建一個要求必須使用SSL連接的復制賬號
從服務器使用CHANGER MASTER TO 命令時指明ssl相關選項
MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | +---------------+----------+特別提示：在配置之前先檢查mysql服務是否支持ssl功能，如果have_ssl的值為'DISABLED'則支持；如果為'NO'則不支持，需要再重新編譯安裝或者安裝具有ssl功能的版本
1）CA
[root@CA ~]# mkdir /etc/my.cnf.d/ssl/ [root@CA ~]# cd /etc/my.cnf.d/ssl/ [root@CA ssl]# openssl genrsa 2048 > cakey.pem [root@CA ssl]# openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650 #自簽證書 Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:testmysqlca Organizational Unit Name (eg, section) []:opt Common Name (eg, your name or your server's hostname) []:ca.testmysqlca.com [root@CA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key > master.csr Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:testmysqlca Organizational Unit Name (eg, section) []:opt Common Name (eg, your name or your server's hostname) []:master.testmysqlca.com [root@CA ssl]# openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt #簽署master證書 [root@CA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave.key > slave.csr Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:testmysqlca Organizational Unit Name (eg, section) []:opt Common Name (eg, your name or your server's hostname) []:slave.testmysqlca.com [root@CA ssl]# openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt #簽署slave證書 [root@CA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave2.key > slave2.csr Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:testmysqlca Organizational Unit Name (eg, section) []:opt Common Name (eg, your name or your server's hostname) []:slave2.testmysqlca.com [root@CA ssl]# openssl x509 -req -in slave2.csr -CA cacert.pem -CAkey cakey.pem -set_serial 03 > slave2.crt #簽署slave2證書 [root@CA ssl]# openssl verify -CAfile cacert.pem master.crt slave.crt slave2.crt #檢查證書是否可用 master.crt: OK slave.crt: OK slave2.crt: OK 先在各個節點上創建/etc/my.cnf.d/ssl/文件夾，將各自的證書，CA的證書和各自的秘鑰文件復制過去 [root@CA ssl]# scp cacert.pem master.crt master.key root@192.168.0.7:/etc/my.cnf.d/ssl/ [root@CA ssl]# scp cacert.pem slave.crt slave.key root@192.168.0.8:/etc/my.cnf.d/ssl/ [root@CA ssl]# scp cacert.pem slave2.crt slave2.key root@192.168.0.9:/etc/my.cnf.d/ssl/
2）master
[root@master ~]# mkdir /etc/my.cnf.d/ssl/ [root@master ~]# vim /etc/my.cnf [mysqld] log_bin binlog_format=ROW log-basename=master server_id=1 ssl #開啟ssl功能 ssl-ca=/etc/my.cnf.d/ssl/cacert.pem #指定CA證書的路徑 ssl-cert=/etc/my.cnf.d/ssl/master.crt #指定自己的證書的路徑 ssl-key=/etc/my.cnf.d/ssl/master.key #指定自己的秘鑰文件路徑 [root@master ~]# systemctl restart mariadb MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass' REQUIRE SSL; #授權用戶并且強制迫使用戶開啟ssl登錄 MariaDB [(none)]> SHOW MASTER LOGS; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 26753 | | master-bin.000002 | 921736 | | master-bin.000003 | 413 | +-------------------+-----------+
3）slave1
[root@slave1 ~]# mkdir /etc/my.cnf.d/ssl/ [root@slave1 ~]# mysql -urepluser -preplpass -h292.168.0.7 --ssl-ca=/etc/my.cnf.d/ssl/cacert.pem --ssl-cert=/etc/my.cnf.d/ssl/slave.crt --ssl-key=/etc/my.cnf.d/ssl/slave.key [root@slave1 ~]# vim /etc/my.cnf [mysqld] read_only=ON server_id=2 relay_log=relay-log relay_log_index=relay-log.index ssl ssl-ca=/etc/my.cnf.d/ssl/cacert.pem ssl-cert=/etc/my.cnf.d/ssl/slave.crt ssl-key=/etc/my.cnf.d/ssl/slave.key [root@slave1 ~]# systemctl restart mariadb MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.7', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master-bin.000003', -> MASTER_LOG_POS=413, -> MASTER_CONNECT_RETRY=10, -> MASTER_SSL=1; #注意，需要指明開啟ssl鏈接 MariaDB [(none)]> START SLAVE;
4）slave2
[root@slave2 ~]# mkdir /etc/my.cnf.d/ssl/ [root@slave2 ~]# mysql -urepluser -preplpass -h292.168.0.7 --ssl-ca=/etc/my.cnf.d/ssl/cacert.pem --ssl-cert=/etc/my.cnf.d/ssl/slave2.crt --ssl-key=/etc/my.cnf.d/ssl/slave2.key [root@slave2 ~]# vim /etc/my.cnf [mysqld] read_only=ON server_id=3 relay_log=relay-log relay_log_index=relay-log.index ssl ssl-ca=/etc/my.cnf.d/ssl/cacert.pem ssl-cert=/etc/my.cnf.d/ssl/slave2.crt ssl-key=/etc/my.cnf.d/ssl/slave2.key [root@slave2 ~]# systemctl restart mariadb MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.7', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master-bin.000003', -> MASTER_LOG_POS=413, -> MASTER_CONNECT_RETRY=10, -> MASTER_SSL=1; MariaDB [(none)]> START SLAVE;
選項：
log_bin 啟用二進制日志，在主節點或級聯復制中間的從節點必須要開啟
binlog_format=ROW 二進制日志記錄方式為基于行的方式記錄，強烈建議開啟
log-basename=master | slave ... 二進制日志的前綴名，不是必須向，但建議標識
server_id = # 服務器ID，各個節點的ID必須唯一
relay_log = relay-log 開啟中繼日志，并以relay-log為文件名開頭，從節點開啟
relay_log_index = relay-log.index 中繼日志索引文件
log_slave_updates 作用是SQL線程重讀中繼日志時將改變數據的操作記錄為二進制日志，在級聯復制中使用
ssl 開啟ssl功能
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key
sync_binlog=1 每次寫后立即同步二進制日志到磁盤
innodb_flush_log_at_trx_commit=1 每次事務提交立即同步日志寫磁盤
sync_master_info=# #次事件后master.info同步到磁盤
skip_slave_start=ON 不自動啟動slave
sync_relay_log=# #次寫后同步relay log到磁盤
sync_relay_log_info=# #次事務后同步relay-log.info到磁盤
auto_increment_offset=1 自動增長開始點，在主主復制中使用
變量：
replicate_do_db= 指定復制庫的白名單
replicate_ignore_db= 指定復制庫黑名單
replicate_do_table= 指定復制表的白名單
replicate_ignore_table= 指定復制表的黑名單
replicate_wild_do_table= foo%.bar% 支持通配符
replicate_wild_ignore_table= 指定復制的表，黑名單
rpl_semi_sync_slave_enabled=1 開啟半同步復制，需要安裝模塊
指令：
START SLAVE; 啟動主從復制
STOP SLAVE; 停止復制
SHOW SLAVE STATUS; 查看復制狀態
Seconds_Behind_Master: 0 從服務器是否落后于主服務
RESET SLAVE ALL; 重置從服務器的配置
MASTER_SSL=1, 配合 CHANGE MASTER TO 使用，開啟ssl加密復制
MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem',
MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt',
MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr } 刪除二進制日志，謹慎操作
SHOW MASTER STATUS 查看二進制日志狀態
SHOW BINLOG EVENTS 查看二進制日志
SHOW BINARY LOGS 查看二進制日志
到此，相信大家對“MySQL復制的原理和作用是什么”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
