mysql 备份数据库

mysql 备份数据库

 

数据库备份方式：

1. Navicate 转储sql文件，运行sql文件

2. Navicate 转储sql文件，source 还原

3. mysqldump 备份还原

 

mysqldump 整个数据库备份还原

注意先开启binlog日志，参考：https://blog.51cto.com/u_15127597/4083496

1. 打开目录：mysql安装目录下的bin目录

执行mysqldump备份命令，注意 > 前后的空格，语句最后不可加 ；

mysqldump -u root -p testplatform >c:\DatabaseBackup\backdbtestplatform.sql 

mysqldump -u root -p --default-character-set=utf8 testplatform >c:\DatabaseBackup\backdbtestplatform.sql 

 

 

 

 2. 执行完成后，可备份目录中查看到sql文件。

 

 3. 还原数据库， 在mysql安装目录下的bin目录下执行， 数据库名：testplatform 

D:\tools\mysql-8.0.25-winx64\bin>mysql -uroot -p testplatform <d:\tools\backdbtestplatform.sql

　　

也可通过source命令还原，用source时，注意先 use 数据库名；

 

导入时出现"ERROR at line 206: Unknown command '\''."的错误提示，然后中断。

查了一下，说是字符集的问题，加上  --default-character-set=utf8，也就是用命令导入成功

mysql -u root -p123456 --default-character-set=utf8 db1 <c:\db_bak.sql  

 

 

 

 

mysql 单表导入导出参考：https://www.jb51.net/article/199361.htm

 

 mysql安装目录下的bin目录下，cmd命令中执行：

 

1. 整库备份，库名：testplatform 

mysqldump -u root -p --default-character-set=utf8 testplatform >c:\DatabaseBackup\backdbtestplatform.sql 

 

2.  整库还原：库名：testplatform 

mysql -uroot -p testplatform <d:\tools\backdbtestplatform.sql

 

 

 

1. 导出单张表： 库名：apaper，表名：tb_code

mysqldump -u root -p  --default-character-set=utf8  apaper tb_code > F:\temp\tb_code.sql

 导出的sql文件内容为：先删除表，再创建表结构，再Insert数据。

 

2. 还原单张表： 库名：apaper，表名：tb_code

mysql -u root -p  --default-character-set=utf8  apaper < F:\temp\tb_code.sql

 

 

 

1. 导出整个库数据结构： 库名：apaper

mysqldump -u root -p -d --add-drop-table --default-character-set=utf8 apaper > F:\temp\apaperstruct.sql

 

说明 ：-d ：没有数据， --add-drop-table： 在每个create语句之前增加一个drop table

 

2. 还原数据库结构：

mysql -uroot -p apaper < F:\temp\apaperstruct.sql

 

 

1. 单表导出部分数据：

mysqldump -u root -p  --default-character-set=utf8 -t apaper tb_codearr --where="id > 4"  > F:\temp\tb_codearr.sql

 -t：只导出数据：insert语句，不导出表结构，不删表不创表

 

 

source 命令还原：

mysql -u root -p

mysql>use 数据库

然后使用source命令，后面参数为脚本文件(如这里用到的.sql) 

注意：文件路径中不允许用中文

mysql>source d:\dbname.sql

  

 

 备份样例

按库备份

mysqldump -u root -p --default-character-set=utf8 testplatform > F:\tempData\testplatform.sql 
root

 

按表备份 

mysqldump -u root -p --default-character-set=utf8 testplatform appliancations > F:\tempData\appliancations.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform attrs > F:\tempData\attrs.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform colorstlyes > F:\tempData\colorstlyes.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform constructioncoordinatesystems > F:\tempData\constructioncoordinatesystems.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform edges > F:\tempData\edges.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform entityinstances > F:\tempData\entityinstances.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform entitys > F:\tempData\entitys.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform filegroups > F:\tempData\filegroups.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform files > F:\tempData\files.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform formdatas > F:\tempData\formdatas.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform gisinstances > F:\tempData\gisinstances.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform nodeinstnce > F:\tempData\nodeinstnce.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform nodes > F:\tempData\nodes.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform occurences > F:\tempData\occurences.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform operations > F:\tempData\operations.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform projects > F:\tempData\projects.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform properties > F:\tempData\properties.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform propertyset > F:\tempData\propertyset.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform sences > F:\tempData\sences.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform senceshapegeometry > F:\tempData\senceshapegeometry.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform shapegeometries > F:\tempData\shapegeometries.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform shapeinstances > F:\tempData\shapeinstances.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform styles > F:\tempData\styles.sql
root

mysqldump -u root -p --default-character-set=utf8 testplatform users > F:\tempData\users.sql
root

 

增量备份

