phpMyAdmin can be used to export or backup MySQL databases easily.
However, if the database size is very big, it probably won’t
be a good idea. phpMyAdmin allows users to save database dump as
file or display on screen, which involves exporting SQL statements
from the server, and transmitting the data across slower network
connection or Internet to user’s computer. This process slow
the exporting process, increase database locking time and thus
MySQL unavailability, slow the server and may simply crash the
Apache HTTPD server if too many incoming web connections hogging
the system’s resources.
The better way to backup and export MySQL
database is by doing the task locally on the server, so that the
tables’ data can be instantly dumped on the local disk
without delay. Thus export speed will be faster and reduce the time
MySQL database or table is locked for accessing. This tutorial is
the guide on how to backup (export) and restore (import) MySQL
database(s) on the database server itself by using the mysqldump
and mysql utilities. There are basically two methods to backup
MySQL, one is by copying all table files (*.frm, *.MYD, and *.MYI
files) or by using mysqlhotcopy utility, but it only works for
MyISAM tables. Below tutorial will concentrate on mysqldump which
works for both MyISAM and InnoDB tables.
How to Export or Backup or Dump A MySQL Database
To export a MySQL database into a dump
file, simply type the following command syntax in the shell. You
can use Telnet or SSH to remotely login to the machine if you
don’t have access to the physical box.
Replace username with a valid MySQL user
ID, password with the valid password for the user (IMPORTANT: no
space after -p and the password, else mysqldump will prompt you for
password yet will treat the password as database name, so the
backup will fail) and database_name with the actual name of the
database you want to export. Finally, you can put whatever name you
like for the output SQL dump file, here been dump.sql.
The while data, tables, structures and
database of database_name will be backed up into a SQL text file
named dump.sql with the above command.
How to Export A MySQL Database Structures Only
If you no longer need the data inside the
database’s tables (unlikely), simply add –no-data
switch to export only the tables’ structures. For example,
the syntax is:
If you only want the data to be backed up,
use –no-create-info option. With this setting, the dump will
not re-create the database, tables, fields, and other structures
when importing. Use this only if you pretty sure that you have a
duplicate databases with same structure, where you only need to
refresh the data.
Backup the database inevitable cause MySQL
server unavailable to applications because when exporting, all
tables acquired a global read lock using FLUSH TABLES WITH READ
LOCK at the beginning of the dump until finish. So although READ
statements can proceed, all INSERT, UPDATE and DELETE statements
will have to queue due to locked tables, as if MySQL is down or
stalled. If you’re using InnoDB, –single-transaction is
the way to minimize this locking time duration to almost
non-existent as if performing an online backup. It works by reading
the binary log coordinates as soon as the lock has been acquired,
and lock is then immediately released.
You can restore from phpMyAdmin, using Import tab. For faster way,
upload the dump file to the MySQL server, and use the following
command to import the databases back into the MySQL server.
mysql -u username -ppassword
database_name < dump.sql
The import and export of MySQL database
not only is important to recover the data when disaster strikes,
but also provides an easy way to migrate or move to another server,
such as when switching web hosting providers. However, do note that
one common problem - character set encoding. Newer release of
mysqldump uses UTF8 as its default charset if nothing is specified,
while older versions (older than 4.1 typically) use Latin1 as
default characterset. If you database charset is Latin1 and dump in
UTF8 collation, the data may ends up become simply rubbish,
garbled, or unreadable (frequently happen with Wordpress blog). If
this case, use –default-character-set=charset_name option to
specify the character set or convert the database to UTF8.
