一、环境配置
操作系统版本：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 kmecab x86_64 0.996-1.module+el8.3.0+242+87d3366a.9 appstream 392 kmysql x86_64 8.0.26-1.module+el8.4.0+652+6de068a7 appstream 12 Mmysql-common x86_64 8.0.26-1.module+el8.4.0+652+6de068a7 appstream 133 kmysql-errmsg x86_64 8.0.26-1.module+el8.4.0+652+6de068a7 appstream 597 kperl-Carp noarch 1.42-396.el8 baseos 29 kperl-Data-Dumper x86_64 2.167-399.el8 baseos 57 kperl-Digest noarch 1.17-395.el8 appstream 26 kperl-Digest-MD5 x86_64 2.55-396.el8 appstream 36 kperl-Encode x86_64 4:2.97-3.el8 baseos 1.5 Mperl-Errno x86_64 1.28-421.el8 baseos 75 kperl-Exporter noarch 5.72-396.el8 baseos 33 kperl-File-Path noarch 2.15-2.el8 baseos 37 kperl-File-Temp noarch 0.230.600-1.el8 baseos 62 kperl-Getopt-Long noarch 1:2.50-4.el8 baseos 62 kperl-HTTP-Tiny noarch 0.074-1.el8 baseos 57 kperl-IO x86_64 1.38-421.el8 baseos 141 kperl-MIME-Base64 x86_64 3.15-396.el8 baseos 30 kperl-Net-SSLeay x86_64 1.88-2.module+el8.6.0+957+15d660ad appstream 378 kperl-PathTools x86_64 3.74-1.el8 baseos 89 kperl-Pod-Escapes noarch 1:1.07-395.el8 baseos 19 kperl-Pod-Perldoc noarch 3.28-396.el8 baseos 85 kperl-Pod-Simple noarch 1:3.35-395.el8 baseos 212 kperl-Pod-Usage noarch 4:1.69-395.el8 baseos 33 kperl-Scalar-List-Utils x86_64 3:1.49-2.el8 baseos 67 kperl-Socket x86_64 4:2.027-3.el8 baseos 58 kperl-Storable x86_64 1:3.11-3.el8 baseos 97 kperl-Term-ANSIColor noarch 4.06-396.el8 baseos 45 kperl-Term-Cap noarch 1.17-395.el8 baseos 22 kperl-Text-ParseWords noarch 3.30-395.el8 baseos 17 kperl-Text-Tabs+Wrap noarch 2013.0523-395.el8 baseos 23 kperl-Time-Local noarch 1:1.280-1.el8 baseos 32 kperl-URI noarch 1.73-3.el8 appstream 115 kperl-Unicode-Normalize x86_64 1.25-396.el8 baseos 81 kperl-constant noarch 1.33-396.el8 baseos 24 kperl-interpreter x86_64 4:5.26.3-421.el8 baseos 6.3 Mperl-libnet noarch 3.11-3.el8 appstream 120 kperl-libs x86_64 4:5.26.3-421.el8 baseos 1.6 Mperl-macros x86_64 4:5.26.3-421.el8 baseos 71 kperl-parent noarch 1:0.237-1.el8 baseos 19 kperl-podlators noarch 4.11-1.el8 baseos 117 kperl-threads x86_64 1:2.21-2.el8 baseos 60 kperl-threads-shared x86_64 1.58-2.el8 baseos 47 kprotobuf-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 kperl-IO-Socket-SSL noarch 2.066-4.module+el8.6.0+957+15d660ad appstream 297 kperl-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 PackagesTotal 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
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 distributionCopyright (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 distributionCopyright (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 distributionCopyright (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 -ADatabase 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 distributionCopyright (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>
