mysql-mysqldump逻辑备份

本文主要是介绍mysql-mysqldump逻辑备份，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

mysqldump逻辑备份---- 推荐优先使用(重点)

mysqldump 是 MySQL 自带的逻辑备份工具。可以保证数据的一致性和服务的可用性。

逻辑备份不占空间比较快捷不需要停服务可以正常导入导出

cd /usr/local/mysql/bin

可以查看 mysqlpump

产生测试库与表
测试表:company.employee5
mysql> create database company;
mysql> CREATE TABLE company.employee5(id int primary key AUTO_INCREMENT not null,name varchar(30) not null,sex enum('male','female') default 'male' not null,hire_date date not null,post varchar(50) not null,job_description varchar(100),salary double(15,2) not null,office int,dep_id int);
mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values ('jack','male','20180202','instructor','teach',5000,501,100),('tom','male','20180203','instructor','teach',5500,501,100),('robin','male','20180202','instructor','teach',8000,501,100),('alice','female','20180202','instructor','teach',7200,501,100),('tianyun','male','20180202','hr','hrcc',600,502,101),('harry','male','20180202','hr',NULL,6000,502,101),('emma','female','20180206','sale','salecc',20000,503,102),('christine','female','20180205','sale','salecc',2200,503,102),('zhuzhu','male','20180205','sale',NULL,2200,503,102),('gougou','male','20180205','sale','',2200,503,102);

如何保证数据一致?在备份的时候进行锁表会自动锁表。锁住之后在备份。

本身为客户端工具:
远程备份语法: # mysqldump  -h 服务器  -u用户名  -p密码   数据库名  > 备份文件.sql
本地备份语法: # mysqldump  -u用户名  -p密码   数据库名  > 备份文件.sql

1.常用备份选项

-A, --all-databases #备份所有库-B, --databases  #备份多个数据库-F, --flush-logs #备份之前刷新binlog日志--default-character-set #指定导出数据时采用何种字符集，如果数据表不是采用默认的latin1字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。--no-data，-d #不导出任何数据，只导出数据库表结构。--lock-tables #备份前，锁定所有数据库表--single-transaction #保证数据的一致性和服务的可用性-f, --force #即使在一个表导出期间得到一个SQL错误，继续。

注意

使用 mysqldump 备份数据库时避免锁表:
对一个正在运行的数据库进行备份请慎重！！ 如果一定要 在服务运行期间备份，可以选择添加 --single-transaction选项，类似执行： mysqldump --single-transaction -u root -p123456 dbname > mysql.sql

2.备份表

备份整个表结构与数据

语法: # mysqldump -u root -p'口令 库名  表名 > /db1.t1.bak
[root@mysql-server ~]# mkdir /home/back  #创建备份目录
[root@mysql-server ~]# mysqldump -uroot -p'qf123' company employee5 > /home/back/company.employee5.bak
备份多个表：
语法: # mysqldump -u root -p'口令 库名  表名1 表名2 > /db1.t1_t2.bak

3.备份库(重点)

最好1一个数据库一个文件后期管理比较轻松

mkdir /data/backupdb
cd /data/backupdb会出现一个告警
mysqldump -u root -p'123456' company > /data/backupdb/company_$(date +%Y%m%d%H%M%S).sqlmysqldump -u root -p'123456' --single-transaction company > /data/backupdb/company_$(date +%Y%m%d%H%M%S).sql

5.恢复全库

登陆mysql创建一个库
mysql> create  database qf_sh;
恢复：
mysql -uroot -p'123456' qf_sh < /data/backupdb/shuihu.sql

6.单独恢复表

恢复方式一
查看是否开启binlog日志
vim /etc/my.cnf
log-bin=/usr/local/mysql/logs/mysql-bin/mylog登录mysql
查看binlog状态是否开启，如果开启则需停止
mysql>show variables like 'sql_log_bin';
ON 表示开启#停止binlog日志
mysql> set sql_log_bin=0;  mysql>show variables like 'sql_log_bin';
此时已经为OFF 停止状态授权
chmod o+x shuihu.sql 登录mysql
show databases;
使用库
use qf_t4;
加载库
source /data/backupdb/shuihu.sql恢复方式二:(最好使用这个，比较方便)
# mysql -u root -p1  db1  < db1.t1.bak库名    备份的文件路径

7.备份及恢复表结构

备份时不包含表内数据

-d #不导出任何数据，只导出数据库表结构，方便建表会出现一个告警
mysqldump -u root -p'123456' --single-transaction -d qf_t4 userinfo >/data/backupdb/userinfo_$(date +%Y%m%d%H%M%S).sql-D 指到数据库qf_t3表 只有结构没有数据
mysql -uroot -p'123456' -D qf_t3 < /data/backupdb/userinfo_20240524173536.sql 1.备份表结构：
语法：mysqldump  -uroot -p'口令 -d database table > dump.sql
[root@mysql-server ~]# mysqldump -uroot -p'qf123' -d company employee5 > /home/back/emp.bak
恢复表结构：
登陆数据库创建一个库
mysql> create database t1;
语法：# mysql -u root -p'口令' -D 库名  < 备份文件名
[root@mysql-server ~]# mysql -uroot -p'qf123' -D t1 < /home/back/emp.bak

8.数据的导入导出,没有表结构。

表的导出和导入只备份表内记录，不会备份表结构，需要通过mysqldump备份表结构，恢复时先恢复表结构，再导入数据。
mysql> show variables like "secure_file_priv";  ----查询导入导出的目录。修改安全文件目录：
1.创建一个目录：mkdir  路径目录
[root@mysql-server ~]# mkdir /sql
2.修改权限
[root@mysql-server ~]# chown mysql:mysql /sql
3.编辑配置文件：
vim /etc/my.cnf
在[mysqld]里追加
secure_file_priv=/sql
4.重新启动mysql.
systemctl restart mysqld1.导出数据(把数据导出去 没有表结构 是个很冷门的需求)
登陆数据查看数据
mysql> show databases; 
mysql> use qf_t4;
mysql> show tables;
查询这个表
select * from userinfo\G; 
//把查询的结果写入到这个文件里sql/out-employee5.bak
mysql> select * from userinfo into outfile '/sql/userinfo.sql';然后进入sql文件夹 
cd /sql  ll 会发现有一个文件
vim userinfo.sql2.数据的导入，就是把数据导入到表结构中 (表格和数据分开放的时候就用这个方法)
先将原来表里面的数据清除掉，只保留表结构
mysql> use qf_t3;
mysql> delete from employee5;
mysql> load data infile '/sql/userinfo.sql' into table userinfo;
如果将数据导入别的表，需要创建这个表并创建相应的表结构。