首页 / 知识

关于mysql：不使用mysqldump复制/复制数据库

2023-04-13 02:27:00

Copy/duplicate database without using mysqldump

如果没有本地访问服务器，是否有任何方法可以将MySQL数据库(包含内容和不包含内容)复制/克隆到另一个数据库中而无需使用mysqldump？

我目前正在使用MySQL 4.0。

我可以看到您说您不想使用mysqldump，但是我在寻找类似解决方案的同时到达了此页面，其他人也可以找到它。考虑到这一点，这是一种从Windows服务器的命令行复制数据库的简单方法：

使用MySQLAdmin或您的首选方法创建目标数据库。在此示例中，db2是目标数据库，将在其中复制源数据库db1。

在命令行上执行以下语句：

mysqldump -h [server] -u [user] -p[password] db1 | mysql -h [server] -u [user] -p[password] db2

注意：-p和[password]之间没有空格

您可以通过运行以下命令来复制没有数据的表：

1	CREATE TABLE x LIKE y;

(请参阅MySQL CREATE TABLE Docs)

您可以编写一个脚本，该脚本从一个数据库获取SHOW TABLES的输出，然后将架构复制到另一个数据库。您应该能够引用架构+表名称，例如：

1	CREATE TABLE x LIKE other_db.y;

就数据而言，您也可以在MySQL中进行操作，但这并不一定很快。创建引用之后，可以运行以下命令复制数据：

1	INSERT INTO x SELECT * FROM other_db.y;

如果您使用的是MyISAM，最好复制表文件。它会更快。如果对每个表的表空间使用INNODB，则应该能够执行相同的操作。

如果您确实使用了INSERT INTO SELECT，请确保使用ALTER TABLE x DISABLE KEYS暂时关闭索引！

EDIT Maatkit也有一些脚本，这些脚本可能对同步数据很有帮助。可能不会更快，但是您可以在实时数据上运行它们的同步脚本而无需太多锁定。

如果您使用的是Linux，则可以使用以下bash脚本：
(它可能需要一些额外的代码清除，但是它可以工作……而且比mysqldump | mysql要快得多)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

#!/bin/bash

DBUSER=user
DBPASSWORD=pwd
DBSNAME=sourceDb
DBNAME=destinationDb
DBSERVER=db.example.com

fCreateTable=""
fInsertData=""
echo"Copying database ... (may take a while ...)"
DBCONN="-h ${DBSERVER} -u ${DBUSER} --password=${DBPASSWORD}"
echo"DROP DATABASE IF EXISTS ${DBNAME}" | mysql ${DBCONN}
echo"CREATE DATABASE ${DBNAME}" | mysql ${DBCONN}
for TABLE in `echo"SHOW TABLES" | mysql $DBCONN $DBSNAME | tail -n +2`; do
createTable=`echo"SHOW CREATE TABLE ${TABLE}"|mysql -B -r $DBCONN $DBSNAME|tail -n +2|cut -f 2-`
fCreateTable="${fCreateTable} ; ${createTable}"
insertData="INSERT INTO ${DBNAME}.${TABLE} SELECT * FROM ${DBSNAME}.${TABLE}"
fInsertData="${fInsertData} ; ${insertData}"
done;
echo"$fCreateTable ; $fInsertData" | mysql $DBCONN $DBNAME

在PHP中：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

请注意，有一个mysqldbcopy命令作为mysql实用程序添加的一部分。
https://dev.mysql.com/doc/mysql-utilities/1.5/en/utils-task-clone-db.html

我真的不知道您所说的"本地访问"是什么意思。
但是对于该解决方案，您需要能够通过ssh访问服务器以将文件复制到存储数据库的位置。

我无法使用mysqldump，因为我的数据库很大(7Go，mysqldump失败)
如果2个mysql数据库的版本差异太大，可能无法正常工作，则可以使用mysql -V检查mysql版本。

1)将数据从远程服务器复制到本地计算机(vps是远程服务器的别名，可以用root@1.2.3.4替换)

1
2
3

ssh vps:/etc/init.d/mysql stop
scp -rC vps:/var/lib/mysql/ /tmp/var_lib_mysql
ssh vps:/etc/init.d/apache2 start

2)导入复制到本地计算机上的数据

1
2
3
4
5
6

/etc/init.d/mysql stop
sudo chown -R mysql:mysql /tmp/var_lib_mysql
sudo nano /etc/mysql/my.cnf
-> [mysqld]
-> datadir=/tmp/var_lib_mysql
/etc/init.d/mysql start

