MySQL的安装、备份还原及主从同步
一、MySQL安装
1、Linux 在线与离线安装
在线安装
[root@localhost ~]# dnf install -y mysql-server.x86_64
[root@localhost ~]# systemctl start mysqld
离线安装
[root@localhost opt]# ll
total 767M
-rw-r--r-- 1 7155 31415 15M Dec 18 2021 mysql-community-client-8.0.28-1.el8.x86_64.rpm
-rw-r--r-- 1 7155 31415 35M Dec 18 2021 mysql-community-client-debuginfo-8.0.28-1.el8.x86_64.rpm
-rw-r--r-- 1 7155 31415 2.5M Dec 18 2021 mysql-community-client-plugins-8.0.28-1.el8.x86_64.rpm
-rw-r--r-- 1 7155 31415 3.0M Dec 18 2021 mysql-community-client-plugins-debuginfo-8.0.28-1.el8.x86_64.rpm
-rw-r--r-- 1 7155 31415 633K Dec 18 2021 mysql-community-common-8.0.28-1.el8.x86_64.rpm
-rw-r--r-- 1 7155 31415 3.7M Dec 18 2021 mysql-community-debuginfo-8.0.28-1.el8.x86_64.rpm
-rw-r--r-- 1 7155 31415 22M Dec 18 2021 mysql-community-debugsource-8.0.28-1.el8.x86_64.rpm
-rw-r--r-- 1 7155 31415 2.2M Dec 18 2021 mysql-community-devel-8.0.28-1.el8.x86_64.rpm
-rw-r--r-- 1 7155 31415 2.2M Dec 18 2021 mysql-community-icu-data-files-8.0.28-1.el8.x86_64.rpm
-rw-r--r-- 1 7155 31415 1.5M Dec 18 2021 mysql-community-libs-8.0.28-1.el8.x86_64.rpm
-rw-r--r-- 1 7155 31415 3.0M Dec 18 2021 mysql-community-libs-debuginfo-8.0.28-1.el8.x86_64.rpm
-rw-r--r-- 1 7155 31415 54M Dec 18 2021 mysql-community-server-8.0.28-1.el8.x86_64.rpm
-rw-r--r-- 1 7155 31415 22M Dec 18 2021 mysql-community-server-debug-8.0.28-1.el8.x86_64.rpm
-rw-r--r-- 1 7155 31415 99M Dec 18 2021 mysql-community-server-debug-debuginfo-8.0.28-1.el8.x86_64.rpm
-rw-r--r-- 1 7155 31415 252M Dec 18 2021 mysql-community-server-debuginfo-8.0.28-1.el8.x86_64.rpm
-rw-r--r-- 1 7155 31415 232M Dec 18 2021 mysql-community-test-8.0.28-1.el8.x86_64.rpm
-rw-r--r-- 1 7155 31415 24M Dec 18 2021 mysql-community-test-debuginfo-8.0.28-1.el8.x86_64.rpm
[root@localhost opt]# dnf install mysql-community-server-8.0.28-1.el8.x86_64.rpm mysql-community-client-8.0.28-1.el8.x86_64.rpm mysql-community-common-8.0.28-1.el8.x86_64.rpm mysql-community-icu-data-files-8.0.28-1.el8.x86_64.rpm mysql-community-client-plugins-8.0.28-1.el8.x86_64.rpm mysql-community-libs-8.0.28-1.el8.x86_64.rpm
[root@localhost ~]# awk '/temporary password/ {print $NF}' /var/log/mysqld.log
(.c,_%cdK3F(
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysqladmin -uroot -p'(.c,_%cdK3F(' password 'MySQL@123'
2、二进制方式安装
Installing MySQL on Unix/Linux Using Generic Binaries
# 获取软件包
[root@localhost ~]# wget -c https://mirrors.aliyun.com/mysql/MySQL-8.0/mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz
[root@localhost ~]# dnf install tar xz -y
# 安装
[root@localhost ~]# groupadd -g 27 -r mysql
[root@localhost ~]# useradd -u 27 -g 27 -c 'MySQL Server' -r -s /sbin/nologin mysql
[root@localhost ~]# tar xf mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
[root@localhost ~]# ln -sv /usr/local/mysql-8.0.28-linux-glibc2.12-x86_64/ /usr/local/mysql
[root@localhost ~]# cd /usr/local/mysql
[root@localhost mysql]# mkdir mysql-files
[root@localhost mysql]# chown mysql:mysql mysql-files
[root@localhost mysql]# chmod 750 mysql-files
[root@localhost mysql]# bin/mysqld --initialize --user=mysql
[root@localhost mysql]# bin/mysql_ssl_rsa_setup
[root@localhost mysql]# bin/mysqld_safe --user=mysql &
# 配置环境变量
[root@localhost mysql]# vi /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin/
[root@localhost mysql]# source /etc/profile.d/mysql.sh
[root@localhost mysql]# yum install ncurses-compat-libs
# 登录并修改密码
[root@localhost mysql]# mysql -uroot -p'nnr765aXFa)p'
mysql> alter user root@localhost identified by 'MySQL@123';
# 提供服务脚本
停止服务：[root@localhost mysql]# mysqladmin -uroot -p'MySQL@123' shutdown
[root@localhost mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@localhost mysql]# chkconfig --add mysqld
# 提供配置文件
[root@localhost ~]# scp /etc/my.cnf 172.16.100.23:/etc/
[root@localhost ~]# scp -r /etc/my.cnf.d/ 172.16.100.23:/etc/
# vi /etc/my.cnf
basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data/
socket=/tmp/mysql.sock
log-error=/usr/local/mysql/data/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid
二、MySQL日志
MySQL日志
日志类型
MySQL有几个不同的日志文件，可以帮助你找出mysqld内部发生的事情：
| 日志文件 | 记入文件中的信息类型 |
|---|---|
| 错误日志 | 记录启动、运行或停止时出现的问题。 |
| 查询日志 | 记录建立的客户端连接和执行的语句。 |
| 二进制日志 | 记录所有更改数据的语句。主要用于复制和即时点恢复。 |
| 慢日志 | 记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。 |
| 事务日志 | 记录InnoDB等支持事务的存储引擎执行事务时产生的日志。 |
二进制日志：如何删除?
三、备份还原
1、备份类型
热备份、温备份、冷备份 （根据服务器状态）
热备份：读、写不受影响；
温备份：仅可以执行读操作；
冷备份：离线备份；读、写操作均中止；
物理备份与逻辑备份 （从对象来分）
物理备份：复制数据文件；
逻辑备份：将数据导出至文本文件中；
完全备份、增量备份、差异备份 （从数据收集来分）
完全备份：备份全部数据；
增量备份：仅备份上次完全备份或增量备份以后变化的数据；
差异备份：仅备份上次完全备份以来变化的数据；
2、备份案例
2.1 mysqldump+binlog实现完全+增量备份
素材准备：
mysql> create database school;
Query OK, 1 row affected (0.01 sec)
mysql> use school
Database changed
mysql> CREATE TABLE `Student` (
-> `Sno` int(10) NOT NULL COMMENT '学号', `Sname` varchar(16) NOT NULL COMMENT '姓名',
-> `Ssex` char(2) NOT NULL COMMENT '性别', `Sage` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄',
-> `Sdept` varchar(16) DEFAULT 'NULL' COMMENT '学生所在系别', PRIMARY KEY (`Sno`)
-> ) ;
Query OK, 0 rows affected, 2 warnings (0.09 sec)
mysql> INSERT INTO `Student` VALUES (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男', 26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务');
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
完全备份：
[root@localhost ~]# mkdir /mysqlbak
[root@localhost ~]# mysqldump --opt -B school > /mysqlbak/school.sql
#查看完全备份
[root@localhost ~]# vim /mysqlbak/school.sql
插入数据：
INSERT INTO Student values(0005,'xumubin','男',29,'中文专业'),(0006,'wangzhao','男',21,'导弹专业');
模拟数据损坏：
mysql> drop database school;
Query OK, 1 row affected (0.04 sec)
查询binlog
mysql> show variables like '%log%';
[root@localhost ~]# ll /var/lib/mysql
total 91616
-rw-r----- 1 mysql mysql 56 Feb 26 18:04 auto.cnf
-rw-r----- 1 mysql mysql 476 Feb 26 19:27 binlog.000001
刷新日志并保存
mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 1822 | No |
| binlog.000002 | 157 | No |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> system cp /var/lib/mysql/binlog.000001 /mysqlbak
# 查看二进制日志
使用mysqlbinlog
[root@localhost mysqlbak]# cp /var/lib/mysql/binlog.000001 /mysqlbak/
[root@localhost mysqlbak]# mysqlbinlog binlog.000001 --base64-output=DECODE-ROWS -vv
#注意5.7版本，insert语句已经加密，默认看不到，查看时加上选项 --base64-output=DECODE-ROWS -vv
1、基于时间点增量恢复
[root@localhost mysqlbak]# mysqlbinlog binlog.000001 --start-datetime="2024-02-19 11:34:53" --stop-datetime="2024-02-19 11:36:35" -r time1.sql
还原完全备份
mysql> source /mysqlbak/school.sql
mysql> select * from school.Student;
+-----+--------+------+------+-----------------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----+--------+------+------+-----------------+
| 1 | 陆亚 | 男 | 24 | 计算机网络 |
| 2 | tom | 男 | 26 | 英语 |
| 3 | 张阳 | 男 | 21 | 物流管理 |
| 4 | alex | 女 | 22 | 电子商务 |
+-----+--------+------+------+-----------------+
4 rows in set (0.01 sec)
还原增量备份
mysql> source /mysqlbak/time1.sql
mysql> select * from school.Student;
+-----+----------+------+------+-----------------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----+----------+------+------+-----------------+
| 1 | 陆亚 | 男 | 24 | 计算机网络 |
| 2 | tom | 男 | 26 | 英语 |
| 3 | 张阳 | 男 | 21 | 物流管理 |
| 4 | alex | 女 | 22 | 电子商务 |
| 5 | xumubin | 男 | 29 | 中文专业 |
| 6 | wangzhao | 男 | 21 | 导弹专业 |
+-----+----------+------+------+-----------------+
6 rows in set (0.00 sec)
2、基于位置点恢复
[root@localhost mysqlbak]# mysqlbinlog binlog.000001 --start-position=1392 --stop-position=1591 -r pos1.sql
2.2 binlog gtid
binlog日志的GTID新特性
1、 GTID 介绍
1> 什么是GTID
GTID(Global Transaction ID)，全局事务标识符。是对于一个已提交事务的编号，并且是一个全局唯一的编号。
它是MySQL 5.6加入的一个强大特性，目的在于能够实现主从自动定位和切换，而不像以前需要指定文件和位置。
2> GTID的格式与存储
- 单个GTID
GTID与主库上提交的每个事务相关联。此标识符不仅对发起事务的库是唯一的，而且在给定复制拓扑中的所有库中都是唯一的。GTID用冒号分隔的一对坐标表示，例如：
8eed0f5b-6f9b-11e9-94a9-005056a57a4e:23
前一部分是主库的server_uuid，后面一部分是主库上按提交事务的顺序确定的序列号，提交的事务序号从1开始。
GTID = server_uuid ：transaction_id
2）GTID集
GTID集是包括一个或多个单个GTID或GTID范围的集合。源自同一服务器的一系列GTID可以折叠为单个表达式，例如：
8eed0f5b-6f9b-11e9-94a9-005056a57a4e:1-321
上面的示例表示源自server_uuid为8eed0f5b-6f9b-11e9-94a9-005056a57a4e服务器的第1到第321个事务。源自同一服务器的多个单GTID或GTID范围可以同时包含在由冒号分隔的单个表达式中，例如：
8eed0f5b-6f9b-11e9-94a9-005056a57a4e:1-3:11:47-49
3）mysql.gtid_executed表
mysql.gtid_executed表结构如下：
mysql> desc mysql.gtid_executed;
+----------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| source_uuid | char(36) | NO | PRI | NULL | |
| interval_start | bigint(20) | NO | PRI | NULL | |
| interval_end | bigint(20) | NO | | NULL | |
+----------------+------------+------+-----+---------+-------+
mysql.gtid_executed表记录的是服务器上已经执行事务的GTID。三个字段分别表示发起事务的服务器UUID、UUID集的起始和结束事务ID。对于单个GTID，后两个字段的值相同。
2> 版本支持
5.6 版本新加的特性,5.7中做了加强
5.6 中不开启,没有这个功能.
5.7 中的GTID,即使不开也会有自动生成
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | OFF |
| gtid_executed_compression_period | 1000 |
| gtid_mode | OFF |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.01 sec)
3> 如何开启
vim /etc/my.cnf || vim /etc/my.cnf.d/mysql-server.cnf 在线安装
gtid-mode=on
enforce-gtid-consistency=true
重启服务查看：
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.00 sec)
4> DDL和DML语句查看gtid
DDL一个语句产生一个gtid
mysql> create database db3;
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000013 | 310 | | | 6181523d-bc2e-11ea-a78b-000c29221146:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
mysql> use db3
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000013 | 471 | | | 6181523d-bc2e-11ea-a78b-000c29221146:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> create table t2 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000013 | 632 | | | 6181523d-bc2e-11ea-a78b-000c29221146:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> create table t3 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000013 | 793 | | | 6181523d-bc2e-11ea-a78b-000c29221146:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
DML一个事务产生一个gtid
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000013 | 1128 | | | 6181523d-bc2e-11ea-a78b-000c29221146:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000013 | 1379 | | | 6181523d-bc2e-11ea-a78b-000c29221146:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
2、基于GTID进行查看binlog
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000013';
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info