欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Mysql數(shù)據(jù)庫之?dāng)?shù)據(jù)備份與恢復(fù)方式

 更新時(shí)間:2025年01月13日 11:14:51   作者:一坨小橙子ovo  
本文詳細(xì)介紹了數(shù)據(jù)庫備份的重要性、分類、方法以及Mysql數(shù)據(jù)庫的備份與恢復(fù)操作,包括完全備份、差異備份、增量備份等,同時(shí),還涉及了日志數(shù)據(jù)庫、備份策略和執(zhí)行備份的定時(shí)任務(wù)(Crontab)等內(nèi)容

一、數(shù)據(jù)備份的重要性

在生產(chǎn)環(huán)境中,數(shù)據(jù)的安全性至關(guān)重要

任何數(shù)據(jù)的丟失都可能產(chǎn)生嚴(yán)重的后果

造成數(shù)據(jù)丟失的原因

  • 程序錯(cuò)誤(損失不會(huì)太大)
  • 人為操作數(shù)據(jù)
  • 運(yùn)算錯(cuò)誤
  • 磁盤故障
  • 災(zāi)難(如火災(zāi)、地震)和盜竊

拓展:容災(zāi)是一個(gè)旨在確保業(yè)務(wù)連續(xù)性的系統(tǒng)工程,它涉及IT系統(tǒng)的設(shè)計(jì)和實(shí)施,以防止用戶業(yè)務(wù)系統(tǒng)受到各種災(zāi)難的影響和破壞。容災(zāi)的核心目的是在自然或人為原因?qū)е律a(chǎn)系統(tǒng)發(fā)生災(zāi)難時(shí),盡可能保證業(yè)務(wù)的連續(xù)性。這通常包括建立兩套或多套功能相同的IT系統(tǒng),這些系統(tǒng)分布在相隔較遠(yuǎn)的不同地點(diǎn),并能夠進(jìn)行健康狀態(tài)監(jiān)視和功能切換。當(dāng)一處系統(tǒng)因意外(如火災(zāi)、地震等)停止工作時(shí),整個(gè)應(yīng)用系統(tǒng)可以切換到另一處,使得系統(tǒng)功能可以繼續(xù)正常工作

二、數(shù)據(jù)庫備份的分類

1.從物理與邏輯的角度分類

物理備份:對(duì)數(shù)據(jù)庫操作系統(tǒng)的物理文件(如數(shù)據(jù)文件、日志文件等)的備份

物理備份方法

  • 冷備份(脫機(jī)備份):是在關(guān)機(jī)數(shù)據(jù)庫的時(shí)候進(jìn)行的(可備份整個(gè)數(shù)據(jù)庫)(不建議使用)
  • 熱備份(聯(lián)機(jī)備份):數(shù)據(jù)庫處于運(yùn)行狀態(tài),依賴于數(shù)據(jù)庫的日志文件
  • 溫備份:數(shù)據(jù)庫鎖定表格(不可寫入但可讀)的狀態(tài)下進(jìn)行備份操作

邏輯備份:對(duì)數(shù)據(jù)庫邏輯組件(如:表等數(shù)據(jù)庫對(duì)象)的備份

2.從數(shù)據(jù)庫的備份策略角度,備份可分為

  • 完全備份:每次對(duì)數(shù)據(jù)庫進(jìn)行完整的備份
  • 差異備份:備份自從上次完全備份之后被修改過的文件
  • 增量備份:只有在上次完全備份或者增量備份后被修改的文件才會(huì)被備份

2.1完全備份

完全備份過程中每次備份都會(huì)進(jìn)行完全備份,會(huì)導(dǎo)致備份文件占用大量的磁盤空間,并且有大量的重復(fù)數(shù)據(jù),只適合第一次備份,不常用

2.2差異備份

差異備份要先進(jìn)行一次完全備份,每次差異備份都會(huì)備份上一次完全備份后的數(shù)據(jù),可能會(huì)出現(xiàn)備份的重復(fù)數(shù)據(jù),導(dǎo)致占用大量的磁盤空間;備份恢復(fù)時(shí),先恢復(fù)完全備份呢,再導(dǎo)入差異備份的數(shù)據(jù)

2.3增量備份

  • 增量備份要先執(zhí)行一次完全備份,每一次增量備份的數(shù)據(jù)都是備份在上一次完全備份或者上一次增量備份后的數(shù)據(jù),不會(huì)出現(xiàn)重復(fù)數(shù)據(jù),也不會(huì)占用額外的磁盤空間
  • 增量備份數(shù)據(jù)恢復(fù)時(shí),需要先恢復(fù)完全備份數(shù)據(jù),再恢復(fù)增量備份數(shù)據(jù)(需要按照次序)

2.4總結(jié)

完全備份差異備份增量備份
執(zhí)行順序每次完全備份會(huì)備份之前完全備份的數(shù)據(jù),會(huì)出現(xiàn)重復(fù)數(shù)據(jù)每次執(zhí)行差異備份會(huì)備份之前每次的差異備份每一次增量備份的數(shù)據(jù)都是備份上一次增量備份后新增的數(shù)據(jù)
占用磁盤空間占用大量的磁盤空間占用少量額外的磁盤空間占用極少量的磁盤空間
數(shù)據(jù)恢復(fù)

把完全備份的文件導(dǎo)入即可

恢復(fù)速度很快

先恢復(fù)完全備份數(shù)據(jù),再導(dǎo)入差異備份數(shù)據(jù)先恢復(fù)完全備份數(shù)據(jù),再恢復(fù)增量備份數(shù)據(jù)(需要按照次序進(jìn)恢復(fù))

備份頻率建議:一周一次的全備,全備的時(shí)間要選擇在不提供業(yè)務(wù)或者業(yè)務(wù)處理較少的時(shí)間段執(zhí)行(建議為01點(diǎn)到05點(diǎn)之間);每天的增量備份;特定場景的差異備份

三、常見的備份方法

物理冷備

  • 備份時(shí)數(shù)據(jù)庫處于關(guān)閉狀態(tài),直接打包數(shù)據(jù)庫文件
  • 備份速度快,恢復(fù)時(shí)也是最簡單的

專用備份工具mydump或mysqlhotcopy

  • mysqldump常用的邏輯備份工具
  • mysqlhotcopy僅擁有備份MyISAM和ARCHIVE表

啟用二進(jìn)制日志進(jìn)行增量備份

  • 進(jìn)行增量備份,需要刷新二進(jìn)制日志

Mysql支持增量備份,進(jìn)行增量備份時(shí)必須啟用二進(jìn)制日志。二進(jìn)制日志文件為用戶提供復(fù)制,對(duì)執(zhí)行的數(shù)據(jù)庫更改所需的信息進(jìn)行恢復(fù)。如果進(jìn)行增量備份(包含上次完全備份或增量備份以來發(fā)生的數(shù)據(jù)修改),需要刷新二進(jìn)制日志

第三方備份工具

  • 免費(fèi)的Mysql熱備份軟件Percona XtraBackup

四、Mysql數(shù)據(jù)庫完全備份

1.完全備份定義

  • 是對(duì)整個(gè)數(shù)據(jù)庫、數(shù)據(jù)庫結(jié)構(gòu)和文件結(jié)構(gòu)的備份
  • 保存的是備份完成時(shí)刻的數(shù)據(jù)庫
  • 是差異備份與增量備份的基礎(chǔ)

