create mysql backups using shell script on ubuntu server

Question

I have a mysql server running on an ubuntu server. on the mysql server I have a database with a few schema and tables. I run a mysql_backup.sh file daily with a cron job to create backups of my mysql database. The mysql_backup.sh file has the script below. I checked and noticed that when the mysql_backup.sh file runs it seems to create a file that just has a few lines of general mysql function descriptions. I've included some example output that the mysql_backup.sh file creates below. Does anyone see what I'm doing wrong and can you suggest how to modify the mysql_backup.sh script so that it creates mysql backups of the database?

mysql_backup.sh

code:

#!/bin/bash
mysqldump -u root -p psswd --all-databases > /home/mysql_backups/backup_$(date +%F.%H%M%S).sql

example current output:

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

Bill Karwin · Accepted Answer · 2022-01-17 15:13:15Z

1

If you put the password on the command-line with the -p flag, you must have no space between the -p and the password. A -p flag with a space after it means it will prompt you for the password, not take it from the next argument. The next argument would be interpreted as the name of the schema to back up, but since you also used --all-databases, that confuses mysqldump.

To make the usage more clear, I suggest using the long flags like this:

#!/bin/bash
mysqldump --user=root --password=psswd --all-databases > /home/mysql_backups/backup_$(date +%F.%H%M%S).sql

Or better yet, don't put credentials on the command line at all. Put them into an options file.

answered Jan 17, 2022 at 15:13

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3476463 Over a year ago

thank you for getting back to me on this. I changed my script to "#!/bin/bash mysqldump --user=root --password=psswdd --all-databases >/home/mysql_backups/backup_$(date +%F.%H%M%S).sql" as you suggested but now it's just outputting a blank file. is there something else I've got wrong?

Bill Karwin Over a year ago

Sounds like yes, if it is giving no output. I guess your password is not literally "psswdd". If your password contains some kinds of special characters, then it may conflict with shell metacharacters. See stackoverflow.com/a/57171921/20860

user3476463 Over a year ago

thanks, yeah my password does have a ! and number. any suggestions on how to handle that?

Bill Karwin Over a year ago

Put it in single-quotes to protect it from shell expansion: --password='yadda!yadda'

user3476463 Over a year ago

thanks that did the trick!

Collectives™ on Stack Overflow

create mysql backups using shell script on ubuntu server

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related