|
|
##### 1.yum安装数据库
|
|
|
|
|
|
下载mysql的yum仓库
|
|
|
|
|
|
```shell
|
|
|
[root@xingdian ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm
|
|
|
```
|
|
|
|
|
|
安装mysql的yum仓库
|
|
|
|
|
|
```shell
|
|
|
[root@xingdian ~]# rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm
|
|
|
```
|
|
|
|
|
|
修改安装版本：
|
|
|
|
|
|
```shell
|
|
|
方法一：命令方式
|
|
|
[root@xingdian ~]# yum repolist all | grep mysql
|
|
|
[root@xingdian ~]# yum -y install yum-utils
|
|
|
[root@xingdian ~]# yum-config-manager --enable mysql57-community
|
|
|
[root@xingdian ~]# yum-config-manager --disable mysql80-community
|
|
|
|
|
|
方法二：修改文件
|
|
|
[root@xingdian ~]# vim /etc/yum.repos.d/mysql-community.repo
|
|
|
```
|
|
|
|
|
|
安装mysql
|
|
|
|
|
|
```shell
|
|
|
[root@xingdian ~]# yum -y install mysql mysql-server
|
|
|
```
|
|
|
|
|
|
查看初始密码
|
|
|
|
|
|
```shell
|
|
|
[root@xingdian ~]# grep 'password' /var/log/mysqld.log
|
|
|
2019-07-13T15:14:31.176905Z 1 [Note] A temporary password is generated for root@localhost: k12zPB1r;2Ta
|
|
|
```
|
|
|
|
|
|
修改密码
|
|
|
|
|
|
```shell
|
|
|
[root@xingdian ~]# mysqladmin -u root -p'k12zPB1r;2Ta' password 'QianFeng@123'
|
|
|
密码：大小写有特殊字符数字
|
|
|
```
|
|
|
|
|
|
#####
|
|
|
#### 2.主从部署
|
|
|
|
|
|
注意：实验之前环境初始化，不要有残留的数据
|
|
|
|
|
|
##### 环境准备
|
|
|
|
|
|
| 节点 | IP地址 |
|
|
|
| :----: | :--------: |
|
|
|
| Master | 10.0.0.128 |
|
|
|
| Slave | 10.0.0.42 |
|
|
|
|
|
|
注意：
|
|
|
|
|
|
所有节点关闭防火墙和selinux
|
|
|
|
|
|
保证yum仓库可用
|
|
|
|
|
|
保证网络畅通
|
|
|
|
|
|
如果是克隆的服务器需要修改每台数据库的server-uuid
|
|
|
|
|
|
修改主机名：（所有节点）（可选操作）
|
|
|
|
|
|
```shell
|
|
|
[root@xingdian ~]# hostnamectl set-hostname master
|
|
|
[root@xingdian ~]# hostnamectl set-hostname slave
|
|
|
```
|
|
|
|
|
|
添加本地解析：（所有节点）（可选操作）
|
|
|
|
|
|
```shell
|
|
|
[root@master ~]# cat /etc/hosts
|
|
|
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
|
|
|
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
|
|
|
10.0.0.128 master
|
|
|
10.0.0.42 slave
|
|
|
|
|
|
[root@slave ~]# cat /etc/hosts
|
|
|
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
|
|
|
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
|
|
|
10.0.0.128 master
|
|
|
10.0.0.42 slave
|
|
|
```
|
|
|
|
|
|
##### Master部署
|
|
|
|
|
|
安装数据库：（略）
|
|
|
|
|
|
启动数据库：（略）
|
|
|
|
|
|
修改数据库初始密码：（略）
|
|
|
|
|
|
主服务器部署：
|
|
|
|
|
|
```shell
|
|
|
[root@master ~]# vim /etc/my.cnf
|
|
|
log-bin
|
|
|
server-id=1
|
|
|
gtid_mode = ON
|
|
|
enforce_gtid_consistency=1
|
|
|
```
|
|
|
|
|
|
创建授权用户：
|
|
|
|
|
|
```shell
|
|
|
[root@master ~]# mysql -u root -pQianFeng@123
|
|
|
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 3
|
|
|
Server version: 5.7.39 MySQL Community Server (GPL)
|
|
|
|
|
|
Copyright (c) 2000, 2022, 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> grant all on *.* to slave@'%' identified by 'QianFeng@123';
|
|
|
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
|
|
|
|
|
mysql> flush privileges;
|
|
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
|
|
|
mysql> exit
|
|
|
Bye
|
|
|
```
|
|
|
|
|
|
重启服务：
|
|
|
|
|
|
```shell
|
|
|
[root@master ~]# systemctl restart mysqld
|
|
|
```
|
|
|
|
|
|
##### Slave部署
|
|
|
|
|
|
安装数据库：（略）
|
|
|
|
|
|
启动数据库：（略）
|
|
|
|
|
|
修改数据库初始密码：（略）
|
|
|
|
|
|
从服务器部署：
|
|
|
|
|
|
```shell
|
|
|
[root@slave ~]# vim /etc/my.cnf
|
|
|
log-bin
|
|
|
server-id=2
|
|
|
gtid_mode = ON
|
|
|
enforce_gtid_consistency=1
|
|
|
relay_log_recovery = on
|
|
|
master-info-repository=TABLE
|
|
|
relay-log-info-repository=TABLE
|
|
|
//这两个参数会将master.info和relay.info保存在表中，默认是Myisam引擎，官方建议用
|
|
|
```
|
|
|
|
|
|
重启服务：
|
|
|
|
|
|
```shell
|
|
|
[root@slave ~]# systemctl restart mysqld
|
|
|
```
|
|
|
|
|
|
配置连接主服务器：
|
|
|
|
|
|
```shell
|
|
|
[root@slave ~]# mysql -u root -pQianFeng@123
|
|
|
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.39-log MySQL Community Server (GPL)
|
|
|
|
|
|
Copyright (c) 2000, 2022, 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> change master to
|
|
|
-> master_host='master',
|
|
|
-> master_user='slave',
|
|
|
-> master_password='QianFeng@123',
|
|
|
-> master_auto_position=1;
|
|
|
Query OK, 0 rows affected, 2 warnings (0.00 sec)
|
|
|
```
|
|
|
|
|
|
启动Slave：
|
|
|
|
|
|
```shell
|
|
|
mysql> start slave;
|
|
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
```
|
|
|
|
|
|
主从状态验证：
|
|
|
|
|
|
```shell
|
|
|
mysql> show slave status\G
|
|
|
*************************** 1. row ***************************
|
|
|
Slave_IO_State: Waiting for master to send event
|
|
|
Master_Host: master
|
|
|
Master_User: slave
|
|
|
Master_Port: 3306
|
|
|
Connect_Retry: 60
|
|
|
Master_Log_File: master-bin.000001
|
|
|
Read_Master_Log_Pos: 154
|
|
|
Relay_Log_File: slave-relay-bin.000002
|
|
|
Relay_Log_Pos: 369
|
|
|
Relay_Master_Log_File: master-bin.000001
|
|
|
Slave_IO_Running: Yes
|
|
|
Slave_SQL_Running: Yes
|
|
|
Replicate_Do_DB:
|
|
|
Replicate_Ignore_DB:
|
|
|
Replicate_Do_Table:
|
|
|
Replicate_Ignore_Table:
|
|
|
Replicate_Wild_Do_Table:
|
|
|
Replicate_Wild_Ignore_Table:
|
|
|
Last_Errno: 0
|
|
|
Last_Error:
|
|
|
Skip_Counter: 0
|
|
|
Exec_Master_Log_Pos: 154
|
|
|
Relay_Log_Space: 576
|
|
|
Until_Condition: None
|
|
|
Until_Log_File:
|
|
|
Until_Log_Pos: 0
|
|
|
Master_SSL_Allowed: No
|
|
|
Master_SSL_CA_File:
|
|
|
Master_SSL_CA_Path:
|
|
|
Master_SSL_Cert:
|
|
|
Master_SSL_Cipher:
|
|
|
Master_SSL_Key:
|
|
|
Seconds_Behind_Master: 0
|
|
|
Master_SSL_Verify_Server_Cert: No
|
|
|
Last_IO_Errno: 0
|
|
|
Last_IO_Error:
|
|
|
Last_SQL_Errno: 0
|
|
|
Last_SQL_Error:
|
|
|
Replicate_Ignore_Server_Ids:
|
|
|
Master_Server_Id: 1
|
|
|
Master_UUID: 00813e87-4321-11ed-a33c-000c29311164
|
|
|
Master_Info_File: mysql.slave_master_info
|
|
|
SQL_Delay: 0
|
|
|
SQL_Remaining_Delay: NULL
|
|
|
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
|
|
|
Master_Retry_Count: 86400
|
|
|
Master_Bind:
|
|
|
Last_IO_Error_Timestamp:
|
|
|
Last_SQL_Error_Timestamp:
|
|
|
Master_SSL_Crl:
|
|
|
Master_SSL_Crlpath:
|
|
|
Retrieved_Gtid_Set:
|
|
|
Executed_Gtid_Set:
|
|
|
Auto_Position: 1
|
|
|
Replicate_Rewrite_DB:
|
|
|
Channel_Name:
|
|
|
Master_TLS_Version:
|
|
|
1 row in set (0.00 sec)
|
|
|
```
|
|
|
|
|
|
数据验证：
|
|
|
|
|
|
主服务器创建数据：
|
|
|
|
|
|
```shell
|
|
|
[root@master ~]# mysql -u root -pQianFeng@123
|
|
|
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 3
|
|
|
Server version: 5.7.39-log MySQL Community Server (GPL)
|
|
|
|
|
|
Copyright (c) 2000, 2022, 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> create database qfcloud;
|
|
|
Query OK, 1 row affected (0.00 sec)
|
|
|
|
|
|
mysql> exit
|
|
|
Bye
|
|
|
```
|
|
|
|
|
|
从服务器查验数据：
|
|
|
|
|
|
```shell
|
|
|
mysql> show databases;
|
|
|
+--------------------+
|
|
|
| Database |
|
|
|
+--------------------+
|
|
|
| information_schema |
|
|
|
| mysql |
|
|
|
| performance_schema |
|
|
|
| qfcloud |
|
|
|
| sys |
|
|
|
+--------------------+
|
|
|
5 rows in set (0.00 sec)
|
|
|
```
|
|
|
|
|
|
##
|
|
|
#### 3.xtrabackup备份
|
|
|
|
|
|
简介：
|
|
|
|
|
|
percona-xtrabackup是开源免费的支持MySQL 数据库热备份的软件；能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份；它不暂停服务创建Innodb热备份；为mysql做增量备份；在mysql服务器之间做在线表迁移；使创建replication更加容易；备份mysql而不增加服务器的负载
|
|
|
|
|
|
|
|
|
|
|
|
安装软件：
|
|
|
|
|
|
```shell
|
|
|
[root@xingdian ~]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y
|
|
|
[root@xingdian ~]# yum install percona-xtrabackup-24 -y
|
|
|
```
|
|
|
|
|
|
##### 完整备份
|
|
|
|
|
|
创建备份目录：
|
|
|
|
|
|
```shell
|
|
|
[root@xingdian ~]# mkdir -p /xtrabackup/full/
|
|
|
```
|
|
|
|
|
|
备份：
|
|
|
|
|
|
```shell
|
|
|
[root@xingdian ~]# innobackupex --user=root --password='QianFeng@123' /xtrabackup/full/
|
|
|
```
|
|
|
|
|
|
查看备份数据：
|
|
|
|
|
|
```shell
|
|
|
[root@xingdian ~]# ls /xtrabackup/full/
|
|
|
2022-09-25_19-40-47
|
|
|
```
|
|
|
创建增量备份存放数据目录：
|
|
|
|
|
|
```shell
|
|
|
[root@xingdian ~]# mkdir /xtrabackup/zeng -p
|
|
|
```
|
|
|
|
|
|
模拟数据增加（略）
|
|
|
|
|
|
第一次增量备份：（周二）
|
|
|
|
|
|
```shell
|
|
|
[root@xingdian ~]# innobackupex --user=root --password='QianFeng@123' --incremental /xtrabackup/zeng/ --incremental-basedir=/xtrabackup/full/2022-09-25_19-40-47/
|
|
|
|
|
|
第一次增量备份的数据：
|
|
|
[root@xingdian ~]# ls /xtrabackup/zeng/
|
|
|
2022-09-25_19-56-00
|
|
|
```
|
|
|
|
|
|
模拟数据增加（略）
|
|
|
|
|
|
第二次增量备份：（周三）
|
|
|
|
|
|
```shell
|
|
|
[root@xingdian ~]# innobackupex --user=root --password='QianFeng@123' --incremental /xtrabackup/zeng/ --incremental-basedir=/xtrabackup/zeng/2022-09-25_19-56-00/
|
|
|
|
|
|
第二次增量备份的数据：
|
|
|
[root@xingdian ~]# ls /xtrabackup/zeng/
|
|
|
2022-09-25_19-56-00 2022-09-25_19-58-12
|
|
|
```
|
|
|
|
|
|
后面的增量备份重复上面的操作（略）
|
|
|
|
