自动或定期备份mysql数据

Question

我想使用<在这里插入最喜欢的PHP框架> / plain php
/我的第二喜欢的语言对mysql数据库中的某些表进行定期备份。我希望它是自动化的，以便以后出现问题时可以还原备份。

我尝试执行查询并将结果保存到文件中。最终得到看起来像这样的代码。

$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身份运行脚本。但这引起了各种各样的问题。这些包括

1. 有时数据不一致
2. 该文件似乎被截断了
3. 无法将输出导入到另一个数据库
4. 有时脚本超时

我也听说过mysqldump。我尝试使用exec它运行，但是会产生错误。

我该如何解决？

Answer 1

CSV和选择进入输出文件

http://dev.mysql.com/doc/refman/5.7/zh-CN/select-
into.html

SELECT … INTO OUTFILE将选定的行写入文件。可以指定列和行终止符以产生特定的输出格式。

这是一个完整的示例：

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控制台执行。

可以使用LOAD DATA INFILE将以这种方式导出的数据导入另一个数据库。

尽管此方法在遍历结果集并逐行保存到文件方面具有优越性， 但不如使用…好。

mysqldump

mysqldump在许多方面都优于SELECT
INTO OUTFILE，生成CSV只是此命令可以执行的许多操作之一。

mysqldump客户端实用程序执行逻辑备份，生成一组SQL语句，可以执行这些语句来重现原始数据库对象定义和表数据。它转储一个或多个MySQL数据库以进行备份或转移到另一台SQL服务器。mysqldump命令还可以生成CSV，其他定界文本或XML格式的输出。

理想情况下，应从您的shell调用mysqldump。可以在php中使用exec来运行它，但是由于产生转储可能会花费很长时间，具体取决于数据量，并且php脚本通常只运行30秒，因此您需要将其作为后台进程运行。

mysqldump并非没有很多问题。

它并非旨在作为用于备份大量数据的快速或可扩展解决方案。对于大数据量，即使备份步骤花费了合理的时间，恢复数据也可能会非常缓慢，因为重播SQL语句会涉及磁盘I
/ O进行插入，创建索引等。

一个经典的例子看到了这个问题：使用python的MySQL备份导致服务器崩溃，其中一个mysqldump似乎在较早的一个mysqldump完成之前启动，并使网站完全没有响应。

MySQL复制

复制使数据可以从一台MySQL数据库服务器（主服务器）复制到一台或多台MySQL数据库服务器（从服务器）。默认情况下，复制是异步的；从站不需要永久连接就可以接收来自主站的更新。根据配置，您可以复制数据库中的所有数据库，选定的数据库甚至选定的表。

因此，复制的操作与SELECT INTO OUTFILE或有所不同。msyqldump理想的做法是使本地副本中的数据保持最新状态（本来可以说得很完美，但有一个称为“从属滞后”的情况）。另一方面，如果您使用计划任务mysqldump每24小时运行一次。想象一下，如果服务器在23小时后崩溃，会发生什么情况？

每次运行mysqldump时，您都会产生大量数据，请定期进行处理，您会发现硬盘已满，或者文件存储账单即将付诸东流。通过复制，只有更改才能传递到服务器（通过使用所谓的binlog）

XtraBackup

复制的替代方法是使用Percona XtraBackup。

Percona XtraBackup是基于MySQL的服务器的开源热备份实用程序，在备份过程中不会锁定数据库。

尽管是Percona，但它与Mysql和Mariadb兼容。它具有执行增量备份的能力，而这正是mysqldump的最大局限。