mysqldump table per *.sql file batch script

Question

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

Clever solution but if you've scheduled your batch file to run nightly, you can lose the batch file altogether and just schedule your php script to run nightly instead. Just put php.exe in your action and pass your php script as your parameter with the -f flag, for example "-f C:\inetpub\wwwroot\myscript.php" — Vincent
– Vincent, Commented Feb 28, 2020 at 22:39

GregD · Accepted Answer · 2012-11-09 12:11:36Z

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.

Thanks for the hint about mysql --xml --execute! Never noticed that.

Chris · Accepted Answer · 2012-11-09 07:33:16Z

0

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)

edited Nov 9, 2012 at 7:33

answered Nov 9, 2012 at 7:25

Chris

1,6431 gold badge19 silver badges29 bronze badges

Collectives™ on Stack Overflow

mysqldump table per *.sql file batch script

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related