Menu
I'm in the process of moving my files onto another computer, and one thing I would like to do is to transfer the data in my MySQL database. I want to dump the databases into .sql files, but also have the create database db_name including in the file, that way I just have to import the file to mysql without having to manually create the databases. Is there a way to do that?
638
Mysqldump is a command-line utility that is used to generate the logical backup of the MySQL database. It produces the SQL Statements that can be used to recreate the database objects and data. The command can also be used to generate the output in the XML, delimited text, or CSV format.
To dump/export a MySQL database, execute the following command in the Windows command prompt: mysqldump -u username -p dbname > filename. sql . After entering that command you will be prompted for your password.
To copy a MySQL database, you need to follow these steps: First, create a new database using CREATE DATABASE statement. Second, export all the database objects and data of the database from which you want to copy using mysqldump tool. Third, import the SQL dump file into the new database.
By default mysqldump always creates the CREATE DATABASE IF NOT EXISTS db_name; statement at the beginning of the dump file.
[EDIT] Few things about the mysqldump file and it's options:
--all-databases, -A
Dump all tables in all databases. This is the same as using the --databases option and naming all the databases on the command line.
--add-drop-database
Add a DROP DATABASE statement before each CREATE DATABASE statement. This option is typically used in conjunction with the --all-databases or --databases option because no CREATE DATABASE statements are written unless one of those options is specified.
--databases, -B
Dump several databases. Normally, mysqldump treats the first name argument on the command line as a database name and following names as table names. With this option, it treats all name arguments as database names. CREATE DATABASE and USE statements are included in the output before each new database.
--no-create-db, -n
This option suppresses the CREATE DATABASE statements that are otherwise included in the output if the --databases or --all-databases option is given.
Some time ago, there was similar question actually asking about not having such statement on the beginning of the file (for XML file). Link to that question is here.
So to answer your question:
--add-drop-database option in your mysqldump statement.--databases or --all-databases and the CREATE DATABASE syntax will be added automaticallyMore information at MySQL Reference Manual
67
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
