MySQL数据库架构搭建及ansible常用模块介绍

最新推荐文章于 2024-06-26 09:54:06 发布

GRAGON_W

最新推荐文章于 2024-06-26 09:54:06 发布

阅读量1.1k

点赞数 1

文章标签：安全 linux 运维

本文链接：https://blog.csdn.net/GRAGON_W/article/details/121060820

版权

本文介绍了MySQL的主从复制和主主复制的实现过程，包括环境搭建、主节点配置及性能验证。此外，详细探讨了如何利用xtrabackup进行全量和增量备份及binlog恢复。最后，讨论了MyCAT在MySQL读写分离中的应用，以及Ansible常用模块的功能，如command、shell、Copy等，以实现自动化运维。

摘要由CSDN通过智能技术生成

一、主从复制及主主复制的实现

1.主从复制

1.1 环境搭建

选择两台服务器安装相同版本号的MySQL数据库

[root@master ~]# yum info mysql-server.x86_64 
Last metadata expiration check: 0:02:30 ago on Sun 31 Oct 2021 10:20:58 AM CST.
Installed Packages
Name         : mysql-server
Version      : 8.0.26
Release      : 1.module_el8.4.0+915+de215114
Architecture : x86_64
Size         : 121 M
Source       : mysql-8.0.26-1.module_el8.4.0+915+de215114.src.rpm
Repository   : @System
From repo    : appstream
Summary      : The MySQL server and related files
URL          : http://www.mysql.com
License      : GPLv2 with exceptions and LGPLv2 and BSD
Description  : MySQL is a multi-user, multi-threaded SQL database server. MySQL is a
             : client/server implementation consisting of a server daemon (mysqld)
             : and many different client programs and libraries. This package contains
             : the MySQL server and some accompanying files and directories.

    [root@slave ~]# yum info mysql-server.x86_64 
Last metadata expiration check: 0:03:16 ago on Sun 31 Oct 2021 10:20:58 AM CST.
Installed Packages
Name         : mysql-server
Version      : 8.0.26
Release      : 1.module_el8.4.0+915+de215114
Architecture : x86_64
Size         : 121 M
Source       : mysql-8.0.26-1.module_el8.4.0+915+de215114.src.rpm
Repository   : @System
From repo    : appstream
Summary      : The MySQL server and related files
URL          : http://www.mysql.com
License      : GPLv2 with exceptions and LGPLv2 and BSD
Description  : MySQL is a multi-user, multi-threaded SQL database server. MySQL is a
             : client/server implementation consisting of a server daemon (mysqld)
             : and many different client programs and libraries. This package contains
             : the MySQL server and some accompanying files and directories.

1.2 主节点配置

在启动服务前，对主从数据库服务器进行配置

 [root@master ~]# cat /etc/my.cnf.d/mysql-server.cnf
[mysqld] #（主服务器选项，永久生效）
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=8 #当前节点设置一个全局惟一的ID号
log-bin #启用二进制日志

启动服务，进入MySQL数据库

[root@master ~]# systemctl start mysqld.service 
[root@master ~]# mysql

记录最新二进制文件及初始位置

mysql> show master logs;
+-------------------+-----------+-----------+
| Log_name          | File_size | Encrypted |
+-------------------+-----------+-----------+
| master-bin.000001 |       179 | No        |
| master-bin.000002 |       156 | No        |   #从服务器备份初始位置
+-------------------+-----------+-----------+
2 rows in set (0.00 sec)

创建从服务器复制账号并赋予其相对应权限

mysql> create user repluser@'10.0.0.%'identified by '123456';
mysql> grant replication slave on  *.* to repluser@'10.0.0.%';

从数据库服务器配置

[root@slave ~]# cat /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=18
log-bin
read-only #从服务器设置只读属性，确保主从数据库服务器之间数据准确性
mysql> CHANGE MASTER TO 
    -> MASTER_HOST='10.0.0.8', 
    -> MASTER_USER='repluser', 
    -> MASTER_PASSWORD='123456', 
    -> MASTER_LOG_FILE='master-bin.000002', 
    -> MASTER_LOG_POS=156;
Query OK, 0 rows affected, 8 warnings (0.02 sec)

指定主数据库服务器，使用有复制权限的用户账号连接至主服务器，并启动复制线程

mysql> CHANGE MASTER TO 
    -> MASTER_HOST='10.0.0.8', 
    -> MASTER_USER='repluser', 
    -> MASTER_PASSWORD='123456', 
    -> MASTER_LOG_FILE='master-bin.000002', 
    -> MASTER_LOG_POS=156;
Query OK, 0 rows affected, 8 warnings (0.02 sec)