2.優(yōu)缺點(diǎn)

  • 優(yōu)點(diǎn):備份與恢復(fù)操作簡單方便
  • 缺點(diǎn):數(shù)據(jù)存在大量的重復(fù);占用大量的備份空間;備份與恢復(fù)時(shí)間長

3.數(shù)據(jù)庫完全備份分類

物理冷備份與恢復(fù)

  • 關(guān)閉Mysql數(shù)據(jù)庫
  • 使用tar命令直接打包數(shù)據(jù)庫文件夾
  • 直接替換現(xiàn)有Mysql目錄即可

Mysqldump備份與恢復(fù)

  • Mysql自帶的備份工具,可方便實(shí)現(xiàn)對(duì)Mysql的備份
  • 可以將指定的庫、表導(dǎo)出為SQL腳本
  • 使用命令Mysql導(dǎo)入備份的數(shù)據(jù)

導(dǎo)出使用的是Mysqldump;導(dǎo)入使用的是Mysql命令

4.Mysql數(shù)據(jù)庫完全備份實(shí)際操作

InnoDB 存儲(chǔ)引擎的數(shù)據(jù)庫在磁盤上存儲(chǔ)成三個(gè)文件:

  • db.opt(表屬性文件)
  • 表名.frm(表結(jié)構(gòu)文件)
  • 表名.ibd(表數(shù)據(jù)文件)

4.1環(huán)境準(zhǔn)備

目前Mysql數(shù)據(jù)庫中存在class數(shù)據(jù)庫,及class數(shù)據(jù)表以及數(shù)據(jù)表中的數(shù)據(jù)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.05 sec)

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

mysql> use class;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table class(id int(6),name varchar(8),remark varchar(40));
Query OK, 0 rows affected (0.01 sec)

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

mysql> insert into class values(1,'cxk','ctrl');
Query OK, 1 row affected (0.01 sec)

mysql> insert into class values(2,'wyb','skateboarding');
Query OK, 1 row affected (0.00 sec)

mysql> select * from class;
+------+------+---------------+
| id   | name | remark        |
+------+------+---------------+
|    1 | cxk  | ctrl          |
|    2 | wyb  | skateboarding |
+------+------+---------------+
2 rows in set (0.00 sec)

4.2物理冷備份與恢復(fù)——使用tar命令備份

4.2.1備份方法一
[root@localhost mysql]#systemctl stop mysqld
#物理冷備份需要先關(guān)閉mysqld服務(wù)
[root@localhost ~]#cd /usr/local/mysql/
[root@localhost mysql]#ls
bin      docs     man         mysql.sock.lock  share
COPYING  include  mysqld.pid  mysql-test       support-files
data     lib      mysql.sock  README           usr
[root@localhost mysql]#tar zcvf data.tar.gz data/ -C /opt/
#使用tar命令進(jìn)行壓縮備份
[root@localhost mysql]#ls /opt
boost_1_59_0.tar.gz  mysql-5.7.17  mysql-5.7.17.tar.gz  rh  data.tar.gz
4.2.2備份方法二
[root@localhost mysql]#systemctl stop mysqld
#物理冷備份需要先關(guān)閉mysqld服務(wù)
[root@localhost mysql]#tar Jcvf /opt/mysql_allbackup$(date +%F).tar.xz /usr/local/mysql/data
#壓縮打包/usr/local/mysql/data下的數(shù)據(jù)  保存在/opt目錄下取名為mysql_allbackup$(date +%F).tar.xz     date +%F代表當(dāng)天的日期
[root@localhost mysql]#ls /opt
boost_1_59_0.tar.gz  mysql-5.7.17.tar.gz               rh
mysql-5.7.17         mysql_allbackup2024-03-25.tar.xz
4.2.3模式移走data數(shù)據(jù)文件目錄
[root@localhost mysql]#mv data/ /home/
[root@localhost mysql]#ls
bin          docs     man         mysql.sock.lock  share
COPYING      include  mysqld.pid  mysql-test       support-files
data.tar.gz  lib      mysql.sock  README           usr
[root@localhost mysql]#systemctl restart mysqld.service
#此時(shí)重啟mysqld數(shù)據(jù)庫 可能會(huì)報(bào)錯(cuò) 或者重新建立一個(gè)新的data數(shù)據(jù)文件目錄(如果是新建的data數(shù)據(jù)文件目錄)那么新建的data數(shù)據(jù)文件目錄中無任何數(shù)據(jù)文件
[root@localhost mysql]#systemctl restart mysqld.service 
[root@localhost mysql]#ls
bin      data.tar.gz  lib         mysql.sock       README         usr
COPYING  docs         man         mysql.sock.lock  share
data     include      mysqld.pid  mysql-test       support-files
#這里看到有一個(gè)新的data數(shù)據(jù)文件目錄已經(jīng)建立
[root@localhost mysql]#cd data/
[root@localhost data]#ls
auto.cnf         client-key.pem  ib_logfile1         private_key.pem  sys
ca-key.pem       ib_buffer_pool  ibtmp1              public_key.pem
ca.pem           ibdata1         mysql               server-cert.pem
client-cert.pem  ib_logfile0     performance_schema  server-key.pem
#這里看不到之前數(shù)據(jù)庫中的文件
4.2.4執(zhí)行數(shù)據(jù)恢復(fù)操作一
[root@localhost mysql]#tar zxvf data.tar.gz -C /usr/local/mysql/
#使用tar命令解壓剛剛完全備份的數(shù)據(jù)文件  指定解壓目錄為/usr/local/mysql目錄下
[root@localhost mysql]#cd data/
[root@localhost data]#ls
auto.cnf    client-cert.pem  ib_logfile0  performance_schema  server-key.pem
ca-key.pem  client-key.pem   ib_logfile1  private_key.pem     sys
ca.pem      ib_buffer_pool   ibtmp1       public_key.pem
class       ibdata1          mysql        server-cert.pem
#此時(shí)我們看到剛剛完全備份的數(shù)據(jù)文件又恢復(fù)了
4.2.5執(zhí)行數(shù)據(jù)恢復(fù)操作二
[root@localhost mysql]#tar Jxvf /opt/mysql_allbackup2024-03-25.tar.xz -C /usr/local/mysql/data
#使用tar命令解壓剛剛的完全備份文件 指定解壓目錄到/usr/local/mysql目錄下
[root@localhost mysql]#ls
bin      data.tar.gz  lib         mysql.sock       README         usr
COPYING  docs         man         mysql.sock.lock  share
data     include      mysqld.pid  mysql-test       support-files
[root@localhost mysql]#cd data/
[root@localhost data]#ls
auto.cnf    client-cert.pem  ib_logfile0  performance_schema  server-key.pem
ca-key.pem  client-key.pem   ib_logfile1  private_key.pem     sys
ca.pem      ib_buffer_pool   ibtmp1       public_key.pem
class       ibdata1          mysql        server-cert.pem
#此時(shí)可以看到剛剛完全備份的數(shù)據(jù)文件內(nèi)容
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use class;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from class;
+------+------+---------------+
| id   | name | remark        |
+------+------+---------------+
|    1 | cxk  | ctrl          |
|    2 | wyb  | skateboarding |
+------+------+---------------+
2 rows in set (0.00 sec)

數(shù)據(jù)庫做遷移的時(shí)候 做整遷的時(shí)候使用該備份方式

4.3物理溫備份與恢復(fù)——使用mysqldump命令

