3 ways to import and export a MySQL Database
ITworld.com
June 07, 2013, 12:00 AM — Nobody ever really wants to move a database, but it is a frequent necessity. Servers get old, providers become unreliable, disk space runs out. In the case of MySQL database migration, you have many options for accomplishing the task. I’ll discuss three of the most common options and when to use each.
Use this method when:
When the process completes, start a new Server Admin connection to the new server that you’ll be importing to. This time, click on the item labeled Data Import/Restore in the left column. Choose the export location from your local computer and set the target database for the import, then click the Start Import button.
Use this method when:
To start, SSH into the first server (using a tool like PuTTy) as a user with sufficient privileges to work with the server. Execute the following command at the terminal to dump the database to a file:
When the export is complete, it’s helpful to compress the file to make it transfer to the new server faster. To do so, run the following command:
Next, transfer the compressed file to your new server, either by making it available at a web address at the old server and using wget, or by using FTP to download and upload the file.
Once the file is on the new server, start be decompressing it using the following command:
When the terminal returns to the ready cursor, the import is complete.
Use this method when:
ITworld.com
June 07, 2013, 12:00 AM — Nobody ever really wants to move a database, but it is a frequent necessity. Servers get old, providers become unreliable, disk space runs out. In the case of MySQL database migration, you have many options for accomplishing the task. I’ll discuss three of the most common options and when to use each.
Option 1 - phpMyAdmin
Whether you’re renting servers or running your own, chances are you have phpMyAdmin installed to manage your MySQL installations. This PHP staple has been the most common MySQL management tool for over a decade.
Source: wikipedia.org
Migrating a database is as simple as
selecting the DB in the left column, clicking the Export link, and
saving the database to a file. Then on the new server, select the DB in
the left column, click the Import link, and choose the file you just
exported. Easy.Use this method when:
-
Both servers have phpMyAdmin installed
-
The database size is under 50MB
-
The database export file is small enough to overcome upload size
limits (note: you can compress the file before uploading to pack more
in)
Option 2 - MySQL Workbench
MySQL Workbench is a desktop GUI product from MySQL itself. It does much more than importing and exporting with features like server health monitoring, SQL data modeling, and more.
Source: mysql.com
Migrating a database starts by establishing a Server Instance for
each of the servers. Once they are configured, you can open the Server
Admin tool by double clicking the instance of the server whose database
is to be exported. Then, in the left column click the item labeled Data
Export. Choose the database to export, configure the options and click
Start Export. This will save the database export file(s) to your
computer. When the process completes, start a new Server Admin connection to the new server that you’ll be importing to. This time, click on the item labeled Data Import/Restore in the left column. Choose the export location from your local computer and set the target database for the import, then click the Start Import button.
Use this method when:
-
You have remote access available to your MySQL servers
-
You don’t have phpMyAdmin installed
-
The database size is fairly large
-
You’ve got a decent internet connection
Option 3 - Command Line
Tried and true, the command line will always be the most efficient - though not the most friendly way - to accomplish a database export/import.To start, SSH into the first server (using a tool like PuTTy) as a user with sufficient privileges to work with the server. Execute the following command at the terminal to dump the database to a file:
mysqldump -u [username] -p [database_name] > [dumpfilename.sql]
You will be prompted for the password and the export will begin. Note
that you should replace each variable in brackets with the proper
values and do not include the brackets. When the export is complete, it’s helpful to compress the file to make it transfer to the new server faster. To do so, run the following command:
tar zcf dumpfilename.tar.gz dumpfilename.sql
replacing dumpfilename with your actual file name.Next, transfer the compressed file to your new server, either by making it available at a web address at the old server and using wget, or by using FTP to download and upload the file.
Once the file is on the new server, start be decompressing it using the following command:
tar zxf dumpfilename.tar.gz
You’ll now have your original file dumpfilename.sql that you can
import into your database. To begin the import process, use the command:
mysql -u [username] -p [database_name] < [dumpfilename.sql]
You will be prompted for the password and the import will begin. Note
that you should replace each variable in brackets with the proper
values and do not include the brackets.When the terminal returns to the ready cursor, the import is complete.
Use this method when:
-
You do not have phpMyAdmin installed
-
You do not have remote web access to the database server
-
You do have SSH access
-
The database size is large
-
You want total control over the process
No comments:
Post a Comment