Mysql – Use –databases on thesqldump or not

backupmysqldump

I have seen this two ways:

mysqldump -u username -ppassword --databases mydb >with.sql
mysqldump -u username -ppassword mydb >without.sql

I did a linux 'diff' on the two and the with.sql file has this:

< CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
< 
< USE `mydb`;

While the without.sql doesn't. I have used –databases on mysqldump but I have seen tutorials showing it without when doing a mysqldump on a single database.

Why are they different? Which one is most common to use? I'm preparing a mysqldump of a database for a client which might run it in Linux or Windows, so is there a concern of being more portable?

Best Answer

mysqldump -u username -ppassword --databases mydb >with.sql

This gives you dump with top most statement

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` 
    /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `mydb`;

In this case client doesn't need to create a database prior to restoration and database will create with default properties .

So in this case you can't change the name of database at another end. Moreover, while using replication some filtering rules will not work.

mysqldump -u username -ppassword mydb >without.sql

In this case table will create in any database, so prior to restoration you need to select the database in which you want this dump to be restored.

This will facilitate you to restore the dump with database name of your choice.

If you will not execute

        use databasename;

prior to restoration, it will throw the error and dump not executed.

So I must say, difference lies on your requirement.

Related Solutions

Mysql – Database Files-Related Issue

Answer to your first question: Each file contains info for the table

cars.frm has the table structure
cars.MYD has the table data
cars.MYI has the table indexes

Given this layout, the cars table uses the storage engine MyISAM.

Answer to your second question: Yes, provided the following circumstances

every table uses the storage engine MyISAM
no INSERTs, UPDATEs, or DELETEs are running
DB Connections using TCP/IP are disabled

To check to see if all the tables are MyISAM, run this query:

SELECT COUNT(1) TableCount,engine
FROM information_schema.tables
WHERE table_schema='db_test_1'
GROUP BY engine;

You should only see just MyISAM. If you see other storage engines, you cannot just copy.

If your root@localhost password is mypass, here is all you need to to safely move all the tables regardless of storage engine:

# USERPASS="-uroot -pmypass"
# service mysql restart --skip-networking
# mysql ${USERPASS} -A -e"CREATE DATABASE db_test_2"
# mysqldump ${USERPASS} --routines --triggers db_test_1 | mysql ${USERPASS} -A -Ddb_test_2
# service mysql restart

If the data is huge, do this instead:

# USERPASS="-uroot -pmypass"
# service mysql restart --skip-networking
# mysql ${USERPASS} -A -e"CREATE DATABASE db_test_2"
# mysqldump ${USERPASS} --routines --triggers db_test_1 > /root/MyData.sql
# mysql ${USERPASS} -A -Ddb_test_2 < /root/MyData.sql
# service mysql restart

Give it a Try !!!

Thesqldump not generating USE statement

Try putting the USE db_name manually

echo "USE db_name" > output.sql
mysqldump -u root db_name tbl_1 tbl_2 tbl_3 >> output.sql

I can see the wisdom of not mixing the USE command with select mysqldumping of tables. You do not want to risk creating database with the name of a production database. You also do not want the developer to become aware of not the database that will be the target. In this way, the mysqldump, in itself, can never be blamed for overwriting target tables.

Best Answer

Related Solutions

Mysql – Database Files-Related Issue

Thesqldump not generating USE statement

Related Question