真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

成都創(chuàng)新互聯(lián)網(wǎng)站制作重慶分公司

詳細(xì)介紹MySQL如何開(kāi)啟主從復(fù)制實(shí)現(xiàn)數(shù)據(jù)同步

主從復(fù)制原理

詳細(xì)介紹MySQL如何開(kāi)啟主從復(fù)制實(shí)現(xiàn)數(shù)據(jù)同步

網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)公司！專(zhuān)注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開(kāi)發(fā)、微信小程序、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了臨沂免費(fèi)建站歡迎大家使用！

主從復(fù)制線程

主節(jié)點(diǎn)

?? dump Thread：為每個(gè)Slave的I/O Thread啟動(dòng)一個(gè)dump線程，用于向其發(fā)送binary log events

從節(jié)點(diǎn)

?? I/O Thread：向Master請(qǐng)求二進(jìn)制日志事件，并保存于中繼日志中
? SQL Thread：從中繼日志中讀取日志事件，在本地完成重放

跟復(fù)制功能相關(guān)的文件

?? master.info：用于保存slave連接至master時(shí)的相關(guān)信息，例如賬號(hào)、密碼、服務(wù)器地址等

?? relay-log.info：保存在當(dāng)前slave節(jié)點(diǎn)上已經(jīng)復(fù)制的當(dāng)前二進(jìn)制日志和本地replay log日志的對(duì)應(yīng)關(guān)系

主從復(fù)制特點(diǎn)

?? 異步復(fù)制
? 主從數(shù)據(jù)不一致比較常見(jiàn)

復(fù)制架構(gòu)

?? Master/Slave,Master/Master,環(huán)狀復(fù)制
? 一主多從
? 從服務(wù)器還可以再有從服務(wù)器
? 一從多主：適用于多個(gè)不同數(shù)據(jù)庫(kù)

復(fù)制需要考慮二進(jìn)制日志時(shí)間記錄格式

?? STATEMENT（5.0之前）
? ROW（5.1之后，推薦）
? MIXED

搭建環(huán)境準(zhǔn)備

主機(jī)IP地址類(lèi)型
CentOS7.6 192.168.36.101 Master
CentOS7.6 192.168.36.103 Slave
CentOS7.6 192.168.36.104 Slave
搭建之前請(qǐng)先確保MySQL軟件包已經(jīng)安裝

一主一從

Master節(jié)點(diǎn)修改數(shù)據(jù)庫(kù)配置文件

[root@Master ~]#cat /etc/my.cnf
[mysqld]
server_id=1     # 為Master節(jié)點(diǎn)設(shè)置一個(gè)全局唯一的ID號(hào)
binlog_format=row       # 基于行復(fù)制的數(shù)據(jù)庫(kù)語(yǔ)句
log-bin=/data/bin/mysql-bin     # 啟用二進(jìn)制日志

重新啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)

[root@Master ~]#service mysqld restart
Restarting mysqld (via systemctl):                         [  OK  ]

Master節(jié)點(diǎn)上創(chuàng)建帶有復(fù)制權(quán)限的用戶賬號(hào)

MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.36.%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)

查看Master的日志位置信息

MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |    912372 |
+------------------+-----------+
1 row in set (0.00 sec)

Slave節(jié)點(diǎn)修改配置文件

[root@Slave-1 ~]#cat /etc/my.cnf
[mysqld]
server_id=2         # Slave節(jié)點(diǎn)設(shè)置全局唯一的ID號(hào)
read_only           # 只讀

重新啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)

[root@Slave-1 ~]#systemctl restart mariadb

使用Master創(chuàng)建的復(fù)制權(quán)限的用戶賬號(hào)進(jìn)行同步

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.36.101',
    ->  MASTER_USER='repluser',
    ->  MASTER_PASSWORD='centos',
    ->  MASTER_PORT=3306,
    ->  MASTER_LOG_FILE='mysql-bin.000001',
    ->  MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.01 sec)

啟動(dòng)Slave線程

MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)

查看線程是否啟動(dòng)

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.36.101
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 7389
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 7673
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes      # 從節(jié)點(diǎn)的IO線程
            Slave_SQL_Running: Yes      # 從節(jié)點(diǎn)的SQL線程
....
        Seconds_Behind_Master: 0        # Master與SLave服務(wù)器差別延遲
.....
             Master_Server_Id: 1
1 row in set (0.00 sec)

檢查數(shù)據(jù)同步情況

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

主節(jié)點(diǎn)中查看數(shù)據(jù)庫(kù)的線程

MariaDB [hellodb]> show processlist;
+----+----------+----------------------+---------+-------------+------+------------------------------------------------------------------
| Id | User     | Host                 | db      | Command     | Time | State
+----+----------+----------------------+---------+-------------+------+------------------------------------------------------------------
|  3 | root     | localhost            | hellodb | Query       |    0 | NULL
|  6 | repluser | 192.168.36.103:51516 | NULL    | Binlog Dump |  115 | Master has sent all binlog to slave; waiting for binlog to be upd
+----+----------+----------------------+---------+-------------+------+------------------------------------------------------------------
2 rows in set (0.00 sec)

從節(jié)點(diǎn)中查看數(shù)據(jù)庫(kù)的線程

MariaDB [(none)]> show processlist;
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+--
| Id | User        | Host      | db   | Command | Time  | State                                                                       | I
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+--
|  4 | root        | localhost | NULL | Query   |     0 | NULL                                                                        | s
|  5 | system user |           | NULL | Connect |   159 | Waiting for master to send event                                            | N
|  6 | system user |           | NULL | Connect | 29259 | Slave has read all relay log; waiting for the slave I/O thread to update it | N
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+--
3 rows in set (0.00 sec)

