Dumping databases for all accounts on a server

Forum Moderators: open

Message Too Old, No Replies

Dumping databases for all accounts on a server

csdude55

5:01 am on Aug 12, 2021 (gmt 0)

I've been using this to back up MySQL one account at a time:

# mysqldump --user=[username] --password=[password] --single-transaction --quick --lock-tables=false [database name] > /backup/[database name].sql

So far so good, it seems to keep the database functional while it's being backed up so no down time :-)

The question now is, is there a way to go through every account on the server and dump all of the MySQL files in to respective .SQL files? Meaning, I don't want all of them dumped in to one huge single file... if there are 50 databases then I want 50 .SQL files, each named the same as the database.

LifeinAsia

7:18 pm on Aug 12, 2021 (gmt 0)

What you've got there is what you need- it should already be doing individual files for each database. All you need to do is create one script file to do them all at once.

If you're asking how to do that part, something like the following should work:

SELECT CONCAT('mysqldump --user=[username] --password=[password] --single-transaction --quick --lock-tables=false ',schema_name,' > /backup/',schema_name,'.sql')
FROMinformation_schema.schemata
WHEREschema_name NOT IN ('information_schema','mysql','performance_schema','sys');

Copy/paste the output then run the script.

csdude55

7:36 pm on Aug 12, 2021 (gmt 0)

FROM information_schema.schemata

Ohhh, THAT's where the names of all of the databases are hiding! Nice, that's exactly what I was looking for :-)

I was hoping for a way to do it via SSH instead of running it through PHP, but this will work fine. Thanks!