Bureaucrats, confirmed, Administrators
4,558
edits
Changes
no edit summary
When using MySQL, I always use a .my.cnf file to store my password so that I can switch to 'root' on the host, and execute whatever commands I need.<pre>[client]user=rootpassword=SuperSecretSauce</pre> == Backup Script ==Here's a quick recipe on using <code>mysqldump</code>
<code>cat ./backup.db.sh</code>
<source lang="bash">
ls -al $backup;
</source>
== Backup One-liner ==
For times when you need to enter a password
<source lang="bash">
db=MYDATABASE;
mysqldump -u db_user $db -p > ./tmp/dump-$(date +%F).$(hostname)-$db.sql
</source>
For all databases on a host
<source lang="bash">
mysql --execute="show databases" | awk '{print $1}' | grep -iv ^Database$ | sed 's/\(.*\)/mysqldump \1 > \1.'$(date +"%Y%m%d")'.sql/'
# Then just redo the command piped to sh
</source>
mysql $DB < $backup
</source>
Using process substitution and <code>zcat</code>, you don't even need to uncompress your gzipped backups first.
<source lang="bash">
mysql -p -u db_user db < <(zcat ./scheduled/eQualityTechnology-2015-03-15T23-11-50.mysql.gz)
</source>
[[Category:Database]]
[[Category:Bash]]