mysql backup/reload question

Discussion in 'Installation/Configuration' started by PortMan, Feb 8, 2007.

  1. PortMan

    PortMan New Member

    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?

  2. martinfst

    martinfst ISPConfig Developer ISPConfig Developer

    Create a .my.cnf file in the home directory of the user, set permissions to 600 and add a section like:
    mysql -h localhost -u db_user -p database1 < database1.sql
    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.

Share This Page