0

I'm using a batch file to back up my MySQL databases on windows. How can I save each table in an individual file? The current batch file saves all the tables into one file for each database.

Here's the batch file

FOR /D %%F IN (*) DO (

SET %%F=!%%F:@002d=-!
%mysqldumpexe% --user=%dbuser% --password=%dbpass% --databases --routines --log-error=%errorLogPath% %%F > "%backupfldr%%%F.%backuptime%.sql"

::How- Grab the list of tables inside the current database
::How- Backup current table from the current database into a.sql (database-table_name.sql)
)
dai
  • 131
  • 1
  • 10
  • [This question](https://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database?rq=1) shows how to retrieve a list of tables from a database. – fvu Oct 10 '17 at 23:03
  • I'm unfamiliar with batch so I don't know how to implement it inside the loop – dai Oct 10 '17 at 23:04
  • Thanks. The relevant link has solved my problem. – dai Oct 13 '17 at 20:51

1 Answers1

2

You need to use mysqldump --tab which saves each table in a separate file. Actually, two files: one .sql file for the DDL and one .csv file for the data.

Then you can use mysqlimport --use-threads <n> to load the data from the csv files in a multi-threaded fashion.

See https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html and https://dev.mysql.com/doc/refman/5.7/en/mysqlimport.html for more details.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828