mysqldump -u root -proot --default-character-set=utf8 -t testplatform appliancations --where="true " > D:\20230131\liuxinyu机器备份\databack-add\appliancations.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform attrs --where="true " > D:\20230131\liuxinyu机器备份\databack-add\attrs.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform colorstlyes --where="true " > D:\20230131\liuxinyu机器备份\databack-add\colorstlyes.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform constructioncoordinatesystems --where="true " > D:\20230131\liuxinyu机器备份\databack-add\constructioncoordinatesystems.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform edges --where="true " > D:\20230131\liuxinyu机器备份\databack-add\edges.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform entityinstances --where="true " > D:\20230131\liuxinyu机器备份\databack-add\entityinstances.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform entitys --where="true " > D:\20230131\liuxinyu机器备份\databack-add\entitys.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform filegroups --where="true " > D:\20230131\liuxinyu机器备份\databack-add\filegroups.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform files --where="true " > D:\20230131\liuxinyu机器备份\databack-add\files.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform formdatas --where="true " > D:\20230131\liuxinyu机器备份\databack-add\formdatas.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform gisinstances --where="true " > D:\20230131\liuxinyu机器备份\databack-add\gisinstances.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform nodeinstnce --where="true " > D:\20230131\liuxinyu机器备份\databack-add\nodeinstnce.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform nodes --where="true " > D:\20230131\liuxinyu机器备份\databack-add\nodes.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform occurences --where="true " > D:\20230131\liuxinyu机器备份\databack-add\occurences.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform operations --where="true " > D:\20230131\liuxinyu机器备份\databack-add\operations.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform projects --where="true " > D:\20230131\liuxinyu机器备份\databack-add\projects.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform properties --where="true " > D:\20230131\liuxinyu机器备份\databack-add\properties.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform propertyset --where="true " > D:\20230131\liuxinyu机器备份\databack-add\propertyset.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform sences --where="true " > D:\20230131\liuxinyu机器备份\databack-add\sences.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform senceshapegeometry --where="true " > D:\20230131\liuxinyu机器备份\databack-add\senceshapegeometry.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform shapegeometries --where="true " > D:\20230131\liuxinyu机器备份\databack-add\shapegeometries.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform shapeinstances --where="true " > D:\20230131\liuxinyu机器备份\databack-add\shapeinstances.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform styles --where="true " > D:\20230131\liuxinyu机器备份\databack-add\styles.sql

mysqldump -u root -proot --default-character-set=utf8 -t testplatform users --where="true " > D:\20230131\liuxinyu机器备份\databack-add\users.sql

 

表结构备份

mysqldump -u root -p -d --add-drop-table --default-character-set=utf8 testplatform > F:\tempData\testplatform.sql

 

按库还原

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\testplatformb.sql 
Pass&Null221

 

按表还原 

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\appliancations.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\attrs.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\colorstlyes.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\constructioncoordinatesystems.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\edges.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\entityinstances.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\entitys.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\filegroups.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\files.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\formdatas.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\gisinstances.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\nodeinstnce.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\nodes.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\occurences.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\operations.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\projects.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\properties.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\propertyset.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\sences.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\senceshapegeometry.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\shapegeometries.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\shapeinstances.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\styles.sql
Pass&Null221

mysql -u root -p  --default-character-set=utf8  testplatformb < C:\0Database\tabs\0Database\users.sql
Pass&Null221

 

 　　

 

 

MYSQL数据库自动备份 Bat 文件 

 创建 bat 文件，使用ANSI格式

@echo off
:start

# 以时间为维度作为备份文件名的后缀
set dt=%time:~0,2%
if %dt% lss 10 set dt=%dt: =0%

set file_name=%date:~0,4%%date:~5,2%%date:~8,2%%dt%%time:~3,2%

# 在这里设置Mysql的安装路径；
set bin_path="C:\Program Files\MySQL\MySQL Server 8.0\bin\"

# 这里设置备份的存储路径
set save_path="D:\DataBaseBack"

md %save_path%\back_folder

set save_path_sql="D:\DataBaseBack\back_folder"

# 这里设置压缩软件的安装路径
set zip_path="C:\Program Files (x86)\7-Zip\7z.exe"

echo 开始备份，运行时间会有点长，请耐等待 
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform appliancations                     > %save_path_sql%\appliancations.sql  
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform attrs                            > %save_path_sql%\attrs.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform colorstlyes                      > %save_path_sql%\colorstlyes.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform constructioncoordinatesystems    > %save_path_sql%\constructioncoordinatesystems.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform edges                            > %save_path_sql%\edges.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform entityinstances                  > %save_path_sql%\entityinstances.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform entitys                          > %save_path_sql%\entitys.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform filegroups                       > %save_path_sql%\filegroups.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform files                            > %save_path_sql%\files.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform formdatas                        > %save_path_sql%\formdatas.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform gisinstances                     > %save_path_sql%\gisinstances.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform nodeinstnce                      > %save_path_sql%\nodeinstnce.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform nodes                            > %save_path_sql%\nodes.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform occurences                       > %save_path_sql%\occurences.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform operations                       > %save_path_sql%\operations.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform projects                         > %save_path_sql%\projects.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform properties                       > %save_path_sql%\properties.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform propertyset                      > %save_path_sql%\propertyset.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform sences                           > %save_path_sql%\sences.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform senceshapegeometry               > %save_path_sql%\senceshapegeometry.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform shapegeometries                  > %save_path_sql%\shapegeometries.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform shapeinstances                   > %save_path_sql%\shapeinstances.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform styles                           > %save_path_sql%\styles.sql
%bin_path%mysqldump.exe -uroot -proot --default-character-set=utf8 testplatform users                            > %save_path_sql%\users.sql