启动slave进程并验证

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G #查看slave 状态

在这里插入图片描述
主从验证
主服务器创建库db2

在这里插入图片描述
从服务器读取到主服务器新增的库

2.主主复制

定义自动增长id字段的数值范围各为奇偶，确保数据id具有唯一性，遵循数据库第二范式规则。

2.1主服务器master1

[root@master1 ~]# cat /etc/my.cnf.d/mysql-server.cnf  #配置文件
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=8
log-bin
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度

重启数据库服务，添加复制账号

mysql> create user repluser@'10.0.0.%' identified by '123456';
mysql> grant replication on *.* to repluser@'10.0.0.%';

mysql> CHANGE MASTER TO 
    -> MASTER_HOST='10.0.0.18', 
    -> MASTER_USER='repluser', 
    -> MASTER_PASSWORD='123456', 
    -> MASTER_LOG_FILE='master2-bin.000001', 
    -> MASTER_LOG_POS=705; #记录另一主服务器启动时二进制日志文件位置

mysql> start slave;

2.2主服务器2 master2

[root@master2 ~]# cat /etc/my.cnf.d/mysql-server.cnf #配置文件
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=18
log-bin
auto_increment_offset=2 #开始点
auto_increment_increment=2 #增长幅度

重启数据库服务，登录数据库

mysql> CHANGE MASTER TO 
    -> MASTER_HOST='10.0.0.8', 
    -> MASTER_USER='repluser', 
    -> MASTER_PASSWORD='123456', 
    -> MASTER_LOG_FILE='master1-bin.000001', 
    -> MASTER_LOG_POS=156;

mysql> start slave; #启用服务与master1同步，自动同步获取在master1上建立的复制账号

mysql> show master logs;
+--------------------+-----------+-----------+
| Log_name           | File_size | Encrypted |
+--------------------+-----------+-----------+
| master2-bin.000001 |       705 | No        |
+--------------------+-----------+-----------+
1 row in set (0.00 sec)

2.3性能效率验证

2.3.1 master1 创建库，新增表

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

mysql> use db1
Database changed

mysql> create table t1 (id int ,name char(20));
mysql> insert t1 (name) values ('user1');

master2查询

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
| NULL | user1 |
+------+-------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          18 |
+-------------+
1 row in set (0.00 sec)

2.3.2 master2 在t1表中插入数据，master1查询

mysql> select @@server_id; #master2
+-------------+
| @@server_id |
+-------------+
|          18 |
+-------------+
1 row in set (0.00 sec)
mysql> insert t1(name) values ('user2');

mysql> select * from t1; #master1
+------+-------+
| id   | name  |
+------+-------+
| NULL | user1 |
| NULL | user2 |
+------+-------+
2 rows in set (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
1 row in set (0.00 sec)

2.2.3 master1和master2同时插入数据

insert t1 (name) values('userX');

mysql> select * from t1;  #master1和master2中t1表数据
+------+-------+
| id   | name  |
+------+-------+
| NULL | user1 |
| NULL | user2 |
| NULL | userX |
| NULL | userX |
+------+-------+
4 rows in set (0.00 sec)

两个节点同时插入数据，发生数据重复

二、xtrabackup实现全量+增量+binlog恢复库

准备两台服务器
数据库服务器 MySQL
备份数据库服务器：Backup_server
rpm包：percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm，选择yum安装，解决rpm包的依赖性。

2.1 全量备份

数据库服务器

mysql> show databases; #未删库前所有库
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

创建备份目录，并完全备份

[root@msyql mysql]# mkdir /backup 
[root@msyql mysql]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/base #父目录需提前创建

MySQL备份文件

[root@msyql mysql]# ll /backup/base/
total 60464
-rw-r----- 1 root root      475 Oct 31 16:04 backup-my.cnf
-rw-r----- 1 root root      155 Oct 31 16:04 binlog.000013
-rw-r----- 1 root root       16 Oct 31 16:04 binlog.index
drwxr-x--- 2 root root      132 Oct 31 16:04 hellodb
-rw-r----- 1 root root     3701 Oct 31 16:04 ib_buffer_pool
-rw-r

最低0.47元/天解锁文章

GRAGON_W

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库架构搭建及ansible常用模块介绍

一、主从复制及主主复制的实现1.主从复制1.1 环境搭建选择两台服务器安装相同版本号的MySQL数据库[root@master ~]# yum info mysql-server.x86_64 Last metadata expiration check: 0:02:30 ago on Sun 31 Oct 2021 10:20:58 AM CST.Installed PackagesName : mysql-serverVersion : 8.0.26Relea
复制链接

扫一扫