一主多從：新添加一個(gè)從節(jié)點(diǎn)

Slave-2 節(jié)點(diǎn)安裝數(shù)據(jù)庫(kù)服務(wù)

[root@Slave-2 ~]#yum install -y mariadb-server

修改 Slave-2 節(jié)點(diǎn)配置文件

[root@Slave-2 ~]#cat /etc/my.cnf
[mysqld]
server_id=3
read_only
....

重新啟動(dòng) Slave-2 節(jié)點(diǎn)數(shù)據(jù)庫(kù)服務(wù)

[root@Slave-2 ~]#systemctl restart mariadb

Slave-2 節(jié)點(diǎn)配置同步信息

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.36.101',
    ->  MASTER_USER='repluser',
    ->  MASTER_PASSWORD='centos',
    ->  MASTER_PORT=3306,
    ->  MASTER_LOG_FILE='mysql-bin.000001',
    ->  MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)

啟用線程

MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)

檢查同步情況

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

主主復(fù)制：兩臺(tái)服務(wù)器互為主從

主機(jī)IP地址類(lèi)型
CentOS7.6 192.168.36.101 Master-1
CentOS7.6 192.168.36.103 Master-2

Master-1 修改配置文件

[root@Master-1 data]#cat /etc/my.cnf
[mysqld]
server_id=1
log_bin     # 啟用日志

# 為避免ID沖突問(wèn)題，需要添加下面兩行配置：Master-1 為奇數(shù)增長(zhǎng)，Master-2 為偶數(shù)增長(zhǎng)
auto_increment_offset=1     # 開(kāi)始點(diǎn)
auto_increment_increment=2  # 增長(zhǎng)幅度
....

Master-2 修改配置文件

[root@Master-2 data]#cat /etc/my.cnf
[mysqld]
server_id=2
log_bin
auto_increment_offset=2
auto_increment_increment=2

重新啟動(dòng)服務(wù)

[root@Master-1 ~]#systemctl restart mariadb
[root@Master-2 ~]#systemctl restart mariadb

Master-1 創(chuàng)建權(quán)限用戶，Master-2 先設(shè)置為從服務(wù)器進(jìn)行同步 Master-1 的數(shù)據(jù)庫(kù)

MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.36.%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)

Master-2 同步Master-1

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.36.101',
    ->  MASTER_USER='repluser',
    ->  MASTER_PASSWORD='centos',
    ->  MASTER_PORT=3306,
    ->  MASTER_LOG_FILE='mysql-bin.000001',
    ->  MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)

啟用線程

MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)

檢查同步情況

MariaDB [(none)]> use mysql;
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
MariaDB [mysql]> select user,password,host from user;
+----------+-------------------------------------------+--------------+
| user     | password                                  | host         |
+----------+-------------------------------------------+--------------+
| root     |                                           | localhost    |
| root     |                                           | 127.0.0.1    |
| root     |                                           | ::1          |
| repluser | *128977E278358FF80A246B5046F51043A2B1FCED | 192.168.36.% |
+----------+-------------------------------------------+--------------+
7 rows in set (0.00 sec)

Master-1 向 Master-2 進(jìn)行同步

MariaDB [mysql]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.36.103',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='centos',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.05 sec)

Master-1 同步狀態(tài)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.36.103
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....
           Master_SSL_Allowed: No
....
             Master_Server_Id: 2
1 row in set (0.01 sec)
此時(shí)Master-1、Master-2 實(shí)現(xiàn)主主復(fù)制功能

半同步復(fù)制

?? 半同步復(fù)制的作用是：主服務(wù)器宕機(jī)后，所有備份服務(wù)器主動(dòng)向同步數(shù)據(jù)最多的服務(wù)器進(jìn)行數(shù)據(jù)的同步，以確保數(shù)據(jù)損失降到最低。

半同步是通過(guò)插件功能達(dá)成的

Master節(jié)點(diǎn)安裝semisync_master.so插件
MariaDB [db1]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.07 sec)
啟用插件功能
MariaDB [db1]> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
設(shè)置超時(shí)時(shí)長(zhǎng)
MariaDB [db1]> SET GLOBAL rpl_semi_sync_master_timeout=6000;
Query OK, 0 rows affected (0.00 sec)
查看Master插件狀態(tài)
MariaDB [db1]> show variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 6000  |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)
Master修改配置文件并重啟數(shù)據(jù)庫(kù)服務(wù)
[root@Master data]#cat /etc/my.cnf
[mysqld]
...
rpl_semi_sync_master_enabled        # 啟用插件功能
...

[root@Master data]#systemctl restart mariadb
Slave節(jié)點(diǎn)安裝semisync_slave.so插件
MariaDB [db1]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
啟用slave插件功能
MariaDB [db1]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
Slave修改配置文件并重啟數(shù)據(jù)庫(kù)服務(wù)
[root@Slave data]#cat /etc/my.cnf
[mysqld]
...
rpl_semi_sync_slave_enabled        # 啟用插件功能
...

[root@Slave data]#systemctl restart mariadb
停止Slave 數(shù)據(jù)庫(kù)服務(wù)進(jìn)行測(cè)試
MariaDB [(none)]> create database db3;
Query OK, 1 row affected (6.00 sec)     # 由等待時(shí)長(zhǎng)可以看出，半同步插件已經(jīng)起到效果

分享題目：詳細(xì)介紹MySQL如何開(kāi)啟主從復(fù)制實(shí)現(xiàn)數(shù)據(jù)同步
文章來(lái)源：http://weahome.cn/article/gieois.html

其他資訊

在線咨詢

微信咨詢

電話咨詢

028-86922220（工作日）

18980820575（7×24）

提交需求

返回頂部