MySQL 使用查询而不使用 mysqldump 备份数据库

Backup database(s) using query without using mysqldump

提问by Ben

I'd like to dump my databases to a file.

我想将我的数据库转储到一个文件中。

Certain website hosts don't allow remote or command line access, so I have to do this using a series of queries.

某些网站主机不允许远程或命令行访问，因此我必须使用一系列查询来执行此操作。

All of the related questions say "use mysqldump" which is a great tool but I don't have command line access to this database.

所有相关问题都说“使用mysqldump”，这是一个很棒的工具，但我没有对该数据库的命令行访问权限。

I'd like CREATEand INSERTcommands to be created at the same time - basically, the same performance as mysqldump. Is SELECT INTO OUTFILEthe right road to travel, or is there something else I'm overlooking - or maybe it's not possible?

我希望同时创建CREATE和INSERT命令 - 基本上，与mysqldump. 是SELECT INTO OUTFILE正确的旅行之路，还是我忽略了其他东西 - 或者不可能？

回答by cerd

Use mysqldump-phpa pure-PHP solution to replicate the function of the mysqldumpexecutable for basic to med complexity use cases - I understand you may not have remote CLI and/or mysql direct access, but so long as you can execute via an HTTP request on a httpd on the host this will work:

使用mysqldump-php纯 PHP 解决方案复制mysqldump可执行文件的功能，用于基本到中等复杂性用例 - 我知道您可能没有远程 CLI 和/或 mysql 直接访问权限，但只要您可以通过 httpd 上的 HTTP 请求执行在主机上这将起作用：

So you should be able to just run the following purely PHP script straight from a secure-directory in /www/ and have an output file written there and grab it with a wget.

因此，您应该能够直接从 /www/ 中的安全目录运行以下纯 PHP 脚本，并在那里写入输出文件并使用 wget 获取它。

mysqldump-php - Pure PHP mysqldump on GitHub

mysqldump-php - GitHub 上的纯 PHP mysqldump

PHP example:

PHP示例：

<?php
require('database_connection.php');
require('mysql-dump.php')
$dumpSettings = array(
    'include-tables' => array('table1', 'table2'),
    'exclude-tables' => array('table3', 'table4'),
    'compress' => CompressMethod::GZIP, /* CompressMethod::[GZIP, BZIP2, NONE] */
    'no-data' => false,            
    'add-drop-table' => false,      
    'single-transaction' => true,   
    'lock-tables' => false,        
    'add-locks' => true,            
    'extended-insert' => true      
);

$dump = new MySQLDump('database','database_user','database_pass','localhost', $dumpSettings);
$dump->start('forum_dump.sql.gz');
    ?>

回答by bob-the-destroyer

With your hands tied by your host, you may have to take a rather extreme approach. Using any scripting option your host provides, you can achieve this with just a little difficulty. You can create a secure web page or strait text dump link known only to you and sufficiently secured to prevent all unauthorized access. The script to build the page/text contents could be written to follow these steps:

由于您的双手被主人束缚，您可能不得不采取一种相当极端的方法。使用您的主机提供的任何脚本选项，您都可以轻松实现这一点。您可以创建一个只有您知道的安全网页或严格的文本转储链接，并且足够安全以防止所有未经授权的访问。可以按照以下步骤编写构建页面/文本内容的脚本：

For each database you want to back up:

对于要备份的每个数据库：

• Step 1: Run SHOW TABLES.

• Step 2: For each table name returned by the above query, run SHOW CREATE TABLEto get the create statement that you could run on another server to recreate the table and output the results to the web page. You may have to prepend "DROP TABLE X IF EXISTS;" before each create statement generated by the results of these queryies (!not in your query input!).

• Step 3: For each table name returned from step 1 again, run a SELECT *query and capture full results. You will need to apply a bulk transformation to this query result before outputing to screen to convert each line into an INSERT INTO tblXstatement and output the final transformed results to the web page/text file download.

• 第 1 步：运行SHOW TABLES.

• 第二步：对于上述查询返回的每个表名，运行SHOW CREATE TABLE得到create语句，你可以在另一台服务器上运行该语句来重新创建表并将结果输出到网页。您可能需要在前面加上“DROP TABLE X IF EXISTS;” 在这些查询的结果生成的每个 create 语句之前（！不在您的查询输入中！）。

• 第 3 步：对于从第 1 步再次返回的每个表名，运行SELECT *查询并捕获完整结果。在输出到屏幕之前，您需要对此查询结果应用批量转换，以将每一行转换为INSERT INTO tblX语句并将最终转换的结果输出到网页/文本文件下载。

The final web page/text download would have an output of all create statements with "drop table if exists" safeguards, and insert statements. Save the output to your own machine as a ".sql" file, and execute on any backup host as needed.

最终的网页/文本下载将输出所有带有“如果存在删除表”保护措施的创建语句和插入语句。将输出作为“.sql”文件保存到您自己的机器上，并根据需要在任何备份主机上执行。

I'm sorry you have to go through with this. Note that preserving mysql user accounts that you need is something else entirely.

我很抱歉你必须经历这个。请注意，保留您需要的 mysql 用户帐户完全是另一回事。

回答by user266926

Use / Install PhpMySQLAdmin on your web server and click export. Many web hosts already offer you this as a service pre-configured, and it's easy to install if you don't already have it (pure php): http://www.phpmyadmin.net/

在您的 Web 服务器上使用/安装 PhpMySQLAdmin，然后单击导出。许多网络主机已经为您提供了这项预先配置的服务，如果您还没有它（纯 php），它很容易安装：http: //www.phpmyadmin.net/

This allows you to export your database(s), as well as perform other otherwise tedious database operations very quickly and easily -- and it works for older versions of PHP < 5.3 (unlike the Mysqldump.php offered as another answer here).

这允许您导出您的数据库，以及非常快速和轻松地执行其他繁琐的数据库操作 - 它适用于旧版本的 PHP < 5.3（与此处作为另一个答案提供的 Mysqldump.php 不同）。

I am aware that the question states 'using query' but I believe the point here is that any means necessary is sought when shell access is not available -- that is how I landed on this page, and PhpMyAdmin saved me!

我知道问题是“使用查询”，但我相信这里的重点是，当 shell 访问不可用时，会寻求任何必要的方法——这就是我登陆此页面的方式，PhpMyAdmin 救了我！