Linux——mysql运维篇
回顾基本语句:
- 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
- select 表的数据查询 from 表 WHERE 过滤条件 group by 列 order by ;
- insert into 表(列1……)values (一行数据【和列是对应输入的】), (第二行数据)……;
- update 表 SET 列 = 值 where 过滤条件 (定位修改的数据行);
- delete from 表 where 过滤条件;
- GRANT // 数据库用户的授权 对应权限的内容见 MySQL CRASH COURSE p257 表 28.1
- revoke // 数据库用户权限的回收【撤销】
- commit / rollback // 对于事务的提交和回滚 【第26章】
- select cur();
- select version();
- join
- UNION
一、安装
1. 添加 yum 仓库[root@bogon ~]# wget https://repo.mysql.com//mysql80-community- release-el8-9.noarch.rpm--2024-04-27 20:31:45-- https://repo.mysql.com//mysql80-communityrelease-el8-9.noarch.rpmResolving repo.mysql.com (repo.mysql.com)... 23.210.109.97,2a02:26f0:d8:980::1d68, 2a02:26f0:d8:98f::1d68Connecting to repo.mysql.com (repo.mysql.com)|23.210.109.97|:443...connected.HTTP request sent, awaiting response... 200 OKLength: 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.006s2024-04-27 20:31:45 (3.02 MB/s) - ‘mysql80-community-release-el8-9.noarch.rpm’ saved [17792/17792][root@bogon ~]# lsaaa Desktop Downloads Musicoriginal-ks.cfg Public Videosanaconda-ks.cfg Documents keys mysql80-community-releaseel8-9.noarch.rpm Pictures Templates[root@bogon ~]# rpm -ivh mysql80-community-release-el8-9.noarch.rpm[root@bogon ~]# ls /etc/yum.repos.d/aa.repo bb.repo mysql-community-debuginfo.repo mysqlcommunity.repo mysql-community-source.repo redhat.repo[root@bogon ~]# yum -y remove mysql* mariadb*[root@bogon ~]# yum module disable mysql -y# 可选 , 如果已经在系统中进行了相关数据的安装( mariadb 以及 mysql )# rpm -qa | egrep 'mysql|mariadb'# 如果有提示任何包的安装信息,需要先卸载对应的包,然后再禁用下面的模块# yum module -y disable mysql # 禁用原本启用的 mysql 模块 ,后续安装时,可以使用第一步所设置的仓库进行安装2. 安装 server 包[root@bogon ~]# yum install mysql-community-server3. 启动服务完成数据的初始化创建基本的数据库和表完成 mysql 超级用户 root@localhost 的创建以及密码的生成(限制 root 用户 只能从mysql 运行的节点登录)[root@mysql8 ~]# systemctl start mysqld# 抓取用户密码[root@mysql8 ~]# grep password /var/log/mysqld.log2024-04-28T00:27:27.590106Z 6 [Note] [MY-010454] [Server] Atemporary password is generated for root@localhost: f%EqxVUy9d5u# 修改密码[root@mysql8 ~]# mysql -u root -p'f%EqxVUy9d5u' -h localhostmysql: [Warning] Using a password on the command line interface canbe insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 9Server version: 8.0.36Copyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current inputstatement.mysql> alter user root@localhost identified by 'Redhat12~';Query OK, 0 rows affected (0.01 sec)
[root@mysql8 ~]# mysql -u root -p'Redhat12~' -h localhostmysql: [Warning] Using a password on the command line interface canbe insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 15Server version: 8.0.36 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.[root@mysql8 ~]# mysql -u root -p'Redhat12~' -h 192.168.110.50mysql: [Warning] Using a password on the command line interface canbe insecure.ERROR 1130 (HY000): Host '192.168.110.50' is not allowed to connectto this MySQL server-h 192.168.110.50 // 指定数据库服务器的 IP 地址
# 从 localhost 登录,授权可以从 192.168.110.50 登录[root@mysql8 ~]# mysql -u root -p'Redhat12~' -h localhostmysql: [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 15Server version: 8.0.36 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.mysql> create user root@'192.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> exitBye再次尝试[root@mysql8 ~]# mysql -u root -p'Redhat12~' -h 192.168.110.50mysql: [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 13Server version: 8.0.36 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current inputstatement.mysql> use mysql; // 其实是一个全新为授权任何库或者表,或者语句,因此除了登录// 什么也不能干// 通过 select * from mysql.user 发现新建的名为 root// 主机名为 192.168.110.50 的用户任何权限都没有ERROR 1044 (42000): Access denied for user 'root'@'192.168.110.50' todatabase 'mysql'mysql> exitBye因为只授权了 可以从 192.168.110.50 登录,在 192.168.110.131 上是无法登录的[root@bogon ~]# mysql -u root -p'Redhat12~' -h 192.168.110.50mysql: [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 thisMySQL server# 引申:如果需要从 192.168.110.131 登录数据库 就需要再授权一个用户可以从192.168.110.131 登录lampgrant all webapp.* to appuser@'192.168.110.%' identified by 'mima';
二、配置

