Copy MySQL database to another server through shell?

Copying MySQL Databases to another server

If you are using MySQL 3.23 or later, you can copy the .frm, .MYI, and .MYD files for MyISAM tables between different architectures that support the same floating-point format. (MySQL takes care of any byte-swapping issues.) See Section 14.1, “The MyISAM Storage Engine”.

The MySQL ISAM data and index files (.ISD and *.ISM, respectively) are dependent upon the architecture and, in some cases, the operating system. If you want to move applications to another machine having a different architecture or operating system than that of the current machine, you should not try to move a database by simply copying the files to the other machine. Use mysqldump instead.

By default, mysqldump creates a file containing SQL statements. You can then transfer the file to the other machine and use it as input to the mysql client.

Try mysqldump –help to see what options are available. If you are moving the data to a newer version of MySQL, you should use mysqldump –opt to take advantage of any optimizations that result in a dump file that is smaller and can be processed faster.

The easiest (although not the fastest) way to move a database between two machines is to run the following commands on the machine on which 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 these commands:

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

You can also store the dump in a file, transfer the file to the target machine, and then load the file into the database there. For example, you can dump a database to a compressed file on the source machine like this:

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

Transfer the file containing the database contents to the target machine and run these commands there
  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

After restoring a SQL Server database, it show a status of “Loading”

Getting the database out of LOADING state When restoring multiple backups, each backup needs...

Backing up and restoring a MySQL database from the command line

. Shell into your box as root, then: mysqldump -a -u USERNAME -p DATABASE > FILENAME.mysql...

Change the DNS servers for the network interfaces on my Windows server

In Network Connections, right-click the local area connection, and then click Properties. In...

Change the DNS servers for the network interfaces on Windows server

In Network Connections, right-click the local area connection, and then click Properties. In...

Change the root password for a *nix server

In order to change the root password login to the server as root and type the following, then...