Access a database db1, for example, the corresponding database file is db1. MDB, to copy the database, direct copy file, sqlite is similar, their database file contains the metadata,
But the mysql database is not a library file, even if it can do a library catalog, but metadata is not in the inside,
Remember once, see an article on the net, which quickly copy the mysql database, the meaning is:
The create database db2 from db1;
The SQL statement is, for example, actually not like that, I forgot that statement,
But now I find I can't find it, I check my document in mysql website, about the building of SQL, did not find this statement, that is how to return a responsibility, who know, have what way to quickly copy the database?
CodePudding user response:
Find an article, is to use the mysqldump copy through a pipe, hope have SQL replication methodhttp://blog.csdn.net/whatlookingfor/article/details/51942566
In order to facilitate fast copying a database, you can use the following command
Copies the db1 database data and table structure to newdb database
Create a new database
# mysql -u root - p123456
Mysql> The CREATE DATABASE ` newdb ` DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;
1
2
3
Duplicate database, use the mysqldump and mysql command combination, a one-time copy
# mysqldump db1 -u root - p123456 - add - drop - table | mysql newdb -u root - p123456
1
Note - written p123456 parameters: -p behind directly with password, no Spaces)
Above is on the same MySQL server replication database method, if you want to copy to the remote another MySQL server, you can use the MySQL "-h host name/IP" parameter, the premise is MySQL allow remote connection, and the transmission efficiency of the remote replication and time is acceptable,
Not in the same mysql server
# mysqldump db1 uroot - p123456 - add - drop - table | mysql -h 192.168.1.22 newdb -u root - p123456
As for rapid replication table method, also put the connection in the
http://blog.csdn.net/btbdylq/article/details/6827981
But these methods, the equivalent of import and export, there is no mysql internal copying files (similar to the sqlite or access) such a quick way,
CodePudding user response:
The above said, let there be use mysqldump command line access, not on the database in the cloud,Therefore, at present only stupid way,
First built library, then establish each table create table like, and then insert the from
This slow
But this can be achieved by SQL operations, do not need special permissions (of course to build library privileges)
Is there better and faster way
CodePudding user response:
If you don't mean data consistency, can directly copy the filesCodePudding user response:
The create table xx as select * from XXX is quickerCodePudding user response:
Copy all files under DATDIR directly