mysql主從

mysql主從
- 1.主從簡介
  - 1.1 主從作用
  - 1.2 主從形式
- 2.主從復制原理
- 3.主從復制配置
  - 3.1 mysql安裝
  - 3.2 mysql主從配置
    - 3.2.1 確保從資料庫與主資料庫里的資料一樣
    - 3.2.2 在主資料庫里創建一個同步賬號授權給從資料庫使用
    - 3.2.3 配置主資料庫
    - 3.2.4 配置從資料庫
    - 測驗驗證
- 4. GTID主從
  - 4.1 GTID概念介紹
  - 4.2 GTID作業原理
  - 4.3 GTID主從配置

1.主從簡介

用一臺資料庫存放資料，若此資料庫服務器宕機了導致資料丟失怎么辦？
業務量大了，資料多了，訪問的人多了，一臺資料庫無法保證服務質量了怎么辦？

1.1 主從作用

防范出現問題，用于故障的切換
讀寫分離，方便進行查詢
備份，反正意外丟失資料

1.2 主從形式

一主一從(一個主資料庫一個輔助資料庫)
主主復制(兩臺資料庫同步)
一主多從---擴展系統讀取的性能，因為讀是在從庫讀取的
多主一從---5.7開始支持
聯級復制

2.主從復制原理

主從復制步驟：
主庫將所有的寫操作記錄到binlog日志中并生成一個log dump執行緒，將binlog日志傳給從庫的I/O執行緒
從庫生成兩個執行緒，一個I/O執行緒，一個SQL執行緒
I/O執行緒去請求主庫的binlog，并將得到的binlog日志寫到relay log（中繼日志）檔案中
SQL執行緒，會讀取relay log檔案中的日志，并決議成具體操作，來實作主從的操作一致，達到最終資料一致的目的

3.主從復制配置

主從復制配置步驟：

確保從資料庫與主資料庫里的資料一樣
在主資料庫里創建一個同步賬號授權給從資料庫使用
配置主資料庫（修改組態檔）
配置從資料庫（修改組態檔）
需求：
搭建兩臺MySQL服務器，一臺作為主服務器，一臺作為從服務器，主服務器進行寫操作，從服務器進行讀操作
環境說明：

資料庫角色	IP	應用與系統版本	有無資料
主資料庫	192.168.222.250	centos8/redhat8 mysql-5.7	有資料
從資料庫	192.168.222.251	centos8/redhat8 mysql-5.7	無資料

3.1 mysql安裝

分別在主從兩臺服務器上安裝mysql-5.7版本，此處略過安裝步驟，若有疑問請參考《mysql進階》

3.2 mysql主從配置

3.2.1 確保從資料庫與主資料庫里的資料一樣

為確保從資料庫與主資料庫里的資料一樣，先全備主資料庫并還原到從資料庫中

先查看主庫有哪些庫:
[root@master ~]# mysql -uroot -plnh123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lnh                |
| mysql              |
| performance_schema |
| sys                |
| tushanbu           |
+--------------------+
再查看從庫有哪些庫:
[root@slave ~]# mysql -uroot -plnh123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
全備主庫:
//全備主庫時需要另開一個終端，給資料庫加上讀鎖，避免在備份期間有其他人在寫入導致資料不一致
[root@master ~]# mysql -uroot -plnh123 
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 8
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> flush tables with read lock;
Query OK, 0 rows affected (0.05 sec)
//此鎖表的終端必須在備份完成以后才能退出
因為主庫已經鎖住，我們可以再開一個主庫的終端進行操作
[root@master ~]# mysqldump -uroot -plnh123 --all-databases > /opt/all-$(date '+%Y%m%d%H%M%S').sql  //全量備份主庫
mysqldump: [Warning] Using a password on the command line interface can be insecure
[root@master ~]# ll /opt/
total 864
-rw-r--r--. 1 root  root  878669 Aug  2 00:42 all-20220802004207.sql
drwxr-xr-x. 7 mysql mysql   4096 Jul 27 22:04 xbz
[root@master ~]# scp /opt/all-20220802004207.sql [email protected]:/opt/
The authenticity of host '192.168.222.251 (192.168.222.251)' can't be established.
ECDSA key fingerprint is SHA256:y11UDaNXs3AnvVUnZQfAim2VHAplF09YOvQp2NemHyk.
Are you sure you want to continue connecting (yes/no/[fingerprint])? y
Please type 'yes', 'no' or the fingerprint: yes
Warning: Permanently added '192.168.222.251' (ECDSA) to the list of known hosts.
[email protected]'s password: 
Permission denied, please try again.
[email protected]'s password: 
all-20220802004207.sql                           100%  858KB  91.7MB/s   00:00    
//將主庫的備份資料傳送到從庫里面去
mysql> quit
Bye
//在主里面退出鎖定
在從庫里面：
[root@slave ~]# ll /opt/
total 864
-rw-r--r--. 1 root  root  878669 Aug  2 00:45 all-20220802004207.sql
drwxr-xr-x. 5 mysql mysql   4096 Aug  1 23:45 xbz
//可以查看到有資料備份的檔案
[root@slave ~]# mysql -uroot -plnh123 < /opt/all-20220802004207.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@slave ~]# mysql -uroot -plnh123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lnh                |
| mysql              |
| performance_schema |
| sys                |
| tushanbu           |
+--------------------+
//將主庫里面的資料成功全部備份到從庫里面實作了主庫和從庫里面的資料一致

