数据备份
备份方式有很多:
mysql自带的备份语句
第三方的备份工具
可视化的图形软件
使用命令进行备份
使用mysqldump命令备份
mysqldump备份原理:查出表的结构转化为create语句,表中的记录转化为insert语句,还原的时候直接运行create语句创建表,运行insert语句还原数据
Usage:直接在服务器终端而非mysql终端执行
(备份一个数据库)
mysqldump -u username -p dbname1 dbname2 >BackupName.sql
(备份多个数据库)
mysqldump -u username -p --databases dbname1 dbname2 >BackupName.sql #--databases:备份多个数据库或表必须加上此参数
(备份所有数据库)
mysqldump -u username -p --all-databases >BackupName.sql
示例:
mysqldump -uroot -p db_admin cards > ~/cards.sql #备份db_admin库的cards表
mysqldump -uroot -p --databases db_admin db_admin1 > ~/db_admin.sql
mysqldump -u root -p --all-databases > ~/mysql.sql
直接复制整个数据库目录
注意还原是要使用相同的版本和存储引擎,否则存储文件类型可能会不同。
查看数据存储目录:(配置文件中也能查看)
show variables like 'datadir%';
直接备份对应名字的数据库目录和表文件
数据恢复
使用mysql命令还原
Usage:
mysql -uroot -p [dbname] <backup.sql
示例:
mysql -uroot -p < db_admin.sql #如果db_admin.sql有创建数据库这个语句就不能指定导入的数据库名
直接复制到数据库目录:只适用于MyISAM存储引擎
数据库迁移
MySQL数据库之间的迁移
1.复制数据库目录,只适用于MyISAM存储引擎
2.使用命令备份和还原数据库
mysqldump -h host1 -u root --password=password1 --all-databases | mysql -h host2 -u root -password=password2
不同数据库之间的迁移
Oracle数据库与MySQL数据库之间迁移可以把.sql文件中的语句进行简单的的修改
MySQL数据库与SQL Server数据库可以通过MyODBC引擎进行迁移
表的导出和导入
补充知识:
secure_file_priv:用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。
secure_file_priv 为 NULL (默认值)时,表示限制mysqld不允许导入或导出。
secure_file_priv 为 /tmp 时,表示限制mysqld只能在/tmp目录中执行导入导出,其他目录不能执行。
secure_file_priv 没有值时,表示不限制mysqld在任意目录的导入导出。
查看 secure_file_priv 的值:
show global variables like '%secure_file_priv%';
设置secure_file_priv:
因为 secure_file_priv 参数是只读参数,不能使用set global命令修改。
打开my.cnf 或 my.ini,加入以下语句后重启mysql
secure_file_priv=''
用SELECT ...INTO OUTFILE导出文本文件
Usage:
SELECT[列名] FROM table[WHERE语句] INTO OUTFILE '目标文件' [OPTION];
#[OPTION]:
FIELDS TERMINATED BY‘字符串’ #设置字符串为字符的分隔符,默认为/t
FIELDS ENCLOSED BY‘字符’ #用于设置字符来扩上字段的值,默认不使用任何字符
FIELDS OPTIOINALLY ENCLOSED BY‘字符’ #设置字符串来扩上char/varchar/text等字符型字段,默认不使用任何字符
FIELDS ESCAPED BY‘字符’ #设置字符串转义,默认为“/”
LINES STARTING BY‘字符串’ #设置每行开头的字符,默认不使用任何字符
LINES TERMINATED BY‘字符串’ #设置每行的结束符,默认为/n
示例:
select * from tb_bookinfo into outfile '/root/info.txt'
用mysqldump命令导出文本文件
Usage:
mysqldump -u root -pPassword -T "目标目录" dbname table [option]; #[option]选项同上
示例:
mysqldump -uroot -p1234 -T /root/ db_admin tb_bookinfo
用mysql命令导出文本文件
Usage:
mysql -u root -pPassword -e"SELECT 语句" dbname >D:/name.txt
mysql -u root -pPassword --xml|-X -e"SELECT 语句" dbname >D:/filename.xml
用LOAD DATA INFILE命令将文本文件导入到数据表
Usage:
LOAD DATA [LOW_PRIORITY|CONCURRENT] [LOCAL] INFILE file_name INTO TABLE table_name [OPTION];
补充:ignore 1 lines #设置忽略第1行
示例:
load data infile '/root/bookcase.txt’ into table tb_bookcase fields terminated by ' ' lines terminated by '\r\n' ignore 1 lines
用mysqlimport命令导入文本文件
Usage:服务器终端输入此命令
mysqlimport –u root –p database_name file_name [option];
#file_name:注意file_name的名字要与database中的表明一致
示例
mysqlimport -u root -p db_database18 "/root/tb_bookcase.txt" "--lines-terminated-by=\r\n" "-fields-terminated-by= "
18 MySQL 从入门到精通——备份与恢复
于 2023-03-11 14:43:16 首次发布