Mysql back up the database and copy the database to another server _ MySQL

You can use mysqladmin to create a database by backing up the mysql database and copying the database to another server.

After importing the mysql database to the target machine, runMysqladmin flush-privilegesTo reload the authorization table information on the server.

D:/MySql 5.1.73/bin/mysqladmin-u root create test2;

D:/Mysql 5.1.73/bin/mysqladmin-u root drop test2;

Warning:

Dropping the database is potentially a very hard thing to do.

Any data stored in the database will be destroyed.

Do you really want to drop the 'test2' database [y/n]

---------------------------------------------------------------------------- Copy the MySQL database to another machine.

You can copy. frm,. MYI, and. MYD files for MyISAM tables between different architectures that support the same floating point format. (MySQL focuses on all byte exchanges ). See Section 15.1 "MyISAM storage engine ".

You can useMysqldumpCreate a file containing SQL statements. Then you can transfer the file to another machine and input it to the MySQL client.

UseMysqldump -- helpSee which options are available. If you are moving data to an updated version of MySQL, you should useMysqldump-optUse various optimized performance to generate smaller and faster dump files.

The simplest (though not the fastest) way to move a database between two machines is to run the following command on the machine where the database is located:

shell> mysqladmin -h 'other_hostname' create db_name

shell> mysqldump --opt db_name | mysql -h 'other_hostname' db_name

If you want to copy a database from a remote machine over a slow network, you can use:

shell> mysqladmin create db_name

shell> mysqldump -h 'other_hostname' --opt --compress db_name | mysql db_name

You can also save the results to a file, transfer the file to the target machine, and load the file to the database. For example, you can use the following command on the source machine to back up the database to a file:

shell> mysqldump --quick db_name | gzip > db_name.contents.gz

(The file created in this example is in the compressed format ). Upload the files containing the database content to the target machine and run the following command:

shell> mysqladmin create db_name

shell> gunzip < db_name.contents.gz | mysql db_name

You can also useMysqldumpAndMysqlimportTo transfer the database. For large tablesMysqldumpIt is much faster. In the following command, DUMPDIR indicates to saveMysqldUmpThe full path name of the output directory.

First, create a directory to save the output file and back up the database:

shell> mkdir DUMPDIR

shell>mysqldump --tab=DUMPDIR db_name

Then, transfer the files in the DUMPDIR directory to the corresponding directory on the target machine and load the files to MySQL:

shell> mysqladmin create db_name            # create database

shell> cat DUMPDIR/*.sql | mysql db_name    # create tables in database

shell> mysqlimport db_name DUMPDIR/*.txt    # load data into tables

Do not forget to copy the MySQL database because the authorization table is saved in the database. You may need to run the command on the new machine with the MySQL root user until the MySQL database is generated.

After importing the mysql database to the target machine, runMysqladminFlush-privilegesTo reload the authorization table information on the server.

Certificate ----------------------------------------------------------------------------------------------------------------------------------

I tried the above method for more than an hour and succeeded, mainly because the above command is not very complete:

shell> mysqladmin -h 'other_hostname' create db_name

（mysqladmin -h other_hostname -u username -p create db_name）

shell> mysqldump --opt db_name | mysql -h 'other_hostname' db_name

(mysqldump -h local_host -u username -p local_db_name | mysql -h other_hostname -u username -p db_name)