4.3.1備份單個(gè)數(shù)據(jù)庫
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class              |
| class2             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
[root@localhost mysql]#mysqldump -uroot -p123456 --databases class > class.sql
#使用mysqldump命令 指定用戶root 指定密碼為123456 指定備份的數(shù)據(jù)庫class 備份存儲(chǔ)為class.sql數(shù)據(jù)文件
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]#ls
bin        data         include  mysqld.pid       mysql-test  support-files
class.sql  data.tar.gz  lib      mysql.sock       README      usr
COPYING    docs         man      mysql.sock.lock  share
[root@localhost mysql]#cat class.sql 
#class.sql數(shù)據(jù)文件存放了一些sql語句  也就是在sql環(huán)境中的操作  注意一定要以sql結(jié)尾 方便后期的恢復(fù)
-- MySQL dump 10.13  Distrib 5.7.17, for Linux (x86_64)
--
-- Host: localhost    Database: class
-- ------------------------------------------------------
-- Server version	5.7.17

/*!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 utf8 */;
/*!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 */;

--
-- Current Database: `class`
--

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

USE `class`;

--
-- Table structure for table `class`
--

DROP TABLE IF EXISTS `class`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `class` (
  `id` int(6) DEFAULT NULL,
  `name` varchar(8) DEFAULT NULL,
  `remark` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `class`
--

LOCK TABLES `class` WRITE;
/*!40000 ALTER TABLE `class` DISABLE KEYS */;
INSERT INTO `class` VALUES (1,'cxk','ctrl'),(2,'wyb','skateboarding');
/*!40000 ALTER TABLE `class` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-03-25 16:04:00
4.3.2備份多個(gè)數(shù)據(jù)庫
[root@localhost mysql]#mysqldump -uroot -p123456 --databases class class2 > class-class2.sql
#使用mysqldump命令 指定用戶root 指定密碼為123456 指定備份的數(shù)據(jù)庫class和class2 備份存儲(chǔ)為class-class.sql數(shù)據(jù)文件
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]#ls
bin               data         lib         mysql.sock.lock  support-files
class-class2.sql  data.tar.gz  man         mysql-test       usr
class.sql         docs         mysqld.pid  README
COPYING           include      mysql.sock  share
[root@localhost mysql]#cat class-class2.sql 
#class-class2.sql數(shù)據(jù)文件存放了一些sql語句  也就是在sql環(huán)境中的操作  注意一定要以sql結(jié)尾 方便后期的恢復(fù)
-- MySQL dump 10.13  Distrib 5.7.17, for Linux (x86_64)
--
-- Host: localhost    Database: class
-- ------------------------------------------------------
-- Server version	5.7.17

/*!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 utf8 */;
/*!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 */;

--
-- Current Database: `class`
--

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

USE `class`;

--
-- Table structure for table `class`
--

DROP TABLE IF EXISTS `class`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `class` (
  `id` int(6) DEFAULT NULL,
  `name` varchar(8) DEFAULT NULL,
  `remark` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `class`
--

LOCK TABLES `class` WRITE;
/*!40000 ALTER TABLE `class` DISABLE KEYS */;
INSERT INTO `class` VALUES (1,'cxk','ctrl'),(2,'wyb','skateboarding');
/*!40000 ALTER TABLE `class` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Current Database: `class2`
--

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

USE `class2`;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-03-25 16:07:25
[root@localhost mysql]#ll
總用量 1428
drwxr-xr-x  2 mysql mysql    4096 3月  19 13:12 bin
-rw-r--r--  1 root  root     2174 3月  25 16:07 class-class2.sql
-rw-r--r--  1 root  root     2031 3月  25 16:04 class.sql
-rw-r--r--  1 mysql mysql   17987 11月 28 2016 COPYING
drwxr-x---  7 mysql mysql     188 3月  25 16:02 data
-rw-r--r--  1 root  root  1401469 3月  25 15:27 data.tar.gz
drwxr-xr-x  2 mysql mysql      55 3月  19 13:10 docs
drwxr-xr-x  3 mysql mysql    4096 3月  19 13:10 include
drwxr-xr-x  4 mysql mysql     191 3月  19 13:12 lib
drwxr-xr-x  4 mysql mysql      30 3月  19 13:11 man
-rw-r-----  1 mysql mysql       5 3月  25 15:58 mysqld.pid
srwxrwxrwx  1 mysql mysql       0 3月  25 15:58 mysql.sock
-rw-------  1 mysql mysql       5 3月  25 15:58 mysql.sock.lock
drwxr-xr-x 10 mysql mysql    4096 3月  19 13:13 mysql-test
-rw-r--r--  1 mysql mysql    2478 11月 28 2016 README
drwxr-xr-x 28 mysql mysql    4096 3月  19 13:13 share
drwxr-xr-x  2 mysql mysql     112 3月  19 13:13 support-files
drwxr-xr-x  4 mysql mysql      30 3月  25 15:49 usr
[root@localhost mysql]#chown mysql:mysql class.sql class-class2.sql 
[root@localhost mysql]#ll
總用量 1428
drwxr-xr-x  2 mysql mysql    4096 3月  19 13:12 bin
-rw-r--r--  1 mysql mysql    2174 3月  25 16:07 class-class2.sql
-rw-r--r--  1 mysql mysql    2031 3月  25 16:04 class.sql
-rw-r--r--  1 mysql mysql   17987 11月 28 2016 COPYING
drwxr-x---  7 mysql mysql     188 3月  25 16:02 data
-rw-r--r--  1 root  root  1401469 3月  25 15:27 data.tar.gz
drwxr-xr-x  2 mysql mysql      55 3月  19 13:10 docs
drwxr-xr-x  3 mysql mysql    4096 3月  19 13:10 include
drwxr-xr-x  4 mysql mysql     191 3月  19 13:12 lib
drwxr-xr-x  4 mysql mysql      30 3月  19 13:11 man
-rw-r-----  1 mysql mysql       5 3月  25 15:58 mysqld.pid
srwxrwxrwx  1 mysql mysql       0 3月  25 15:58 mysql.sock
-rw-------  1 mysql mysql       5 3月  25 15:58 mysql.sock.lock
drwxr-xr-x 10 mysql mysql    4096 3月  19 13:13 mysql-test
-rw-r--r--  1 mysql mysql    2478 11月 28 2016 README
drwxr-xr-x 28 mysql mysql    4096 3月  19 13:13 share
drwxr-xr-x  2 mysql mysql     112 3月  19 13:13 support-files
drwxr-xr-x  4 mysql mysql      30 3月  25 15:49 usr

注意:這里一定要將sql結(jié)尾的備份數(shù)據(jù)文件修改為mysql屬主和屬組,否則mysqld服務(wù)無法調(diào)用

4.3.3備份所有的庫
[root@localhost mysql]#mysqldump -uroot -p123456 --all-databases > alldatabases.sql
#使用mysqldump命令 指定用戶root 指定密碼為123456 指定備份的所有數(shù)據(jù)庫 備份存儲(chǔ)為alldatabases.sql數(shù)據(jù)文件
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]#ls
alldatabases.sql  COPYING      include     mysql.sock       share
bin               data         lib         mysql.sock.lock  support-files
class-class2.sql  data.tar.gz  man         mysql-test       usr
class.sql         docs         mysqld.pid  README
4.3.4備份單個(gè)數(shù)據(jù)表
[root@localhost mysql]#mysqldump -uroot -p123456 class class > class_class.sql
#使用mysqldump命令 指定用戶為root 指定密碼為123456 指定class數(shù)據(jù)庫下的class數(shù)據(jù)表 備份到當(dāng)前目錄下 取名為class_class.sql數(shù)據(jù)文件
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]#ls
alldatabases.sql  class.sql    docs     mysqld.pid       README
bin               COPYING      include  mysql.sock       share
class-class2.sql  data         lib      mysql.sock.lock  support-files
class_class.sql   data.tar.gz  man      mysql-test       usr
4.3.5備份多個(gè)數(shù)據(jù)表
mysql> create table test(id int(6),name char(8),hobby varchar(40));
Query OK, 0 rows affected (0.00 sec)

mysql> create table test2(id int(6),name char(8),hobby varchar(40));
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-----------------+
| Tables_in_class |
+-----------------+
| class           |
| test            |
| test2           |
+-----------------+
3 rows in set (0.00 sec)
[root@localhost mysql]#mysqldump -uroot -p123456 class class test test2 > class_class_test_test2.sql
#使用mysqldump命令 指定用戶為root 指定密碼為123456 指定class數(shù)據(jù)庫下的class、test、test2三個(gè)數(shù)據(jù)表 備份到當(dāng)前目錄下 取名為class_class_test_test2.sql數(shù)據(jù)文件
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]#ls
alldatabases.sql            class.sql    include     mysql.sock.lock  usr
bin                         COPYING      lib         mysql-test
class-class2.sql            data         man         README
class_class.sql             data.tar.gz  mysqld.pid  share
class_class_test_test2.sql  docs         mysql.sock  support-files
4.3.6只備份數(shù)據(jù)表結(jié)構(gòu)
mysql> desc class;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(6)      | YES  |     | NULL    |       |
| name   | varchar(8)  | YES  |     | NULL    |       |
| remark | varchar(40) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
[root@localhost mysql]#mysqldump -uroot -p123456 -d class class > classdesc.sql
#使用mysqldump命令 指定用戶為root 指定密碼123456 -d只保存表結(jié)構(gòu)(desc)class數(shù)據(jù)庫下class數(shù)據(jù)表的結(jié)構(gòu)  保存在當(dāng)前目錄下取名為classdesc.sql數(shù)據(jù)文件
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]#ls
alldatabases.sql            class.sql    lib              README
bin                         COPYING      man              share
class-class2.sql            data         mysqld.pid       support-files
class_class.sql             data.tar.gz  mysql.sock       usr
class_class_test_test2.sql  docs         mysql.sock.lock
classdesc.sql               include      mysql-test
[root@localhost mysql]#cat classdesc.sql 
-- MySQL dump 10.13  Distrib 5.7.17, for Linux (x86_64)
--
-- Host: localhost    Database: class
-- ------------------------------------------------------
-- Server version	5.7.17

/*!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 utf8 */;
/*!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 */;

--
-- Table structure for table `class`
--

DROP TABLE IF EXISTS `class`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `class` (
  `id` int(6) DEFAULT NULL,
  `name` varchar(8) DEFAULT NULL,
  `remark` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-03-25 16:20:21
[root@localhost mysql]#grep -v "^--" classdesc.sql |grep -v "^/"|grep -v "^$"
#過濾出SQL語句
#整條命令解釋:過濾出classdesc.sql數(shù)據(jù)文件中不是以"--"開頭的、不是以"/"開頭的并且不是空行的內(nèi)容
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(6) DEFAULT NULL,
  `name` varchar(8) DEFAULT NULL,
  `remark` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5.Mysql數(shù)據(jù)庫完全恢復(fù)實(shí)際操作

使用mysqldump命令導(dǎo)出的文件可以使用導(dǎo)入的方法

  • Source命令
  • Mysql命令

拓展:mysql -e是指在bash環(huán)境執(zhí)行SQL語句,-e指調(diào)用命令(此命令行方便在Shell腳本中運(yùn)行)

mysql> show tables;
+-----------------+
| Tables_in_class |
+-----------------+
| class           |
| test            |
| test2           |
+-----------------+
3 rows in set (0.00 sec)
[root@localhost mysql]#mysql -uroot -p123456 -e 'drop table class.test2;'
#在bash環(huán)境使用mysql命令登入數(shù)據(jù)庫指定用戶root 指定密碼 指定sql命令刪除class數(shù)據(jù)庫下的test2數(shù)據(jù)表
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show tables;
+-----------------+
| Tables_in_class |
+-----------------+
| class           |
| test            |
+-----------------+
2 rows in set (0.00 sec)

5.1使用Source命令恢復(fù)數(shù)據(jù)庫

mysql> source /usr/local/mysql/class_class_test_test2.sql
mysql> show tables;
+-----------------+
| Tables_in_class |
+-----------------+
| class           |
| test            |
| test2           |
+-----------------+
3 rows in set (0.00 sec)

5.2使用Mysql命令恢復(fù)數(shù)據(jù)庫

[root@localhost mysql]#mysql -uroot -p123456 -e 'drop table class.class;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]#mysql -uroot -p123456 -e 'show tables from  class;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| Tables_in_class |
+-----------------+
| test            |
| test2           |
+-----------------+
[root@localhost mysql]#mysql -uroot -p123456 class < class_class_test_test2.sql
#使用mysql命令 指定用戶root 指定密碼為123456 將該目錄下的class_class_test_test2.sql數(shù)據(jù)文件導(dǎo)入到class數(shù)據(jù)庫中
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]#mysql -uroot -p123456 -e 'show tables from  class;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| Tables_in_class |
+-----------------+
| class           |
| test            |
| test2           |
+-----------------+
[root@localhost mysql]#mysql -uroot -p123456 -e 'select * from  class.class;' 
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+---------------+
| id   | name | remark        |
+------+------+---------------+
|    1 | cxk  | ctrl          |
|    2 | wyb  | skateboarding |
+------+------+---------------+

5.3有無--database的區(qū)別

Mysqldump嚴(yán)格來說是屬于溫備份,需要對(duì)表進(jìn)行寫入的鎖定。

在全量備份與恢復(fù)中,class數(shù)據(jù)庫,class數(shù)據(jù)庫中有class數(shù)據(jù)表

  • 當(dāng)備份增加--database時(shí),表示針對(duì)class整個(gè)數(shù)據(jù)庫;
  • 當(dāng)備份不增加--databases時(shí),表示只針對(duì)class數(shù)據(jù)庫下所有的數(shù)據(jù)表
5.3.1有database
[root@localhost mysql]#mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class              |
| class2             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@localhost mysql]#mysql -uroot -p123456 -e 'show tables from  class;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| Tables_in_class |
+-----------------+
| class           |
| test            |
| test2           |
+-----------------+
[root@localhost mysql]#mysqldump -uroot -p123456 --databases class > class_all.sql
#使用mysqldump命令 指定root用戶 指定密碼 備份class數(shù)據(jù)庫下所有內(nèi)容到當(dāng)前目錄  取名為class_all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]#ls
alldatabases.sql            classdesc.sql  include          mysql-test
bin                         class.sql      lib              README
class_all.sql               COPYING        man              share
class-class2.sql            data           mysqld.pid       support-files
class_class.sql             data.tar.gz    mysql.sock       usr
class_class_test_test2.sql  docs           mysql.sock.lock
[root@localhost mysql]#cat class_all.sql 
-- MySQL dump 10.13  Distrib 5.7.17, for Linux (x86_64)
--
-- Host: localhost    Database: class
-- ------------------------------------------------------
-- Server version	5.7.17

/*!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 utf8 */;
/*!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 */;

--
-- Current Database: `class`
--

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

USE `class`;

--
-- Table structure for table `class`
--

DROP TABLE IF EXISTS `class`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `class` (
  `id` int(6) DEFAULT NULL,
  `name` varchar(8) DEFAULT NULL,
  `remark` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `class`
--

LOCK TABLES `class` WRITE;
/*!40000 ALTER TABLE `class` DISABLE KEYS */;
INSERT INTO `class` VALUES (1,'cxk','ctrl'),(2,'wyb','skateboarding');
/*!40000 ALTER TABLE `class` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(6) DEFAULT NULL,
  `name` char(8) DEFAULT NULL,
  `hobby` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test`
--

LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `test2`
--

DROP TABLE IF EXISTS `test2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test2` (
  `id` int(6) DEFAULT NULL,
  `name` char(8) DEFAULT NULL,
  `hobby` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test2`
--

LOCK TABLES `test2` WRITE;
/*!40000 ALTER TABLE `test2` DISABLE KEYS */;
/*!40000 ALTER TABLE `test2` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-03-25 16:51:08
[root@localhost mysql]#mysql -uroot -p123456 -e 'drop database class;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]#mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class2             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@localhost mysql]#mysql -uroot -p123456 class < class_all.sql 
#恢復(fù)數(shù)據(jù)庫
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1049 (42000): Unknown database 'class'
[root@localhost mysql]#mysql -uroot -p123456 -e 'create database class;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]#mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class              |
| class2             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@localhost mysql]#mysql -uroot -p123456 class < class_all.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]#mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class              |
| class2             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@localhost mysql]#mysql -uroot -p123456 -e 'select * from class.class;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+---------------+
| id   | name | remark        |
+------+------+---------------+
|    1 | cxk  | ctrl          |
|    2 | wyb  | skateboarding |
+------+------+---------------+
5.3.2無database
[root@localhost mysql]#mysqldump -uroot -p123456 class > /opt/class_all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]#ls /opt/|grep "class"
class_all.sql
[root@localhost mysql]#mysql -uroot -p123456 -e 'drop database class;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]#mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class2             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@localhost mysql]#mysql -uroot -p123456 -e 'create database class;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]#mysql -uroot -p123456 class < /opt/class_all.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]#mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class              |
| class2             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@localhost mysql]#mysql -uroot -p123456 -e 'select * from class.class;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+---------------+
| id   | name | remark        |
+------+------+---------------+
|    1 | cxk  | ctrl          |
|    2 | wyb  | skateboarding |
+------+------+---------------+

6.Crontab -e——執(zhí)行定時(shí)備份

0 1 * * 6 /usr/local/mysql/bin/mysqldump -uroot -p123456 class > class_all_$(date +%F).sql;
/usr/local/mysql/bin/mysqladmin -uroot -p flush-logs

或者

0 1 * * 6 /usr/local/mysql/bin/mysqldump -uroot -p123456 class > class_all_$(date +%Y%m%d).sql;
/usr/local/mysql/bin/mysqladmin -uroot -p flush-logs

五、日志

數(shù)據(jù)庫日志對(duì)于數(shù)據(jù)庫的備份和恢復(fù)中起著至關(guān)重要的作用

日志默認(rèn)存放位置/usr/local/mysql/data文件夾下

1.配置文件

[root@localhost mysql]#vim /etc/my.cnf
[root@localhost mysql]#sed -n '26,33p' /etc/my.cnf
log-error=/usr/local/mysql/data/mysql_error.log
#錯(cuò)誤日志  存放位置子啊/usr/local/mysql/data/目錄下  錯(cuò)誤日志文件名為mysql_error.log

general_log=ON
#通用查詢?nèi)罩鹃_啟

general_log_file=/usr/local/mysql/data/mysql_general.log
#通用查詢?nèi)罩?保存位置在/usr/local/mysql/data目錄下  通用查詢?nèi)罩疚募麨閙ysql_general.log

log-bin=mysql-bin
#二進(jìn)制日志(binlog):用來記錄所有更新了數(shù)據(jù)或者已經(jīng)潛在更新了數(shù)據(jù)的語句,記錄了數(shù)據(jù)的更改,可用于數(shù)據(jù)恢復(fù),默認(rèn)開啟

slow_query_log=ON
#慢查詢開啟  慢查詢:用來記錄所有執(zhí)行時(shí)間超過long_query_time秒的語句,可以找到哪些查詢語句執(zhí)行時(shí)間長,以便提醒優(yōu)化,默認(rèn)關(guān)閉

slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
#慢查詢?nèi)罩灸J(rèn)存放位置  /usr/local/mysql/data/  慢查詢?nèi)罩疚募麨閙ysql_slow_query.log

long_query_time=5
#設(shè)置超過5秒執(zhí)行的語句被記錄  缺省時(shí)為10秒

binlog_format = MIXED
#指定二進(jìn)制日志(binlog)的記錄格式為MIXED(混合輸入)

二進(jìn)制日志開啟后,重啟mysql會(huì)在data目錄中看到二進(jìn)制日志(mysql-bin.000001,mysql-bin.000002...文件)開啟二進(jìn)制日志會(huì)產(chǎn)生一個(gè)索引文件及索引列表(mysql-bin.index

[root@localhost mysql]#ls
alldatabases.sql            classdesc.sql  include          mysql-test
bin                         class.sql      lib              README
class_all.sql               COPYING        man              share
class-class2.sql            data           mysqld.pid       support-files
class_class.sql             data.tar.gz    mysql.sock       usr
class_class_test_test2.sql  docs           mysql.sock.lock
[root@localhost mysql]#cd data/
[root@localhost data]#ls
auto.cnf        ibdata1      mysql             mysql_error.log       sys
class           ib_logfile0  mysql-bin.000001  mysql_general.log
class2          ib_logfile1  mysql-bin.000002  mysql_slow_query.log
ib_buffer_pool  ibtmp1       mysql-bin.index   performance_schema

其中,索引文件記錄更新的sql語句;

索引文件刷新方式

  • 重啟Mysql服務(wù)的時(shí)候會(huì)更新索引文件,用于記錄新的更新的sql語句
  • 刷新二進(jìn)制日志

二進(jìn)制日志(binlog)有三種不同的記錄格式

  • STATEMENT(基于SQL語句)
  • ROW(基于行)
  • MIXED(混合輸入)

默認(rèn)格式是STATEMENT記錄格式

1.1STATEMENT(基于SQL語句)記錄格式

每一條設(shè)計(jì)到被修改的SQL都會(huì)記錄在binlog

  • 缺點(diǎn):日志量過大,如sleep()函數(shù),last_insert_id()>,以及user-defined fuctions(udf)、主從復(fù)制等架構(gòu)記錄日志時(shí)會(huì)出現(xiàn)問題
  • 總結(jié):增刪改查通過sql語句來實(shí)現(xiàn)記錄,如果用高并發(fā)可能會(huì)出錯(cuò),可能時(shí)間差異或者延遲,可能不是我們想想的恢復(fù)可能你先刪除或者在修改,可能會(huì)倒過來。準(zhǔn)確率低

如果使用STATEMENT記錄格式,假如刪除數(shù)據(jù)庫數(shù)據(jù)表中的第四行的數(shù)據(jù),再次恢復(fù)數(shù)據(jù)表的數(shù)據(jù),不一定是第四行,有可能將第四行恢復(fù)到最后

1.2ROW(基于行)記錄格式

只記錄變動(dòng)的記錄,不記錄SQL的上下文環(huán)境

  • 缺點(diǎn):如果遇到update......set....where true 那么binlog的數(shù)據(jù)量會(huì)越來越大
  • 總結(jié):update、delete以多行數(shù)據(jù)起作用,來用行記錄下來,只記錄變動(dòng)的記錄,不記錄sql的上下文環(huán)境,比如sql語句記錄一行,但是ROW就可能記錄10行,但是準(zhǔn)確性高,高并發(fā)的時(shí)候由于操作量,性能變低 比較大所以記錄都記下來

1.3MIXED(混合輸入)記錄格式——推薦使用

一般的語句使用statement,函數(shù)使用ROW方式存儲(chǔ)。

根據(jù)并發(fā)量進(jìn)行分配,并發(fā)量低選擇STATEMENT,并發(fā)量高選擇ROW

2.查看數(shù)據(jù)庫日志

mysql> show variables like 'log_bin%';
#查看二進(jìn)制日志是否開啟
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
+---------------------------------+---------------------------------------+
5 rows in set (0.00 sec)

mysql> show variables like 'general%';
#查看通用查詢?nèi)罩緺顟B(tài)
+------------------+-----------------------------------------+
| Variable_name    | Value                                   |
+------------------+-----------------------------------------+
| general_log      | ON                                      |
| general_log_file | /usr/local/mysql/data/mysql_general.log |
+------------------+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> show variables like '%slow%';
#查看慢查詢?nèi)罩臼欠耖_啟
+---------------------------+--------------------------------------------+
| Variable_name             | Value                                      |
+---------------------------+--------------------------------------------+
| log_slow_admin_statements | OFF                                        |
| log_slow_slave_statements | OFF                                        |
| slow_launch_time          | 2                                          |
| slow_query_log            | ON                                         |
| slow_query_log_file       | /usr/local/mysql/data/mysql_slow_query.log |
+---------------------------+--------------------------------------------+
5 rows in set (0.00 sec)

mysql> show variables like 'long_query_time';
#查看慢查詢時(shí)間設(shè)置
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

variables 表示變量 like 表示模糊查詢

  • xxx%:以xxx為開頭的字段
  • %xxx:以xxx為結(jié)尾的字段
  • %xxx%:只要出現(xiàn)xxx字段的都會(huì)顯示出來
  • xxx:精準(zhǔn)查詢

2.1查看二進(jìn)制日志文件內(nèi)容

[root@localhost data]#cp /usr/local/mysql/data/mysql-bin.000001 /opt
[root@localhost data]#vim /opt/mysql-bin.000001 
[root@localhost data]#cat /opt/mysql-bin.000001                             _tbina?fw{5.7.17-loga?f8

**4??a?f#1f穵?f±'
[root@localhost data]#mysqlbinlog --no-defaults /opt/mysql-bin.000001 
#使用mysql服務(wù)自帶的binlog二進(jìn)制解釋器默認(rèn)字符集查看/opt目錄下的/mysql-bin.000001數(shù)據(jù)文件
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240325 17:11:06 server id 1  end_log_pos 123 CRC32 0x86c752f5 	Start: binlog v 4, server v 5.7.17-log created 240325 17:11:06 at startup
ROLLBACK/*!*/;
BINLOG '
qj8BZg8BAAAAdwAAAHsAAAAAAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACqPwFmEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AfVSx4Y=
'/*!*/;
# at 123
#240325 17:11:06 server id 1  end_log_pos 154 CRC32 0x28e966b9 	Previous-GTIDs
# [empty]
# at 154
#240325 17:12:21 server id 1  end_log_pos 177 CRC32 0xf627839d 	Stop
ERROR: Could not read entry at offset 177: Error in log format or read error.
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@localhost data]#mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001 
#使用mysql服務(wù)自帶的binlog二進(jìn)制解釋器默認(rèn)字符集查看/opt目錄下的/mysql-bin.000001數(shù)據(jù)文件
#--base64-output=decode-rows:使用64位編碼機(jī)制去解碼(decode)并按行讀?。╮ows)
#-v: 顯示詳細(xì)內(nèi)容
#--no-defaults : 默認(rèn)字符集(不加會(huì)報(bào)UTF-8的錯(cuò)誤)



/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240325 17:11:06 server id 1  end_log_pos 123 CRC32 0x86c752f5 	Start: binlog v 4, server v 5.7.17-log created 240325 17:11:06 at startup
ROLLBACK/*!*/;
# at 123
#240325 17:11:06 server id 1  end_log_pos 154 CRC32 0x28e966b9 	Previous-GTIDs
# [empty]
# at 154
#240325 17:12:21 server id 1  end_log_pos 177 CRC32 0xf627839d 	Stop
ERROR: Could not read entry at offset 177: Error in log format or read error.
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;



[root@localhost data]#mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001 > /opt/mysql-bin.000001
#可以將解碼后的文件導(dǎo)出為txt格式  方便查閱

二進(jìn)制日志內(nèi)容需要關(guān)注的重點(diǎn)部分

  • at:開始的位置點(diǎn)
  • end_log_pos:結(jié)束的位置點(diǎn)
  • 時(shí)間戳:240325 18:05:20(2024年3月25日18點(diǎn)05分20秒)
  • SQL語句

六、增量備份

1.環(huán)境準(zhǔn)備

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

mysql> use class
Database changed
mysql> show table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> show tables;
Empty set (0.00 sec)

mysql> create table class(id int(6),name char(8),hobby varchar(40));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into class values(1,'cxk','ctrl');
Query OK, 1 row affected (0.00 sec)

mysql> insert into class values(2,'wyb','skateboarding');
Query OK, 1 row affected (0.00 sec)

mysql> select * from class;
+------+------+---------------+
| id   | name | hobby         |
+------+------+---------------+
|    1 | cxk  | ctrl          |
|    2 | wyb  | skateboarding |
+------+------+---------------+
2 rows in set (0.00 sec)
[root@localhost data]#mysqladmin -uroot -p123456 flush-logs
#使用mysqladmin命令 指定root用戶 指定密碼為123456 刷新日志
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]#ls
auto.cnf        ib_logfile0       mysql-bin.000002   mysql_slow_query.log
class           ib_logfile1       mysql-bin.000003   performance_schema
class2          ibtmp1            mysql-bin.index    sys
ib_buffer_pool  mysql             mysql_error.log
ibdata1         mysql-bin.000001  mysql_general.log
#此時(shí)刷新后的日志為mysql-bin.000003該二進(jìn)制日志為空,之前的數(shù)據(jù)內(nèi)容存放在刷新前的日志

