技術文章 » CentOS7環境下資料庫運維---主從複製、讀寫分離

CentOS7環境下資料庫運維---主從複製、讀寫分離

2023-03-26 06:01:08

1.理解MySQL主從複製原理

主伺服器開啟binlog紀錄檔，從庫生成log dump執行緒，將binlog紀錄檔傳給從庫I/O執行緒，從庫生成倆個執行緒，一個是I/O執行緒，一個是SQL執行緒，I/O執行緒去請主庫的binlog紀錄檔，並將binlog紀錄檔中的檔案寫入relay log中，sql執行緒會讀取relay log 中的內容，並解析成具體的操作，來實現主從一致，達到最終資料一致的目的。

2.完成MySQL主從複製（一主兩從）

環境準備：

主機名 IP地址 埠號
node01 192.168.11.110 3306
node02 192.168.11.111 3306
node03 192.168.11.112 3306

資料庫準備：

create database company;
use company
CREATE TABLE `emp`  (
  `empno` int(4) NOT NULL,
  `ename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `job` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `mgr` int(4) NULL DEFAULT NULL,
  `hiredate` date NOT NULL,
  `sai` int(255) NOT NULL,
  `comm` int(255) NULL DEFAULT NULL,
  `deptno` int(2) NOT NULL,
  PRIMARY KEY (`empno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `emp` VALUES (1001, '甘寧', '文員', 1013, '2000-12-17', 8000, NULL, 20);
INSERT INTO `emp` VALUES (1002, '黛綺絲', '銷售員', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO `emp` VALUES (1003, '殷天正', '銷售員', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO `emp` VALUES (1004, '劉備', '經理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO `emp` VALUES (1005, '謝遜', '銷售員', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO `emp` VALUES (1006, '關羽', '經理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO `emp` VALUES (1007, '張飛', '經理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO `emp` VALUES (1008, '諸葛亮', '分析師', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1009, '曾阿牛', '董事長', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO `emp` VALUES (1010, '韋一笑', '銷售員', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO `emp` VALUES (1011, '周泰', '文員', 1006, '2007-05-23', 11000, NULL, 20);
INSERT INTO `emp` VALUES (1012, '程普', '文員', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO `emp` VALUES (1013, '龐統', '分析師', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1014, '黃蓋', '文員', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO `emp` VALUES (1015, '張三', '保潔員', 1001, '2013-05-01', 80000, 50000, 50);

方式一：基於三臺伺服器實現主從複製

主庫設定：

1、在mysqld標籤下新增server_id並開啟bin_log紀錄檔

[root@node01 ~]# cat /etc/my.cnf
[mysqld]
log_bin=mysql_bin
server_id=1

2、重啟資料庫服務

[root@node01 ~]# systemctl restart mysqld.service

3、授權同步賬號和密碼

mysql> grant replication slave on *.* to 'rep'@'192.168.11.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

4、檢視授權資訊

mysql> show grants for 'rep'@'192.168.11.%';
+--------------------------------------------------------+
| Grants for [email protected].%                            |
+--------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.11.%' |
+--------------------------------------------------------+
1 row in set (0.00 sec)

5、對錶操作

# 鎖表設定為唯讀
# 為後邊備份準備，注意生產環境要提前申請停機時間，停服
mysql> flush tables with read lock;

# 超過時間不操作會自動解鎖，檢視超時時間
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| wait_timeout                | 28800    |
+-----------------------------+----------+
13 rows in set (0.01 sec)

# 檢視主庫狀態
mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |    11824 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

6、備份資料庫資料

# 建立備份目錄
[root@node01 ~]# mkdir /server/backup -p

[root@node01 ~]# mysqldump -uroot -p -A -B | gzip > /server/backup/mysql_bak.$(date +%F).sql.gz
Enter password:

7、解鎖

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

8、主庫備份資料傳送到從庫

# 在從庫上常見備份目錄
[root@node02 ~]# mkdir /server/backup -p

# scp傳送
[root@node01 ~]# scp /server/backup/mysql_bak.2023-03-25.sql.gz  192.168.11.111:/server/backup/
[root@node01 ~]# scp /server/backup/mysql_bak.2023-03-25.sql.gz  192.168.11.112:/server/backup/

從庫設定：

1、關閉bin_log引數，設定server-id

[root@node02 ~]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
server_id=2

2、重啟資料庫服務

[root@node02 ~]# systemctl restart mysqld.service

3、還原從主庫傳輸過來的資料檔案

[root@node02 ~]# cd /server/backup/
[root@node02 backup]# gzip -d mysql_bak.2023-03-25.sql.gz
[root@node02 backup]# mysql -uroot -p < mysql_bak.2023-03-25.sql
Enter password:

4、檢查資料完整性

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

mysql> use company;
mysql> select * from company;
# 資料完整，恢復完成

5、設定主從同步

# 檢視主庫的binlog和pos位置點
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |    11824 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 從庫上設定
mysql> change master to
    -> master_host='192.168.11.110',
    -> master_user='rep',
    -> master_password='123456',
    -> master_log_file='mysql_bin.000001',
    -> master_log_pos=11824;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

6、啟動從庫同步並檢查狀態

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.11.110
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 11824
               Relay_Log_File: node02-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
# 看目前最後倆行是否為YES，倆個執行緒都為YES才OK

測試：

1、主庫建立一個資料庫

mysql> create database test_master;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| company            |
| mysql              |
| performance_schema |
| sys                |
| test_master        |
+--------------------+
6 rows in set (0.00 sec)

2、從庫檢查

[root@node02 backup]# mysql -uroot  -p -e 'show databases;'
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| company            |
| mysql              |
| performance_schema |
| sys                |
| test_master        |
+--------------------+

第二臺從庫一樣的設定，除了server_id不同

方式二：基於docker實現主從複製

環境準備：

主機名 IP地址 埠
mysql01 192.168.11.10 3306
mysql02 192.168.11.10 3307
msyql03 192.168.11.10 3308

安裝docker環境：

# step 1: 安裝必要的一些系統工具
sudo yum install -y yum-utils device-mapper-persistent-data lvm2
# Step 2: 新增軟體源資訊
sudo yum-config-manager --add-repo https://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo
# Step 3
sudo sed -i 's+download.docker.com+mirrors.aliyun.com/docker-ce+' /etc/yum.repos.d/docker-ce.repo
# Step 4: 更新並安裝Docker-CE
sudo yum makecache fast
sudo yum -y install docker-ce
# Step 4: 開啟Docker服務
sudo service docker start

# 注意：
# 官方軟體源預設啟用了最新的軟體，您可以通過編輯軟體源的方式獲取各個版本的軟體包。例如官方並沒有將測試版本的軟體源置為可用，您可以通過以下方式開啟。同理可以開啟各種測試版本等。
# vim /etc/yum.repos.d/docker-ce.repo
#   將[docker-ce-test]下方的enabled=0修改為enabled=1
#
# 安裝指定版本的Docker-CE:
# Step 1: 查詢Docker-CE的版本:
# yum list docker-ce.x86_64 --showduplicates | sort -r
#   Loading mirror speeds from cached hostfile
#   Loaded plugins: branch, fastestmirror, langpacks
#   docker-ce.x86_64            17.03.1.ce-1.el7.centos            docker-ce-stable
#   docker-ce.x86_64            17.03.1.ce-1.el7.centos            @docker-ce-stable
#   docker-ce.x86_64            17.03.0.ce-1.el7.centos            docker-ce-stable
#   Available Packages
# Step2: 安裝指定版本的Docker-CE: (VERSION例如上面的17.03.0.ce.1-1.el7.centos)
# sudo yum -y install docker-ce-[VERSION]

1、執行三個容器，mysql01 mysql02 mysql03

# 重啟docker服務
[root@template ~]# systemctl restart docker.service

# 拉取映象
[root@template ~]# docker run --name mysql01 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 --lower_case_table_names=1
[root@template ~]# docker run --name mysql02 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 --lower_case_table_names=1
[root@template ~]# docker run --name mysql03 -p 3308:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 --lower_case_table_names=1

2、修改組態檔

將容器裡面的組態檔複製出來，主要修改伺服器的設定；在root目錄下建立一個/server/backup的目錄存放從Docker容器裡面複製過來的組態檔。進入目錄：cd /server/backup

因為在docker中vi命令都沒有

# 建立備份目錄
[root@template ~]# mkdir /server/backup -p

# 使用docker cp將檔案傳到宿主機
[root@template ~]# cd /server/backup/

# 進入容器檢視mysql檔案
[root@template backup]# docker exec -it  mysql01 bash
bash-4.2# mysql -uroot -p
Enter password:

# 從Docker容器裡面複製過來的組態檔,組態檔路徑不一樣
[root@template ~]# docker cp mysql01:/etc/my.cnf  mysql01.cnf
[root@template ~]# docker cp mysql02:/etc/my.cnf  mysql02.cnf
Successfully copied 3.072kB to /root/mysql02.cnf
[root@template ~]# docker cp mysql03:/etc/my.cnf  mysql03.cnf
Successfully copied 3.072kB to /root/mysql03.cnf
[root@template ~]# ll
total 16
-rw-------. 1 root root 1425 Mar  3 18:52 anaconda-ks.cfg
-rw-r--r--. 1 root root 1159 Mar 22 04:51 mysql01.cnf
-rw-r--r--. 1 root root 1159 Mar 22 04:51 mysql02.cnf
-rw-r--r--. 1 root root 1159 Mar 22 04:51 mysql03.cnf

3、主庫的mysql01.cnf

[root@template ~]# vim mysql01.cnf
[mysqld]
server_id=1
log_bin=mysql01.bin
# 新增server_id 和 開啟紀錄檔

4、從庫修改server_id即可

[root@template ~]# vim mysql02.cnf
server_id=2
[root@template ~]# vim mysql03.cnf
server_id=3

5、修改完成後，將 mysql01.cnf mysql02.cnf mysql03.cnf 三個檔案傳入容器中

[root@template ~]# docker cp mysql01.cnf mysql01:/etc/my.cnf
Successfully copied 3.072kB to mysql01:/etc/my.cnf
[root@template ~]# docker cp mysql02.cnf mysql02:/etc/my.cnf
Successfully copied 3.072kB to mysql02:/etc/my.cnf
[root@template ~]# docker cp mysql03.cnf mysql03:/etc/my.cnf
Successfully copied 3.072kB to mysql03:/etc/my.cnf

6、重啟資料庫

[root@template ~]# docker restart mysql01  mysql02 mysql03

7、測試連線

[root@node03 ~]# mysql -uroot -p123456 -h 192.168.11.10 -P 3306
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.41-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> \q
Bye
[root@node03 ~]# mysql -uroot -p123456 -h 192.168.11.10 -P 3307
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.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> \q
Bye
[root@node03 ~]# mysql -uroot -p123456 -h 192.168.11.10 -P 3308
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.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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>

8、在3306作為主庫

[root@template backup]# docker exec -it mysql01 bash
bash-4.2# mysql -uroot -p123456

9、建立一個rep使用者

mysql> create user 'rep'@'%' identified by '123456';
Query OK, 0 rows affected (0.02 sec)

10、新增許可權

mysql> grant replication slave on *.* to 'rep'@'%';
Query OK, 0 rows affected (0.00 sec)

11、重新整理許可權表

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

12、測試用rep登入

bash-4.2# mysql -urep -p123456

13、進入從庫做設定

# 檢視主庫上的資訊，注意使用者，要用root使用者，開始用的rep錯誤資訊如下：
mysql> show master status;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation
# 檢視主庫上的資訊
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql01.000001 |      745 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


[root@template ~]# docker exec -it mysql02 bash
[root@template ~]# docker exec -it mysql03 bash

bash-4.2# mysql -uroot -p123456
mysql> change master to
    -> master_host="192.168.11.10",
    -> master_user="rep",
    -> master_password="123456",
    -> master_log_file="mysql01.000001",
    -> master_log_pos=745;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

14、開啟slave並且檢視倆個執行緒狀態

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.11.10
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql01.000001
          Read_Master_Log_Pos: 745
               Relay_Log_File: cb6044d1b02b-relay-bin.000002
                Relay_Log_Pos: 318
        Relay_Master_Log_File: mysql01.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

15、可以用使用者端連線測試，實驗完成！！！

3.基於MySQL一主兩從設定，完成MySQL讀寫分離設定

在docker環境上完成！！！

1、使用MYCAT2安裝JDK，因為MYCAT是基於JDK1.8開發的

[root@template ~]# yum install -y jdk-8u261-linux-x64.rpm
[root@template ~]# java -version
java version "1.8.0_261"

2、下載壓縮包和jar包

#建立/data/tools目錄
[root@template ~]# mkdir -p /data/tools
[root@template ~]# cd /data/tools/
[root@template ~]# wget -c http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.21.zip
[root@template ~]# wget -c http://dl.mycat.org.cn/2.0/1.21-release/mycat2-1.21-release-jar-with-dependencies.jar

3、安裝MyCAT2

[root@template tools]# ll
total 149484
-rw-r--r--. 1 root root 151819628 May  9  2022 mycat2-1.21-release-jar-with-dependencies.jar
-rw-r--r--. 1 root root   1246974 May  9  2022 mycat2-install-template-1.21.zip

4、安裝unzip

[root@template tools]# yum install -y unzip

5、解壓到指定目錄

[root@template tools]# unzip mycat2-install-template-1.21.zip -d /data/

6、修改許可權

[root@template ~] cd /data/mycat/lib/
[root@template bin]# chmod +x *
[root@template bin]# cp /data/tools/mycat2-1.21-release-jar-with-dependencies.jar ./

7、檢視mycat目錄結構

[root@template bin]# ll /data/mycat/
total 8
drwxr-xr-x. 2 root root 4096 Mar 25 22:56 bin
drwxr-xr-x. 9 root root  275 Mar  5  2021 conf
drwxr-xr-x. 2 root root 4096 Mar  5  2021 lib
drwxr-xr-x. 2 root root    6 Mar  5  2021 logs

8、啟動mycat

./mycat start 啟動
./mycat console 前臺執行
./mycat install 新增到系統自動啟動
./mycat remove 取消隨系統自動啟動
./mycat restart 重啟
./mycat pause 暫停
./mycat status 檢視啟動狀態

9、出現以下資訊表示啟動成功

[root@template bin]# ./mycat start
Starting mycat2...
[root@template bin]# cat /data/mycat/logs/wrapper.log
STATUS | wrapper  | 2023/03/25 22:59:23 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2023/03/25 22:59:23 | Launching a JVM...
INFO   | jvm 1    | 2023/03/25 22:59:23 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2023/03/25 22:59:23 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2023/03/25 22:59:23 |
INFO   | jvm 1    | 2023/03/25 22:59:23 | WrapperSimpleApp: Unable to locate the class io.mycat.MycatCore: java.lang.ClassNotFoundException: io.mycat.MycatCore
INFO   | jvm 1    | 2023/03/25 22:59:23 |
INFO   | jvm 1    | 2023/03/25 22:59:23 | WrapperSimpleApp Usage:
INFO   | jvm 1    | 2023/03/25 22:59:23 |   java org.tanukisoftware.wrapper.WrapperSimpleApp {app_class} [app_arguments]
INFO   | jvm 1    | 2023/03/25 22:59:23 |
INFO   | jvm 1    | 2023/03/25 22:59:23 | Where:
INFO   | jvm 1    | 2023/03/25 22:59:23 |   app_class:      The fully qualified class name of the application to run.
INFO   | jvm 1    | 2023/03/25 22:59:23 |   app_arguments