Menu
I try to backup my database with mysqldump and cronjobs.
Well, I added the following command to the crontab of user root:
*/30 * * * * mysqldump -u root -pVERYSECUREPASSWORD --all-databases > /var/www/cloud/dump_komplett.sql &> /dev/null
This works fine so far, but the problem is that the password is set in this command.
So I want to include a .database.cnf file that look like this
[mysqldump]
user=root
password=VERYSECUREPASSWORD
and changed the mysqldump command to
mysqldump --defaults-extra-file="/var/crons/mysql/.database.cnf" --all-databases -u root > /var/www/cloud/dump_komplett.sql
to solve this problem.
But this command fails with the error:
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
I don't know what's wrong.
Here are some commands I also tried:
mysqldump --defaults-extra-file="/var/crons/mysql/.database.cnf" --all-databases > /var/www/cloud/dump_komplett.sql
mysqldump --defaults-file="/var/crons/mysql/.database.cnf" --all-databases > /var/www/cloud/dump_komplett.sql
mysqldump --defaults-file="/var/crons/mysql/.database.cnf" --all-databases -u root > /var/www/cloud/dump_komplett.sql
and .database.cnf contents I also tried:
[client]
user=root
password=VERYSECUREPASSWORD
[mysqldump]
host=localhost
user=root
password=VERYSECUREPASSWORD
[client]
host=localhost
user=root
password=VERYSECUREPASSWORD
561
By default, mysqldump locks all the tables it's about to dump. This ensure the data is in a consistent state during the dump.
If mysqldump is not identified by the cmd prompt that means it cannot recognize where the mysqldump.exe is located. You need to add path of the directory where the exe is located in the PATH variable under environment variables. After doing that your command will start working in the cmd prompt.
mysqldump can retrieve and dump table contents row by row, or it can retrieve the entire content from a table and buffer it in memory before dumping it. Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the --quick option (or --opt , which enables --quick ).
To backup multiple MySQL databases with one command you need to use the --database option followed by the list of databases you want to backup. Each database name must be separated by space. The command above will create a dump file containing both databases.
The user has to be specified in the command and not in the file with the u parameter.
For more details on scheduling cron jobs using mysqldump, check this answer
53
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