2.使用二進(jìn)制日志備份與恢復(fù)

2.1數(shù)據(jù)備份

[root@localhost data]#mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]#ls
auto.cnf        ib_logfile0  mysql-bin.000001  mysql_error.log       sys
class           ib_logfile1  mysql-bin.000002  mysql_general.log
ib_buffer_pool  ibtmp1       mysql-bin.000003  mysql_slow_query.log
ibdata1         mysql        mysql-bin.index   performance_schema
[root@localhost data]#mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000003

[root@localhost data]#cp mysql-bin.000003 /opt/
[root@localhost data]#ls /opt/
boost_1_59_0.tar.gz  mysql-5.7.17         mysql-bin.000002  rh
class_class.sql      mysql-5.7.17.tar.gz  mysql-bin.000003

刪除數(shù)據(jù)表

[root@localhost data]#mysql -uroot -p123456 -e 'show tables from class;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| Tables_in_class |
+-----------------+
| class           |
+-----------------+
[root@localhost data]#mysql -uroot -p123456 -e 'drop table class.class;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]#mysql -uroot -p123456 -e 'show tables from class;'
mysql: [Warning] Using a password on the command line interface can be insecure.

2.2數(shù)據(jù)恢復(fù)

[root@localhost data]#ls /opt/
boost_1_59_0.tar.gz  mysql-5.7.17         mysql-bin.000002
class_class.sql      mysql-5.7.17.tar.gz  rh
[root@localhost data]#mysqlbinlog --no-defaults /opt/mysql-bin.000003| mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]#mysql -uroot -p123456 -e 'select * from class.class'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+---------------+
| id   | name | hobby         |
+------+------+---------------+
|    1 | cxk  | ctrl          |
|    2 | wyb  | skateboarding |
+------+------+---------------+