如果您使用其他版本，则可能需要运行

1
2
3

/etc/init.d/mysql stop
mysql_upgrade -u root -pPASSWORD --force #that step took almost 1hrs
/etc/init.d/mysql start

所有先前的解决方案都可以解决问题，但是，它们只是不会复制所有内容。我创建了一个PHP函数(尽管有些冗长)，该函数复制了包括表，外键，数据，视图，过程，函数，触发器和事件在内的所有内容。这是代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138

/* This function takes the database connection, an existing database, and the new database and duplicates everything in the new database. */
function copyDatabase($c, $oldDB, $newDB) {

// creates the schema if it does not exist
$schema ="CREATE SCHEMA IF NOT EXISTS {$newDB};";
mysqli_query($c, $schema);

// selects the new schema
mysqli_select_db($c, $newDB);

// gets all tables in the old schema
$tables ="SELECT table_name
FROM information_schema.tables
WHERE table_schema = '{$oldDB}'
AND table_type = 'BASE TABLE'";
$results = mysqli_query($c, $tables);

// checks if any tables were returned and recreates them in the new schema, adds the foreign keys, and inserts the associated data
if (mysqli_num_rows($results) > 0) {

// recreates all tables first
while ($row = mysqli_fetch_array($results)) {
$table ="CREATE TABLE {$newDB}.{$row[0]} LIKE {$oldDB}.{$row[0]}";
mysqli_query($c, $table);
}

// resets the results to loop through again
mysqli_data_seek($results, 0);

// loops through each table to add foreign key and insert data
while ($row = mysqli_fetch_array($results)) {

// inserts the data into each table
$data ="INSERT IGNORE INTO {$newDB}.{$row[0]} SELECT * FROM {$oldDB}.{$row[0]}";
mysqli_query($c, $data);

// gets all foreign keys for a particular table in the old schema
$fks ="SELECT constraint_name, column_name, table_name, referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE referenced_table_name IS NOT NULL
AND table_schema = '{$oldDB}'
AND table_name = '{$row[0]}'";
$fkResults = mysqli_query($c, $fks);

// checks if any foreign keys were returned and recreates them in the new schema
// Note: ON UPDATE and ON DELETE are not pulled from the original so you would have to change this to your liking
if (mysqli_num_rows($fkResults) > 0) {
while ($fkRow = mysqli_fetch_array($fkResults)) {
$fkQuery ="ALTER TABLE {$newDB}.{$row[0]}
ADD CONSTRAINT {$fkRow[0]}
FOREIGN KEY ({$fkRow[1]}) REFERENCES {$newDB}.{$fkRow[3]}({$fkRow[1]})
ON UPDATE CASCADE
ON DELETE CASCADE;";
mysqli_query($c, $fkQuery);
}
}
}
}

// gets all views in the old schema
$views ="SHOW FULL TABLES IN {$oldDB} WHERE table_type LIKE 'VIEW'";
$results = mysqli_query($c, $views);

// checks if any views were returned and recreates them in the new schema
if (mysqli_num_rows($results) > 0) {
while ($row = mysqli_fetch_array($results)) {
$view ="SHOW CREATE VIEW {$oldDB}.{$row[0]}";
$viewResults = mysqli_query($c, $view);
$viewRow = mysqli_fetch_array($viewResults);
mysqli_query($c, preg_replace("/CREATE(.*?)VIEW/","CREATE VIEW", str_replace($oldDB, $newDB, $viewRow[1])));
}
}

// gets all triggers in the old schema
$triggers ="SELECT trigger_name, action_timing, event_manipulation, event_object_table, created
FROM information_schema.triggers
WHERE trigger_schema = '{$oldDB}'";
$results = mysqli_query($c, $triggers);

// checks if any triggers were returned and recreates them in the new schema
if (mysqli_num_rows($results) > 0) {
while ($row = mysqli_fetch_array($results)) {
$trigger ="SHOW CREATE TRIGGER {$oldDB}.{$row[0]}";
$triggerResults = mysqli_query($c, $trigger);
$triggerRow = mysqli_fetch_array($triggerResults);
mysqli_query($c, str_replace($oldDB, $newDB, $triggerRow[2]));
}
}

// gets all procedures in the old schema
$procedures ="SHOW PROCEDURE STATUS WHERE db = '{$oldDB}'";
$results = mysqli_query($c, $procedures);

// checks if any procedures were returned and recreates them in the new schema
if (mysqli_num_rows($results) > 0) {
while ($row = mysqli_fetch_array($results)) {
$procedure ="SHOW CREATE PROCEDURE {$oldDB}.{$row[1]}";
$procedureResults = mysqli_query($c, $procedure);
$procedureRow = mysqli_fetch_array($procedureResults);
mysqli_query($c, str_replace($oldDB, $newDB, $procedureRow[2]));
}
}

// gets all functions in the old schema
$functions ="SHOW FUNCTION STATUS WHERE db = '{$oldDB}'";
$results = mysqli_query($c, $functions);

// checks if any functions were returned and recreates them in the new schema
if (mysqli_num_rows($results) > 0) {
while ($row = mysqli_fetch_array($results)) {
$function ="SHOW CREATE FUNCTION {$oldDB}.{$row[1]}";
$functionResults = mysqli_query($c, $function);
$functionRow = mysqli_fetch_array($functionResults);
mysqli_query($c, str_replace($oldDB, $newDB, $functionRow[2]));
}
}

// selects the old schema (a must for copying events)
mysqli_select_db($c, $oldDB);

// gets all events in the old schema
$query ="SHOW EVENTS
WHERE db = '{$oldDB}';";
$results = mysqli_query($c, $query);

// selects the new schema again
mysqli_select_db($c, $newDB);

// checks if any events were returned and recreates them in the new schema
if (mysqli_num_rows($results) > 0) {
while ($row = mysqli_fetch_array($results)) {
$event ="SHOW CREATE EVENT {$oldDB}.{$row[1]}";
$eventResults = mysqli_query($c, $event);
$eventRow = mysqli_fetch_array($eventResults);
mysqli_query($c, str_replace($oldDB, $newDB, $eventRow[3]));
}
}
}