echo 开始压缩，运行时间会有点长，请耐等待
# %zip_path% a -tzip -p123  %save_path%\testplatform_%file_name%.zip %save_path_sql%
%zip_path% a -tzip  %save_path%\testplatform_%file_name%.zip %save_path_sql%

echo 开始清理sql格式备份
echo 清理SQL格式备份文件，只保留压缩包
rd /s /q %save_path_sql%

echo 删除60天前的zip文件
forfiles /p D:\DataBaseBack\ /d -60 /m "*.zip" /c "cmd /c del @file"

 

 

 

Linux自动备份 mysql 数据库

#!/bin/bash

#容器ID 
container_id="a8180c8eaf24"
#登录用户名
mysql_user="root"
#登录密码(注意 如果密码包含特殊符号 前面要用'\')
mysql_password="root221"
mysql_port="3306"
#备份的数据库名
mysql_database1="mate"
mysql_database2="platform"
mysql_database3="railassist"
mysql_database4="railcde"
mysql_database5="testplatform"
mysql_database6="matomo"

# 备份文件存放地址(根据实际情况填写)
backup_dir="/filestorage/z-publish/external/DBBack/data"

backup_time=`date +%Y%m%d%H%M`

# 备份指定数据库中数据
docker exec  $container_id mysqldump  -P$mysql_port -u$mysql_user -p$mysql_password   --default-character-set=utf8  $mysql_database1 > $backup_dir/bak-$mysql_database1-$backup_time.sql
 
docker exec  $container_id mysqldump  -P$mysql_port -u$mysql_user -p$mysql_password   --default-character-set=utf8  $mysql_database2 > $backup_dir/bak-$mysql_database2-$backup_time.sql

docker exec  $container_id mysqldump  -P$mysql_port -u$mysql_user -p$mysql_password   --default-character-set=utf8  $mysql_database3 > $backup_dir/bak-$mysql_database3-$backup_time.sql

docker exec  $container_id mysqldump  -P$mysql_port -u$mysql_user -p$mysql_password   --default-character-set=utf8  $mysql_database4 > $backup_dir/bak-$mysql_database4-$backup_time.sql

docker exec  $container_id mysqldump  -P$mysql_port -u$mysql_user -p$mysql_password   --default-character-set=utf8  $mysql_database5 > $backup_dir/bak-$mysql_database5-$backup_time.sql

docker exec  $container_id mysqldump  -P$mysql_port -u$mysql_user -p$mysql_password   --default-character-set=utf8  $mysql_database6 > $backup_dir/bak-$mysql_database6-$backup_time.sql



# 删除15天前的备份
find /filestorage/z-publish/external/DBBack/data -type f -mtime +15 | xargs rm -f
 
echo "over"

 

 

 

只备份存储过程、函数和视图

如果你只想备份存储过程、函数和视图，而不包括表数据，你可以使用 --no-data 选项来避免备份表数据，同时确保包含例行程序和视图：

 

 

mysqldump -u [username] -p[password] --no-data [database_name] --routines --no-create-info > [backup_routines_views.sql]

• --no-data：不备份表数据。

• --routines：包含存储过程和函数。

• --no-create-info：不包括表的创建信息，只包括存储过程、函数和视图。

然而，这个命令不会单独备份视图，因为视图通常与表一起被备份。如果你想要一个更细粒度的控制，可能需要手动编写脚本来提取特定的对象。

单独备份视图

MySQL 并没有提供一个直接只备份视图的 mysqldump 选项。但是，你可以通过查询 INFORMATION_SCHEMA 来获取视图的定义，并将它们导出到一个文件中。例如：

 

 

SELECT CONCAT('CREATE OR REPLACE VIEW ', table_name, ' AS ', view_definition, ';')

FROM INFORMATION_SCHEMA.VIEWS

WHERE table_schema = '[database_name]';

将上述 SQL 查询的结果复制到一个文件中，即可得到所有视图的创建语句。

 

 

 

 

 

 

 

 

 

引用：https://blog.csdn.net/Alen_Liu_SZ/article/details/122152872

引用：https://blog.csdn.net/weixin_39558317/article/details/113110078