CODEWITHSUNDEEP
mysql
Jorge Risquez
Jorge Risquez

Reputation: 35

Complicated mysqldump

Let's set this example:

I have a database with four tables in it, set up like this:

a_database
   table_1
   table_2
   table_3
   table_4

Now, in my project i use tables 1 and 2 to store system configuration options, settings, etc. and tables 3 and 4 for data stored by the user.

Lets say i want to migrate this database to a new database, but carrying the data and structure for tables 1 and 2 and only the structure for tables 3 and 4.

Could this be done using the mysqldump command?

mysqldump -u -p a_database > path-to-file

Upvotes: 1

Views: 47

Answers (1)

Ergest Basha
Ergest Basha

Reputation: 8973

You can do it with one command combining the dumps like the below command:

mysqldump -u -p  a_database table_1 table_2 > dump.sql && mysqldump -u  -p --no-data a_database table_3 table_4 >> dump.sql

As far as i know i don't think you can do it only with one mysqldump command.

You can add below command to the dumps based on your needs:

--single-transaction --- > option if you don't want or can't do table locks

-d --- > -d is --no-data for short.

-R --- > Also consider adding --routines (-R for short ) if you're database has stored procedures/functions

-B --- > Include the CREATE DATABASE command. --databases dbname (shorthand: -B dbname)

-r --- > To avoid having the character set of your shell interfere with encoding, -r schema.sql is preferred over > schema.sql. Also a good idea to specify the character set explicitly with --default-character-set=utf8 (or whatever). You'll still want to check the set names at the top of the dump file. I've been caught in MySQL charset encoding hell before

--routines --- > Also consider adding --routines if you're database has stored procedures/functions

Upvotes: 1

Related Questions