如何在同一台服务器上复制 MySQL 数据库

How to duplicate a MySQL database on the same server

提问by Christoph Grimmer-Dietrich

I have a large MySQL database, lets call it live_db, which I want to replicate on the same machine to provide a test system to play around with (test_db), including table structure and data. In regular intervals I want to update the test_dbwith the content of the live_db; if possible incremental.

我有一个大型 MySQL 数据库，我们称之为live_db，我想在同一台机器上复制它以提供一个测试系统来使用 ( test_db)，包括表结构和数据。每隔一段时间，我想test_db用live_db;的内容更新; 如果可能，增量。

Is there some built-in mechanism in MySQL to do that? I think that master-slave replication is not the thing I want since it should be possible to alter data in the test_db. These changes do not have to be preserved, though.

MySQL 中是否有一些内置机制可以做到这一点？我认为主从复制不是我想要的，因为应该可以更改test_db. 但是，不必保留这些更改。

Regards,

问候，

CGD

CGD

回答by Michael Berkowski

The mysqlcommand line client will accept a stream of SQL statements from standard input. You can therefore pipe the output of mysqldumpdirectly into mysqlon the command line. Doing this as a cron job will regularly overwrite your test data with updated live data:

在mysql命令行客户端将接受来自标准输入的SQL语句的流。因此，您可以将 的输出mysqldump直接通过管道传输到mysql命令行。将此作为 cron 作业将定期使用更新的实时数据覆盖您的测试数据：

mysql --user=username --password=passwd -e 'DROP DATABASE test_db;'
mysql --user=username --password=passwd -e 'CREATE DATABASE test_db;'
mysqldump --user=username --password=passwd live_db | mysql --user=username --password=passwd test_db

Note that since your data is large, it will take a long time.

请注意，由于您的数据很大，因此需要很长时间。

回答by Samuel ?slund

Michaels answer abowe works well but does not copy events, stored procedures or triggers.

Michaels answer abowe 运行良好，但不复制事件、存储过程或触发器。

To copy those a few more switches is needed for mysqldump: --events --triggers --routines

要复制 mysqldump 需要更多的开关： --events --triggers --routines

To complement an already made copy:

要补充已经制作的副本：

mysqldump --user=username --password=passwd --no-data --no-create-info --no-create-db --events --triggers --routines live_db | mysql --user=username --password=passwd test_db

mysqldump --user=username --password=passwd --no-data --no-create-info --no-create-db --events --triggers --routines live_db | mysql --user=username --password=passwd test_db

回答by LSerni

I have been doing this for several years in different contexts, with small to medium databases (1 G to 100 G). The quick and dirty mysqldump works for smaller data sets; the smaller they are the better it goes.

我已经在不同的环境中使用中小型数据库（1 G 到 100 G）这样做了几年。快速而肮脏的 mysqldump 适用于较小的数据集；它们越小越好。

When you exceed 5-10 GB, depending on the MySQL load, quick and dirty does not cut it anymore.

当您超过 5-10 GB 时，根据 MySQL 负载，快速和肮脏不再削减它。

mysqldump

转储

The problem with MySQLdump is that while it dumps, the live database is either unusable, very awkward to use, or the backup will not be consistent. Unless you have a wide enough time window when the unusability of the live database is not important because the database needs not be in use anyway (for example late at night).

MySQLdump 的问题是，当它转储时，实时数据库要么无法使用，使用起来非常尴尬，要么备份不一致。除非您有足够宽的时间窗口，当实时数据库的不可用性并不重要时，因为无论如何都不需要使用数据库（例如深夜）。

The default options (herea discussion of the why) make the database next to unusable while it's being dumped, unlessthe usage is just reading data and little of that. On a busy ecommerce site, you're looking at a client pile-up crash.

默认选项（这里讨论原因）使数据库在被转储时几乎无法使用，除非使用只是读取数据而很少。在繁忙的电子商务网站上，您正在查看客户堆积如山的崩溃。

