Pages

Sunday, June 9, 2013

3 ways to import and export a MySQL Database, ITworld.com

3 ways to import and export a MySQL DatabaseITworld.comJune
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)

With large or very complex databases, you run the risk of the PHP
request timing out before the operation completes. This will leave you
with a partial import and likely a broken application. This method is
best used for smaller databases like an average wordpress installation.

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

This method works much better than phpMyAdmin on larger databases as
you don’t usually run into a timeout issue. On the downside, if your
up/download speed is low it can take a really long time. The software
can be pretty buggy on occasion as well, it routinely crashes and needs
restarting. The requirement for both MySQL servers to be network
accessible can be a deal breaker for this method.

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:

Followers

Important Links

Note:

@2zuseful is the user friendly blog to provide information about the useful data in all fields in the world for people, these posts are are collecting from the different web sources to share the information to all my friends, if its useful to you read and share with your friends and get knowledge, if its having any copy right content we don't have any objections to remove,please fell free to share with me.