Create backup from Magento DB for move to another server.

[jason_long]

Hello.

My MySQL databases information are as below:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| livezilla          |
| mysql              |
| performance_schema |
| store              |
| temp               |
| test               |
+--------------------+
mysql> select user from mysql.user;
+-----------+
| user      |
+-----------+
| jason   |
| root      |
| jason   |
| jason   |
| root      |
| livezilla |
| magento   |
| root      |
+-----------+

And "/app/etc/config.xml" is:

<default_setup>
                <connection>
                    <host><![CDATA[localhost]]></host>
                    <username><![CDATA[magento]]></username>
                    <password><![CDATA[PASS]]></password>
                    <dbname><![CDATA[store]]></dbname>
                    <initStatements><![CDATA[SET NAMES utf8]]></initStatements>
                    <model><![CDATA[mysql4]]></model>
                    <type><![CDATA[pdo_mysql]]></type>
                    <pdoType><![CDATA[]]></pdoType>
                    <active>1</active>
                </connection>

According to Link, what is the correct syntax for create a backup from my DB? Is below syntax correct:

$ mysqldump -h localhost -u magento -pPASS --single-transaction store > data.sql

Thank you.

[Mukesh Tiwari]

Hi @jason_long 

The syntax provided is correct. If you want the database dump in compressed format use following command.

mysqldump h hostname -u username -p databasename | gzip > database.sql.gz

It will give you the dump file in gz compressed format.

[jason_long]

Thank you for your reply.
I have two questions:
1- can I export DB with .db suffix and then import it? Or it must be .sql?
2- For import it on another server I must create a DB with the same name and then import it or when I import DB it created automatically?

[Mukesh Tiwari]

Hi @jason_long 

The exported DB should be with .sql extension. 

On another server you may create a DB with different name. If you create a DB with different name then you should change the entry in the local.xml 

 <dbname><![CDATA[newdbname]]></dbname>

[jason_long]

Must I create an empty DB with the same name or when I import DB backup it create everything that needed?

[jason_long]

Can anyone answer to my question?