So you use InnoDB and the modern options (not defaults, as far as I know)

所以你使用 InnoDB 和现代选项（据我所知，不是默认值）

--single-transaction --skip-lock-tables

which allow the site to run, albeit slower than normal, during the dump. Depending on what the usage is, it might be noticeablyslower.

这允许站点在转储期间运行，尽管比正常情况慢。根据使用情况，它可能会明显变慢。

While you're at it, also dump other data which might be important:

在此过程中，还要转储其他可能很重要的数据：

--events --triggers --routines

(...oh, and this still won't have dumped user permissions. To use as a test perhaps it wasn't so important).

（...哦，这仍然不会转储用户权限。用作测试可能不是那么重要）。

There is a workaround I've found "advised" (!) as a "great hack", which basically disables transactional integrityallowing the database to run at full speed while it's being dumped. Somewhat like removing the brakes from your car to lighten it and have it run faster, yeah it will work, but it'll have some side effects that you might not notice immediately. You willalmost surely notice them - and it will be when you'll need them most, and it won't be pretty.

我发现有一个解决方法“建议”（！）作为“伟大的黑客”，它基本上禁用了事务完整性，允许数据库在转储时全速运行。有点像从你的车上拆下刹车来减轻它的重量并让它跑得更快，是的，它会起作用，但它会产生一些你可能不会立即注意到的副作用。您将几乎肯定会注意到他们-这将是当你最需要他们，也不会漂亮。

However, for a testdatabase, it could still work.

但是，对于测试数据库，它仍然可以工作。

Xtrabackup

Xtrabackup

If you have a "serious" database, what's the reason not to have a "serious" backup?

如果您有一个“严重”的数据库，那么没有“严重”备份的原因是什么？

Slave replication

从复制

Another possibility if you have space to spare - and, nowadays, 20 Gb is not that much - is that of using an ancillary database.

如果您有空闲空间（如今 20 Gb 并没有那么多），另一种可能性是使用辅助数据库。

You can install a second copy of MySQL Serveron a different port, and have it be the slave. Then you will have two identical databases (live master, live slave). The first timeyou will still have to run a full dump, with all the problems it involves.

您可以在不同的端口上安装MySQL Server 的第二个副本，并将其作为从属服务器。然后您将拥有两个相同的数据库（实时主数据库、实时从数据库）。第一次你仍然需要运行一个完整的转储，以及它涉及的所有问题。

When you need to clone the test database, stop the slave replication - the live slave will now remain "frozen" in time - and backup the live slave to the test db, using MySQLbackup or just copying the data files. Once done, you restart the replication.

当您需要克隆测试数据库时，停止从属复制 - 实时从属现在将及时保持“冻结”状态 - 并将实时从属备份到测试数据库，使用 MySQLbackup 或仅复制数据文件。完成后，重新启动复制。

The impact on the live master is negligible, and the slave can actually be used for non-update-critical selects.

对 live master 的影响可以忽略不计，slave 实际上可以用于非更新关键的选择。

回答by Priyank

For all the mac users, with sequel pro all you need to do is go to database (menu) -> Duplicate database. Done!

对于所有 mac 用户，使用 sequel pro，您只需转到数据库（菜单）-> 复制数据库。完毕！

回答by Vasili

In case you prefer MySQL Migration Toolkit, you may double click on schema name in Data Mapping step and change target schema name.

如果您更喜欢 MySQL Migration Toolkit，您可以在数据映射步骤中双击架构名称并更改目标架构名称。

回答by Jonathan Parent Lévesque

This solution works fine, but it won't do the deal if you are using PHPunit for unit testing.

此解决方案工作正常，但如果您使用 PHPunit 进行单元测试，则无法解决问题。

Using password in command line generates a warning which is catch by PHPUnit and generate an exception (yeah quite a big deal...)

在命令行中使用密码会生成一个警告，该警告被 PHPUnit 捕获并生成一个异常（是的，非常重要......）

The way to work around this is to use configuration file.

