wordpress 做图片站,网站建设的广告语,长沙企业网站设计,钢材网站模板回顾基本语句#xff1a; 数据定义语言 ( DDL ) 。这类语言用于定义和修改数据库的结构#xff0c;包括创建、删除和修改数据库、表、视图和索引等对象。主要的语句关键字包括 CREATE 、 DROP 、 ALTER 、 RENAME 、 TRUNCATE 等。 create database 数据库 …回顾基本语句 数据定义语言 ( DDL ) 。这类语言用于定义和修改数据库的结构包括创建、删除和修改数据库、表、视图和索引等对象。主要的语句关键字包括 CREATE 、 DROP 、 ALTER 、 RENAME 、 TRUNCATE 等。 create database 数据库 create table 表 列1 数据类型 [ 约束条件] 列2 数据类型 【约束条件】…… add CONTRAINT 增加一些主键或者外键的约束 drop table/ drop database; truncate 表 rename 旧表名 TO 新表名 alter 表 add 列 / drop 列 / create view (虚表) / drop view create user / drop user 数据操作语言 (DML ) 。用于添加、删除、更新和查询数据库中的记录。这些语句是关系型数据库的核心用于操作表中的数据。主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。 select 表的数据查询 from 表 WHERE 过滤条件 group by 列 order by ; insert into 表列1……values (一行数据【和列是对应输入的】) 第二行数据…… update 表 SET 列 值 where 过滤条件 定位修改的数据行 delete from 表 where 过滤条件 数据控制语言 ( DCL ) 。用于定义和管理数据库、表、字段或用户的访问权限和安全级别。主要的语 句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 等。 GRANT // 数据库用户的授权 对应权限的内容见 MySQL CRASH COURSE p257 表 28.1 revoke // 数据库用户权限的回收【撤销】 commit / rollback // 对于事务的提交和回滚 【第26章】 数据查询语言 (DQL) 。这是专门用于从数据库中检索数据 / f select cur(); select version(); 联合查询等 join UNION 一、安装 1. 源码编译安装 1. 获取源码 1. boost(包含这个组件的源码包 2. cmake --- 生成 makefile 3. make 4. make install 5. 进行一些基础的优化 2. 二进制 软件包中包括已经编译完成的二进制程序以及需要的文件和配置文件 1. 直接获取安装需要的所有软件包的 bundle 包 然后进行安装 【 mysql 安装】 2. 直接配置 mysql 官方的仓库 通过yum 仓库安装 1. 添加 yum 仓库 [rootbogon ~]# wget https://repo.mysql.com//mysql80-community- release-el8-9.noarch.rpm --2024-04-27 20:31:45-- https://repo.mysql.com//mysql80-community release-el8-9.noarch.rpm Resolving repo.mysql.com (repo.mysql.com)... 23.210.109.97, 2a02:26f0:d8:980::1d68, 2a02:26f0:d8:98f::1d68 Connecting to repo.mysql.com (repo.mysql.com)|23.210.109.97|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 17792 (17K) [application/x-redhat-package-manager] Saving to: ‘mysql80-community-release-el8-9.noarch.rpm’ mysql80-community-release-el8-9 100% [] 17.38K - -.-KB/s in 0.006s 2024-04-27 20:31:45 (3.02 MB/s) - ‘mysql80-community-release-el8- 9.noarch.rpm’ saved [17792/17792] [rootbogon ~]# ls aaa Desktop Downloads Music original-ks.cfg Public Videos anaconda-ks.cfg Documents keys mysql80-community-release el8-9.noarch.rpm Pictures Templates [rootbogon ~]# rpm -ivh mysql80-community-release-el8-9.noarch.rpm [rootbogon ~]# ls /etc/yum.repos.d/ aa.repo bb.repo mysql-community-debuginfo.repo mysql community.repo mysql-community-source.repo redhat.repo [rootbogon ~]# yum -y remove mysql* mariadb* [rootbogon ~]# yum module disable mysql -y # 可选 如果已经在系统中进行了相关数据的安装 mariadb 以及 mysql # rpm -qa | egrep mysql|mariadb # 如果有提示任何包的安装信息需要先卸载对应的包然后再禁用下面的模块 # yum module -y disable mysql # 禁用原本启用的 mysql 模块 后续安 装时可以使用第一步所设置的仓库进行安装 2. 安装 server 包 [rootbogon ~]# yum install mysql-community-server 3. 启动服务 完成数据的初始化 创建基本的数据库和表 完成 mysql 超级用户 rootlocalhost 的创建以及密码的生成限制 root 用户 只能从 mysql 运行的节点登录 [rootmysql8 ~]# systemctl start mysqld # 抓取用户密码 [rootmysql8 ~]# grep password /var/log/mysqld.log 2024-04-28T00:27:27.590106Z 6 [Note] [MY-010454] [Server] A temporary password is generated for rootlocalhost: f%EqxVUy9d5u # 修改密码 [rootmysql8 ~]# mysql -u root -pf%EqxVUy9d5u -h localhost mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.36 Copyright (c) 2000, 2024, 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 alter user rootlocalhost identified by Redhat12~; Query OK, 0 rows affected (0.01 sec) 3. 补充 MySQL 用户管理 默认情况下只要 rootlocalhost 也就是说 只能通过 127.0.0.1 建立连接并连接到数据库 一旦在连接数据库时不是使用 127.0.0.1 就会拒绝请求 [rootmysql8 ~]# mysql -u root -pRedhat12~ -h localhost mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 8.0.36 MySQL Community Server - GPL Copyright (c) 2000, 2024, 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. [rootmysql8 ~]# mysql -u root -pRedhat12~ -h 192.168.110.50 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1130 (HY000): Host 192.168.110.50 is not allowed to connect to this MySQL server -h 192.168.110.50 // 指定数据库服务器的 IP 地址 需要先创建对应客户端 IP 地址的用户才可以访问 mysql-1: 192.168.110.50 bogon: 192.168.110.131 # 从 localhost 登录授权可以从 192.168.110.50 登录 [rootmysql8 ~]# mysql -u root -pRedhat12~ -h localhost mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 8.0.36 MySQL Community Server - GPL Copyright (c) 2000, 2024, 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. mysql create user root192.168.110.50 identified by Redhat12~; Query OK, 0 rows affected (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) mysql exit Bye 再次尝试 [rootmysql8 ~]# mysql -u root -pRedhat12~ -h 192.168.110.50 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 8.0.36 MySQL Community Server - GPL Copyright (c) 2000, 2024, 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 mysql; // 其实是一个全新为授权任何库或者表或者语句因此除了登 录 // 什么也不能干 // 通过 select * from mysql.user 发现新建的名为 root // 主机名为 192.168.110.50 的用户任何权限都没有 ERROR 1044 (42000): Access denied for user root192.168.110.50 to database mysql mysql exit Bye 因为只授权了 可以从 192.168.110.50 登录在 192.168.110.131 上是无法登录的 [rootbogon ~]# mysql -u root -pRedhat12~ -h 192.168.110.50 mysql: [Warning] Using a password on the command line interface can beinsecure. ERROR 1130 (HY000): Host 192.168.110.131 is not allowed to connect to this MySQL server # 引申如果需要从 192.168.110.131 登录数据库 就需要再授权一个用户可以从 192.168.110.131 登录 lamp grant all webapp.* to appuser192.168.110.% identified by mima; 4. 升级降级 1. 确定安装方式 2. 数据迁移 二、配置 调整数据库管理系统的密码策略 MySQL :: MySQL 8.0 Reference Manual :: 8.4.3.2 Password Validation Options and Variableshttps://dev.mysql.com/doc/refman/8.0/en/validate-password-options-variables.html 数据目录 一般写在配置文件中 datadir auto.cnf 持久生效的所有变量以及对应的值 bin log .index binlog.000002 binlog.000003 bin log .000001 日志 client-key.pem server-key.pem ca-key.pem ca.pem private_key.pem cert.pem server-cert.pem public_key.pem 加密通信需要使用的秘钥和证书 mysql performance_schema sys 这三个是目录所有的下的库都会创建属于自己的目录来保存库中表的数据 使用 存储引擎的表文件会被存储成一个文件 使用存储引擎的表文件会存储成多个文件 ibdata1 #ib_16384_0.dblwr ibtmp1 mysql.ibd #ib_16384_1.dblwr #innodb_redo undo_001 client- ib_buffer_pool #innodb_temp undo_002 存储引擎运行期间产生的额外数据索引、事务等 临时性的运行文件 mysql 对于所有用户和相关的配置都保存在具体的数据表中下面引入一些简单的关于用户授权的表格 mysql.user: 用户账号信息以及全局范围的授权数据 【所有库所有表都可以进行查询操作则显示此用户具有查询权限】 global_grants: 全局授权表表数据本身不断发生变换根据实时授权的信息变化 db: 对于库级别操作的授权 tables_priv: 显示表级别的授权 columns_priv: 对于数据列的授权 procs_priv: 对于存储过程的授权 【create procedures】 proxies_proc: 代理用户权限 default_role: 默认的角色角色可以理解为已经定义好的权限的合集直接可以授权给指定用户避免一个一个授权的尴尬 password_history: 密码变更的历史信息 系统运行维护相关表 general_log 记录查询记录 slow_log 慢查询日志 time_zone: 时区信息和时区差距 主从复制系统表格 gtid_executed: GTID 组数据的保存以gtid写入数据保证数据一致性 ndb_binlog_index: 二进制日志的索引 slave* : 从节点的状态
配置文件 熟悉 mysql 配置文件结构 主配置文件 额外的附加配置文件路径 数据库的配置是通过一些环境变量控制可以在运行状态下直接修改环境变量的值生效配置,但是 因为服务在重启时会加载记录在配置文件中的变量的值因为会恢复成默认值如果需要配置持久化 生效需要直接写入配置文件。 数据库的配置文件包括多个不同的配置块 ([] 来表示不同的配置块 ) [mysqld] 对于服务端的守护进程去进行配置。 MySQL 日志 二进制启用后后导致数据库运行效率降低。可以通过二进制日志实现数据备份和数据同步的作。 二进制日志不能很好的应对突然发生的服务中断停止只有已经执行完毕的语句和事务才会录 张中断时正在进行的数据变化不会被记录到二进制日志。 -- log - bin 启用二进制日志。 -- skip - log - bin -- disable - log - bin 禁用二进制日志。 二进制日志的更新更新编号 1 、每一次数据库守护进程重新启动 2 、 执行 flush 二进制日志 3 、设定了二进制日志的最大大小 三、备份与还原 数据库的数据备份就是为了进行数据还原。保证数据完整性和一致性。备份 1. 物理备份 1. 物理备份一般是针对数据库的数据目录进行的备份可以直接使用文件操作命令实现 2. 物理备份速度取决于磁盘 IO 速度效率很高 3. 输出的备份日志更具有可读性 4. 物理备份可能包含不需要的日志文件以及配置 5. 对于数据备份的颗粒度需要取决于不同的存储引擎 6. 物理备份的时候一般需要设置专门的读写锁或者停止 mysql 服务来避免在备份过程中出现任何新数据的写入避免备份遗漏数据。 2. 逻辑备份mysqldump 1. 通过向数据库服务查询相关数据来产生数据备份 2. 逻辑备份的效率远低于物理备份 3. 逻辑备份的数据量可能远大于物理备份兼容性相对较低逻辑备份不能在版本差异较大的数据库之间实现数据的恢复 4. 逻辑备份完全规避日志和配置文件 5. 逻辑备份期间数据库需要处于运行状态 6. 逻辑备份使用 mysqldump SELELCT OUTFILE 文件路径 7. 进行数据恢复通过 mysql 或者 LOAD DATA mysqlimport 实现 在线备份和离线备份 热备份 和 冷备份 在线 mysql 服务处于运行状态下进行的备份叫做在线备份热备份反之 冷备份和离线备份 一般对于一直处于业务处理场景下的数据库进行热备份 对于业务处理量较小或者是指主业务库数据 副本节点进行冷备份。 本地备份和远程备份 在数据库的节点上进行的备份是本地备份。从远程节点进行备份是远程备份。 cp / tar // 本地备份物理备份 scp / sftp / rsync // 可以针对数据库进行远程备份程序 物理备份 快照备份 数据库本身并不提供这个功能。逻辑卷和 xfs 、 Veritas 、 ZFS 文件系统都提供快照的功能 全量备份 增量备份 每一次备份都是对于数据库全部文件的备份称之为全量备份。 每一次备份会和上一次备份比较仅备份发生变化的部分称之为增量备份。 数据表的数据完整性 1. innodb 数据库一般不存在数据碎坏丢失的问题 2. MyISAM 数据表比较可能因为缺少表结构文件、表索引、表数据文件等导致数据损坏通过MyISAM 检查工具检查表数据的完整性 mysqldump 备份数据库 实验 使用场景 1. 为了防止数据丢失对整个数据库进行全量备份 2. 在进行主从架构搭建的时候向从库导入数据的一种手段 3. 作为实验数据的源数据 1. 为整个数据库制作一个副本而不需要直接修改源数据库的数据 2. 为了检验数据库升级之后的数据迁移能够顺利进行 逻辑 备份 1. 备份 mysqldump 2. 恢复数据 1. 系统 shell mysql -u root -p [db] dump.sql 2. MYSQL shell source 备份文件路径 [rootmysql-1 ~]# mysql -u root -predhat
mysql: [Warning] Using a password on the command line interface can beinsecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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 inputstatement.
mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| sys |
--------------------
4 rows in set (0.01 sec)
mysql create database test;
Query OK, 1 row affected (0.00 sec)
mysql use test;
Database changed
mysql create table a (id int, name char(8));
Query OK, 0 rows affected (0.02 sec)
mysql insert into a values(1, aaa), (2, bbb), (3, ccc);
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql select * from a;
------------
| id | name |
------------
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
------------
3 rows in set (0.00 sec)
mysql exit
Bye
[rootmysql-1 ~]# mysqldump -u root -predhat --databases test backup.sql
// 有创建库的语句所以只能恢复到原本的库中
mysqldump: [Warning] Using a password on the command line interface can be
insecure.
[rootmysql-1 ~]# cat backup.sql
-- MySQL dump 10.13 Distrib 8.0.36, for Linux (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 8.0.36
/*!40101 SET OLD_CHARACTER_SET_CLIENTCHARACTER_SET_CLIENT */;
/*!40101 SET OLD_CHARACTER_SET_RESULTSCHARACTER_SET_RESULTS */;
/*!40101 SET OLD_COLLATION_CONNECTIONCOLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!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 */;
--
-- Current Database: test
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ test /*!40100 DEFAULT CHARACTER
SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTIONN
*/;
USE test;
--
-- Table structure for table a
--
DROP TABLE IF EXISTS a;
/*!40101 SET saved_cs_client character_set_client */;
/*!50503 SET character_set_client utf8mb4 */;
CREATE TABLE a (
id int DEFAULT NULL,
name char(8) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;
/*!40101 SET character_set_client saved_cs_client */;
--
-- Dumping data for table a
--
LOCK TABLES a WRITE;
/*!40000 ALTER TABLE a DISABLE KEYS */;
INSERT INTO a VALUES (1,aaa),(2,bbb),(3,ccc);
/*!40000 ALTER TABLE a 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 2024-04-28 23:47:16
[rootmysql-1 ~]# mysqldump -u root -predhat test backup01.sql
mysqldump: [Warning] Using a password on the command line interface can be
insecure.
// 没有创建库的语句所以可能恢复的任意指定的数据库中
[rootmysql-1 ~]# cat backup01.sql
-- MySQL dump 10.13 Distrib 8.0.36, for Linux (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 8.0.36
/*!40101 SET OLD_CHARACTER_SET_CLIENTCHARACTER_SET_CLIENT */;
/*!40101 SET OLD_CHARACTER_SET_RESULTSCHARACTER_SET_RESULTS */;
/*!40101 SET OLD_COLLATION_CONNECTIONCOLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!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 a
--
DROP TABLE IF EXISTS a;
/*!40101 SET saved_cs_client character_set_client */;
/*!50503 SET character_set_client utf8mb4 */;
CREATE TABLE a (
id int DEFAULT NULL,
name char(8) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;
/*!40101 SET character_set_client saved_cs_client */;
--
-- Dumping data for table a
--
LOCK TABLES a WRITE;
/*!40000 ALTER TABLE a DISABLE KEYS */;
INSERT INTO a VALUES (1,aaa),(2,bbb),(3,ccc);
/*!40000 ALTER TABLE a 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 2024-04-28 23:50:33
[rootmysql-1 ~]# mysql -u root -predhat
mysql: [Warning] Using a password on the command line interface can be
insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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 drop database test;
Query OK, 1 row affected (0.01 sec)
mysql exit
Bye
[rootmysql-1 ~]# mysql -u root -predhat backup.sql
mysql: [Warning] Using a password on the command line interface can be
insecure.
[rootmysql-1 ~]# mysql -u root -predhat
mysql: [Warning] Using a password on the command line interface can be
insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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 |
| test |
--------------------
5 rows in set (0.00 sec)
mysql select * from test.a;
------------
| id | name |
------------
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
------------
3 rows in set (0.00 sec)
mysql exit
Bye
# 另一种恢复数据的方法
[rootmysql-1 ~]# mysql -u root -predhat
mysql: [Warning] Using a password on the command line interface can be
insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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 drop database test;
Query OK, 1 row affected (0.01 sec)
mysql source /root/backup.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (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, 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.02 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, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
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 select * from test.a;
------------
| id | name |
------------
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
------------
3 rows in set (0.00 sec)
mysql drop database test;
Query OK, 1 row affected (0.01 sec)
mysql source /root/backup01.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)
ERROR 1046 (3D000): No database selected
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 1046 (3D000): No database selected
Query OK, 0 rows affected (0.00 sec)
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
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 select * from test.a;
ERROR 1049 (42000): Unknown database test
mysql create database test;
Query OK, 1 row affected (0.01 sec)
mysql use test;
Database changed
mysql source /root/backup01.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)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
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 select * from a;
------------
| id | name |
------------
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
------------
3 rows in set (0.00 sec)
mysql drop table a;
Query OK, 0 rows affected (0.01 sec)
mysql exit
Bye
[rootmysql-1 ~]# mysql -u root -predhat test backup01.sql
mysql: [Warning] Using a password on the command line interface can be
insecure.
[rootmysql-1 ~]# mysql -u root -predhat
mysql: [Warning] Using a password on the command line interface can be
insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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 select * from test.a;
------------
| id | name |
------------
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
------------
3 rows in set (0.00 sec)
mysql
mysql exit
Bye
# 使用--tab 在备份时分离表结构和表数据在恢复时也可以分开恢复
[rootmysql-1 ~]# mysqldump -u root -p --tab/var/lib/mysql-files test
Enter password:
[rootmysql-1 ~]# ls /var/lib/mysql-files/
a.sql a.txt
# 恢复表
[rootmysql-1 ~]# mysql -u root -predhat test /var/lib/mysql-files/a.sql
mysql: [Warning] Using a password on the command line interface can be
insecure.
# 需要test库是存在的
# 恢复数据
[rootmysql-1 ~]# mysqlimport -u root -predhat test /var/lib/mysqlfiles/a.txt
mysqlimport: [Warning] Using a password on the command line interface can
be insecure.
test.a: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 物理备份 1. 备份使用文件备份的命令 直接创建压缩包备份或者 cp 等命令创建数据文件副本 2. 恢复 把备份的文件还原到原本的数据目录中即可文件的权限和 selinux 标签是需要注意 [rootmysql-1 ~]# mkdir /db_backup; cp -r /var/lib/mysql/test/ /db_backup/
[rootmysql-1 ~]# ls /var/lib/mysql/test/
a.ibd
[rootmysql-1 ~]# ls /db_backup/test/
a.ibd
[rootmysql-1 ~]# rm -rf /var/lib/mysql/test/
[rootmysql-1 ~]# mysql -u root -predhat
mysql: [Warning] Using a password on the command line interface can be
insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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 |
| test |
--------------------
5 rows in set (0.00 sec)
mysql use test;
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_test |
----------------
| a |
----------------
1 row in set (0.00 sec)
mysql select * from a;
------------
| id | name |
------------
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
------------
3 rows in set (0.01 sec)
mysql exit
Bye
[rootmysql-1 ~]# systemctl restart mysqld
[rootmysql-1 ~]# ls /var/lib/mysql/test
ls: cannot access /var/lib/mysql/test: No such file or directory
[rootmysql-1 ~]# mysql -u root -predhat
mysql: [Warning] Using a password on the command line interface can be
insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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 |
| test |
--------------------
5 rows in set (0.01 sec)
mysql use test;
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 a;
ERROR 1812 (HY000): Tablespace is missing for table test.a.
mysql exit
Bye
[rootmysql-1 ~]# systemctl stop mysqld
[rootmysql-1 ~]# mv /db_backup/test/ /var/lib/mysql/
[rootmysql-1 ~]# ls /var/lib/mysql/test
a.ibd
[rootmysql-1 ~]# restorecon -Rvv /var/lib/mysql/test/
Relabeled /var/lib/mysql/test from unconfined_u:object_r:default_t:s0 to
unconfined_u:object_r:mysqld_db_t:s0
Relabeled /var/lib/mysql/test/a.ibd from
unconfined_u:object_r:default_t:s0 to unconfined_u:object_r:mysqld_db_t:s0
[rootmysql-1 ~]# chown -R mysql.mysql /var/lib/mysql/test/
[rootmysql-1 ~]# systemctl restart mysqld
[rootmysql-1 ~]# mysql -u root -predhat
mysql: [Warning] Using a password on the command line interface can be
insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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 select * from test.a;
------------
| id | name |
------------
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
------------
3 rows in set (0.01 sec)
mysql exit
Bye
# 使用tar 不需要关注文件的权限和标签
[rootmysql-1 ~]# cd /var/lib/mysql
[rootmysql-1 mysql]# tar -czf testdb.tar.gz test/
[rootmysql-1 mysql]# tar -tf testdb.tar.gz
test/
test/a.ibd
[rootmysql-1 mysql]# systemctl stop mysql
Failed to stop mysql.service: Unit mysql.service not loaded.
[rootmysql-1 mysql]# systemctl stop mysqld
[rootmysql-1 mysql]# pwd
/var/lib/mysql
[rootmysql-1 mysql]# rm -rf testq
[rootmysql-1 mysql]# rm -rf test
[rootmysql-1 mysql]# tar -xzf testdb.tar.gz .
tar: .: Not found in archive
tar: Exiting with failure status due to previous errors
[rootmysql-1 mysql]#
[rootmysql-1 mysql]# tar -xzf testdb.tar.gz -C .
[rootmysql-1 mysql]# ls test
a.ibd
[rootmysql-1 mysql]# ll -d test
drwxr-x---. 2 mysql mysql 19 Apr 29 00:09 test
[rootmysql-1 mysql]# ll -dZ test
drwxr-x---. 2 mysql mysql unconfined_u:object_r:mysqld_db_t:s0 19 Apr 29
00:09 test
[rootmysql-1 mysql]# rm -rf test
[rootmysql-1 mysql]# ll test
ls: cannot access test: No such file or directory
[rootmysql-1 mysql]# !tar
tar -xzf testdb.tar.gz -C .
[rootmysql-1 mysql]# ll -dZ test
drwxr-x---. 2 mysql mysql unconfined_u:object_r:mysqld_db_t:s0 19 Apr 29
00:09 test
[rootmysql-1 mysql]# ll -Z test
total 112
-rw-r-----. 1 mysql mysql unconfined_u:object_r:mysqld_db_t:s0 114688 Apr
29 00:09 a.ibd
[rootmysql-1 mysql]# systemctl start mysqld
[rootmysql-1 mysql]# mysql -u root -predhat
mysql: [Warning] Using a password on the command line interface can be
insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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 select * from test.a;
------------
| id | name |
------------
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
------------
3 rows in set (0.00 sec)
mysql exit
Bye 增量备份一般是通过二进制日志来实现的
文章转载自： http://www.morning.wtnyg.cn.gov.cn.wtnyg.cn http://www.morning.fqmbt.cn.gov.cn.fqmbt.cn http://www.morning.kpgms.cn.gov.cn.kpgms.cn http://www.morning.jpkk.cn.gov.cn.jpkk.cn http://www.morning.ghwtn.cn.gov.cn.ghwtn.cn http://www.morning.sthgm.cn.gov.cn.sthgm.cn http://www.morning.kqlrl.cn.gov.cn.kqlrl.cn http://www.morning.xqndf.cn.gov.cn.xqndf.cn http://www.morning.ndpwg.cn.gov.cn.ndpwg.cn http://www.morning.jbgzy.cn.gov.cn.jbgzy.cn http://www.morning.wpcfh.cn.gov.cn.wpcfh.cn http://www.morning.tlyms.cn.gov.cn.tlyms.cn http://www.morning.kcxtz.cn.gov.cn.kcxtz.cn http://www.morning.rfwqt.cn.gov.cn.rfwqt.cn http://www.morning.nfnxp.cn.gov.cn.nfnxp.cn http://www.morning.qinhuangdjy.cn.gov.cn.qinhuangdjy.cn http://www.morning.ksggr.cn.gov.cn.ksggr.cn http://www.morning.rzscb.cn.gov.cn.rzscb.cn http://www.morning.gwyml.cn.gov.cn.gwyml.cn http://www.morning.rnzbr.cn.gov.cn.rnzbr.cn http://www.morning.fksyq.cn.gov.cn.fksyq.cn http://www.morning.lrmts.cn.gov.cn.lrmts.cn http://www.morning.lhytw.cn.gov.cn.lhytw.cn http://www.morning.lzdbb.cn.gov.cn.lzdbb.cn http://www.morning.tkyxl.cn.gov.cn.tkyxl.cn http://www.morning.hhfwj.cn.gov.cn.hhfwj.cn http://www.morning.prddj.cn.gov.cn.prddj.cn http://www.morning.rqqct.cn.gov.cn.rqqct.cn http://www.morning.tpssx.cn.gov.cn.tpssx.cn http://www.morning.rggky.cn.gov.cn.rggky.cn http://www.morning.dodoking.cn.gov.cn.dodoking.cn http://www.morning.yrmpr.cn.gov.cn.yrmpr.cn http://www.morning.mlnby.cn.gov.cn.mlnby.cn http://www.morning.clzly.cn.gov.cn.clzly.cn http://www.morning.jgncd.cn.gov.cn.jgncd.cn http://www.morning.bojkosvit.com.gov.cn.bojkosvit.com http://www.morning.wtsr.cn.gov.cn.wtsr.cn http://www.morning.dktyc.cn.gov.cn.dktyc.cn http://www.morning.qsy36.cn.gov.cn.qsy36.cn http://www.morning.hbkkc.cn.gov.cn.hbkkc.cn http://www.morning.hqllx.cn.gov.cn.hqllx.cn http://www.morning.bmnm.cn.gov.cn.bmnm.cn http://www.morning.c7625.cn.gov.cn.c7625.cn http://www.morning.mjyrg.cn.gov.cn.mjyrg.cn http://www.morning.mnccq.cn.gov.cn.mnccq.cn http://www.morning.clbzy.cn.gov.cn.clbzy.cn http://www.morning.ctwwq.cn.gov.cn.ctwwq.cn http://www.morning.twfdm.cn.gov.cn.twfdm.cn http://www.morning.ctsjq.cn.gov.cn.ctsjq.cn http://www.morning.kzdgz.cn.gov.cn.kzdgz.cn http://www.morning.jstggt.cn.gov.cn.jstggt.cn http://www.morning.yrlfy.cn.gov.cn.yrlfy.cn http://www.morning.pznnt.cn.gov.cn.pznnt.cn http://www.morning.znnsk.cn.gov.cn.znnsk.cn http://www.morning.splcc.cn.gov.cn.splcc.cn http://www.morning.bgpch.cn.gov.cn.bgpch.cn http://www.morning.fpzz1.cn.gov.cn.fpzz1.cn http://www.morning.tzpqc.cn.gov.cn.tzpqc.cn http://www.morning.dnmgr.cn.gov.cn.dnmgr.cn http://www.morning.bnylg.cn.gov.cn.bnylg.cn http://www.morning.qrcxh.cn.gov.cn.qrcxh.cn http://www.morning.mftdq.cn.gov.cn.mftdq.cn http://www.morning.bpmdr.cn.gov.cn.bpmdr.cn http://www.morning.krgjc.cn.gov.cn.krgjc.cn http://www.morning.yfcyh.cn.gov.cn.yfcyh.cn http://www.morning.tsgxz.cn.gov.cn.tsgxz.cn http://www.morning.ctbr.cn.gov.cn.ctbr.cn http://www.morning.psxwc.cn.gov.cn.psxwc.cn http://www.morning.rfpxq.cn.gov.cn.rfpxq.cn http://www.morning.rhmt.cn.gov.cn.rhmt.cn http://www.morning.xfwnk.cn.gov.cn.xfwnk.cn http://www.morning.cqrenli.com.gov.cn.cqrenli.com http://www.morning.ptqds.cn.gov.cn.ptqds.cn http://www.morning.zcqtr.cn.gov.cn.zcqtr.cn http://www.morning.ywtbk.cn.gov.cn.ywtbk.cn http://www.morning.skkmz.cn.gov.cn.skkmz.cn http://www.morning.zlxrg.cn.gov.cn.zlxrg.cn http://www.morning.beijingzy.com.cn.gov.cn.beijingzy.com.cn http://www.morning.fplqh.cn.gov.cn.fplqh.cn http://www.morning.kpgft.cn.gov.cn.kpgft.cn
