主從形式
原理:
(1)master伺服器將資料的改變記錄二進位制binlog日誌,當master上的資料發生改變時,則將其改變寫入二進位制日誌中;
(2)slave伺服器會在一定時間間隔內對master二進位制日誌進行探測其是否發生改變,如果發生改變,則開始一個I/OThread請求master二進位制事件
(3)同時主節點為每個I/O執行緒啟動一個dump執行緒,用於向其傳送二進位制事件,並儲存至從節點本地的中繼日誌中,從節點將啟動SQL執行緒從中繼日誌中讀取二進位制日誌,在本地重放,使得其資料和主節點的保持一致。
主從複製配置
主從複製配置步驟:
- 確保從資料庫與主資料庫裡的資料一樣
- 在主資料庫裡建立一個同步賬號授權給從資料庫使用
- 配置主資料庫(修改配置檔案)
- 配置從資料庫(修改配置檔案
搭建兩臺MySQL
伺服器,一臺作為主伺服器,一臺作為從伺服器,主伺服器進行寫操作,從伺服器進行讀操作
主資料庫:IP192.168.248.130 無資料
從資料庫:IP192.168.248.131 無資料
關閉主從防火牆和selinux
[root@slave ~]# systemctl stop firewalld [root@slave ~]# setenforce 0
檢視主從資料庫又那些庫
#主庫 [root@master ~]# mysql -uroot -p123456 -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ #從庫 [root@slave ~]# mysql -uroot -p123456 -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
在主資料庫裡建立一個同步賬號授權給從資料庫使用
#主庫建立同步賬號 mysql> grant replication slave on *.* to 'test'@'192.168.248.131' identified by 'test123'; #在從庫登入測試 [root@slave ~]# mysql -utest -ptest123 -h192.168.248.130 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.31-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
配置主庫資料庫
#配置主庫資料庫 [mysqld] #在mysqld下 ............... ............... server-id = 10 # 新增啟用binlog日誌 log-bin = mysql_bin #新增據庫伺服器唯一識別符號,主庫的server-id值必須比從庫的小 #重啟mysql [root@localhost ~]# service mysqld start Starting MySQL. SUCCESS! #檢視主庫狀態 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql_bin.000001 | 451 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.01 sec)
配置從庫資料庫
#配置從庫資料庫 [root@slave ~]# vim /etc/my.cnf [mysqld] ............ ............ server-id = 20 relay-log = mysql_relay_bin #重啟mysql [root@slave ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.Logging to '/opt/data/slave.err'. SUCCESS! #配置並啟動主從複製 mysql> change master to -> master_host='192.168.248.130', -> master_user='test', -> master_password='test123', -> master_log_file='mysql_bin.000001', -> master_log_POS=451; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) #檢視從伺服器狀態 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.248.130 Master_User: test Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin.000001 Read_Master_Log_Pos: 451 Relay_Log_File: mysql_relay_bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql_bin.000001 Slave_IO_Running: Yes #看到兩個yes表示成功 Slave_SQL_Running: Yes
測試驗證
在主庫中寫入資料
#在主庫中建立表寫入資料 mysql> create database t1; Query OK, 1 row affected (0.01 sec) mysql> use t1; Database changed mysql> create table test(id int not null auto_increment,name varchar(20),age tinyint,primary key(id)); Query OK, 0 rows affected (0.01 sec) mysql> insert test(name,age) values('xx',34),('ww',24),('gg',25); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from test; +----+------+------+ | id | name | age | +----+------+------+ | 1 | xx | 34 | | 2 | ww | 24 | | 3 | gg | 25 | +----+------+------+ 3 rows in set (0.00 sec)
在從庫中檢視資料是否同步
[root@slave ~]# mysql -uroot -p123456 -e 'select * from t1.test;' mysql: [Warning] Using a password on the command line interface can be insecure. +----+------+------+ | id | name | age | +----+------+------+ | 1 | xx | 34 | | 2 | ww | 24 | | 3 | gg | 25 | +----+------+------+
搭建兩臺MySQL
伺服器,一臺作為主伺服器,一臺作為從伺服器,主伺服器進行寫操作,從伺服器進行讀操作
主資料庫:IP192.168.248.130 有資料
從資料庫:IP192.168.248.131 無資料
主庫上有資料
[root@localhost ~]# mysql -uroot -p -e 'show databases;' Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | jobs | | mysql | | performance_schema | | sys | +--------------------+ [root@master ~]# mysql -uroot -p -e 'select * from jobs.student;' Enter password: +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | alice | 12 | | 2 | natasha | 12 | | 3 | harry | 11 | +----+---------+------+
從庫上無資料
[root@slave ~]# mysql -uroot -p -e 'show databases;' Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
全備主庫
#全備主庫時需要另開一個終端,給資料庫加上讀鎖,避免在備份期間有其他人在寫入導致資料不一致 mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.01 sec [root@master ~]# mysqldump -uroot -p123456 --all-databases > all_$(date +%F_%T).sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@master ~]# ls all_2021-01-02_18:34:26.sql #將備份資料推送至從伺服器 [root@master ~]# scp /root/all_2021-01-02_18\:34\:26.sql 'root'@192.168.248.131:/root/ root@192.168.248.131's password: all_2021-01-02_18:34:26.sql 100% 843KB 87.2MB/s 00:00 #同步從庫與主庫,檢視資料 [root@slave ~]# mysql -uroot -p <all_2021-01-02_18\:34\:26.sql Enter password: [root@slave ~]# mysql -uroot -p -e 'show databases;' Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | jobs | | mysql | | performance_schema | | sys | +--------------------+ [root@slave ~]# mysql -uroot -p -e 'select * from jobs.student;' Enter password: +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | alice | 12 | | 2 | natasha | 12 | | 3 | harry | 11 | +----+---------+------+ #退出讀鎖終端
後面就和主從無資料配置一樣