有效复制/复制/备份数据库表 - mysql [英] Copy/duplicate/backup database tables effectively - mysql
问题描述
但是有时脚本失败，结果被破坏，因此在执行脚本之前，我必须备份10个表。然而，我仍然需要备份表以保持用户行为的历史记录。因为足球比赛可能持续超过50多个赛季。
任务：使用php脚本复制数据库表。当我开始使用 sqlyog 来备份表格时。这是工作，但它是耗时的，因为我必须等待每个表被重复。此外，对于大桌子，在重复大桌子时， sqlyog 应用程序崩溃可能会非常烦人。
当前解决方案：我已经创建了一个简单的应用程序，界面可以完成这项工作，而且工作得很好。它由三个文件组成，一个用于数据库连接，第二个用于数据库操作，第三个用于用户界面，并使用第二个文件的代码。
事情是，有时它被困在复制表过程的中间。
目标：创建管理员使用的应用程序，方便使用mysql + php进行数据库备份。
我的问题：如何确保复制脚本一定会完全备份表，而不挂断服务器或中断脚本即可。
在这里我将包括我的代码复制功能，但基本上这些是我认为问题所在的两个关键线：
//重复表结构
$ query =CREATE TABLE $ this-> dbName.` $ newTableName` LIKE $ this-> dbName.` $ oldTable`;
//重复表数据
$ query =INSERT INTO $ this-> dbName.` $ newTableName` SELECT * FROM $ this-> dbName.` $ oldTable`;
其余代码仅供验证，以防发生错误。如果你想看看整个代码，就是我的客人。以下是这个功能：
private function duplicateTable（$ oldTable，$ newTableName）{
if this-> isExistingTable（$ oldTable））
{
$ this-> printLogger（Original Table is valid -table exists-：$ oldTable）;
}
else
{
$ this-> printrR（原始表无效 - 不存在 - ：$ oldTable）;
返回false;
}
if（！$ this-> isExistingTable（$ newTableName））//确保新表不存在alrady
{
$ this-> printLogger（Distination Table name is valid -no table with this name-：$ newTableName）;
$ query =CREATE TABLE $ this-> dbName.` $ newTableName` LIKE $ this-> dbName.` $ oldTable`;
$ result = mysql_query（$ query）或$ this-> printrR（查询中的错误。查询：\\\
$ query\\\
错误：mysql_error（））;
}
else
{
$ this-> printrR（Distination Table is invalid。-table already exists- $ newTableName）;
$ this-> printr（现在检查表实际匹配，：$ oldTable => $ newTableName \\\
）;
$ varifyStatus = $ this-> varifyDuplicatedTables（$ oldTable，$ newTableName）;
if（$ varifyStatus> = 0）
{
$ this-> printrG（表匹配，似乎在$ oldTable => $ newTableName之前重复）
}
else
{
$ this-> printrR（重复表存在，但不匹配原始！$ oldTable => $ newTableName） ;
}
return false;
}
如果（$ result）
{
$ this-> printLogger（查询执行1/2）;
}
else
{
$ this-> printrR（Something go wrong duplicateTable\\\
Query：$ query\\\
\\\
\\\
MySql_Error：。mysql_error （））;
返回false;
}
if（！$ this-> isExistingTable（$ newTableName））//验证表已创建
{
$ this- > printrR（Attemp to duplicate table structure failed $ newTableName table was created found。）;
返回false;
}
else
{
$ this-> printLogger（表创建成功：$ newTableName）;
//现在检查表结构
$ this-> printLogger（Now compare indexes ...）;
$ autoInc = $ this-> checkAutoInc（$ oldTable，$ newTableName）;
if（$ autoInc == 1）
{
$ this-> printLogger（Auto inc似乎ok）;
}
elseif（$ autoInc == 0）
{
$ this-> printLogger（No inc key for both tables，Continue anways）;
}
elseif（$ autoInc == -1）
{
$ this-> printLogger（No match inc key！）;
}
$ time = $ oldTable =='team_details'？ 5：2;
$ msg = $ oldTable =='team_details'？ team_details可能需要一段时间，请稍候。 ： 请稍候。;
$ this-> printLogger（Sleep for $ time ... \\\
）;
sleep（$ time）;
$ this-> printLogger（准备复制数据... \\\
）;
$ query =INSERT INTO $ this-> dbName.` $ newTableName` SELECT * FROM $ this-> dbName.` $ oldTable`;
$ this-> printLogger（处理copyign数据查询$ msg ... \\\
\\\
\\\
）;
$ result = mysql_query（$ query）或$ this-> printrR（查询中的错误。查询：\\\
$ query\\\
错误：mysql_error（））;
//如果大表
sleep（$ time），则通常会发生错误; //使db进程当前请求。
$ this-> printLogger（Query executed 2/2）;
sleep（$ time）; //使db进程当前请求。
如果（$ result）
{
$ this-> printLogger（创建的表（$ newTableName）和数据已被复制！
$ this-> printLogger（确认行数...）;
/////////////////////////////
//开始检查
$ numRows = $ this-> checkCountRows（$ oldTable，$ newTableName）;
if（$ numRows）
{
$ this-> printLogger（表重复成功）;
返回true;
}
else
{
$ this-> printLogger（表重复，但请检查num rows $ newTableName）;
return -3;
}
//支票结算
/////////////////////////// //
} // if（！$ result）的结尾查询2/2
else
{
$ this-> printrR（发生错误重复Table\ nINSERT INTO $ oldTable - > $ newTableName\\\
\\\
$ query\\\
mysql_error（）\\\
。mysql_error（））;
返回false;
}
}
}
如你注意到的功能是只能复制一个表，这就是为什么还有另一个函数，它从用户那里获取一个表的数组，并将表名数组逐个传递给duplicateTable（）。
这个问题应该包含任何其他功能，请让我知道。
一个解决方案涌入我的脑海，将复制表通过部分添加任何改进，我不知道插入如何工作，但也许如果我可以插入让我们说25％的时间可能有帮助？
但是有时脚本失败，结果
因此，在执行
脚本之前，我必须备份10个表。
也许你需要在此使用其他解决方案：交易。您需要将失败的脚本中使用的所有查询都包装到事务中。如果事务失败，所有数据将与操作开始时相同。如果查询执行正确 - 你可以。
Reason: I was assigned to run some script that advances a website,it's a fantasy football site and there are several instants of the site located into different domains. Some has more than 80k users and each users supposed to have a team that consists of 15 players. Hence some tables have No.users x No.players rows.
However Sometimes the script fails and the result gets corrupted, therefore I must backup 10 tables in question before i execute the script. Nevertheless, I still need to backup the tables to keep historical record of users action. Because football matches may last for 50+ game weeks.
Task: To duplicate db tables using php script. When i started i used to backup the tables using sqlyog. it's works but it's time consuming since I have to wait for each table to be duplicated. Besides, for large tables the sqlyog application crashes during the duplicating of large tables which may be very annoying.
Current solution: I have created a simple application with interface that does the job and it works great. It consist of three files, one for db connection, 2nd for db manipulation, 3rd for user interface and to use the 2nd file's code. The thing is, sometimes it get stuck at the middle of duplicating tables process.
Objective: To create an application to be used by admin to facilitate database backing up using mysql+php.
My Question: How to ensure that the duplicating script will definitely backup the table completely without hanging the server or interrupting the script.
Down here I will include my code for duplicating function, but basically these are the two crucial lines that i think the problem is located in them:
//duplicate tables structure
$query = "CREATE TABLE $this->dbName.`$newTableName` LIKE $this->dbName.`$oldTable`";
//duplicate tables data
$query = "INSERT INTO $this->dbName.`$newTableName` SELECT * FROM $this->dbName.`$oldTable`";
The rest of the code is solely for validation in case error occur. If you wish to take a look at the whole code, be my guest. Here's the function:
private function duplicateTable($oldTable, $newTableName) {
if ($this->isExistingTable($oldTable))
{
$this->printLogger("Original Table is valid -table exists- : $oldTable ");
}
else
{
$this->printrR("Original Table is invalid -table does not exist- : $oldTable ");
return false;
}
if (!$this->isExistingTable($newTableName))// make sure new table does not exist alrady
{
$this->printLogger("Distination Table name is valid -no table with this name- : $newTableName");
$query = "CREATE TABLE $this->dbName.`$newTableName` LIKE $this->dbName.`$oldTable`";
$result = mysql_query($query) or $this->printrR("Error in query. Query:\n $query\n Error: " . mysql_error());
}
else
{
$this->printrR("Distination Table is invalid. -table already exists- $newTableName");
$this->printr("Now checking if tables actually match,: $oldTable => $newTableName \n");
$varifyStatus = $this->varifyDuplicatedTables($oldTable, $newTableName);
if ($varifyStatus >= 0)
{
$this->printrG("Tables match, it seems they were duplicated before $oldTable => $newTableName");
}
else
{
$this->printrR("The duplicate table exists, yet, doesn't match the original! $oldTable => $newTableName");
}
return false;
}
if ($result)
{
$this->printLogger("Query executed 1/2");
}
else
{
$this->printrR("Something went wrong duplicateTable\nQuery: $query\n\n\nMySql_Error: " . mysql_error());
return false;
}
if (!$this->isExistingTable($newTableName))//validate table has been created
{
$this->printrR("Attemp to duplicate table structure failed $newTableName table was not found after creating!");
return false;
}
else
{
$this->printLogger("Table created successfully: $newTableName");
//Now checking table structure
$this->printLogger("Now comparing indexes ... ");
$autoInc = $this->checkAutoInc($oldTable, $newTableName);
if ($autoInc == 1)
{
$this->printLogger("Auto inc seems ok");
}
elseif ($autoInc == 0)
{
$this->printLogger("No inc key for both tables. Continue anyways");
}
elseif ($autoInc == -1)
{
$this->printLogger("No match inc key!");
}
$time = $oldTable == 'team_details' ? 5 : 2;
$msg = $oldTable == 'team_details' ? "This may take a while for team_details. Please wait." : "Please wait.";
$this->printLogger("Sleep for $time ...\n");
sleep($time);
$this->printLogger("Preparing for copying data ...\n");
$query = "INSERT INTO $this->dbName.`$newTableName` SELECT * FROM $this->dbName.`$oldTable`";
$this->printLogger("Processing copyign data query.$msg...\n\n\n");
$result = mysql_query($query) or $this->printrR("Error in query. Query:\n $query\n Error: " . mysql_error());
// ERROR usually happens here if large tables
sleep($time); //to make db process current requeste.
$this->printLogger("Query executed 2/2");
sleep($time); //to make db process current requeste.
if ($result)
{
$this->printLogger("Table created ($newTableName) and data has been copied!");
$this->printLogger("Confirming number of rows ... ");
/////////////////////////////////
// start checking count
$numRows = $this->checkCountRows($oldTable, $newTableName);
if ($numRows)
{
$this->printLogger("Table duplicated successfully ");
return true;
}
else
{
$this->printLogger("Table duplicated, but, please check num rows $newTableName");
return -3;
}
// end of checking count
/////////////////////////////////
}//end of if(!$result) query 2/2
else
{
$this->printrR("Something went wrong duplicate Table\nINSERT INTO $oldTable -> $newTableName\n\n$query\n mysql_error() \n " . mysql_error());
return false;
}
}
}
AS you noticed the function is only to duplicate one table, that's why there is another function that that takes an array of tables from the user and pass the tables names array one by one to duplicateTable(). Any other function should be included for this question, please let me know.
One solution pops into my mind, would duplicating tables by part by part add any improvement, I'm not sure how Insert into works, but maybe if I could insert let's say 25% at a time it may help?
However Sometimes the script fails and the result gets corrupted, therefore I must backup 10 tables in question before i execute the script.
Probably you need to use another solution here: transactions. You need to wrap up all queries you are using in failing script into transaction. If transaction fails all data will be the same as in the beginning of the operation. If queries got executed correctly - you are OK.
这篇关于有效复制/复制/备份数据库表 - mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
