Mysql Fast Copy Database data table method _mysql

Sometimes, for example, to build a test environment, or to clone a Web site, you need to replicate an existing MySQL database. You can implement it very simply by using the following methods.

Suppose that the existing database name is DB1, and you want to copy one, named Newdb. The steps are as follows:

1. Create a new database first newdb

#mysql-
 u root-ppassword
mysql>create
 DATABASE ' newdb ' DEFAULT CHARACTER SET UTF8 COLLATE Utf8_general_ CI;

2. Use of mysqldump and MySQL command combination, one-time replication

#mysqldump
 db1-u root-ppassword--add-drop-table | MySQL newdb-u root-ppassword

(Note the-ppassword parameter writing:-P followed by the password, no space in the middle)

These are the ways to replicate a database on the same MySQL server. If you want to replicate to a remote MySQL server, you can use the "-H hostname/IP" parameter of MySQL. The premise is that MySQL allows remote connections, and that the transfer efficiency and time of remote replication are acceptable.

#mysqldump
 Db1-uroot-ppassword--add-drop-table | mysql-h 192.168.1.22 Newdb-uroo

MySQL Copy data table method

Examples are as follows:

The MYTBL table in the production database is quickly copied to the mytbl_new,2 command as follows:

CREATE TABLE mytbl_new like production.mytbl;
INSERT mytbl_new SELECT * from PRODUCTION.MYTBL;

The first command is to create a new datasheet mytbl_new and copy the MYTBL datasheet structure.

The second command is to copy the data from the datasheet mytbl to the new table mytbl_new.

Note: PRODUCTION.MYTBL is the name of the database that specifies the table to be replicated production. It is optional.

If there is no production. , the MySQL database will assume mytbl in the current operation of the database.

Other methods:

Programme 1:

Copy an entire table

CREATE TABLE  new_table  SELECT  *  from  old_table;  

Copy, do not copy data

CREATE TABLE  new_table  SELECT  *  from  old_table  where  0;

Note: This scenario simply builds a table of the results of the SELECT statement. So new_table This table will not have a primary key, index.

Programme 2:

If we have one of these tables:

ID Username password
-----------------------------------
1 Admin *************
2 Sameer *************
3 Stewart *************

CREATE TABLE IF not EXISTS ' admin ' ( 
' id ' int (6) unsigned not NULL auto_increment, 
' username ' varchar (%) NOT NULL Default ', 
' password ' varchar (MB) default NULL, 
PRIMARY KEY (' id ') 
engine=myisam default Charset=latin1 auto_increment=4;

1. The following statement copies the table structure to the new table Newadmin. (Data in the table will not be copied)

CREATE TABLE newadmin like admin

2. The following statement copies the data to the new table. Note: This statement actually only builds a table of the results of the SELECT statement. So newadmin This table will not have a primary key, index.

CREATE TABLE newadmin 
( 
SELECT * from 
admin 
)

3. If you want to really copy a table. You can use the following statement.

CREATE TABLE newadmin like admin; 
INSERT into Newadmin SELECT * from admin;

4. We can operate a different database.

CREATE TABLE newadmin like shop.admin; 
CREATE TABLE newshop.newadmin like shop.admin;

5. We can also copy some of the fields from a table.

CREATE TABLE newadmin 
( 
SELECT username, password from admin 
)

6. We can also change the name of the field of the newly created table.

CREATE TABLE newadmin 
( 
SELECT ID, username as uname, password as pass from admin 
)

7. We can also copy part of the data.

CREATE TABLE newadmin 
( 
SELECT * from admin WHERE Left (username,1) = ' s ' 
)

8. We can also define the field information in the table while creating the table.

CREATE TABLE newadmin 
( 
ID INTEGER not NULL auto_increment PRIMARY KEY 
) 
( 
SELECT * from admin 
)

The above content is small to introduce the MySQL quickly copy database data table method, I hope you like.