The following command will use mysqldump to create a dump of all available databases in the HOST that are available for the user USER.
The results will be found in a file that begins with the current date and will contain the hostname as part of the name.
USER="myUser"; PASS="myPASS"; HOST="myHost"; mysqldump -u "$USER" -p"$PASS" -h "$HOST" --all-databases > "`date +%F`-backup-all.$HOST.sql"
The user must have the LOCK TABLES privilege for the above command to work or else you will get the following error.
mysqldump: Got error: 1044: "Access denied for user 'USER'@'HOST' to database 'DBNAME'" when using LOCK TABLES
In case you cannot give the privilege to the user, then use the parameter --single-transaction to mitigate the problem. The command changes as follows.
USER="myUser"; PASS="myPASS"; HOST="myHost"; mysqldump -u "$USER" -p"$PASS" -h "$HOST" --single-transaction --all-databases > "`date +%F`-backup-all.$HOST.sql"
This post is also available in: Greek
