21 Dec 2015 / 1 min read

Backing up mysql databases into individual .sql files

Lately I found myself having to restore individual mysql databases, ideally using individual .sql files

> Archived article from 2015, from the old nutellino.it: commands and versions may be out of date.

> Machine translation from Italian: the original is here.

# archive# mysql

.archive-gate.is-locked .archive-gated{filter:none;pointer-events:auto;user-select:auto;max-height:none;-webkit-mask-image:none;mask-image:none}.archive-backdrop,.archive-dialog{display:none}

Lately I found myself having to restore individual mysql databases, ideally using individual .sql files

To do this, you need root credentials or a mysql user with the same permissions.

The command is the following:

bash

mysql -N -uroot -pPASSWORD -e 'show databases' | while read dbname; do mysqldump --single-transaction=TRUE -uroot -pPASSWORD --complete-insert "$dbname" > /backup/"$dbname"_`date +%Y%m%d`.sql; done

This way if we have, for example, the databases urano, giove and marte, we'll find their .sql counterparts in the /backup folder

🙂