您可以使用MySQL查询完整地创建数据库副本吗 [英] Can you use a MySQL Query to Completely Create a Copy of the Database

问题描述

我有一个带有5个表的MySQL数据库的LIVE版本和一个TEST版本.

I have a LIVE version of a MySQL database with 5 tables and a TEST version.

我一直在使用phpMyAdmin将LIVE版本的每个表的副本复制到TEST版本.

I am continually using phpMyAdmin to make a copy of each table in the LIVE version to the TEST version.

有人有mysql查询语句来制作数据库的完整副本吗?查询字符串将需要考虑结构，数据，自动增量值以及与需要复制的表相关的任何其他内容.

Does anyone have the mysql query statement to make a complete copy of a database? The query string would need to account for structure, data, auto increment values, and any other things associated with the tables that need to be copied.

谢谢.

推荐答案

好，经过大量研究，在Google上进行了搜索并仔细阅读了每个人的评论，我制作了以下脚本-现在从浏览器地址栏中运行.经过测试，它确实可以完成我需要的工作.谢谢大家的帮助.

Ok, after a lot of research, googling, and reading through everyone's comments herein, I produced the following script -- which I now run from the browser address bar. Tested it and it does exactly what I needed it to do. Thanks for everyone's help.

<?php
function duplicateTables($sourceDB=NULL, $targetDB=NULL) {
    $link = mysql_connect('{server}', '{username}', '{password}') or die(mysql_error()); // connect to database
    $result = mysql_query('SHOW TABLES FROM ' . $sourceDB) or die(mysql_error());
    while($row = mysql_fetch_row($result)) {
        mysql_query('DROP TABLE IF EXISTS `' . $targetDB . '`.`' . $row[0] . '`') or die(mysql_error());
        mysql_query('CREATE TABLE `' . $targetDB . '`.`' . $row[0] . '` LIKE `' . $sourceDB . '`.`' . $row[0] . '`') or die(mysql_error());
        mysql_query('INSERT INTO `' . $targetDB . '`.`' . $row[0] . '` SELECT * FROM `' . $sourceDB . '`.`' . $row[0] . '`') or die(mysql_error());
        mysql_query('OPTIMIZE TABLE `' . $targetDB . '`.`' . $row[0] . '`') or die(mysql_error());
    }
    mysql_free_result($result);
    mysql_close($link);
} // end duplicateTables()
duplicateTables('liveDB', 'testDB');
?>

这篇关于您可以使用MySQL查询完整地创建数据库副本吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！