mysqldump

[paweltech]

good morning,
with mysql I have to manage several databases and I want to do the scheduled backup with crontab
Using the backup command for all databases:

mysqldump -u root -p [password_user_root] --all-databases | gzip> /var/www/example.com/backupsql/wp_`date + \% F _ \% T`.sql.gz

I have no problems.
But if instead I wanted to backup the single database:

mysqldump -u root -p [password_user_root] --databases wp | gzip> /var/www/example.com/backupsql/wp_`date + \% F _ \% T`.sql.gz

I get the error

[TheFu]

https://ubuntuforums.org/showthread....7#post13802377
is how I do it. Notice that the password is read from a file, not in the command options which any userid can see in the process table. Also, that the full path to any programs are spelled out. Never trust the PATH in any script, especially one started from cron.

There a many other solutions too.

[LHammonds]

Just want to +1 what TheFu said about placing the password in clear view as part of the command...which is exposed in your command history and the process list. You can also store the password in my.cnf which will process the credentials automatically.

When scheduling in crontab, always specify the full path to everything (including the programs) and never use relative paths...unless you are explicitly specifying the path environment at the beginning.

You are probably just showing us the crux of the problem and what you are doing is more complicated than just scheduling that one command but in case it is not, I'd recommend calling a script from crontab and placing your commands inside that and including error-catching, logging and notifications so the backups can be more robust and let you know if things go sideways or maybe even self-correct if you plan ahead for certain scenarios (low disk space, write permissions, unmounted targets, etc.)

LHammonds

[paweltech]

You are probably just showing us the crux of the problem and what you are doing is more complicated than just scheduling that one command ...

LHammonds

Exact
Leaving aside security, I would like to understand why copying all the databases I have no problem, but if with the same command I copied only a single database, I get an error
So I ask you, what's wrong with mysqldump for single database?

I thank you for the precious support you are giving me!!!

[EuclideanCoffee]

Nothing is wrong for mysqldump single database. I do this routinely. Without the error message however, I cannot debug.

[paweltech]

Nothing is wrong for mysqldump single database. I do this routinely. Without the error message however, I cannot debug.

I try to explain myself better:
With the option for single databases I received the error:

root@linux:/home/paolo# mysqldump -u root -p [password_user_root] --databases wp | gzip > /var/www/example.com/backupsql/wp_`date +\%F_\%T`.sql.gz
Enter password:
mysqldump: Got error: 1049: Unknown database '[password_user_root]' when selecting the database

With the option for all databases the password is not required and the dump is performed correctly:

root@linux:/home/paolo# mysqldump -u root -p [password_user_root] --all-databases | gzip > /var/www/example.com/backupsql/wp_`date +\%F_\%T`.sql.gz
root@linux:/home/paolo#

[SeijiSensei]

I've never used the "--databases" switch, though I usually prefer to use --all-databases. Try

mysqldump -u root -p [password_user_root] wp | gzip> /var/www/example.com/backupsql/wp_`date + \% F _ \% T`.sql.gz

[paweltech]

Using the --all-databases option, in case you want to restore a single db, how should I operate ?

[spjackson]

I try to explain myself better:
With the option for single databases I received the error:

root@linux:/home/paolo# mysqldump -u root -p [password_user_root] --databases wp | gzip > /var/www/example.com/backupsql/wp_`date +\%F_\%T`.sql.gz
Enter password:
mysqldump: Got error: 1049: Unknown database '[password_user_root]' when selecting the database

With the option for all databases the password is not required and the dump is performed correctly:

root@linux:/home/paolo# mysqldump -u root -p [password_user_root] --all-databases | gzip > /var/www/example.com/backupsql/wp_`date +\%F_\%T`.sql.gz
root@linux:/home/paolo#

The correct syntax is "-p[password_user_root]" without a space. (As already said, it's not a good idea to do this, but if you are going to do it anyway you need the correct syntax.) This is also the case when using --all-databases, so I cannot explain how it works there. That format (with a space between -p and the password) does not work for me with --all-databases.

Also, the extra spaces in the date command arguments do not work either.

$ date + \% F _ \% T
date: extra operand ‘%’
Try 'date --help' for more information.
$ date +\%F_\%T
2020-02-25_13:54:21

[SeijiSensei]

Using the --all-databases option, in case you want to restore a single db, how should I operate ?

Each database will begin with a "-- Current Database" comment and a CREATE DATABASE command like this:

--
-- Current Database: `anklaw`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `anklaw` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `anklaw`;

Find the one that corresponds to the database you're trying to recover. Copy from there to the next entry, paste it into a file, then run it from the command prompt using the mysql client.