网站ui案例,贵阳市建设局网站,淮南人才网,如何用html做班级网站MySQL逻辑备份#xff1a;
mysqldump基本备份#xff1a;
本质#xff1a;导出的是sql语句文件。
优点#xff1a;无论是什么存储引擎#xff0c;都可以用mysqldump备份成sql语句。
缺点#xff1a;速度较慢#xff0c;导入的时候出现格式不兼容的突发情况#xff…
MySQL逻辑备份
mysqldump基本备份
本质导出的是sql语句文件。
优点无论是什么存储引擎都可以用mysqldump备份成sql语句。
缺点速度较慢导入的时候出现格式不兼容的突发情况自己本身无法直接做增量备份。
提供三种级别的备份表级、库级和全库级。 不停止业务、不区分引擎。 基本语法 表级别备份 mysqldump [OPTIONS] database [tables] 库级别备份 mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] 全库级别备份 mysqldump [OPTIONS] --all-databases [OPTIONS] 3、mysqldump表级备份和还原
案例把db_itheima数据库中的tb_student数据表进行备份。
[rootmysql-server ~]# mkdir /tmp/sqlbak
[rootmysql-server ~]# mysqldump -uroot db_itheima tb_student /tmp/sqlbak/tb_student.sql -p
Enter password:[rootmysql-server sqlbak]# pwd
/tmp/sqlbak
[rootmysql-server sqlbak]# ll
total 4
-rw-r--r-- 1 root root 2226 Sep 28 08:25 tb_student.sql说明
如果导出的是数据库就用数据库的名称作为sql文件的文件名。
如果导出的是数据表就用数据表的名称作为sql文件的文件名。 然后我们再检查下这个tb_student.sql文件的内容
-- MySQL dump 10.13 Distrib 5.7.43, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database: db_itheima
-- ------------------------------------------------------
-- Server version 5.7.43-log/*!40101 SET OLD_CHARACTER_SET_CLIENTCHARACTER_SET_CLIENT */;
/*!40101 SET OLD_CHARACTER_SET_RESULTSCHARACTER_SET_RESULTS */;
/*!40101 SET OLD_COLLATION_CONNECTIONCOLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET OLD_TIME_ZONETIME_ZONE */;
/*!40103 SET TIME_ZONE00:00 */;
/*!40014 SET OLD_UNIQUE_CHECKSUNIQUE_CHECKS, UNIQUE_CHECKS0 */;
/*!40014 SET OLD_FOREIGN_KEY_CHECKSFOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS0 */;
/*!40101 SET OLD_SQL_MODESQL_MODE, SQL_MODENO_AUTO_VALUE_ON_ZERO */;
/*!40111 SET OLD_SQL_NOTESSQL_NOTES, SQL_NOTES0 */;--
-- Table structure for table tb_student
--DROP TABLE IF EXISTS tb_student;
/*!40101 SET saved_cs_client character_set_client */;
/*!40101 SET character_set_client utf8 */;
CREATE TABLE tb_student (id int(11) NOT NULL AUTO_INCREMENT,name varchar(20) DEFAULT NULL,age tinyint(3) unsigned DEFAULT 0,gender enum(male,female) DEFAULT NULL,subject enum(ui,java,yunwei,python) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT6 DEFAULT CHARSETutf8;
/*!40101 SET character_set_client saved_cs_client */;--
-- Dumping data for table tb_student
--LOCK TABLES tb_student WRITE;
/*!40000 ALTER TABLE tb_student DISABLE KEYS */;
INSERT INTO tb_student VALUES (1,刘备,33,male,java),(2,关羽,32,male,yunwei),(3,张飞,30,male,python),(4,貂蝉,18,female,ui),(5,大乔,18,female,ui);
/*!40000 ALTER TABLE tb_student ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONEOLD_TIME_ZONE */;/*!40101 SET SQL_MODEOLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKSOLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKSOLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENTOLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTSOLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTIONOLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTESOLD_SQL_NOTES */;-- Dump completed on 2023-09-28 8:25:55
[rootmysql-server ~]#所谓的逻辑备份就是我们把数据库的增删改的SQL语句导出到一个sql文件。 还可以多张表进行备份。 还原恢复数据表
有两种方式 # mysql .sql文件位置 -p Enter password:123 或 # mysql -uroot -p Enter password:123 mysql source .sql文件的位置 说明source这个是mysql里面的用于导入sql文件的。
先将tb_student数据表进行删除。然后再进行备份操作。
[rootmysql-server ~]# mysql db_itheima /tmp/sqlbak/tb_student.sql -p
Enter password:
[rootmysql-server ~]#
[rootmysql-server ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.43-log MySQL Community Server (GPL)Copyright (c) 2000, 2023, 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 use db_itheima;
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_db_itheima |
----------------------
| tb_student |
----------------------
1 row in set (0.00 sec)mysql select * from tb_student;
-----------------------------------
| id | name | age | gender | subject |
-----------------------------------
| 1 | 刘备 | 33 | male | java |
| 2 | 关羽 | 32 | male | yunwei |
| 3 | 张飞 | 30 | male | python |
| 4 | 貂蝉 | 18 | female | ui |
| 5 | 大乔 | 18 | female | ui |
-----------------------------------
5 rows in set (0.00 sec)[rootmysql-server ~]# mysql -e select * from db_itheima.tb_student -uroot -p
Enter password:
-----------------------------------
| id | name | age | gender | subject |
-----------------------------------
| 1 | 刘备 | 33 | male | java |
| 2 | 关羽 | 32 | male | yunwei |
| 3 | 张飞 | 30 | male | python |
| 4 | 貂蝉 | 18 | female | ui |
| 5 | 大乔 | 18 | female | ui |
-----------------------------------
经过检查看到还原成功。 mysql use db_itheima;
Database changedmysql show tables;
Empty set (0.00 sec)mysql source /tmp/sqlbak/tb_student.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, 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.05 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, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0Query 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)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql select * from tb_student;
-----------------------------------
| id | name | age | gender | subject |
-----------------------------------
| 1 | 刘备 | 33 | male | java |
| 2 | 关羽 | 32 | male | yunwei |
| 3 | 张飞 | 30 | male | python |
| 4 | 貂蝉 | 18 | female | ui |
| 5 | 大乔 | 18 | female | ui |
-----------------------------------
5 rows in set (0.00 sec)表级的备份和还原。
可以对单表和多表进行备份与还原。 mysqldump库级备份和还原
案例把db_itheima数据库进行备份。
[rootmysql-server ~]# mysqldump --databases db_itheima /tmp/sqlbak/db_itheima.sql -p
Enter password:[rootmysql-server ~]# ll /tmp/sqlbak
total 8
-rw-r--r-- 1 root root 2381 Sep 28 08:52 db_itheima.sql
-rw-r--r-- 1 root root 2226 Sep 28 08:25 tb_student.sql
还原
mysql drop database db_itheima;
Query OK, 1 row affected (0.00 sec)mysql source /tmp/sqlbak/db_itheima.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, 1 warning (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 1 row affected (0.00 sec)Database 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 (0.05 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, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0Query 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)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| db_itheima |
| mysql |
| performance_schema |
| sys |
--------------------
5 rows in set (0.00 sec)mysql use db_itheima;
Database changedmysql select * from tb_student;
-----------------------------------
| id | name | age | gender | subject |
-----------------------------------
| 1 | 刘备 | 33 | male | java |
| 2 | 关羽 | 32 | male | yunwei |
| 3 | 张飞 | 30 | male | python |
| 4 | 貂蝉 | 18 | female | ui |
| 5 | 大乔 | 18 | female | ui |
-----------------------------------
5 rows in set (0.00 sec)MySQL 全库级备份
在MySQL中如果想使用mysqldump进行全库级备份必须开启二进制日志
开启二进制日志
# vim my.cnf
[mysqld]
...
server-id10
log-bin/mysql_3306/data/binlog# service mysql_3306 restart
注意其中是mysqld代表服务端。 mysqldump高级选项说明 刷新日志
就是看到日志比较大 了就人为地进行日志刷新。
锁表的概念不是把表锁住而是指阻止对表的增加、删除、修改操作。在这个备份期间里是阻止的但是备份结束后又可以进行操作了。在备份阶段这个时候比较慢。
如果是innodb引擎那么备份的时候就需要加上--single-transaction。 --master-data参数其他说明
1恢复时会执行默认是1
2需要RELOAD privilege并必须打开二进制文件
3这个选项会自动打开--lock-all-tables关闭--lock-tables 备份
前提是一定要打开二进制日志
[rootmysql-server ~]# mysqldump --all-databases --master-data --single-transaction /tmp/sqlbak/all.sql -p
Enter password:[rootmysql-server ~]# ll /tmp/sqlbak
total 880
-rw-r--r-- 1 root root 890065 Sep 28 09:15 all.sql
-rw-r--r-- 1 root root 2381 Sep 28 08:52 db_itheima.sql
-rw-r--r-- 1 root root 2226 Sep 28 08:25 tb_student.sql其中--master-data无所谓加还是不加。
--single-transactioninnoDB引擎就是必须要加的。 还原
# mysql all.sql -p
Enter password:123 总结 mysqldump工具备份的是SQL语句故备份不需要停服务 使用备份文件恢复时要保证数据库处于运行状态 只能实现全库指定库表级别的某一时刻的备份本身不能增量备份 适用于中小型数据库
