|
|
| (8 intermediate revisions by 2 users not shown) |
| Line 1: |
Line 1: |
| − | 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.
| + | quick recipe on using mysqldump |
| − | <source lang="bash">
| |
| − | file=~/.my.cnf
| |
| − | touch $file
| |
| − | chmod 600 $file
| |
| − | cat <<EOF >> $file
| |
| − | [client]
| |
| − | user=root
| |
| − | password=SuperSecretSauce
| |
| − | EOF
| |
| − | | |
| − | </source>
| |
| − | | |
| − | == Backup Script ==
| |
| − | Here's a quick recipe using <code>mysqldump</code>
| |
| | | | |
| − | | + | == Backup == |
| − | <code>cat ./backup.db.sh</code>
| |
| | <source lang="bash"> | | <source lang="bash"> |
| − | #!/bin/sh
| + | DB=wiki |
| − | # @author Greg Rundlett <info@eQuality-Tech.com>
| + | backupdir="$HOME/backups"; |
| − | # 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 name
| |
| − | DB=$1
| |
| − | if [ $# -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 DB
| |
| − | fi
| |
| − | # 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="/vagrant/mediawiki/backups";
| |
| − | backupdir="/backups"; | |
| | if [ ! -d "$backupdir" ]; then | | if [ ! -d "$backupdir" ]; then |
| − | mkdir -p "$backupdir"; | + | 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 sequence
| |
| − | backup="${backupdir}/dump-$(date +%F).$(hostname)-${DB}.01.sql";
| |
| − | # and we'll increment the counter in the filename if it already exists
| |
| − | i=1
| |
| − | filename=$(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 bash
| |
| − | done
| |
| − | if /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 | | fi |
| − | </source>
| + | backup="$backupdir/dump-$(hostname)-$DB.sql"; |
| − | | + | /usr/bin/mysqldump $DB > $backup; |
| − | | + | ls -al $backup; |
| − | == 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 --single-transaction \1 > \1.'$(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">
| |
| − | sudo mysqldump --single-transaction mydb | gzip -c | ssh ubuntu@10.0.50.53 'cat > /home/ubuntu/mydb.dump.sql.gz'
| |
| | </source> | | </source> |
| | | | |
| Line 93: |
Line 17: |
| | mysql $DB < $backup | | mysql $DB < $backup |
| | </source> | | </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]]
| |
