自动或定期备份 mysql 数据

2022-01-30 00:00:00 database php mysql mysql-backup

我想使用<在此处插入最喜欢的 PHP 框架> 定期备份我的 mysql 数据库中的一些表./plain 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

  1. 有时数据不一致
  2. 文件似乎被截断了
  3. 输出无法导入其他数据库
  4. 有时脚本会超时

我也听说过 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 和 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 '
'
  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 服务器的数据库.mysql转储命令还可以生成 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.

相关文章