3.節(jié)點(diǎn)恢復(fù)

3.1插入數(shù)據(jù)

mysql> insert into class values(3,'zs','sing song');
Query OK, 1 row affected (0.00 sec)

mysql> insert into class values(4,'lyx','an mo');
Query OK, 1 row affected (0.00 sec)

mysql> insert into class values(5,'xzq','sing');
Query OK, 1 row affected (0.00 sec)

mysql> select * from class;
+------+------+---------------+
| id   | name | hobby         |
+------+------+---------------+
|    1 | cxk  | ctrl          |
|    2 | wyb  | skateboarding |
|    3 | zs   | sing song     |
|    4 | lyx  | an mo         |
|    5 | xzq  | sing          |
+------+------+---------------+
5 rows in set (0.00 sec)

3.2備份二進(jìn)制日志文件

[root@localhost data]#cp /usr/local/mysql/data/mysql-bin.000004 /opt
[root@localhost data]#ls /opt/
boost_1_59_0.tar.gz  mysql-5.7.17         mysql-bin.000002  mysql-bin.000004
class_class.sql      mysql-5.7.17.tar.gz  mysql-bin.000003  rh
[root@localhost data]#mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

3.3刪除數(shù)據(jù)庫

mysql> drop database class;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

3.4恢復(fù)最原始數(shù)據(jù)

