Currently I am dumping my database (running under Linux Mint), from the command line, using the following:-

mysqldump -u[user] -p[password] MyDatabase > MyDatabase.sql

Now rather than dumping to a self-contained file I need to dump to a Project Folder so that I have a .sql file per table. However despite trawling the web and a couple of MySQL books I have been unable to find an option for this. Can anyone help?


2 Answers 2


Do it in 2 steps...

Write a SELECT against information_schema.TABLES to generate one mysqldump statement per table. Send the output of that to a file; then execute that file. (Also, give the file execute permission).


To dump each table in its own sql file, you would need the list of the tables,

// define your database name here

// Select the list of tables into an array 'mytables'
// Note repalce the user and password in the mysql statement before execution.
mytables=$(mysql -uroot -se "select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='${mydbname}' and TABLE_TYPE='BASE TABLE'")

// Now loop through each table and dump into its own file.
for table in $mytables
        echo "Table : ${table}";
        mysqldump -uroot ${mydbname}  ${table} > ${mydbname}.${table}.sql

Not the answer you're looking for? Browse other questions tagged or ask your own question.