博客作业第五周-mysql主从复制、高可用性、运维自动化ansible

1、如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点

实验环境：master： centos8 10.0.0.68 mariadb-10.3.17
slave： centos8 10.0.0.58 mariadb-10.3.17
两台主机分别安装mariadb

[root@master ~]#yum -y install mariadb-server
[root@slave ~]#yum -y install mariadb-server

master配置

(1)为主节点指定server-id和开启二进制日志，启动服务

[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf 

[server]

# this is only for the mysqld standalone daemon
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld/mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id=68         #指定id
log_bin				#开启二进制日志

#
# * Galera-related settings
#
[galera]
"/etc/my.cnf.d/mariadb-server.cnf" 56L, 1478C

[root@master ~]#systemctl start mariadb.service 

（2）模拟数据库运行一段时间导入测试数据库

[root@master ~]#mysql < hellodb_innodb.sql 
**MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

修改表行

**MariaDB [hellodb]> update students set age=25,gender='F' where stuid=25;
Query OK, 1 row affected (0.003 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   |  25 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.000 sec)
**

（3）生产中遇到运行一段时间的数据库，先查看当前二进制日志的位置（show master logs;），然后对数据库进行完全备份

**MariaDB [hellodb]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |     28198 |
| mariadb-bin.000002 |      9157 |
+--------------------+-----------+
2 rows in set (0.000 sec)
**

[root@master ~]#mysqldump -A --single-transaction --master-data=1 -F > /backup/all.sql

查看备份文件，003日志389往后即需要同步的内容

[root@master ~]#vim /backup/all.sql 

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;  #改行记录备份时二进制日志位置

--
-- GTID to start replication from
--

-- SET GLOBAL gtid_slave_pos='0-68-80';

--
-- Current Database: `hellodb`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `hellodb`;

--
-- Table structure for table `classes`
--

DROP TABLE IF EXISTS `classes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `classes` (
  `ClassID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `Class` varchar(100) DEFAULT NULL,
  `NumOfStu` smallint(5) unsigned DEFAULT NULL,

（4）创建有复制权限的账号

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

slave配置

（1）修改server-id 启动服务

[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf 

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld/mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id=58      #指定id

（2）将主节点的完全备份文件拷贝到从节点，进行编辑添加信息，添加信息分别为：要复制的主节点IP地址、用于复制的用户名、密码、数据库端口号

[root@master ~]#scp /backup/all.sql root@10.0.0.58:/root
The authenticity of host '10.0.0.58 (10.0.0.58)' can't be established.
ECDSA key fingerprint is SHA256:2At7o6y8rVcjVAyXEeyB5nXI7CCJvPe+0QYOi8Y7AvU.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '10.0.0.58' (ECDSA) to the list of known hosts.
root@10.0.0.58's password: 
all.sql                                                                                                                                   100%  476KB  40.5MB/s   00:00    

[root@slave ~]#vim all.sql 

-- MySQL dump 10.17  Distrib 10.3.17-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version       10.3.17-MariaDB-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO
  MASTER_HOST='10.0.0.68',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
 MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;

（3）将修改好的文件导入到数据库中

[root@slave ~]#mysql < all.sql 

（4）查看复制状态

MariaDB [hellodb]> show slave status\G;
*************************** 1. row ***************************