[root@localhost data]#mysqlbinlog --no-defaults mysql-bin.000001| mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from class.class;
+------+------+---------------+
| id   | name | hobby         |
+------+------+---------------+
|    1 | cxk  | ctrl          |
|    2 | wyb  | skateboarding |
+------+------+---------------+
2 rows in set (0.00 sec)

3.5恢復(fù)第三條數(shù)據(jù),跳過第四條數(shù)據(jù),恢復(fù)第五條數(shù)據(jù)

[root@localhost data]#mysqlbinlog --no-defaults mysql-bin.000004
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240325 19:05:35 server id 1  end_log_pos 123 CRC32 0x8cca9a0f 	Start: binlog v 4, server v 5.7.17-log created 240325 19:05:35
BINLOG '
f1oBZg8BAAAAdwAAAHsAAAAAAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AQ+ayow=
'/*!*/;
# at 123
#240325 19:05:35 server id 1  end_log_pos 154 CRC32 0xc343a14d 	Previous-GTIDs
# [empty]
# at 154
#240325 18:57:29 server id 1  end_log_pos 219 CRC32 0x2363efd7 	Anonymous_GTID	last_committed=0	sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#240325 18:57:29 server id 1  end_log_pos 342 CRC32 0x0134024f 	Query	thread_id=23	exec_time=597	error_code=0
SET TIMESTAMP=1711364249/*!*/;
SET @@session.pseudo_thread_id=23/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1437073414/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE "class"."class" /* generated by server */
/*!*/;
# at 342
#240325 19:04:31 server id 1  end_log_pos 407 CRC32 0x6ecfc60a 	Anonymous_GTID	last_committed=1	sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 407
#240325 19:04:31 server id 1  end_log_pos 543 CRC32 0x2b9a9ccc 	Query	thread_id=23	exec_time=175	error_code=0
use `class`/*!*/;
SET TIMESTAMP=1711364671/*!*/;
create table class(id int(6),name char(8),hobby varchar(40))
/*!*/;
# at 543
#240325 19:05:03 server id 1  end_log_pos 608 CRC32 0x93301d7a 	Anonymous_GTID	last_committed=2	sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 608
#240325 19:05:03 server id 1  end_log_pos 689 CRC32 0x9a4dfeb5 	Query	thread_id=23	exec_time=143	error_code=0
SET TIMESTAMP=1711364703/*!*/;
BEGIN
/*!*/;
# at 689
#240325 19:05:03 server id 1  end_log_pos 805 CRC32 0x00cd6a8b 	Query	thread_id=23	exec_time=143	error_code=0
SET TIMESTAMP=1711364703/*!*/;
insert into class values(1,'cxk','ctrl')
/*!*/;
# at 805
#240325 19:05:03 server id 1  end_log_pos 836 CRC32 0xe04e6909 	Xid = 235
COMMIT/*!*/;
# at 836
#240325 19:05:16 server id 1  end_log_pos 901 CRC32 0x75fad878 	Anonymous_GTID	last_committed=3	sequence_number=4
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 901
#240325 19:05:16 server id 1  end_log_pos 982 CRC32 0x93099481 	Query	thread_id=23	exec_time=130	error_code=0
SET TIMESTAMP=1711364716/*!*/;
BEGIN
/*!*/;
# at 982
#240325 19:05:16 server id 1  end_log_pos 1107 CRC32 0xcb3e3715 	Query	thread_id=23	exec_time=130	error_code=0
SET TIMESTAMP=1711364716/*!*/;
insert into class values(2,'wyb','skateboarding')
/*!*/;
# at 1107
#240325 19:05:16 server id 1  end_log_pos 1138 CRC32 0x725b9d8c 	Xid = 241
COMMIT/*!*/;
# at 1138
#240325 19:10:43 server id 1  end_log_pos 1203 CRC32 0xdc7ee633 	Anonymous_GTID	last_committed=4	sequence_number=5
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1203
#240325 19:10:43 server id 1  end_log_pos 1284 CRC32 0xa40309c5 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1711365043/*!*/;
BEGIN
/*!*/;
# at 1284
#240325 19:10:43 server id 1  end_log_pos 1404 CRC32 0xd6055200 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1711365043/*!*/;
insert into class values(3,'zs','sing song')
/*!*/;
# at 1404
#240325 19:10:43 server id 1  end_log_pos 1435 CRC32 0xc6f4a164 	Xid = 250
COMMIT/*!*/;
# at 1435
#240325 19:11:01 server id 1  end_log_pos 1500 CRC32 0xd23eccd1 	Anonymous_GTID	last_committed=5	sequence_number=6
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1500
#240325 19:11:01 server id 1  end_log_pos 1581 CRC32 0x9a58b66d 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1711365061/*!*/;
BEGIN
/*!*/;
# at 1581
#240325 19:11:01 server id 1  end_log_pos 1698 CRC32 0x0583193e 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1711365061/*!*/;
insert into class values(4,'lyx','an mo')
/*!*/;
# at 1698
#240325 19:11:01 server id 1  end_log_pos 1729 CRC32 0xbecfd71f 	Xid = 251
COMMIT/*!*/;
# at 1729
#240325 19:11:14 server id 1  end_log_pos 1794 CRC32 0x9d8d8f8f 	Anonymous_GTID	last_committed=6	sequence_number=7
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1794
#240325 19:11:14 server id 1  end_log_pos 1875 CRC32 0x1124da5c 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1711365074/*!*/;
BEGIN
/*!*/;
# at 1875
#240325 19:11:14 server id 1  end_log_pos 1991 CRC32 0xee4f0216 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1711365074/*!*/;
insert into class values(5,'xzq','sing')
/*!*/;
# at 1991
#240325 19:11:14 server id 1  end_log_pos 2022 CRC32 0x4d943848 	Xid = 252
COMMIT/*!*/;
# at 2022
#240325 19:12:56 server id 1  end_log_pos 2069 CRC32 0xd63fcb53 	Rotate to mysql-bin.000005  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@localhost data]#mysqlbinlog --no-defaults --start-position='1284' --stop-position='1435' mysql-bin.000004|mysql -uroot -p123456
#第三條數(shù)據(jù)插入at值為1284  結(jié)束值為1435
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> select * from class.class;
+------+------+---------------+
| id   | name | hobby         |
+------+------+---------------+
|    1 | cxk  | ctrl          |
|    2 | wyb  | skateboarding |
|    3 | zs   | sing song     |
+------+------+---------------+
3 rows in set (0.00 sec)