解决这个问题的方法是使用配置文件。

In my case, I don't want to maintain password and user both in configuration files and PHP code, so I generate the configuration file from the code and check out if it exists (otherwise I use the username and password directly in command line as a fall back option).

就我而言，我不想在配置文件和 PHP 代码中同时维护密码和用户，所以我从代码生成配置文件并检查它是否存在（否则我直接在命令行中使用用户名和密码作为后备选项）。

Here's an example, in PHP, how to copy a setup database to create a new one with a different name (if you are, by example, managing a main domain with a different subdomain/database for each of your customer):

这是一个示例，在 PHP 中，如何复制设置数据库以创建具有不同名称的新数据库（例如，如果您要为每个客户管理具有不同子域/数据库的主域）：

/**
* If the $dbName doesn't exist, then create it.
* 
* @param $errorMessage String to return the error message.
* @param $dbName String name of the database to create.
* @param $cleanExisting Boolean if the database exist, clear it to recreate it.
*
* @return boolean ($dbExists)
*/
private function createDatabase(&$errorMessage, $dbName, $clearExisting = false){

    $command = "";
    $configurationString = "[client]" . "\r\n" . "user=" . parent::$support_user . "\r\n" . "password=" . md5(parent::$support_pass);
    $dbExist = false;
    $path = realpath(dirname(__FILE__));

    $connectionString = " --defaults-extra-file=" . $path . "\mysql.cnf ";

    $dbName = strtolower($dbName);

    if ($this->isDestinationDbNameValid($errorMessage, $dbName)) {

        $dbExist = $this->isDestinationDbExist($errorMessage, $dbName);

        if (empty($errorMessage) and ($dbExist === false or $clearExisting === true)) {

            if (file_put_contents($path . '/mysql.cnf', $configurationString) === false) {

                $connectionString = " --user=" . parent::$support_user . " --password=" . md5(parent::$support_pass). " ";
            }

            if ($dbExist and $clearExisting) {

                $command = $path . '/../../../mysql/bin/mysql ' . $connectionString . ' --execute="DROP DATABASE ' . $dbName  .';" &';
            }

            $command .= '"' . $path . '/../../../mysql/bin/mysql" ' . $connectionString . ' --execute="CREATE DATABASE ' . $dbName . ';" &"' .
                        $path . '/../../../mysql/bin/mysqldump" ' . $connectionString . ' --events --triggers --routines setup | "' .
                        $path . '/../../../mysql/bin/mysql" ' . $connectionString . $dbName;

            exec($command);

            $dbExist = $this->isDestinationDbExist($errorMessage, $dbName);

            if (!$dbExist) {

                $errorMessage = parent::getErrorMessage("COPY_SETUP_DB_ERR", "An error occurred during the duplication process of the setup database.");
            }
        }
    }

    return $dbExist;
}

Additional note:

补充说明：

1. I had to use double quote (") instead of single quote (') around my SQL statements.

2. I had to use the ampersand (&) to separate my different command.

3. This example doesn't include the validation for the new database name (isDestinationDbNameValid() method). No need to mention that you should never trust user input...

4. You also have to write your custom method to validate that the database copy worked as expected (isDestinationDbExist() method). You should at least validate that the database exist, a table from your setup exist and, optionally, validate for stored programs.

1. 我不得不在我的 SQL 语句周围使用双引号 (") 而不是单引号 (')。

2. 我不得不使用与号 (&) 来分隔不同的命令。

3. 此示例不包括对新数据库名称的验证（isDestinationDbNameValid() 方法）。无需提及您永远不应该相信用户输入......

4. 您还必须编写自定义方法来验证数据库副本是否按预期工作（isDestinationDbExist() 方法）。您至少应该验证数据库是否存在，您的设置中的表是否存在，并且可以选择验证存储的程序。

Use the force wisely my friends,

明智地使用力量，我的朋友们，

Jonathan Parent-Lévesque from Montreal

来自蒙特利尔的 Jonathan Parent-Lévesque