CODEWITHSUNDEEP
batch-filebackupmysql
imperium2335
imperium2335

Reputation: 24112

mysqldump table per *.sql file batch script

I have done some digging around and I can not find a way to make mysqldump create a file per table. I have about 100 tables (and growing) that I would like to be dumped into separate files without having to write a new mysqldump line for each table I have.

E.g. instead of my_huge_database_file.sql which contains all the tables for my DB. I'd like mytable1.sql, mytable2.sql etc etc

Does mysqldump have a parameter for this or can it be done with a batch file? If so how.

It is for backup purposes.

I think I may have found a work around, and that is to make a small PHP script that fetches the names of my tables and runs mysqldump using exec().

$result = $dbh->query("SHOW TABLES FROM mydb") ;

while($row = $result->fetch()) {

exec('c:\Xit\xampp\mysql\bin\mysqldump.exe -uroot -ppw mydb > c:\dump\\'.$row[0]) ;
}

In my batch file I then simply do:

php mybackupscript.php

Upvotes: 2

Views: 2848

Answers (2)

GregD
GregD

Reputation: 2867

Instead of SHOW TABLES command, you could query the INFORMATION_SCHEMA database. This way you could easily dump every table for every database and also know how many tables there are in a given database (i.e. for logging purposes). In my backup, I use the following query:

  SELECT DISTINCT CONVERT(`TABLE_SCHEMA` USING UTF8) AS 'dbName' 
       , CONVERT(`TABLE_NAME` USING UTF8) AS 'tblName'
       , (SELECT COUNT(`TABLE_NAME`)
    FROM `INFORMATION_SCHEMA`.`TABLES`
   WHERE `TABLE_SCHEMA` = dbName
GROUP BY `TABLE_SCHEMA`) AS 'tblCount'
    FROM `INFORMATION_SCHEMA`.`TABLES` 
   WHERE `TABLE_SCHEMA` NOT IN ('INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA', 'mysql')
ORDER BY 'dbName' ASC 
       , 'tblName' ASC;

You could also put a syntax in the WHERE clause such as TABLE_TYPE != 'VIEW', to make sure that the views will not get dump.

Upvotes: 1

Chris
Chris

Reputation: 1643

I can't test this, because I don't have a Windows MySQL installation, but this should point you to the right direction:

@echo off
mysql -u user -pyourpassword database -e "show tables;" > tables_file
for /f "skip=3 delims=|" %%TABLE in (tables_file) do (mysqldump -u user -pyourpassword database %%TABLE > %%TABLE.sql)

Upvotes: 0

Related Questions