mysql backup/reload question

[PortMan]

I have FC6 and use the following script to backup a mysql database from the command line:

mysqldump -h localhost -u db_user -p database1 > database1.sql

I have a few questions:
1. Using this I have to enter the db_user password. Is there a way to have the password in the script so it will run automatically?

2. What would the script look like to be able to load the database from the command line to restore?

3. Is there a way to have "Drop Database..." added to the backup script?

Thanks.

[martinfst]

1. Using this I have to enter the db_user password. Is there a way to have the password in the script so it will run automatically?

Create a .my.cnf file in the home directory of the user, set permissions to 600 and add a section like:

[mysqladmin]

user=db_user
password=user_password

2. What would the script look like to be able to load the database from the command line to restore?

mysql -h localhost -u db_user -p database1 < database1.sql

3. Is there a way to have "Drop Database..." added to the backup script?

Not directly. You can only add the --add-drop-table to clear the tables. Otherwise you need to do a little (automated) editing of the output. Example to add this as the 1st line in your dump (untested):

sed -e '1i\drop database database1;' database1.sql > /tmp/database1.sql.$$ && mv /tmp/database1.sql.$$ database1.sql && rm /tmp/databas1.sql.$$

If you want to add the drop table as the last line, you could use

echo "drop table database1" >> database1.sql

Be aware that this totally uncontrolled and if something happens (e.g. the dump failed) you loose what you had. I'm not a fan of automatic deletions. You have been warned! Use at own risk.