- 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 日志
三、备份与还原
1. 物理备份
2. 逻辑备份(mysqldump)
在线备份和离线备份 (热备份 和 冷备份)
本地备份和远程备份
快照备份
全量备份 增量备份
数据表的数据完整性
mysqldump 备份数据库 实验
[root@mysql-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
[root@mysql-1 ~]# mysqldump -u root -predhat --databases test > backup.sql
// 有创建库的语句,所以只能恢复到原本的库中
mysqldump: [Warning] Using a password on the command line interface can be
insecure.
[root@mysql-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_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!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: `test`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER
SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N'
*/;
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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2024-04-28 23:47:16
[root@mysql-1 ~]# mysqldump -u root -predhat test > backup01.sql
mysqldump: [Warning] Using a password on the command line interface can be
insecure.
// 没有创建库的语句,所以可能恢复的任意指定的数据库中
[root@mysql-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_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
*/;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2024-04-28 23:50:33
[root@mysql-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
[root@mysql-1 ~]# mysql -u root -predhat < backup.sql
mysql: [Warning] Using a password on the command line interface can be
insecure.
[root@mysql-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
# 另一种恢复数据的方法
[root@mysql-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
[root@mysql-1 ~]# mysql -u root -predhat test < backup01.sql
mysql: [Warning] Using a password on the command line interface can be
insecure.
[root@mysql-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 在备份时,分离表结构和表数据,在恢复时,也可以分开恢复
[root@mysql-1 ~]# mysqldump -u root -p --tab=/var/lib/mysql-files test
Enter password:
[root@mysql-1 ~]# ls /var/lib/mysql-files/
a.sql a.txt
# 恢复表
[root@mysql-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库是存在的
# 恢复数据
[root@mysql-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
[root@mysql-1 ~]# mkdir /db_backup; cp -r /var/lib/mysql/test/ /db_backup/
[root@mysql-1 ~]# ls /var/lib/mysql/test/
a.ibd
[root@mysql-1 ~]# ls /db_backup/test/
a.ibd
[root@mysql-1 ~]# rm -rf /var/lib/mysql/test/
[root@mysql-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
[root@mysql-1 ~]# systemctl restart mysqld
[root@mysql-1 ~]# ls /var/lib/mysql/test
ls: cannot access '/var/lib/mysql/test': No such file or directory
[root@mysql-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
[root@mysql-1 ~]# systemctl stop mysqld
[root@mysql-1 ~]# mv /db_backup/test/ /var/lib/mysql/
[root@mysql-1 ~]# ls /var/lib/mysql/test
a.ibd
[root@mysql-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
[root@mysql-1 ~]# chown -R mysql.mysql /var/lib/mysql/test/
[root@mysql-1 ~]# systemctl restart mysqld
[root@mysql-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 不需要关注文件的权限和标签
[root@mysql-1 ~]# cd /var/lib/mysql
[root@mysql-1 mysql]# tar -czf testdb.tar.gz test/
[root@mysql-1 mysql]# tar -tf testdb.tar.gz
test/
test/a.ibd
[root@mysql-1 mysql]# systemctl stop mysql
Failed to stop mysql.service: Unit mysql.service not loaded.
[root@mysql-1 mysql]# systemctl stop mysqld
[root@mysql-1 mysql]# pwd
/var/lib/mysql
[root@mysql-1 mysql]# rm -rf testq
[root@mysql-1 mysql]# rm -rf test
[root@mysql-1 mysql]# tar -xzf testdb.tar.gz .
tar: .: Not found in archive
tar: Exiting with failure status due to previous errors
[root@mysql-1 mysql]#
[root@mysql-1 mysql]# tar -xzf testdb.tar.gz -C .
[root@mysql-1 mysql]# ls test
a.ibd
[root@mysql-1 mysql]# ll -d test
drwxr-x---. 2 mysql mysql 19 Apr 29 00:09 test
[root@mysql-1 mysql]# ll -dZ test
drwxr-x---. 2 mysql mysql unconfined_u:object_r:mysqld_db_t:s0 19 Apr 29
00:09 test
[root@mysql-1 mysql]# rm -rf test
[root@mysql-1 mysql]# ll test
ls: cannot access 'test': No such file or directory
[root@mysql-1 mysql]# !tar
tar -xzf testdb.tar.gz -C .
[root@mysql-1 mysql]# ll -dZ test
drwxr-x---. 2 mysql mysql unconfined_u:object_r:mysqld_db_t:s0 19 Apr 29
00:09 test
[root@mysql-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
[root@mysql-1 mysql]# systemctl start mysqld
[root@mysql-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
相关文章:

Linux——mysql运维篇
回顾基本语句: 数据定义语言 ( DDL ) 。这类语言用于定义和修改数据库的结构,包括创建、删除和修改数据库、表、视图和索引等对象。主要的语句关键字包括 CREATE 、 DROP 、 ALTER 、 RENAME 、 TRUNCATE 等。 create database 数据库 &…...

力扣每日一题-统计已测试设备-2024.5.10
力扣题目:统计已测试设备 题目链接: 2960.统计已测试设备 题目描述 代码思路 根据题目内容,第一感是根据题目模拟整个过程,在每一步中修改所有设备的电量百分比。但稍加思索,发现可以利用已测试设备的数量作为需要减少的设备电…...

代码+视频,R言语处理数据中的缺失值
在SCI论文中,我们不可避免和缺失数据打交道,特别是在回顾性研究,对于缺失的协变量(就是混杂因素),我们可以使用插补补齐数据,但是对于结局变量和原因变量的缺失,我们不能这么做。部分…...
PGSync安装使用教程(PostgreSQL数据实时同步至Elasticsearch)
说明 pgsync项目有两个,一个是ankane/pgsync,用于pgsql之间的数据同步,另一个是toluaina/pgsync,用于pgsql的数据同步至es,本教程适用于第二个项目。 pgsync应该是目前为止唯二支持es8的数据同步工具,另一…...
前端主题切换的多种方式
动态link标签加载不同主题css **原理:**提前准备好几套CSS主题样式文件,在点击切换主题时,创建link标签动态加载到head标签中,或者是动态改变link标签的href属性。 缺点: 动态加载样式文件,如果文件过大网…...
使用RESTful API构建 web 应用程序
RESTful API是一种基于HTTP协议的架构风格,用于设计网络应用程序的 API。它强调使用标准的HTTP方法(如GET、POST、PUT和DELETE)对资源进行操作,并使用统一的资源标识符(URI)来唯一标识每个资源。RESTful AP…...
KaiOS Data PDN 数据建立流程
代码逻辑 APN创建 在 DataCallManager.jsm中,会对所有apnsetting创建一个datacall,其中会包含dataprofile的成员(通过apn参数来创建),在之后的流程用于直接发送到modem建立PDN。 PDN建立 1、DataCallManager.jsm -dcInterface.setupDataCall //RILNetworkInterface.c…...

Mybatis-Plus常用的增删改查坑
添加依赖 <!--实体类上加上Data注解就不用写get,set,toString,equals等方法了--><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional…...

初识指针(4)<C语言>
前言 前面的文章,已经对指针的基础概念以及运用有了初步了解,我们可以进一步探究指针比较深入的知识,下文将主要介绍:使用指针数组模拟二维数组、字符指针变量、数组指针、二维数组传参的本质、函数指针、typedef关键字等。 目录…...

pyqt 工具栏QToolBar控件
pyqt 工具栏QToolBar控件 QToolBar控件介绍效果代码 QToolBar控件介绍 QToolBar 是 PyQt(中的一个控件,它提供了一个工具栏,通常包含一系列的工具按钮或下拉菜单,用于提供对应用程序功能的快速访问。 QToolBar 通常与 QMainWind…...
SystemVerilog/Verilog中的仿真延时建模之模块路径延时
一 概要 模块路径延迟,描述的是模块中信号从源端到目的端传输的延迟。 路径以及对应的延迟是在模块中的specify块中指定的,其中信号源端一般为input或者inout,而目的端则只能为output或者inout.在specify中指定的模块路径,常见的形式主要三种,分别是: 简单路径(Simple Path…...
代码随想录算法训练营Day36 | 738.单调递增的数字、968.监控二叉树、贪心算法总结 | Python | 个人记录向
本文目录 738.单调递增的数字做题看文章 968.监控二叉树做题看文章 贪心算法总结以往忽略的知识点小结个人体会 738.单调递增的数字 代码随想录:738.单调递增的数字 Leetcode:738.单调递增的数字 做题 无思路。 看文章 例如:98ÿ…...

FME学习之旅---day26
我们付出一些成本,时间的或者其他,最终总能收获一些什么。 【由于上周,上班状态不是很好,事情多又杂,没有学习的劲头,就短暂的休息了一下下。双休爬山,给自己上了强度,今天才缓过来…...
JAVA学习-练习试用Java实现电话号码的字母组合
问题: 给定一个仅包含数字 2-9 的字符串,返回所有它能表示的字母组合。答案可以按 任意顺序 返回。 给出数字到字母的映射如下(与电话按键相同)。注意 1 不对应任何字母。 示例 1: 输入:digits "23" 输…...
js代码中关于async await的滥用 async await的滥用
概念: 返回值: async返回一个promise---这个返回值是默认自发行为 async function name1(params) {console.log(params) } let result name1(lion) console.log(result) // Promise { undefined } result.then(res > console.log(res)) // undefin…...

基础算法,贪心算法,贪心策略,OJ练习
文章目录 一、概念二、OJ练习2.1 区间选点2.2 区间合并2.3 区间2.4 合并果子2.5 排队接水2.6 货仓选址2.7 防晒2.8 畜栏预定2.9 雷达设备2.10 国王游戏2.11 耍杂技的牛2.12 给树染色2.13 任务2.14 能量石 三、总结 一、概念 贪心是一种在每次决策时采取当前意义下最优策略的算…...

一文讲透亚马逊云三层架构
关于三层架构,我们有很多想说的话: (以下内容以下都在VPC中) cloudfront做CDN加速网关规划S3做静态网站托管APIGateway作为统一网关入口认证/限流Lambda 作为传统后端,并发,底层架构Redis缓存DDB作为持久化…...

只需3步,使用Stable Diffusion无限生成AI数字人视频(附安装包)
基本方法 搞一张照片,搞一段语音,合成照片和语音,同时让照片中的人物动起来,特别是头、眼睛和嘴。 语音合成 语音合成的方法很多,也比较成熟了,大家可以选择自己方便的,直接录音也可以&#…...
RustGUI学习(iced)之小部件(七):如何使用图像image部件来显示图片?
前言 本专栏是学习Rust的GUI库iced的合集,将介绍iced涉及的各个小部件分别介绍,最后会汇总为一个总的程序。 iced是RustGUI中比较强大的一个,目前处于发展中(即版本可能会改变),本专栏基于版本0.12.1. 概述 这是本专栏的第七篇,主要讲述图像Image部件的使用,会结合实例…...

Substrate 入门课第 14 期圆满结束,岗位内推直达知名 Web3 项目!
Substrate,一个完全免费且开源的框架,利用 Rust 语言的强大功能和安全性,为全球开发者提供了一个高效和灵活的开发环境。借助其模块化的设计,即使是新手开发者也能在短短 15 分钟内搭建起定制化的区块链。自 2020 年以来ÿ…...

深入浅出Asp.Net Core MVC应用开发系列-AspNetCore中的日志记录
ASP.NET Core 是一个跨平台的开源框架,用于在 Windows、macOS 或 Linux 上生成基于云的新式 Web 应用。 ASP.NET Core 中的日志记录 .NET 通过 ILogger API 支持高性能结构化日志记录,以帮助监视应用程序行为和诊断问题。 可以通过配置不同的记录提供程…...

基于uniapp+WebSocket实现聊天对话、消息监听、消息推送、聊天室等功能,多端兼容
基于 UniApp + WebSocket实现多端兼容的实时通讯系统,涵盖WebSocket连接建立、消息收发机制、多端兼容性配置、消息实时监听等功能,适配微信小程序、H5、Android、iOS等终端 目录 技术选型分析WebSocket协议优势UniApp跨平台特性WebSocket 基础实现连接管理消息收发连接…...
【SpringBoot】100、SpringBoot中使用自定义注解+AOP实现参数自动解密
在实际项目中,用户注册、登录、修改密码等操作,都涉及到参数传输安全问题。所以我们需要在前端对账户、密码等敏感信息加密传输,在后端接收到数据后能自动解密。 1、引入依赖 <dependency><groupId>org.springframework.boot</groupId><artifactId...

屋顶变身“发电站” ,中天合创屋面分布式光伏发电项目顺利并网!
5月28日,中天合创屋面分布式光伏发电项目顺利并网发电,该项目位于内蒙古自治区鄂尔多斯市乌审旗,项目利用中天合创聚乙烯、聚丙烯仓库屋面作为场地建设光伏电站,总装机容量为9.96MWp。 项目投运后,每年可节约标煤3670…...

从零开始打造 OpenSTLinux 6.6 Yocto 系统(基于STM32CubeMX)(九)
设备树移植 和uboot设备树修改的内容同步到kernel将设备树stm32mp157d-stm32mp157daa1-mx.dts复制到内核源码目录下 源码修改及编译 修改arch/arm/boot/dts/st/Makefile,新增设备树编译 stm32mp157f-ev1-m4-examples.dtb \stm32mp157d-stm32mp157daa1-mx.dtb修改…...

【论文阅读28】-CNN-BiLSTM-Attention-(2024)
本文把滑坡位移序列拆开、筛优质因子,再用 CNN-BiLSTM-Attention 来动态预测每个子序列,最后重构出总位移,预测效果超越传统模型。 文章目录 1 引言2 方法2.1 位移时间序列加性模型2.2 变分模态分解 (VMD) 具体步骤2.3.1 样本熵(S…...

2025季度云服务器排行榜
在全球云服务器市场,各厂商的排名和地位并非一成不变,而是由其独特的优势、战略布局和市场适应性共同决定的。以下是根据2025年市场趋势,对主要云服务器厂商在排行榜中占据重要位置的原因和优势进行深度分析: 一、全球“三巨头”…...

Docker 本地安装 mysql 数据库
Docker: Accelerated Container Application Development 下载对应操作系统版本的 docker ;并安装。 基础操作不再赘述。 打开 macOS 终端,开始 docker 安装mysql之旅 第一步 docker search mysql 》〉docker search mysql NAME DE…...

【JVM面试篇】高频八股汇总——类加载和类加载器
目录 1. 讲一下类加载过程? 2. Java创建对象的过程? 3. 对象的生命周期? 4. 类加载器有哪些? 5. 双亲委派模型的作用(好处)? 6. 讲一下类的加载和双亲委派原则? 7. 双亲委派模…...

PHP 8.5 即将发布:管道操作符、强力调试
前不久,PHP宣布了即将在 2025 年 11 月 20 日 正式发布的 PHP 8.5!作为 PHP 语言的又一次重要迭代,PHP 8.5 承诺带来一系列旨在提升代码可读性、健壮性以及开发者效率的改进。而更令人兴奋的是,借助强大的本地开发环境 ServBay&am…...