Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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,
#!/bin/bash
// define your database name here
mydbname="MyDatabase"
// 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
do
echo "Table : ${table}";
mysqldump -uroot ${mydbname} ${table} > ${mydbname}.${table}.sql
done
