首页 > 数据库 >mysql备份还原

mysql备份还原

时间:2022-09-05 01:22:58浏览次数:61  
标签:rows -- 备份 sec 还原 mysql NULL root

一、环境配置

操作系统版本:Rocky Linux release 8.6 (Green Obsidian)

系统内核:Linux mysql 4.18.0-372.9.1.el8.x86_64 #1 SMP Tue May 10 14:48:47 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux

mysql版本:mysql-server-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64

二、安装mysql

[root@mysql ~]# yum install mysql-server
Last metadata expiration check: 0:00:17 ago on 2022-09-01T17:58:34 CST.
Dependencies resolved.
===========================================================================================================================================================================
 Package                                        Architecture               Version                                                     Repository                     Size
===========================================================================================================================================================================
Installing:
 mysql-server                                   x86_64                     8.0.26-1.module+el8.4.0+652+6de068a7                        appstream                      25 M
Installing dependencies:
 mariadb-connector-c-config                     noarch                     3.1.11-2.el8_3                                              appstream                      14 k
 mecab                                          x86_64                     0.996-1.module+el8.3.0+242+87d3366a.9                       appstream                     392 k
 mysql                                          x86_64                     8.0.26-1.module+el8.4.0+652+6de068a7                        appstream                      12 M
 mysql-common                                   x86_64                     8.0.26-1.module+el8.4.0+652+6de068a7                        appstream                     133 k
 mysql-errmsg                                   x86_64                     8.0.26-1.module+el8.4.0+652+6de068a7                        appstream                     597 k
 perl-Carp                                      noarch                     1.42-396.el8                                                baseos                         29 k
 perl-Data-Dumper                               x86_64                     2.167-399.el8                                               baseos                         57 k
 perl-Digest                                    noarch                     1.17-395.el8                                                appstream                      26 k
 perl-Digest-MD5                                x86_64                     2.55-396.el8                                                appstream                      36 k
 perl-Encode                                    x86_64                     4:2.97-3.el8                                                baseos                        1.5 M
 perl-Errno                                     x86_64                     1.28-421.el8                                                baseos                         75 k
 perl-Exporter                                  noarch                     5.72-396.el8                                                baseos                         33 k
 perl-File-Path                                 noarch                     2.15-2.el8                                                  baseos                         37 k
 perl-File-Temp                                 noarch                     0.230.600-1.el8                                             baseos                         62 k
 perl-Getopt-Long                               noarch                     1:2.50-4.el8                                                baseos                         62 k
 perl-HTTP-Tiny                                 noarch                     0.074-1.el8                                                 baseos                         57 k
 perl-IO                                        x86_64                     1.38-421.el8                                                baseos                        141 k
 perl-MIME-Base64                               x86_64                     3.15-396.el8                                                baseos                         30 k
 perl-Net-SSLeay                                x86_64                     1.88-2.module+el8.6.0+957+15d660ad                          appstream                     378 k
 perl-PathTools                                 x86_64                     3.74-1.el8                                                  baseos                         89 k
 perl-Pod-Escapes                               noarch                     1:1.07-395.el8                                              baseos                         19 k
 perl-Pod-Perldoc                               noarch                     3.28-396.el8                                                baseos                         85 k
 perl-Pod-Simple                                noarch                     1:3.35-395.el8                                              baseos                        212 k
 perl-Pod-Usage                                 noarch                     4:1.69-395.el8                                              baseos                         33 k
 perl-Scalar-List-Utils                         x86_64                     3:1.49-2.el8                                                baseos                         67 k
 perl-Socket                                    x86_64                     4:2.027-3.el8                                               baseos                         58 k
 perl-Storable                                  x86_64                     1:3.11-3.el8                                                baseos                         97 k
 perl-Term-ANSIColor                            noarch                     4.06-396.el8                                                baseos                         45 k
 perl-Term-Cap                                  noarch                     1.17-395.el8                                                baseos                         22 k
 perl-Text-ParseWords                           noarch                     3.30-395.el8                                                baseos                         17 k
 perl-Text-Tabs+Wrap                            noarch                     2013.0523-395.el8                                           baseos                         23 k
 perl-Time-Local                                noarch                     1:1.280-1.el8                                               baseos                         32 k
 perl-URI                                       noarch                     1.73-3.el8                                                  appstream                     115 k
 perl-Unicode-Normalize                         x86_64                     1.25-396.el8                                                baseos                         81 k
 perl-constant                                  noarch                     1.33-396.el8                                                baseos                         24 k
 perl-interpreter                               x86_64                     4:5.26.3-421.el8                                            baseos                        6.3 M
 perl-libnet                                    noarch                     3.11-3.el8                                                  appstream                     120 k
 perl-libs                                      x86_64                     4:5.26.3-421.el8                                            baseos                        1.6 M
 perl-macros                                    x86_64                     4:5.26.3-421.el8                                            baseos                         71 k
 perl-parent                                    noarch                     1:0.237-1.el8                                               baseos                         19 k
 perl-podlators                                 noarch                     4.11-1.el8                                                  baseos                        117 k
 perl-threads                                   x86_64                     1:2.21-2.el8                                                baseos                         60 k
 perl-threads-shared                            x86_64                     1.58-2.el8                                                  baseos                         47 k
 protobuf-lite                                  x86_64                     3.5.0-13.el8                                                appstream                     148 k
