Howto backup and restore MySql Database
for Newbies
Here
is the easiest way to backup/restore your mysql database. Forget about phpMyAdmin and others like that, they have security vulnerabilities! We suggest you to
use raw
commands.
The easiest way is using mysqdump. In the following example we will back up one website called example.com and move it
to our new server :)
Let's get started:
1. We assume you are using Linux, so open up a terminal:
$ mysqldump -u kickar -p example > /home/kickar/backup/example.sql
Enter Password:
$
In this command the user kickar had privilegies to dump out this database, -p
is Using Password set to Yes. "example"
is the name of the database and we just dumped it in a directory called
"backup" in kickar's home dir. The name of the backed up database is
example.sql. If everythig goes fine and you do not specify verbose mode
you will see no output of this command. Right now all the tables from
the database are dumped, because we didn't specify nothing. We just
backed up the whole database.
Now Let's restore it:
We assume that you have copied some where on the new server. In our example we copied it in backup directory in the home dir :
Now you need to log in and create empty database so we can dump our existing one in the empty.
$mysql -u kickar -p
Enter Password:
mysql> create database example;
Query OK, 1 row affected (0.00 sec)
mysql>quit
Now lets dump our old databse into the new one:
$ mysql -u kickar -p example < /home/kickar/backup/example.sql
Enter Password:
$
If you do not specify verbose option and everything goes fine you will not see any output.
Now just to make sure everything is where it has to be let's log in and check the tables in our database:
$mysql -u kickar -p
Enter Password:
mysql>use database example;
Database changed
mysql> show tables;
+---------------------------------------+
|
Tables_in_example
|
+---------------------------------------+
| jos_banner
|
| jos_bannerclient
|
|
jos_bannertrack
|
|
jos_categories
|
|
jos_components
|
|
jos_contact_details
|
|
jos_content
|
| jos_content_frontpage |
| jos_content_rating
|
| jos_core_acl_aro
|
| jos_core_acl_aro_groups |
| jos_core_acl_aro_map |
| jos_core_acl_aro_sections |
+---------------------------------------+
51 rows in set (0.00 sec)
mysql> quit
This is just a sample joomla database, but as you see all the tables are successfuly dumped out!
In case the above way doesn't work for you, there is another way that works too :
Alternative :
Log in to mysql, create new Database and use the "source" built in command :$ mysql -u kickar -p Enter
Password:
mysql> create database example;
Query OK, 1 row affected (0.00 sec)mysql> use example;
Database changedmysql> source /home/kickar/backup/example.sql
And the output will be something like : Query OK, 0 rows affected (0.00
sec)Query OK, 0 rows affected (0.00
sec)Query OK, 0 rows affected (0.00
sec)Query OK, 0 rows affected (0.00
sec)Query OK, 0 rows affected (0.00
sec)Query OK, 0 rows affected (0.00
sec)After that you can check if all the tables are resotred again :
mysql> show tables;
+---------------------------------------+
|
Tables_in_example
|
+---------------------------------------+
| jos_banner
|
| jos_bannerclient
|
|
jos_bannertrack
|
|
jos_categories
|
|
jos_components
|
|
jos_contact_details
|
|
jos_content
|
| jos_content_frontpage |
| jos_content_rating
|
| jos_core_acl_aro
|
| jos_core_acl_aro_groups |
| jos_core_acl_aro_map |
| jos_core_acl_aro_sections |
+---------------------------------------+
51 rows in set (0.00 sec)That was all folks, so easy all that can be done in one minute!If you have any questions
plese refer to our FORUM.
de.li.cio.us
. 