使用時(shí)間節(jié)點(diǎn)恢復(fù)第五條數(shù)據(jù)

[root@localhost data]#mysqlbinlog --no-defaults --start-datetime='2024-03-25 19:11:14' mysql-bin.000004|mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> select * from class.class;
+------+------+---------------+
| id   | name | hobby         |
+------+------+---------------+
|    1 | cxk  | ctrl          |
|    2 | wyb  | skateboarding |
|    3 | zs   | sing song     |
|    5 | xzq  | sing          |
+------+------+---------------+
4 rows in set (0.00 sec)

七、總結(jié)

1.物理冷備份

  • 關(guān)閉Mysqld服務(wù)
  • 使用tar命令進(jìn)行打包data目錄
  • 恢復(fù)直接解壓即可

2.邏輯備份

  • mysqldump -u -p --database 備份庫1,庫2 > xxx.sql #備份多個(gè)數(shù)據(jù)庫
  • mysqldump -u -p --all-database > xxx.sql #備份所有數(shù)據(jù)庫
  • mysql -u -p 庫1,庫2 表1,表2 > xxx.sql #備份多庫多表

3.完全恢復(fù)

  • mysql -u -p < xxx.sql #恢復(fù)整個(gè)庫
  • mysql -u -p 庫名 < xxx.sql #恢復(fù)表

