Linux——mysql运维篇

[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