Installing weak dependencies:
 perl-IO-Socket-IP                              noarch                     0.39-5.el8                                                  appstream                      46 k
 perl-IO-Socket-SSL                             noarch                     2.066-4.module+el8.6.0+957+15d660ad                         appstream                     297 k
 perl-Mozilla-CA                                noarch                     20160104-7.module+el8.6.0+965+850557f9                      appstream                      14 k
Enabling module streams:
 mysql                                                                     8.0                                                                                            
 perl                                                                      5.26                                                                                           
 perl-IO-Socket-SSL                                                        2.066                                                                                          
 perl-libwww-perl                                                          6.34                                                                                           

Transaction Summary
===========================================================================================================================================================================
Install  48 Packages

Total download size: 50 M
Installed size: 231 M
Is this ok [y/N]: 

安装完后会安装如下mysql相关rpm包

启动mysql服务

[root@mysql ~]# systemctl enable --now mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
[root@mysql ~]# 

三、往数据库导入测试数据

hellodb_innodb.sql

-- MySQL dump 10.13  Distrib 5.5.33, for Linux (x86_64)
--
-- Host: localhost    Database: hellodb
-- ------------------------------------------------------
-- Server version    5.5.33-log

/*!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: `hellodb`
--

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

USE `hellodb`;

--
-- Table structure for table `classes`
--

DROP TABLE IF EXISTS `classes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `classes` (
  `ClassID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `Class` varchar(100) DEFAULT NULL,
  `NumOfStu` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`ClassID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `classes`
--

LOCK TABLES `classes` WRITE;
/*!40000 ALTER TABLE `classes` DISABLE KEYS */;
INSERT INTO `classes` VALUES (1,'Shaolin Pai',10),(2,'Emei Pai',7),(3,'QingCheng Pai',11),(4,'Wudang Pai',12),(5,'Riyue Shenjiao',31),(6,'Lianshan Pai',27),(7,'Ming Jiao',27),(8,'Xiaoyao Pai',15);
/*!40000 ALTER TABLE `classes` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `coc`
--

DROP TABLE IF EXISTS `coc`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `coc` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ClassID` tinyint(3) unsigned NOT NULL,
  `CourseID` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `coc`
--

LOCK TABLES `coc` WRITE;
/*!40000 ALTER TABLE `coc` DISABLE KEYS */;
INSERT INTO `coc` VALUES (1,1,2),(2,1,5),(3,2,2),(4,2,6),(5,3,1),(6,3,7),(7,4,5),(8,4,2),(9,5,1),(10,5,9),(11,6,3),(12,6,4),(13,7,4),(14,7,3);
/*!40000 ALTER TABLE `coc` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `courses`
--

DROP TABLE IF EXISTS `courses`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `courses` (
  `CourseID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `Course` varchar(100) NOT NULL,
  PRIMARY KEY (`CourseID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `courses`
--

LOCK TABLES `courses` WRITE;
/*!40000 ALTER TABLE `courses` DISABLE KEYS */;
INSERT INTO `courses` VALUES (1,'Hamo Gong'),(2,'Kuihua Baodian'),(3,'Jinshe Jianfa'),(4,'Taiji Quan'),(5,'Daiyu Zanghua'),(6,'Weituo Zhang'),(7,'Dagou Bangfa');
/*!40000 ALTER TABLE `courses` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `scores`
--

DROP TABLE IF EXISTS `scores`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `scores` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `StuID` int(10) unsigned NOT NULL,
  `CourseID` smallint(5) unsigned NOT NULL,
  `Score` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `scores`
--

LOCK TABLES `scores` WRITE;
/*!40000 ALTER TABLE `scores` DISABLE KEYS */;
INSERT INTO `scores` VALUES (1,1,2,77),(2,1,6,93),(3,2,2,47),(4,2,5,97),(5,3,2,88),(6,3,6,75),(7,4,5,71),(8,4,2,89),(9,5,1,39),(10,5,7,63),(11,6,1,96),(12,7,1,86),(13,7,7,83),(14,8,4,57),(15,8,3,93);
/*!40000 ALTER TABLE `scores` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `students`
--

DROP TABLE IF EXISTS `students`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `students` (
  `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Age` tinyint(3) unsigned NOT NULL,
  `Gender` enum('F','M') NOT NULL,
  `ClassID` tinyint(3) unsigned DEFAULT NULL,
  `TeacherID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `students`
--

LOCK TABLES `students` WRITE;
/*!40000 ALTER TABLE `students` DISABLE KEYS */;
INSERT INTO `students` VALUES (1,'Shi Zhongyu',22,'M',2,3),(2,'Shi Potian',22,'M',1,7),(3,'Xie Yanke',53,'M',2,16),(4,'Ding Dian',32,'M',4,4),(5,'Yu Yutong',26,'M',3,1),(6,'Shi Qing',46,'M',5,NULL),(7,'Xi Ren',19,'F',3,NULL),(8,'Lin Daiyu',17,'F',7,NULL),(9,'Ren Yingying',20,'F',6,NULL),(10,'Yue Lingshan',19,'F',3,NULL),(11,'Yuan Chengzhi',23,'M',6,NULL),(12,'Wen Qingqing',19,'F',1,NULL),(13,'Tian Boguang',33,'M',2,NULL),(14,'Lu Wushuang',17,'F',3,NULL),(15,'Duan Yu',19,'M',4,NULL),(16,'Xu Zhu',21,'M',1,NULL),(17,'Lin Chong',25,'M',4,NULL),(18,'Hua Rong',23,'M',7,NULL),(19,'Xue Baochai',18,'F',6,NULL),(20,'Diao Chan',19,'F',7,NULL),(21,'Huang Yueying',22,'F',6,NULL),(22,'Xiao Qiao',20,'F',1,NULL),(23,'Ma Chao',23,'M',4,NULL),(24,'Xu Xian',27,'M',NULL,NULL),(25,'Sun Dasheng',100,'M',NULL,NULL);
/*!40000 ALTER TABLE `students` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `teachers`
--

DROP TABLE IF EXISTS `teachers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `teachers` (
  `TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) NOT NULL,
  `Age` tinyint(3) unsigned NOT NULL,
  `Gender` enum('F','M') DEFAULT NULL,
  PRIMARY KEY (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `teachers`
--

LOCK TABLES `teachers` WRITE;
/*!40000 ALTER TABLE `teachers` DISABLE KEYS */;
INSERT INTO `teachers` VALUES (1,'Song Jiang',45,'M'),(2,'Zhang Sanfeng',94,'M'),(3,'Miejue Shitai',77,'F'),(4,'Lin Chaoying',93,'F');
/*!40000 ALTER TABLE `teachers` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `toc`
--

DROP TABLE IF EXISTS `toc`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `toc` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CourseID` smallint(5) unsigned DEFAULT NULL,
  `TID` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `toc`
--

LOCK TABLES `toc` WRITE;
/*!40000 ALTER TABLE `toc` DISABLE KEYS */;
/*!40000 ALTER TABLE `toc` 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 2013-09-03  2:51:27
View Code

testlog.sql

create table testlog (id int auto_increment primary key,name char(10),salary int default 20);

delimiter $$

create procedure  sp_testlog() 
begin  
declare i int;
set i = 1; 
while i <= 100000 
do  insert into testlog(name,salary) values (concat('wang',FLOOR(RAND() * 100000)),FLOOR(RAND() * 1000000)); 
set i = i +1; 
end while; 
end$$

delimiter ;

mysql> source /root/hellodb_innodb.sql  

mysql> source /root/testlog.sql

mysql> call sp_testlog;  #生成10万条测试数据

mysql> select count(*) from testlog; #查看testlog表里数据总数

mysql> source /root/hellodb_innodb.sql
Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected, 1 warning (0,00 sec)

。。。。。。。。。。。
Query OK, 0 rows affected (0,00 sec) Query OK, 0 rows affected (0,00 sec) Query OK, 0 rows affected (0,00 sec) Query OK, 0 rows affected (0,00 sec) mysql> mysql> mysql> source /root/testlog.sql Query OK, 0 rows affected (0,01 sec) Query OK, 0 rows affected (0,00 sec) mysql> mysql> call sp_testlog; Query OK, 1 row affected (1 min 18,47 sec) mysql> select count(*) from testlog; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0,01 sec) mysql>

四、备份与还原数据库

4.1、完全备份

InnoDB建议备份策略

mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --master-data=2 --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

#新版8.0.26以上

mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --sourcedata=2 --flush-privileges --default-character-set=utf8 --hex-blob>${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM建议备份策略

mysqldump -uroot -p123456 -A -F -E -R -x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob>${BACKUP}/fullbak_${BACKUP_TIME}.sql

4.2、完全备份还原

完全备份数据库
[root@mysql-8-0-26 ~]# mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=2 --flush-privileges --default-character-set=utf8 --hex-blob >/backup/fullbak_`date +%F-%H_%M_%S`.sql Enter password: [root@mysql-8-0-26 ~]# [root@mysql-8-0-26 ~]# ll -h /backup/ total 165M -rw-r--r-- 1 root root 165M Sep 4 20:11 fullbak_2022-09-04-19_56_15.sql [root@mysql-8-0-26 ~]#

停止数据库,删除原有数据库文件 [root@mysql-8-0-26 ~]# systemctl stop mysqld [root@mysql-8-0-26 ~]# rm -fr /var/lib/mysql/*

启动数据库进行数据库还原 [root@mysql-8-0-26 ~]# systemctl start mysqld [root@mysql-8-0-26 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, 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> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec) mysql> source /backup/fullbak_2022-09-04-19_56_15.sql 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 'WARNING: --master-data is deprecated and will be removed in a future version. Us' at line 1 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) .................... Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'NULL' Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | employees | | hellodb | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> mysql> use hellodb Database changed mysql> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | emp | | scores | | students | | t1 | | t2 | | teachers | | testlog | | toc | +-------------------+ 11 rows in set (0.00 sec) mysql> select count(*) from testlog; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0.01 sec) mysql>

4.3、分库备份

4.3.1分库备份并压缩

方法1
for db in `mysql -uroot -p123456 -e 'show databases'|grep -Ewv '^(Database|information_schema|performance_schema|sys)$'`;do mysqldump -uroot -p123456 -B $db |gzip > /backup/$db.sql.gz;done
方法2 mysql -uroot -p123456 -e 'show databases'|grep -Ewv '^(Database|information_schema|performance_schema\sys)$'|while read db;do mysqldump -uroot -p123456 -B $db | gzip > /backup/$db.sql.gz;done

 4.3.2、指定数据库恢复

解压需要还原的数据库
[root@mysql-8-0-26 db]# ll -h total 36M -rw-r--r-- 1 root root 35M Sep 4 23:01 employees-2022-09-04-22_38_26.sql.gz -rw-r--r-- 1 root root 945K Sep 4 23:01 hellodb-2022-09-04-22_38_46.sql.gz -rw-r--r-- 1 root root 259K Sep 4 23:01 mysql-2022-09-04-22_38_49.sql.gz [root@mysql-8-0-26 db]# gzip hellodb-2022-09-04-22_38_46.sql.gz -d . gzip: . is a directory -- ignored [root@mysql-8-0-26 db]# [root@mysql-8-0-26 db]# ll -h total 165M -rw-r--r-- 1 root root 161M Sep 4 23:01 employees-2022-09-04-22_38_26.sql.gz -rw-r--r-- 1 root root 2.6M Sep 4 23:01 hellodb-2022-09-04-22_38_46.sql -rw-r--r-- 1 root root 1.2M Sep 4 23:01 mysql-2022-09-04-22_38_49.sql.gz [root@mysql-8-0-26 db]# [root@mysql-8-0-26 db]#

停止mysql服务,并清掉原有的数据库文件,让给此时数据处于一个干净的环境 [root@mysql-8-0-26 db]# systemctl stop mysqld [root@mysql-8-0-26 db]# rm -fr /var/lib/mysql/*

启动mysql,并进行指定库还原 [root@mysql-8-0-26 db]# systemctl start mysqld [root@mysql-8-0-26 db]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, 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> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> source /root/db/hellodb-2022-09-04-22_38_46.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) 。。。。。。。。。。 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> use hellodb Database changed mysql> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | emp | | scores | | students | | t1 | | t2 | | teachers | | testlog | | toc | +-------------------+ 11 rows in set (0.00 sec) mysql> select count(*) from testlog; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0.01 sec) mysql> mysql>

4.3.3、利用二进制日志,还原数据库最新状态

设置二进制日志独立存放
[root@mysql-8-0-26 ~]# vi /etc/my.cnf [root@mysql-8-0-26 ~]# [root@mysql-8-0-26 ~]# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] log-bin=/data/mysql/mysql-bin [root@mysql-8-0-26 ~]#

创建二进制日志存放路径 [root@mysql-8-0-26 ~]# mkdir /data/mysql [root@mysql-8-0-26 ~]# chown -R mysql.mysql /data/mysql/ [root@mysql-8-0-26 ~]# ll -h /data/ total 330M drwxr-xr-x 2 root root 132 Sep 4 22:38 db drwxr-xr-x 2 root root 123 Sep 4 22:41 db1 -rw-r--r-- 1 root root 165M Sep 4 19:55 fullbak_2022-09-04-19_55_50.sql -rw-r--r-- 1 root root 165M Sep 4 19:56 fullbak_2022-09-04-19_56_15.sql -rw-r--r-- 1 root root 945K Sep 4 22:32 hellodb.sql.gz drwxr-xr-x 2 mysql mysql 6 Sep 4 23:42 mysql
[root@mysql-8-0-26 ~]#

重启服务并让其生效 [root@mysql-8-0-26 ~]# systemctl restart mysqld [root@mysql-8-0-26 ~]# ll -h /data/mysql/ total 8,0K -rw-r----- 1 mysql mysql 156 Sep 4 23:43 mysql-bin.000001 -rw-r----- 1 mysql mysql 29 Sep 4 23:43 mysql-bin.index [root@mysql-8-0-26 ~]#

4.3.4、完全备份,并记录备份的二进制日志

mysqldump -uroot -p -A -F --default-character-set=utf8mb4 --singlet-ransaction --master-data=2 | gzip > /backup/all_`date +%F-%H_%M_%S`.sql.gz
[root@mysql-8-0-26 backup]# mysqldump -uroot -p -A -F --default-character-set=utf8mb4 --single-transaction --master-data=2 | gzip > /backup/all_`date +%F-%H_%M_%S`.sql.gz
Enter password: 
[root@mysql-8-0-26 backup]# ll -h
total 36M
-rw-r--r-- 1 root root 36M Sep  4 23:54 all_2022-09-04-23_53_57.sql.gz
[root@mysql-8-0-26 backup]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26 Source distribution

Copyright (c) 2000, 2021, 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> show master logs; #查看并标记二进制日志位置
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       203 | No        |
| mysql-bin.000002 |       203 | No        |
| mysql-bin.000003 |       156 | No        |
+------------------+-----------+-----------+
3 rows in set (0,00 sec)

mysql> use hellodb
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> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| emp               |
| scores            |
| students          |
| t1                |
| t2                |
| teachers          |
| testlog           |
| toc               |
+-------------------+
11 rows in set (0,00 sec)

mysql> insert students (name,age,gender)value('mage',20,'M');  #新增数据库数据
Query OK, 1 row affected (0,00 sec)

mysql> insert students (name,age,gender)value('wang',22,'M'); #新增数据库数据
Query OK, 1 row affected (0,00 sec)

mysql> 
mysql>
mysql> exit
Bye
[root@mysql-8-0-26 backup]# 

解压备份数据库 [root@mysql-8-0-26 backup]# [root@mysql-8-0-26 backup]# ls all_2022-09-04-23_53_57.sql.gz [root@mysql-8-0-26 backup]# gzip -d all_2022-09-04-23_53_57.sql.gz [root@mysql-8-0-26 backup]# ll -h total 165M -rw-r--r-- 1 root root 165M Sep 4 23:54 all_2022-09-04-23_53_57.sql [root@mysql-8-0-26 backup]# cd

停止数据库,并对数据库造成破坏 [root@mysql-8-0-26 ~]# systemctl stop mysqld [root@mysql-8-0-26 ~]# rm -fr /var/lib/mysql/* [root@mysql-8-0-26 ~]# [root@mysql-8-0-26 ~]# [root@mysql-8-0-26 ~]# systemctl start mysqld [root@mysql-8-0-26 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, 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> show master logs; +------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +------------------+-----------+-----------+ | mysql-bin.000001 | 203 | No | | mysql-bin.000002 | 203 | No | | mysql-bin.000003 | 787 | No | | mysql-bin.000004 | 179 | No | +------------------+-----------+-----------+ 6 rows in set (0,00 sec) mysql> set sql_log_bin=0; #关闭二进制日志记录 Query OK, 0 rows affected (0,00 sec) mysql> source /backup/all_2022-09-04-23_53_57.sql #对数据进行数据恢复 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 'WARNING: --master-data is deprecated and will be removed in a future version. Us' at line 1 Query OK, 0 rows affected (0,00 sec) Query OK, 0 rows affected (0,00 sec) Query OK, 0 rows affected (0,00 sec) Query OK, 0 rows affected (0,00 sec) .................... Query OK, 0 rows affected (0,00 sec) Query OK, 0 rows affected (0,00 sec) Query OK, 0 rows affected (0,00 sec) Query OK, 0 rows affected (0,00 sec) Query OK, 0 rows affected (0,00 sec) Query OK, 0 rows affected (0,00 sec) Query OK, 0 rows affected (0,00 sec) Query OK, 0 rows affected (0,00 sec) ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'NULL' Query OK, 0 rows affected (0,00 sec) Query OK, 0 rows affected (0,00 sec) Query OK, 0 rows affected (0,00 sec) mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | employees | | hellodb | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0,00 sec) mysql> use hellodb Database changed mysql> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | emp | | scores | | students | | t1 | | t2 | | teachers | | testlog | | toc | +-------------------+ 11 rows in set (0,00 sec) mysql> select * from students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0,00 sec) mysql>

4.3.5、查看二进制标志位,并对二进制日志进行备份


[root@mysql-8-0-26 backup]# grep '^-- CHANGE MASTER TO' all_2022-09-04-23_53_57.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=156; [root@mysql-8-0-26 backup]# [root@mysql-8-0-26 backup]# [root@mysql-8-0-26 backup]# cd /data/mysql/ [root@mysql-8-0-26 mysql]# [root@mysql-8-0-26 mysql]# ll -h total 28K -rw-r----- 1 mysql mysql 203 Sep 4 23:50 mysql-bin.000001 -rw-r----- 1 mysql mysql 203 Sep 4 23:54 mysql-bin.000002 -rw-r----- 1 mysql mysql 787 Sep 5 00:27 mysql-bin.000003 -rw-r----- 1 mysql mysql 179 Sep 5 00:30 mysql-bin.000004 -rw-r----- 1 mysql mysql 174 Sep 5 00:31 mysql-bin.index [root@mysql-8-0-26 mysql]# mysqlbinlog mysql-bin.000003 --start-position=156 >/backup/inc.sql [root@mysql-8-0-26 mysql]# ll /backup/ total 168256 -rw-r--r-- 1 root root 172284990 Sep 4 23:54 all_2022-09-04-23_53_57.sql -rw-r--r-- 1 root root 4216 Sep 5 00:42 inc.sql [root@mysql-8-0-26 mysql]# [root@mysql-8-0-26 mysql]# mysqlbinlog mysql-bin.000004 >> /backup/inc.sql

4.3.6、导入二进制日志,并查看新增数据是否恢复到最新

mysql> 
mysql> source /backup/inc.sql
Query OK, 0 rows affected, 1 warning (0,01 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Charset changed
Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected, 1 warning (0,00 sec)

Query OK, 0 rows affected, 1 warning (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected, 1 warning (0,00 sec)

Query OK, 0 rows affected, 1 warning (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> 
mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | mage          |  20 | M      |    NULL |      NULL |
|    27 | wang          |  22 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0,00 sec)

mysql> set sql_log_bin=1;  #恢复二进制日志记录开启状态
Query OK, 0 rows affected (0,00 sec)

mysql> 

 

标签:rows,--,备份,sec,还原,mysql,NULL,root
From: https://www.cnblogs.com/cyh00001/p/16645888.html

相关文章

  • Linux 安装MySql
     1.下载MySql下载MySql:https://downloads.mysql.com/archives/community/2.上传到linux服务器上    3.解压将xz转换成tarxz-dmysql-8.0.20-linux-glibc2......
  • Mysql提权
    跑项目久了,太久没做技术,好多东西不记得翻车了,好记性不如烂笔头吧。场景:通过Webshell拿到了数据库的连接地址一些网站源代码文件中会包含数据库连接文件,通过查看这些文件可......
  • Homebrew更换阿里云镜像源或还原官方镜像站
    还原官方镜像站更换阿里云镜像站cd"$(brew--repo)"gitremoteset-urloriginhttps://mirrors.aliyun.com/homebrew/brew.gitcd"$(brew--repo)/Library/Taps/hom......
  • mysql备份恢复与迁移
    DBA职责1,设计备份策略全备增量时间自动2,日常备份检查备份存在性备份空间够用否3,定期恢复演练一个季度或者半年4,故障恢复通过现有备份,能够将数据库恢......
  • Navicat远程连接MySQL报错2059
    使用Navicat远程连接偶尔报错如下: 原因:mysql8之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password进入MySQL后台执行如下......
  • openEuler 22安装zabbix 4.0,提示无法安装zabbix-server-mysql和zabbix-web-mysql
    1.首先安装lamp环境作为zabbix的运行环境,也就是web展示界面环境dnfinstall-yhttpdmariadb-servermariadbphpphp-mysqlndphp-gdlibjpeg*php-ldapphp-odbcphp-p......
  • navicat连接远程数据库,mysql workbench导出数据表
    navicat连接远程数据库: mysqlworkbench导出数据表: ......
  • mysql 报错This function has none of DETERMINISTIC解决方案
    本文章向朋友们介绍开启bin-log日志mysql报错:ThisfunctionhasnoneofDETERMINISTIC,NOSQL解决办法,创建存储过程时出错信息:ERROR1418(HY000):Thisfunctionh......
  • 解决navicat连接mysql数据库报Client does not support..
    解决navicat连接mysql数据库报Clientdoesnotsupport...正在改BUG于2019-11-2521:05:35发布740收藏版权MySQL8.0的加密方式改变了,加密规则是caching_sha2_pas......
  • mysql
    服务器处理客户端请求:客户端进程向服务器进程发送一段文本(MySQL语句),服务器进程处理后再向客户端进程发送一段文本(处理结果)。流程:连接管理,解析与优化(查询缓存,语法解......