自动或定期备份mysql数据 [英] Automated or regular backup of mysql data
问题描述
我想使用<在此处插入最喜欢的 PHP 框架> 定期备份我的 mysql 数据库中的一些表；/纯 php/我第二喜欢的语言.我希望它是自动化的，以便以后可以在出现问题时恢复备份.
I want to take regular backups of some tables in my mysql database using <insert favorite PHP framework here> / plain php / my second favorite language. I want it to be automated so that the backup can be restored later on in case something goes wrong.
我尝试执行查询并将结果保存到文件中.最终的代码看起来有点像这样.
I tried executing a query and saving the results to a file. Ended up with code that looks somewhat like this.
$sql = 'SELECT * FROM my_table ORDER id DESC';
$result = mysqli_query( $connect, $sql );
if( mysqli_num_rows( $result ) > 0){
$output=fopen('/tmp/dumpfile.csv','w+');
/* loop through recordset and add that to the file */
while( $row = mysqli_fetch_array( $result ) ) {
fputcsv( $output, $row, ',', '"');
}
fclose( $output );
}
我在我的本地机器上设置了一个 cron 作业来使用此代码访问网页.我还尝试在服务器上编写一个 cronjob，将脚本作为 CLI 运行.但它会导致各种各样的问题.这些包括
I set up a cron job on my local machine to hit the web page with this code. I also tried writing a cronjob on the server run the script as a CLI. But it's causing all sorts of problems. These include
- 有时数据不一致
- 文件似乎被截断了
- 无法将输出导入另一个数据库
- 有时脚本会超时
我也听说过 mysqldump.我试图用 exec 运行它，但它产生了一个错误.
I have also heard about mysqldump. I tried to run it with exec but it produces an error.
我该如何解决这个问题?
How can I solve this?
推荐答案
CSV and SELECT INTO OUTFILE
http://dev.mysql.com/doc/refman/5.7/en/select-into.html
SELECT ... INTO OUTFILE 将选定的行写入文件.列和可以指定行终止符以生成特定的输出格式.
SELECT ... INTO OUTFILE writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format.
这是一个完整的例子:
SELECT * FROM my_table INTO OUTFILE '/tmp/my_table.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
文件保存在服务器上，选择的路径需要可写.虽然这个查询可以通过 PHP 和 Web 请求执行，但最好通过 mysql 控制台执行.
The file is saved on the server and the path chosen needs to be writable. Though this query can be executed through PHP and a web request, it is best executed through the mysql console.
以这种方式导出的数据可以使用加载数据文件
The data that's exported in this manner can be imported into another database using LOAD DATA INFILE
虽然这种方法在遍历结果集并逐行保存到文件中更胜一筹，但它不如使用....
While this method is superior iterating through a result set and saving to a file row by row, it's not as good as using....
mysqldump 在很多方面都优于 SELECT INTO OUTFILE方式，生成 CSV 只是此命令可以执行的众多操作之一.
mysqldump is superior to SELECT INTO OUTFILE in many ways, producing CSV is just one of the many things that this command can do.
mysqldump 客户端实用程序执行逻辑备份，生成一组可以执行以重现原始 SQL 语句的数量数据库对象定义和表数据.它转储一个或多个 MySQL用于备份或传输到另一个 SQL 服务器的数据库.mysqldump命令还可以生成 CSV、其他分隔文本或 XML 格式的输出格式.
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.
理想情况下，mysqldump 应该从您的 shell 中调用.可以在 php 中使用 exec 来运行它，但是因为产生了转储可能需要很长时间，具体取决于数据量，而 php 脚本通常只运行 30 秒，您需要将其作为后台进程运行.
Ideally mysqldump should be invoked from your shell. It is possible to use exec in php to run it but since producing the dump might take a long time depending on the amount of data, and php scripts usually run only for 30 seconds, you would need to run it as a background process.
mysqldump 并非没有问题.
它不是一种快速或可扩展的备份解决方案大量的数据.对于大数据量，即使备份步骤需要合理的时间，恢复数据可能会很慢因为重放 SQL 语句涉及用于插入的磁盘 I/O，索引创建等.
It is not intended as a fast or scalable solution for backing up substantial amounts of data. With large data sizes, even if the backup step takes a reasonable time, restoring the data can be very slow because replaying the SQL statements involves disk I/O for insertion, index creation, and so on.
一个经典的例子看这个问题:Server crash on MySQL使用 python 备份，其中一个 mysqldump 似乎在前一个完成之前就开始了，并使网站完全没有响应.
A classic example see this question: Server crash on MySQL backup using python where one mysqldump seems to start before the earlier one has finished and rendered the website completely unresponsive.
复制启用来自一台 MySQL 数据库服务器(主服务器)的数据复制到一个或多个 MySQL 数据库服务器(从属).默认情况下复制是异步的；奴隶不需要永久连接以接收来自主站的更新.根据配置，您可以复制所有数据库，选择数据库，甚至是数据库中选定的表.
Replication enables data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves). Replication is asynchronous by default; slaves do not need to be connected permanently to receive updates from the master. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.
因此 replication 与 SELECT 的操作不同INTO OUTFILE 或 msyqldump 理想的做法是将本地副本中的数据保持在几乎最新的状态(本来可以说是完全同步的，但有一种叫做从延迟的东西)另一方面，如果您使用计划任务每 24 小时运行一次 mysqldump.想象一下，如果服务器在 23 小时后崩溃会发生什么?
Thus replication operates differently from SELECT INTO OUTFILE or msyqldump It's ideal keeping the data in the local copy almost upto date (Would have said perfectly in sync but there is something called slave lag) On the other hand if you use a scheduled task to run mysqldump once every 24 hours. Imagine what can happen if the server crashes after 23 hours?
每次运行 mysqldump 时，您都会产生大量数据，继续定期运行，您会发现硬盘已满或文件存储费用高得惊人.通过复制，只有更改会传递到服务器(通过使用所谓的 binlog)
Each time you run mysqldump you are producing a large amount of data, keep doing it regularly and you will find your hard disk filled up or your file storage bills are hitting the roof. With replication only the changes are passed on to the server (by using the so called binlog)
复制的替代方法是使用 Percona XtraBackup.
An alternative to replication is to use Percona XtraBackup.
Percona XtraBackup 是 MySQL 的开源热备份实用程序 -在备份期间不会锁定数据库的基于服务器.
Percona XtraBackup is an open-source hot backup utility for MySQL - based servers that doesn’t lock your database during the backup.
虽然是 Percona，但它与 Mysql 和 Mariadb 兼容.它具有增量备份的能力，这是mysqldump最大的局限性.
Though by Percona, it's compatible with Mysql and Mariadb. It has the ability to do incremental backups lack of which is the biggest limitation of mysqldump.
这篇关于自动或定期备份mysql数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
