现在大多数网站使用都是数据库mysql或者mariadb数据库，因为最直接的原因就是他是开源的的，不需要支付二外的费用，并且性能也很不错，这也是mysql和mariadb如此受人们喜欢的原因。那么我们在进行的数据库的备份的时候，如何正确的备份数据库呢？特别是生产环境，因为网站正在访问的时候，不能关闭网站备份吧。
于是我们就使用mysqldump命令进行压缩备份mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz 或者不压缩mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql.gz但是在备份的过程中我们可以看到cpu的负载极具上升，并且网站打开非常慢甚至打不开，浏览器一直转圈。
这是我们就要想办法了，经过多次百度查询最终找到解决办法：如下进行热备份才是最靠谱的，这样网站又可以访问又不影响备份。在执行下面的解决办法之前我们首先要看看数据库时候支持热备份吗？
首先我们要查看数据库表的存储引擎，如果是Engine可以进行，还好我的数据库是这个引擎。
| 1 | SHOW TABLE STATUS |
第二步若果是Engine引擎执行下面的命令进行备份
| 1 | mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql |
--single-transaction 生成一个检查点，该检查点允许备份在接收传入更改时捕获检查点之前的所有数据。这些传入的更改不会成为备份的一部分。这确保了所有表都具有相同的时间点。
--routines 备份所有存储过程和存储函数。
--triggers 备份具有它们的每个表的所有触发器。
下面我们要了解下数据有几种备份类型。
数据库备份类型
| 备份类型 | 描述 |
| 逻辑备份 | 这种类型的备份是通过使用create database、create table和insert等SQL语句保存表示逻辑数据库结构的信息来创建的。当您想要从一个MySQL版本升级到另一个版本时，这种类型的备份是理想的，但是它是一种较慢的备份方法。 |
| 物理备份 | 这种类型的备份是对实际数据库文件或磁盘分区的备份，这种类型的备份可以非常快地进行备份和恢复。 |
| 完整备份 | 完整备份是包含数据库中所有内容的独立备份，然后可以在另一台服务器上恢复。完整备份可以是逻辑备份，也可以是物理备份。 |
| 增量备份 | 这种类型的备份只包含上次备份更改后的数据。这种类型的备份的优点是速度更快，因为没有太多的数据要备份，但是缺点是需要更长的时间来恢复。 |
| 固定点备份 | 这是在确切时刻的备份，通常您关闭数据库（或静止模式）然后进行备份。 |
| 热备份 | 在数据库运行时采用这种类型的备份，在备份期间不会阻止读取和写入 |
| 温备份 | 这种类型的备份是在数据库运行时进行的，但是不会阻止读取，但禁止写入对数据库进行任何修改。 |
| 冷备份 | 类似于固定点的备份，因为数据库在备份开始之前关闭 |
关于备份的类型我们讨论已经足够多了，让我们来看看如何进行备份，MySQL可以使用很多备份工具，见下表
Backup tools for MySQL | |||||
| Backup method | Storage engine | Impact | Backup speed | Recovery speed | Recovery granularity |
| mysqldump | ALL | WARM | MEDUIM | SLOWEST | MOST FLEXIBLE |
| mysqldump | INNODB | HOT | MEDUIM | SLOWEST | MOST FLEXIBLE |
| select into outfile | ALL | WARM | SLOW | SLOW | MOST FLEXIBLE |
| mk-parallel-backup | ALL | WARM | MEDUIM | MEDUIM | FLEXIBLE |
| ibbackup | INNODB | HOT | FAST | FAST | FLEXIBLE |
| ibbackup | ALL | WARM | FAST | FAST | FLEXIBLE |
| backup command in mysqld | ALL | HOT | FAST | FAST | FLEXIBLE |
| filesystem (copy files) | ALL | COLD | FASTEST | FASTEST | NOT FLEXIBLE |
| snapshot (using LVM, ZFS, VMWare) | ALL | ALMOST HOT | FAST | FAST | LEAST FLEXIBLE |
| mysqlhotcopy | MyISAM | MOSTLY COLD | FAST | FAST | FLEXIBLE |
mysqldump命令备份数据库
mysqldump命令已经存在很长时间了，它提供了对整个数据库、单个数据库、单个表甚至数据子集的逻辑备份，使用-where选项，它通常被称为数据转储。输出是ascii格式的，这意味着您可以在vi或记事本中打开它，并根据需要更改包含的内容。我不打算详细介绍mysqldump命令的所有选项，而是向您展示一些示例
mysqldump 备份所有的数据库
| 1 2 | ## backup all databases mysqldump --user=root --password --all-databases > backup_<date>_all.sql |
例如：mysqldump --user=root --password --all-databases > backup_2019.08.25_all.sql
mysqldump 备份特定的数据库
| 1 2 | ## backup a specific database mysqldump --user=root --password <database_name> > backup_<date>_<database_name>.sql |
例如：mysqldump --user=root --password sky8gdatabase > backup_2019.08.25_sky8gdatabase .sql
mysqldump 备份数据库中一个表数据
| 1 2 | ## backup a table from a database mysqldump --user=root --password <database_name> <table_name> > backup_<date>_<database_name>_<table_name>.sql |
例如：mysqldump --user=root --password sky8gdatabase wp-user > backup_2019.08.25_sky8gdatabase_wp-user.sql
mysqldump 备份数据库中特定的数据
| 1 2 | ## backup some specific data mysqldump --user=root --password <database_name> <table_name> --where "last_name='VALLE' order by first_name" > backup_<date>.sql |
例如：mysqldump --user=root --password sky8gdatabase wp-user --where "last_name='VALLE' order by first_name" > backup_2019.08.25.sql
mysqldump 一个数据库备份到另一个数据库
| 1 2 | ## dumping from one database to another mysqldump --databases <database_name> | mysql -h <destination_host> <database_name> |
mysqldump压缩与未压缩备份
备份压缩可以节省存储空间和网络流量，从而将备份数据传输到其他服务器上。压缩确实增加了一些CPU开销，但开销是依赖于算法的，并且对于MySQL Enterprise Backup使用的默认算法来说相当低。此外，压缩通常会大大减少IO开销，这可能会缩短恢复时间，尤其是对于速度较慢的IO设备。但是，在还原过程中，您需要时间进行解压缩，同时还需要压缩和解压缩数据的存储空间。因此，在考虑是否创建压缩备份时，请考虑额外的存储空间以及还原期间所需的额外时间。
将备份数据流式传输到另一台服务器时，您可能希望在原始服务器或目标服务器上压缩备份，具体取决于哪个服务器具有更多的备用CPU容量以及压缩可以节省多少网络流量。
mysqldump 不使用压缩备份
| 1 | mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql.gz |
mysqldump 使用压缩备份
| 1 | mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz |
mysqldump命令导入(恢复)数据库
mysqldump 恢复所有的数据库
| 1 2 | ## all databases mysql --user=root --password < backup.sql |
mysqldump 恢复特定的数据库
| 1 2 | ## specific database mysql --user=<user> --password <database_name> < backup_<dataabse_name>.sql |
mysqldump的定时计划任务
1.创建MySQL备份脚本
现在，将以下内容复制到脚本文件中(例如:/backup/mysql-backup.sh)，并保存在Linux系统上。使用此链接下载脚本。然后根据您的环境在脚本的“Update below values”部分更改一些配置值。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | #!/bin/bash export PATH=/bin:/usr/bin:/usr/local/bin TODAY=`date +"%d%b%Y"` ################################################################ ################## Update below values ######################## DB_BACKUP_PATH='/backup/dbbackup' MYSQL_HOST='localhost' MYSQL_PORT='3306' MYSQL_USER='root' MYSQL_PASSWORD='mysecret' DATABASE_NAME='mydb' BACKUP_RETAIN_DAYS=30 ## Number of days to keep local backup copy ################################################################# mkdir -p ${DB_BACKUP_PATH}/${TODAY} echo "Backup started for database - ${DATABASE_NAME}" mysqldump -h ${MYSQL_HOST} \ -P ${MYSQL_PORT} \ -u ${MYSQL_USER} \ -p${MYSQL_PASSWORD} \ ${DATABASE_NAME} | gzip > ${DB_BACKUP_PATH}/${TODAY}/${DATABASE_NAME}-${TODAY}.sql.gz if [ $? -eq 0 ]; then echo "Database backup successfully completed" else echo "Error found during backup" exit 1 fi ##### Remove backups older than {BACKUP_RETAIN_DAYS} days ##### DBDELDATE=`date +"%d%b%Y" --date="${BACKUP_RETAIN_DAYS} days ago"` if [ ! -z ${DB_BACKUP_PATH} ]; then cd ${DB_BACKUP_PATH} if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then rm -rf ${DBDELDATE} fi fi ### End of script #### |
2.创建或下载脚本后，请确保将执行权限设置为正常运行。
| 1 | chmod +x /backup/mysql-backup.sh |
3.在Crontab中调用脚本
现在将crontab中的脚本安排为每日运行，并定期完成备份。使用crontab -e命令在系统上编辑crontab。添加以下设置以在凌晨2点启用备份。
| 1 | 0 2 * * * root /backup/mysql-backup.sh |
保存crontab文件。启用cron后，脚本将自动进行备份，但要确保每周或每月进行一次检查备份。