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.<source lang="bash">file=~/.my.cnftouch $filechmod 600 $filecat <<EOF >> $file[client]user=rootpassword=SuperSecretSauceEOF </source> == Backup Script ==Here's a quick recipe on using <code>mysqldump</code>
== Backup ==<code>cat ./backup.db.sh</code>
<source lang="bash">
#!/bin/sh# @author Greg Rundlett <info@eQuality-Tech.com># This is a quick shell script to create a sql dump of your database.# You may need to adjust the path of mysqldump, # or sudo apt-get install mysqldump if it doesn't exist # We'll make it so you can pass the database name as the first parameter # to the script for playbook / cron / non-interactive use# If no parameter is passed, we'll prompt you for the nameDB=$1if [ $# -ne 1 ]; then echo "Here are the current databases on the server" mysql -u root --batch --skip-column-names -e 'show databases;' echo "Enter the name of the database you want to backup" read DBfi# If on a Virtual Machine, use a location that is exported to the host, # so that our backups are accessible even if the virtual machine is no longer accessible.# backupdir=wiki"/vagrant/mediawiki/backups";backupdir="$HOME/backups";
if [ ! -d "$backupdir" ]; then
mkdir -p "$backupdir";fi # shell parameter expansion # see http://www.gnu.org/software/bash/manual/html_node/Shell-Parameter-Expansion.html# we'll start with a default backup file named '01' in the sequencebackup="${backupdir}/dump-$(date +%F).$(hostname)-${DB}.01.sql";# and we'll increment the counter in the filename if it already existsi=1filename=$(basename "$backup") # foo.txt (basename is everything after the last slash)extension=${filename##*.} # .txt (filename with the longest matching pattern of *. being deleted)file=${filename%.*} # foo (filename with the shortest matching pattern of .* deleted)file=${file%.*} # repeat the strip to get rid of the counter# file=${filename%.{00..99}.$extension} # foo (filename with the shortest matching pattern of .[01-99].* deleted)while [ -f $backup ]; do backup="$backupdir/${file}.$(printf '%.2d' $(( i+1 ))).${extension}" i=$(( i+1 )) # increments $i # note that i is naked because $(( expression )) is arithmetic expansion in bashdoneif /usr/bin/mysqldump --single-transaction "$DB" > "$backup"; then echo "backup created successfully" ls -al "$backup"; echo "A command such as" echo "mysql -u root $DB < $backup" echo "will restore the database from the chosen sql dump file"else echo "ERROR: Something went wrong with the backup" exit 1
fi
backup</source> == Backup One-liner ==For times when you need to enter a password<source lang="bash">db=MYDATABASE;mysqldump -u db_user $backupdirdb -p > ./tmp/dump-$(date +%F).$(hostname)-$DBdb.sql</source> For all databases on a host<source lang=";bash">mysql --execute="show databases" | awk '{print $1}' | grep -iv ^Database$ | sed 's/usr/bin\(.*\)/mysqldump --single-transaction \1 > \1.'$DB (date +"%Y%m%d")'.sql/'# Then just redo the command piped to sh</source> One of many ways to dump a database from one machine to another<source lang="bash"> $backup;ls sudo mysqldump --single-transaction mydb | gzip -al $backup;c | ssh ubuntu@10.0.50.53 'cat > /home/ubuntu/mydb.dump.sql.gz'
</source>
<source lang="bash">
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]]
[[Category:System Administration]]