4.增量備份

  1. 要先開啟二進(jìn)制日志,設(shè)置二進(jìn)制格式為MIXED
  2. 進(jìn)行一次完全備份,可每周備份一次,通過crontable -e 進(jìn)行編輯
  3. mysqladmin -uroot -p flush-logs 刷新二進(jìn)制日志文件 分割出二進(jìn)制日志文件,由于刷新之前的數(shù)據(jù)都會(huì)記錄在老的二進(jìn)制文件里
  4. 可以通過mysqlbinlog --no-defaults --base64-output=decode-rows -v 二進(jìn)制文件名稱 可以查看日志內(nèi)容
  5. 可以通過mysqlbinlog --no-defaults 二進(jìn)制日志文件名 mysql -uroot -p 恢復(fù)丟失的數(shù)據(jù)庫

位置恢復(fù)——position

  • start:--start-position
  • stop:--stop-position
  • start、stop:--start-position --stop-position

時(shí)間恢復(fù)——datetime

  • start:--start-datetime
  • stop:--stop-datetime
  • start、stop:--start-datetime --stop-datetime

以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。

相關(guān)文章

  • MySQL學(xué)習(xí)之MySQL基本架構(gòu)與鎖

    MySQL學(xué)習(xí)之MySQL基本架構(gòu)與鎖

    這篇文章主要介紹了MySQL的基本架構(gòu)和鎖,鎖的分類有兩種有按粒度分,按功能,也有不同的類型,感興趣的小伙伴可以參考閱讀
    2023-03-03
  • MySQL Daemon failed to start錯(cuò)誤解決辦法

    MySQL Daemon failed to start錯(cuò)誤解決辦法

    這篇文章主要介紹了MySQL Daemon failed to start錯(cuò)誤解決辦法的相關(guān)資料,需要的朋友可以參考下
    2017-01-01
  • Windows10下mysql 8.0.19 安裝配置方法圖文教程

    Windows10下mysql 8.0.19 安裝配置方法圖文教程

    這篇文章主要為大家詳細(xì)介紹了Windows10下mysql 8.0.19 安裝配置方法圖文教程,文中示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下
    2020-02-02
  • mysql 5.7.17的最新安裝教程圖文詳解

    mysql 5.7.17的最新安裝教程圖文詳解

    mysql-5.7.17-winx64是現(xiàn)在最新版本的Mysql,這是免安裝的,所以要進(jìn)行些配置,下面通過本文給大家介紹mysql 5.7.17的最新安裝教程圖文詳解,感興趣的朋友一起學(xué)習(xí)吧
    2017-03-03
  • 詳解MySQL更新語句的執(zhí)行流程

    詳解MySQL更新語句的執(zhí)行流程

    這篇文章主要介紹了MySQL架構(gòu)的更新語句的執(zhí)行流程,
    2024-03-03
  • MySql數(shù)據(jù)庫基礎(chǔ)知識(shí)點(diǎn)總結(jié)

    MySql數(shù)據(jù)庫基礎(chǔ)知識(shí)點(diǎn)總結(jié)

    這篇文章主要介紹了MySql數(shù)據(jù)庫基礎(chǔ)知識(shí)點(diǎn),總結(jié)整理了mysql數(shù)據(jù)庫基本創(chuàng)建、查看、選擇、刪除以及數(shù)據(jù)類型相關(guān)操作技巧,需要的朋友可以參考下
    2020-06-06
  • Mysql中被鎖住的表查詢以及如何解鎖詳解

    Mysql中被鎖住的表查詢以及如何解鎖詳解

    這篇文章主要介紹了Mysql中被鎖住的表查詢以及如何解鎖的相關(guān)資料,這些方法可以幫助你釋放鎖并恢復(fù)表的正常使用,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下
    2025-03-03
  • MySQL按時(shí)間進(jìn)行表分區(qū)的方法代碼

    MySQL按時(shí)間進(jìn)行表分區(qū)的方法代碼

    本文介紹如何在MySQL中創(chuàng)建按月份分區(qū)的表,并通過修改配置文件或使用數(shù)據(jù)庫事件來實(shí)現(xiàn)自動(dòng)分區(qū),文中通過代碼介紹的非常詳細(xì),對(duì)大家學(xué)習(xí)或者使用MySQL具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2024-09-09
  • MySQL 讀寫分離實(shí)例詳解

    MySQL 讀寫分離實(shí)例詳解

    這篇文章主要介紹了MySQL 讀寫分離實(shí)例詳解的相關(guān)資料,這里對(duì)讀寫MySQL分離進(jìn)行了簡單介紹,并附實(shí)例代碼,需要的朋友可以參考下
    2016-11-11
  • Mysql中的日期時(shí)間函數(shù)小結(jié)

    Mysql中的日期時(shí)間函數(shù)小結(jié)

    本文主要介紹了Mysql中的日期時(shí)間函數(shù)小結(jié),文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2023-01-01

最新評(píng)論