十一月新闻大事件摘抄,seo搜索引擎优化推广,wordpress加入夜间模式,18款禁用网站app直播一、逻辑备份 逻辑备份#xff1a;备份的是建表#xff0c;建库#xff0c;插入数据等操作所执行SQL语句#xff0c;适用于中小型数据库#xff0c;效率相对较低#xff0c;提供三种级别的备份#xff0c;表级#xff0c;库级和全库级。 本质#xff1a;导出的是SQL语…一、逻辑备份 逻辑备份备份的是建表建库插入数据等操作所执行SQL语句适用于中小型数据库效率相对较低提供三种级别的备份表级库级和全库级。 本质导出的是SQL语句 优点不论是什么存储引擎都可以用mysqldump备份成SQL语句 缺点速度较慢导出时可能会出现格式不兼容的突发状况无法做增量备份和累计增量备份 数据一致服务可用:如何保证数据一致在备份的时候进行锁表会自动锁表。锁住之后在备份。 二、逻辑备份
1、备份全部数据库 语法mysqldump -u指定用户 -p指定密码 -A 文件名 mysqldump -u指定用户 -p指定密码 --all-databases 文件名 [rootlocalhost ~]# mysqldump -uroot -p123 -A all.mysqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[rootlocalhost ~]# mysqldump -uroot -p123 --all-database all1.txt
mysqldump: [Warning] Using a password on the command line interface can be insecure.
2.备份部分数据库 语法mysqldump -u指定用户 -p密码 -B 数据库名1 数据库名2 文件名 mysqldump -u指定用户 -p密码 --databases 数据库名1 数据库名2 文件名 [rootlocalhost ~]# mysqldump -uroot -p123 -B db1 db1.txt
mysqldump: [Warning] Using a password on the command line interface can be insecure.[rootlocalhost ~]# mysqldump -uroot -p123 --databases db1 school db2.txt
mysqldump: [Warning] Using a password on the command line interface can be insecure.3.备份表 语法mysqldump -u指定用户 -p指定密码 数据库名 表名 文件名 [rootlocalhost ~]# mysqldump -uroot -p123 db1 employee employee.txt
mysqldump: [Warning] Using a password on the command line interface can be insecure.
4.备份表结构 语法mysqldump -u指定用户 -p指定密码 -d 数据库名 表名 [rootlocalhost ~]# mysqldump -uroot -p123 -d db1 employee jiegou.txt
mysqldump: [Warning] Using a password on the command line interface can be insecure.
5.备份表数据 语法select * from 表.库 into outfile/var/lib/mysql-files/文件名 mysql show variables like secure%; #查看默认导出路径
-----------------------------------------
| Variable_name | Value |
-----------------------------------------
| secure_auth | ON |
| secure_file_priv | /var/lib/mysql-files/ |
-----------------------------------------
2 rows in set (0.00 sec)mysql select * from mysql.user into outfile /var/lib/mysql-files/b.xfs;
Query OK, 6 rows affected (0.00 sec)
扩展修改默认的导出路径
[rootlocalhost opt]# mkdir backup #创建默认目录
[rootlocalhost opt]# chown -R mysql.mysql /opt/backup#修改目录的属主和属组
[rootlocalhost opt]# vim /etc/my.cnf #修改配置文件
secure_file_priv/opt/backup
[rootlocalhost opt]# systemctl restart mysqld #重启mysql[rootlocalhost opt]# mysql -p123 #进入mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.43 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 show variables like secure%; #再次查看文件的默认路径
--------------------------------
| Variable_name | Value |
--------------------------------
| secure_auth | ON |
| secure_file_priv | /opt/backup/ |
--------------------------------
2 rows in set (0.01 sec)
6.恢复
1命令行恢复数据库 a)命令行恢复 语法mysql -u用户名 -p密码 之前备份的文件 mysql drop database db1; #先删除数据库db1
Query OK, 15 rows affected (0.07 sec)
mysql show databases; #查看数据库
--------------------
| Database |
--------------------
| information_schema |
| db3 |
| mysql |
| performance_schema |
| school |
| sys |
--------------------
6 rows in set (0.00 sec)[rootlocalhost ~]# mysql -uroot -p123 db1.txt #恢复数据库
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql show databases; #再次查看数据库
--------------------
| Database |
--------------------
| information_schema |
| db1 |
| db3 |
| mysql |
| performance_schema |
| school |
| sys |
--------------------
7 rows in set (0.00 sec) b)数据库里面恢复 语法source 备份数据库的路径 mysql drop database db1; #删除数据库db1
Query OK, 15 rows affected (0.05 sec)mysql source /root/db1.txt #恢复数据库db1
Query OK, 0 rows affected (0.00 sec).
.Query OK, 0 rows affected (0.00 sec)mysql show databases; #查看数据库
--------------------
| Database |
--------------------
| information_schema |
| db1 |
| db3 |
| mysql |
| performance_schema |
| school |
| sys |
--------------------
7 rows in set (0.00 sec)
2恢复表
a)在命令行恢复 语法mysql -u用户 -p密码 表所在的数据库 备份的文件 [rootlocalhost ~]# mysql -uroot -p123 db1 employee.txt
mysql: [Warning] Using a password on the command line interface can be insecure.
b)在数据库里面恢复 语法source 备份的路径 mysql source /root/employee.txt
Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)3恢复表结构 语法mysql -u用户 -p密码 -D 数据库名 备份的文件 [rootlocalhost ~]# mysql -uroot -p123 -D db1 jiegou.txt
mysql: [Warning] Using a password on the command line interface can be insecure.mysql desc employee;
--------------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------------------------------
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum(man,woman) | YES | | man | |
| hire_date | date | YES | | NULL | |
| post | varchar(20) | YES | | NULL | |
| job_description | varchar(100) | YES | | NULL | |
| salary | double(15,2) | NO | | NULL | |
| office | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
--------------------------------------------------------------------------
9 rows in set (0.00 sec)mysql select * from employee;
Empty set (0.00 sec)4恢复表中数据
mysql truncate employee; #清空表中数据
Query OK, 0 rows affected (0.02 sec)mysql select * from employee; #查看表中数据
Empty set (0.00 sec)mysql load data infile/opt/backup/a.txtinto table employee;
Query OK, 15 rows affected (0.01 sec)
Records: 15 Deleted: 0 Skipped: 0 Warnings: 0mysql select * from employee;
-----------------------------------------------------------------------------------------
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
-----------------------------------------------------------------------------------------
| 1 | qiancheng | man | 2018-03-14 | hr | talk | 7000.00 | 501 | 102 |
| 20 | tom | man | 2017-09-15 | instructor | teach | 8000.00 | 501 | 100 |
| 21 | alince | woman | 2013-04-28 | instructor | teach | 5500.00 | 501 | 100 |
| 22 | robin | man | 2020-09-18 | instructor | teach | 7200.00 | 501 | 100 |
| 23 | zhuzhu | man | 2016-12-09 | hr | hrcc | 6000.00 | 502 | 101 |
| 24 | gougou | woman | 2015-04-27 | hr | NULL | 6000.00 | 502 | 101 |
| 30 | maomao | man | 2019-08-12 | sale | talk | 20000.00 | 503 | 102 |
| 31 | yiyi | man | 2015-06-17 | talk | NULL | 8000.00 | NULL | NULL |
| 40 | harry | woman | 2018-02-05 | hr | hrcc | 6900.00 | 502 | 102 |
| 41 | tianyuan | man | 2018-02-05 | null | salecc | 9700.00 | 501 | 102 |
| 42 | xiaoyi | man | 2018-02-05 | null | salecc | 5700.00 | 501 | 102 |
| 50 | zxvb | man | 2019-04-23 | hr | NULL | 8000.00 | NULL | NULL |
| 51 | ab | man | NULL | NULL | NULL | 6500.00 | NULL | NULL |
| 52 | cd | man | NULL | NULL | NULL | 7600.00 | NULL | NULL |
| 53 | ef | man | NULL | NULL | NULL | 8900.00 | NULL | NULL |
-----------------------------------------------------------------------------------------
15 rows in set (0.00 sec)
