#1  
Old 9th February 2007, 00:08
PortMan PortMan is offline
Member
 
Join Date: Jun 2006
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default mysql backup/reload question

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.
Reply With Quote
Sponsored Links
  #2  
Old 9th February 2007, 10:00
martinfst martinfst is offline
Senior Member
 
Join Date: Dec 2006
Location: Hilversum, The Netherlands
Posts: 880
Thanks: 1
Thanked 18 Times in 17 Posts
Send a message via MSN to martinfst Send a message via Skype™ to martinfst
 
Default

Quote:
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:
Code:
[mysqladmin]

user=db_user
password=user_password
Quote:
2. What would the script look like to be able to load the database from the command line to restore?
Code:
mysql -h localhost -u db_user -p database1 < database1.sql
Quote:
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):
Code:
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
Code:
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.
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MYSQL; setting/updating the root password question... rh-penguin HOWTO-Related Questions 2 30th January 2007 19:47
Virtual Mail (postfix, courier, mysql) on Debian question vertical98 HOWTO-Related Questions 5 10th December 2006 15:22
Messed up ISPConfig-2.2.8 Upgrade Morons Installation/Configuration 4 29th November 2006 13:17
Quick question about Postfix + MySQL asterix HOWTO-Related Questions 5 12th September 2006 17:45
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 17:04


All times are GMT +2. The time now is 13:29.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.