MySql运维篇

#创建备份目录

mkdir -p /data/backup

#完全备份
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/base/ -uroot -p'Openlab123!' -H localhost -P 3306
对备份参数的解释:
--defaults-file=/etc/my.cnf: 这个选项指定了 MySQL 的配置文件路径。
--backup: 这个选项告诉 xtrabackup 执行一个备份操作。
--target-dir=/data/backup/base/: 此选项指定了备份数据存储的目标目录。
-uroot: 使用 -u 选项后跟用户名（在这里是 root）来指定连接数据库时使用的 MySQL 用户。
-p'Openlab123!': -p 选项后跟的是用于认证的密码。

注意:每次备份时需要将已有的数据清除掉用rm -rf /data/backup/

2.6.2恢复数据库

# 停止MySQL服务，并删除MySQL数据目录下的所有文件
systemctl stop mysqld
rm -rf /var/lib/mysql/*
# 准备需要备份的文件
xtrabackup --prepare --target-dir=/data/backup/base/
# 开始恢复数据库
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/base
# 授权MySQL数据目录给MySQL用户
chown -R mysql.mysql /var/lib/mysql
# 重启MySQL服务并登录MySQL
systemctl restart mysqld
mysql -uroot -p

2.7增量备份实验

2.7.1增量备份示例

# 在进行增量备份前，需要进行一次完全备份
mkdir -p /data/backup/
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/base/ -uroot -p#A123321000a -H localhost -P 3306 --no-server-version-check

# 在全备的基础上，添加了新的数据后进行了增量备份1
insert into students values (4,'王二');
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc1/ --incremental-basedir=/data/backup/base/ -uroot -p#A123321000a -H localhost -P 3306 --no-server-version-check

# 在增量备份1的基础上，再次添加新的数据后进行了增量备份2
insert into students values (5,'黎明');
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc2 --incremental-basedir=/data/backup/inc1/ -uroot -p#A123321000a -H localhost -P 3306 --no-server-version-check

注意:每次增量备份基于的目标是上一次的备份，产生变化的数据

2.7.2增量备份恢复

# 停止MySQL服务并删除MySQL数据目录下的所有文件
systemctl stop mysqld
rm -rf /var/lib/mysql/*

# 准备需要备份的文件
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base/
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base/ --incremental-dir=/data/backup/inc1
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/base/ --incremental-dir=/data/backup/inc2

# 开始恢复数据库
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/base/

重新授权后重启MySQL服务
chown -R mysql.mysql /var/lib/mysql
systemctl restart mysqld

这里注意:最后一次增量备份恢复时，不需要添加--apply-log-only参数

2.8差异备份实验

2.8.1差异备份示例

# 在进行差异备份前，需要进行一次完全备份
mkdir -p /data/backup/
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/base/ -uroot -p#A123321000a -H localhost -P 3306 --no-server-version-check
# 在全备的基础上，添加了新的数据后进行了差异备份1
insert into students values (5,'黎明');
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc1/ --incremental-basedir=/data/backup/base/ -uroot -p#A123321000a -Hlocalhost -P 3306 --no-server-version-check
# 在全备的基础上，添加了新的数据后进行了差异备份2
insert into students values (6,'章节');
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc2/ --incremental-basedir=/data/backup/base/ -uroot -p#A123321000a -H localhost -P 3306 --no-server-version-check

注意:每次差异备份基于的目标是第一次的完全备份，产生变化的数据

2.8.2差异备份恢复

# 停止MySQL服务，并删除MySQL数据目录下的所有文件
systemctl stop mysqld
rm -rf /var/lib/mysql/*
# 准备需要备份的文件
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base/
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base/ --incremental-dir=/data/backup/inc1
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/base/ --incremental-dir=/data/backup/inc2
# 开始恢复数据库
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/base/
# 授权MySQL数据目录给MySQL用户
chown -R mysql.mysql /var/lib/mysql
# 重启MySQL服务并登录MySQL
systemctl restart mysqld
mysql -uroot -p
注意:最后一次增量备份恢复时，不需要添加--apply-log-only参数

2.8压缩备份实验

创建备份目录，开始压缩备份:

mkdir -p /data/backup/compressed/
xtrabackup --defaults-file=/etc/my.cnf --backup --compress --target-dir=/data/backup/compressed/ -uroot -p#A123321000a -H localhost -P 3306 --no-server-version-check

解压缩:

#解压缩前，删除原有的数据
systemctl stop mysqld
rm -rf /var/lib/mysql/*
解压缩(压缩的文件，需要解压后才能使用):
xtrabackup --defaults-file=/etc/my.cnf --decompress --target-dir=/data/backup/compressed/

恢复数据到mysql中:

#准备备份文件
xtrabackup --prepare --target-dir=/data/backup/compressed
#开始拷贝文件
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/compressed/
#重启服务并登录MYSQL
systemctl restart mysqld
mysql -uroot -p
注意:解压时报错,安装yum install qpress -y

三.数据库集群

3.1集群目的

实现负载均衡，解决高并发

高可用HA，提高服务的可用性

远程灾备，保证数据的有效性

3.2集群原理图

原理:当主库中的数据发生变化时，会记录到他的二进制日志中，从库的I/O线程会读取主库中的二进制日志，然后写入中继日志中，最后SQL线程会读取中继日志中的信息，将数据存储到本地数据库中。

3.3mysql集群环境搭建

IP规划:

master1:192.168.145.133
master2:192.168.145.135
slave1:192.168.145.134
slave2:192.168.145.136
mycat:192.168.145.137(不安装mysql)

域名配置(5台机器都配置):

vim /etc/hosts
192.168.145.133 master1
192.168.145.135 master2
192.168.145.134 slave1
192.168.145.136 slave2
192.168.145.137 mycat
在第一台配置域名解析的服务器上对剩余4台机器快捷配置域名解析:
scp /etc/hosts 192.168.145.134:/etc/
scp /etc/hosts 192.168.145.135:/etc/
scp /etc/hosts 192.168.145.136:/etc/
scp /etc/hosts 192.168.145.137:/etc/

注意:在进行环境搭建时，必须是全新的未安装mysql的服务器

3.4一主一从(M-S)实验1

环境:

主(master1)
从(master2)
前提:安装了mysql，以及做好了域名解析:基于mysql集群的环境下进行

主(master1)端配置:

准备数据1(验证主从同步使用):
       create database master1db;
       create table master1db.master1tab(name char(50));
       insert into master1db.master1tab values(111);
insert into master1db.master1tab values(222);

开启二进制日志:
vim /etc/my.cnf #添加以下两行
log_bin
server-id=1
systemctl restart mysqld

授予用户权限
#登录数据库
mysql -uroot -p
#创建用户yulang，允许从IP地址以192.168.145开头的主机连接，并设置密码为#A123321000a
CREATE USER 'yulang'@'192.168.145.%' IDENTIFIED BY '#A123321000a';
#授予用户yulang从IP地址以192.168.145开头的主机上的所有数据库的复制从服务器和复制客户端权限
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'yulang'@'192.168.145.%';

退出mysql，备份master数据库的数据
mysqldump -p'#A123321000a' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql # 导出数据库的备份，使用密码#A123321000a，采用单个事务导出，生成带有日期的文件名
准备数据2(验证主从同步使用):
mysql -uroot -p
insert into master1db.master1tab values(333);
insert into master1db.master1tab values(444);

从(master2)端配置:

测试yulang用户是否可用:
mysql -uyulang -p#A123321000a -h master1
当这里登不进时，需要在每台服务器上关闭selinux和防火墙
setenforce 0
systemctl stop firewalld

启动服务器序号:
vim /etc/my.cnf
server-id=2
systemctl restart mysqld
mysql -uroot -p

手动同步数据，在主服务器端将数据同步到从服务器:
scp 2024-03-07-mysql-all.sql master2:/tmp/ #这一步在主服务器端操作
mysql -uroot -p
set sql_log_bin=0;
source /tmp/2024-03-07-mysql-all.sql; #将从服务器中临时目录中的数据同步到mysql中
select * from master1db.master1tab; #查询是否同步成功

指定从服务器上的主服务器:
vim 2024-03-07-mysql-all.sql #此步在主服务器操作，将显示的数据添加到从服务器中
CHANGE MASTER TO MASTER_LOG_FILE='bogon-bin.000002', MASTER_LOG_POS=157;

#指定主从关系

CHANGE MASTER TO MASTER_HOST='master1', MASTER_USER='yulang', MASTER_PASSWORD='#A123321000a', MASTER_LOG_FILE='bogon-bin.000002', MASTER_LOG_POS=157;

启动从设备，让主从关系生效:
start slave;

查看同步状态:

show slave status\G; #显示如下两行时，说明同步成功

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

验证是否同步成功:

我们向主服务器插入数据:

mysql> insert into master1db.master1tab values(666);
Query OK, 1 row affected (0.03 sec)

mysql> select * from master1db.master1tab;
+------+
| name |
+------+
| 111 |
| 222 |
| 333 |
| 444 |
| 555 |
| 666 |
+------+
6 rows in set (0.00 sec)

转向从服务器查看同步的数据:

mysql> select * from master1db.master1tab;
+------+
| name |
+------+
| 111 |
| 222 |
| 333 |
| 444 |
| 555 |
| 666 |
+------+
6 rows in set (0.01 sec)

发现主服务器的数据确实同步到了从服务器中

3.5一主一从(M-S)实验2

与实验1的区别:自动记录二进制位置，不需要手动指定

master1端配置:

启动二进制日志，服务器ID，GTID
vim /etc/my.cnf
log_bin
server-id=1
gtid_mode=ON
enforce_gtid_consistency=1
systemctl restart mysqld

备份数据
mysqldump -p --all-databases --single-transaction --set-gtid-purged=OFF --flush-logs > "$(date +\%F-\%H)-mysql-all.sql"
scp 2024-03-08-17-mysql-all.sql master2:/tmp
root@master2's password: #注意这里不要输成了mysql的登录密码

模拟数据变化
insert into master1db.master1tab values(777);

master2配置:

重置从服务器的数据库
systemctl stop mysqld
rm -rf /var/lib/mysql/*
systemctl start mysqld
grep password /var/log/mysqld.log
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY '#A123321000a';

测试主服务器上的yulang用户是否可用
mysql -h master1 -uyulang -p #登录到master1数据库

启动二进制日志，服务器ID，GTID
vim /etc/my.cnf
log_bin
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1
systemctl restart mysqld

将tmp目录下的数据同步到mysql中
mysql -uroot -p
set sql_log_bin=0; #临时关闭二进制日志
source /tmp/2024-03-08-17-mysql-all.sql
select * from master1db.master1tab;

设置主从关系
change master to master_host='master1',master_user='yulang',master_password='#A123321000a',master_auto_position=1;
这里注意我遇到了 Slave_IO_Running: Connecting的问题，其原因是我在进行设置主从关系的时候，将密码打错了
解决:
stop slave;
change master to master_host='master1',master_user='yulang',master_password='#A123321000a',master_auto_position=1;
显示主从状态
show slave status\G;