3.2.2 在主資料庫里創建一個同步賬號授權給從資料庫使用

[root@master ~]# mysql -uroot -plnh123
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 10
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> grant replication slave on *.* to 'repl'@'192.168.222.251' identified by 'repl123';    
Query OK, 0 rows affected, 1 warning (0.01 sec)
//這里是授權從庫的主機ip
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye

3.2.3 配置主資料庫

[root@master ~]# vim /etc/my.cnf 
[root@master ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/xbz
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/xbz/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


log-bin=mysql_bin  //啟用binlog日志
server-id = 10     //資料庫服務器唯一識別符號，主庫的server-id值必須比從庫的大   //添加這兩行 10的目的是因為后面可能會有主庫什么的
[root@master ~]# systemctl restart mysqld.service //重啟服務
[root@master ~]# ss -antl    //查看埠
State    Recv-Q   Send-Q     Local Address:Port      Peer Address:Port   Process   
LISTEN   0        128              0.0.0.0:22             0.0.0.0:*                
LISTEN   0        128                    *:80                   *:*                
LISTEN   0        128                 [::]:22                [::]:*                
LISTEN   0        80                     *:3306                 *:*                
[root@master ~]# mysql -uroot -plnh123
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 2
Server version: 5.7.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> show master status;    //查看狀態
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.2.4 配置從資料庫

[root@slave ~]# vim /etc/my.cnf 
[root@slave ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/xbz
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/xbz/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

server-id = 20  //設定從庫的唯一識別符號，從庫的server-id值必須大于主庫的該值  
relay-log = myrelay //啟用中繼日志relay-log
[root@slave ~]# systemctl restart mysqld.service //重啟服務
[root@slave ~]# ss -antl   //查看埠
State    Recv-Q   Send-Q     Local Address:Port      Peer Address:Port   Process   
LISTEN   0        128              0.0.0.0:22             0.0.0.0:*                
LISTEN   0        128                 [::]:22                [::]:*                
LISTEN   0        80                     *:3306                 *:*                
[root@slave ~]# mysql -uroot -plnh123
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 2
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> change master to    //配置并啟動主從復制
    -> master_host='192.168.222.250',
    -> master_user='repl',
    -> master_password='repl123',
    -> master_log_file='mysql_bin.000001',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 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.222.250
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 603
               Relay_Log_File: myrelay.000002
                Relay_Log_Pos: 769
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes  //這里必須是yes(這里如果沒有yes那么就是找不到指定東西)
            Slave_SQL_Running: Yes  //這里必須是yes(這里如果沒有yes那么就是查詢不了)
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           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: 603
              Relay_Log_Space: 968
              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: 10
                  Master_UUID: 09b3989b-0d96-11ed-b83a-000c2905f428
             Master_Info_File: /opt/xbz/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: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

測驗驗證

在主服務器的lnh庫的xbz表中插入資料：

mysql> use lnh;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table xbz(id int not null,name varchar(20),age int);
Query OK, 0 rows affected (0.03 sec)

mysql> desc xbz;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into xbz values (1,'dad',20),(2,'tom',22),(3,'jerry',12);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from xbz;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | dad   |   20 |
|  2 | tom   |   22 |
|  3 | jerry |   12 |
+----+-------+------+
3 rows in set (0.00 sec)

在從資料庫中查看資料是否同步：

[root@slave ~]# mysql -uroot -plnh123 
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 6
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> use lnh;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from xbz;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | dad   |   20 |
|  2 | tom   |   22 |
|  3 | jerry |   12 |
+----+-------+------+
3 rows in set (0.00 sec)
//測驗成功

4. GTID主從

4.1 GTID概念介紹

GTID即全域事務ID (global transaction identifier), 其保證為每一個在主上提交的事務在復制集群中可以生成一個唯一的ID，GTID最初由google實作，官方MySQL在5.6才加入該功能，mysql主從結構在一主一從情況下對于GTID來說就沒有優勢了，而對于2臺主以上的結構優勢例外明顯，可以在資料不丟失的情況下切換新主，使用GTID需要注意: 在構建主從復制之前，在一臺將成為主的實體上進行一些操作（如資料清理等），通過GTID復制，這些在主從成立之前的操作也會被復制到從服務器上，引起復制失敗，也就是說通過GTID復制都是從最先開始的事務日志開始，即使這些操作在復制之前執行，比如在server1上執行一些drop、delete的清理操作，接著在server2上執行change的操作，會使得server2也進行server1的清理操作，
GTID實際上是由UUID+TID (即transactionId)組成的，其中UUID(即server_uuid) 產生于auto.conf檔案(cat /data/mysql/data/auto.cnf)，是一個MySQL實體的唯一標識，TID代表了該實體上已經提交的事務數量，并且隨著事務提交單調遞增，所以GTID能夠保證每個MySQL實體事務的執行（不會重復執行同一個事務，并且會補全沒有執行的事務），GTID在一組復制中，全域唯一.

如圖：server1（master）宕機了，這個時候server2（slave）已經同步到了server1的全部資料，而server3（slave）只是同步到了一半，這時要是把Server2提升為主，Server3變成Server2的從，這時在Server3上執行change的時候需要做一些計算，這個問題在5.6的GTID出現后，就顯得非常的簡單，由于同一事務的GTID在所有節點上的值一致，那么根據Server3當前停止點的GTID就能定位到Server2上的GTID，甚至由于MASTER_AUTO_POSITION功能的出現，我們都不需要知道GTID的具體值，直接使用CHANGE MASTER TO MASTER_HOST='xxx', MASTER_AUTO_POSITION命令就可以直接完成failover的作業，
====== GTID和Binlog的關系 ======

GTID在binlog中的結構：

GTID event 結構:

Previous_gtid_log_event
Previous_gtid_log_event 在每個binlog 頭部都會有每次binlog rotate的時候存盤在binlog頭部Previous-GTIDs在binlog中只會存盤在這臺機器上執行過的所有binlog，不包括手動設定gtid_purged值，換句話說，如果你手動set global gtid_purged=xx；那么xx是不會記錄在Previous_gtid_log_event中的，

GTID和Binlog之間的關系是怎么對應的呢? 如何才能找到GTID=? 對應的binlog檔案呢？
假設有3個binlog: bin.001,bin.002,bin.003,
bin.001 : Previous-GTIDs=empty; binlog_event有: 1-40
bin.002 : Previous-GTIDs=1-40; binlog_event有: 41-80
bin.003 : Previous-GTIDs=1-80; binlog_event有: 81-120
假設現在我們要找GTID=$A，那么MySQL的掃描順序為:
從最后一個binlog開始掃描（即: bin.003）
bin.003的Previous-GTIDs=1-80，如果$A=100 > Previous-GTIDs,那么肯定在bin.003中
bin.003的Previous-GTIDs=1-80，如果$A=79 包含在Previous-GTIDs中,那么繼續對比上一個binlog檔案 bin.002,然后再回圈前面2個步驟，直到找到為止.

GTID相關引數:

引數	comment
gtid_executed	執行過的所有GTID
gtid_purged	丟棄掉的GTID
gtid_mode	GTID模式
gtid_next	session級別的變數，下一個gtid
gtid_owned	正在運行的GTID
enforce_gtid_consistency	保證GTID安全的引數

開啟GTID的必備條件:
gtid_mode=on (必選)
enforce-gtid-consistency=1 （必選）
log_bin=mysql-bin （可選） #高可用切換，最好開啟該功能
log-slave-updates=1 （可選） #高可用切換，最好打開該功能

4.2 GTID作業原理

master更新資料時，會在事務前產生GTID，一同記錄到binlog日志中，
slave端的i/o 執行緒將變更的binlog，寫入到本地的relay log中，
sql執行緒從relay log中獲取GTID，然后對比slave端的binlog是否有記錄，
如果有記錄，說明該GTID的事務已經執行，slave會忽略，
如果沒有記錄，slave就會從relay log中執行該GTID的事務，并記錄到binlog，
在決議程序中會判斷是否有主鍵，如果沒有就用二級索引，如果沒有就用全部掃描，

4.3 GTID主從配置

環境說明：

資料庫角色	IP	應用與系統版本
主資料庫	192.168.222.250	centos8/redhat8 mysql-5.7
從資料庫	192.168.222.251	centos8/redhat8 mysql-5.7

我這里是直接進行yum/dnf下載mysql的
詳細操作可以看我的《mysql基礎里面》
做之前要確保主從庫的里面資料一樣
主庫配置，vim /etc/my.cnf，添加以下配置，重啟mysql，

[root@master ~]# vim /etc/my.cnf   //添加下面這幾行
log-bin=mysql_bin
server-id=10
gtid_mode=on
enforce-gtid-consistency=true
log-slave-updates=on
[root@master ~]# systemctl restart mysqld.service  //重啟服務

關閉防火墻

[root@master ~]# systemctl stop firewalld.service 
[root@master ~]# systemctl disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master ~]# vim /etc/selinux/config 
SELINUX=disabled
[root@master ~]# setenforce 0

從庫配置，vim /etc/my.cnf, 添加以下配置，重啟mysql，

[root@slave ~]# vim /etc/my.cnf   //添加下面這幾行
server-id=20
relay-log=myrelay
gtid_mode=on
enforce-gtid-consistency=true
log-slave-updates=on
read_only=on
master-info-repository=TABLE
relay-log-info-repository=TABLE
[root@slave ~]# systemctl restart mysqld.service //重啟服務

關閉防火墻

[root@slave ~]# systemctl stop firewalld.service 
[root@slave ~]# systemctl disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@slave ~]# vim /etc/selinux/config 
SELINUX=disabled
[root@slave ~]# setenforce 0

主庫授權復制用戶，

[root@master ~]# mysql -uroot -plnh123
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 3
Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'repl'@'%' identified by 'repl123!';
Query OK, 0 rows affected, 1 warning (0.01 sec)

從庫設定要同步的主庫資訊，并開啟同步，

[root@slave ~]# mysql -uroot -p'lnh123'
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 22
Server version: 5.7.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> change master to master_host='192.168.222.250',master_port=3306,master_user='repl',master_password='repl123!',master_auto_position=1;
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.222.250
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 3044
               Relay_Log_File: myrelay.000002
                Relay_Log_Pos: 3257
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes  //這里必須是yes
            Slave_SQL_Running: Yes  //這里必須是yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           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: 3044
              Relay_Log_Space: 3456
              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: 10
                  Master_UUID: 316e0fd9-1208-11ed-9c1a-000c2905f428
             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: 316e0fd9-1208-11ed-9c1a-000c2905f428:1-13
            Executed_Gtid_Set: 316e0fd9-1208-11ed-9c1a-000c2905f428:1-13
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

配置完之后，通過查看slave的狀態，可以看是否配置成功，同時可以在主庫進行一些操作，提交一些事務(insert,update)，之后資料就會自動同步到從庫，
在主庫里面新建xbz庫，和創建表，插入一些資料

[root@master ~]# mysql -uroot -plnh123
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 8
Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database xbz;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xbz                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use xbz;
Database changed
mysql> create table hai(id int not null,name varchar(10),age int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into hai values(1,'xb',20),(2,'hb',21);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from hai;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | xb   |   20 |
|  2 | hb   |   21 |
+----+------+------+
2 rows in set (0.00 sec)

在從庫里面查看

[root@slave ~]# mysql -uroot -p'lnh123'
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 22
Server version: 5.7.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xbz                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use xbz;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from hai;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | xb   |   20 |
|  2 | hb   |   21 |
+----+------+------+
2 rows in set (0.02 sec)

轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/500714.html

標籤：Linux

上一篇：RAR Extractor - ZIP Unarchiver for Mac(rar壓縮解壓工具)

下一篇：Topaz Gigapixel AI for Mac(圖片無損放大軟體)