实际上，我想用PHP来实现这一点，但是这里的答案都没有很大帮助，所以这是我使用MySQLi的解决方案(非常简单)：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

// Database variables

$DB_HOST = 'localhost';
$DB_USER = 'root';
$DB_PASS = '1234';

$DB_SRC = 'existing_db';
$DB_DST = 'newly_created_db';

// MYSQL Connect

$mysqli = new mysqli( $DB_HOST, $DB_USER, $DB_PASS ) or die( $mysqli->error );

// Create destination database

$mysqli->query("CREATE DATABASE $DB_DST" ) or die( $mysqli->error );

// Iterate through tables of source database

$tables = $mysqli->query("SHOW TABLES FROM $DB_SRC" ) or die( $mysqli->error );

while( $table = $tables->fetch_array() ): $TABLE = $table[0];

// Copy table and contents in destination database

$mysqli->query("CREATE TABLE $DB_DST.$TABLE LIKE $DB_SRC.$TABLE" ) or die( $mysqli->error );
$mysqli->query("INSERT INTO $DB_DST.$TABLE SELECT * FROM $DB_SRC.$TABLE" ) or die( $mysqli->error );

endwhile;

创建SQL命令以复制行：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

select @fromdb:="crm";
select @todb:="crmen";

SET group_concat_max_len=100000000;

SELECT GROUP_CONCAT( concat("CREATE TABLE `",@todb,"`.`",table_name,"` LIKE `",@fromdb,"`.`",table_name,"`;
",
"INSERT INTO `",@todb,"`.`",table_name,"` SELECT * FROM `",@fromdb,"`.`",table_name,"`;")

SEPARATOR '

')

as sqlstatement
FROM information_schema.tables where table_schema=@fromdb and TABLE_TYPE='BASE TABLE';

不使用mysqldump克隆数据库表的最佳方法：

创建一个新的数据库。

使用查询创建克隆查询：

1
2
3
4

SET @NewSchema = 'your_new_db';
SET @OldSchema = 'your_exists_db';
SELECT CONCAT('CREATE TABLE ',@NewSchema,'.',table_name, ' LIKE ', TABLE_SCHEMA ,'.',table_name,';INSERT INTO ',@NewSchema,'.',table_name,' SELECT * FROM ', TABLE_SCHEMA ,'.',table_name,';')
FROM information_schema.TABLES where TABLE_SCHEMA = @OldSchema AND TABLE_TYPE != 'VIEW';

运行该输出！

但是请注意，上面的脚本只是快速克隆表，而不是视图，触发器和用户功能：您可以通过mysqldump --no-data --triggers -uroot -ppassword快速获取结构，然后仅用于克隆insert语句。

为什么是实际问题？因为如果数据库超过2Gb，则mysqldumps的上载速度会很慢。而且，您不能仅通过复制数据库文件(例如快照备份)来克隆InnoDB表。

数据库方法服务器复制