(1).Mysql备份类型
1)按照备份时对数据库的影响分为
Hot backup(热备):也叫在线备份。指在数据库运行中直接备份,对正在运行的数据库没有任何影响。
Cold backup(冷备):也叫离线备份。指在数据库停止的情况下备份。
Warm backup(温备):在数据库运行时备份,会加一个全局锁以保证数据的一致性,会对当前数据库的操作有影响。
2)按照备份后的文件内容分为
逻辑备份:指备份后的文件内容是可读的,通常为文本文件,内容一般是SQL语句或表内的实际数据(mysqldump和select * into outfile),一般适用于数据库的升级和迁移,还原时间较长。
裸文件备份:也叫物理备份。拷贝数据库的物理文件,数据库既可以处于运行状态(mysqlhotcopy、ibbackup、xtrabackup一类工具),也可以处于停止状态,还原时间较短。
3)按照备份数据库的内容分为
完全备份:对数据库进行完整的备份。
增量备份:在上一次完整备份的基础上,对更新的数据进行备份(xtrabackup)
日志备份:二进制日志备份,主从同步。
(2).mysqldump
mysqldump是逻辑备份工具,支持MyISAM和InnoDB引擎。数据库运行时,MyISAM引擎只支持温备,InnoDB支持热备和温备。
1)导出数据
导出所有数据库
[root@youxi1 ~]# mysqldump -uroot -p123456 -A > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@youxi1 ~]# mysqldump -uroot -p123456 --all-databases > all2.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@youxi1 ~]# ll all.sql all2.sql
-rw-r--r-- 1 root root 773437 6月 14 13:52 all2.sql
-rw-r--r-- 1 root root 773437 6月 14 13:51 all.sql
导出指定数据库里的所有数据表
[root@youxi1 ~]# mysqldump -uroot -p123456 mysql > mysql.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@youxi1 ~]# ll mysql.sql
-rw-r--r-- 1 root root 1089455 6月 14 13:53 mysql.sql
导出指定数据库里的指定数据表
[root@youxi1 ~]# mysqldump -uroot -p123456 test_db user_tb > user.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@youxi1 ~]# ll user.sql
-rw-r--r-- 1 root root 1883 6月 14 13:54 user.sql
只导出指定数据库里的所有数据表的表结构
[root@youxi1 ~]# mysqldump -uroot -p123456 -d test_db > test_db_table.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@youxi1 ~]# ll test_db_table.sql
-rw-r--r-- 1 root root 1618 6月 14 13:56 test_db_table.sql
只导出数据库里的所有数据表的所有数据
[root@youxi1 ~]# mysqldump -uroot -p123456 -t test_db > test_db_data.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@youxi1 ~]# ll test_db_data.sql
-rw-r--r-- 1 root root 1513 6月 14 13:57 test_db_data.sql
导出指定数据库的所有内容
[root@youxi1 ~]# mysqldump -uroot -p123456 -B test_db > test_db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@youxi1 ~]# ll test_db.sql
-rw-r--r-- 1 root root 2029 6月 14 14:02 test_db.sql
注意:以上几个实例,只有-A、--all-databases和-B选项有创建数据库,并使用数据库的语句。这和后面的导入数据息息相关。
2)导入数据
如果使用-A、--all-databases和-B选项导出的数据库可以使用mysql直接导入
mysql -uroot -p123456 < all.sql
如果不是则需要进入到mysql内部,选择数据库才能导入数据;或者使用mysql时指定数据库
>use mysql
>source mysql.sql
mysql -uroot -p123456 mysql < mysql.sql
(3).mysqlhotcopy
mysql5.7已经没有这个命令了,多用于mysql5.5之前。mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库或单个表,属于裸文件备份(物理备份),只能用于MyISAM引擎的数据库。本质是使用锁表语句,然后cp或scp。
可以使用yum命令安装
yum -y install perl-DBD*;
1)备份
备份一个数据库到目标目录
mysqlhotcopy -u [用户名] -p [密码] [数据库名] [目标目录]
备份多个数据库到目标目录
mysqlhotcopy -u [用户名] -p [密码] [数据库名1 数据库名2...] [目标目录]
备份数据库中的某个表到指定目录
mysqlhotcopy -u [用户名] -p [密码] [数据库名]./[表名]/ [目标目录]
2)还原
在Linux中Mysql数据库都是以物理文件存在的,数据库对应的是目录。如果想要还原只要将原来的文件拷贝回原目录即可。
单个数据库备份还原方式:如果文件所有这和所属组不是mysql,请使用chown修改。
cp -ra [目标目录] /var/lib/mysql/[数据库名]/
(4).mysqldump和mysqlhotcopy比较
1.mysqldump 是采用SQL级别的备份机制,它将数据表导成 SQL 脚本文件,数据库大时,占用系统资源较多,支持常用的MyISAM,innodb引擎;mysqlhotcopy只是简单的缓存写入和文件复制的过程,占用资源和备份速度比mysqldump快很多很多,特别适合大的数据库,但需要注意的是:mysqlhotcopy只支持MyISAM 引擎。
2.mysqlhotcopy只能运行在数据库目录所在的机器上,mysqldump可以用在远程客户端。
3.mysqlhotcopy恢复只需要拷贝备份文件到源目录覆盖即可,mysqldump需要导入SQL文件到原来库中。
4.两者都在线执行lock tables(锁表)以及unlock tables(解锁表)。