Difference between revisions of "Mysqldump"

From Freephile Wiki
Jump to navigation Jump to search
VisualWikitext
(add single-transaction switch)
(5 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.
 
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">
+
<pre>
−
file=~/.my.cnf
 
−
touch $file
 
−
chmod 600 $file
 
−
cat <<EOF >> $file
 
 
[client]
 
[client]
 
user=root
 
user=root
 
password=SuperSecretSauce
 
password=SuperSecretSauce
−
EOF
+
</pre>
−
 
 
−
</source>
 
  
 
== Backup Script ==
 
== Backup Script ==
Line 19: Line 13:
 
<source lang="bash">
 
<source lang="bash">
 
#!/bin/sh
 
#!/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
+
DB=wiki
−
# to the script for playbook / cron / non-interactive use
+
backupdir="$HOME/backups";
−
# 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
 
fi
−
 
+
backup="$backupdir/dump-$(date +%F).$(hostname)-$DB.sql";
−
# shell parameter expansion
+
# increment the filename if it already exists
−
# see http://www.gnu.org/software/bash/manual/html_node/Shell-Parameter-Expansion.html
+
# http://www.gnu.org/software/bash/manual/html_node/Shell-Parameter-Expansion.html#Shell-Parameter-Expansion
−
# 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
 
i=1
−
filename=$(basename "$backup") # foo.txt (basename is everything after the last slash)
+
filename=$(basename "$backup") # foo.txt
−
extension=${filename##*.}             # .txt (filename with the longest matching pattern of *. being deleted)
+
extension=${filename##*.}     # .txt
−
file=${filename%.*}                         # foo (filename with the shortest matching pattern of .* deleted)
+
file=${filename%.*}           # foo
−
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
 
while [ -f $backup ]; do
−
   backup="$backupdir/${file}.$(printf '%.2d' $(( i+1 ))).${extension}"
+
   backup="$backupdir/${file}.${i}.${extension}"
−
   i=$(( i+1 )) # increments $i  
+
   i=$(( i+1 ))   # increments $i
−
  # note that i is naked because $(( expression )) is arithmetic expansion in bash
 
 
done
 
done
−
if /usr/bin/mysqldump --single-transaction "$DB" > "$backup"; then
+
/usr/bin/mysqldump $DB > $backup;
−
  echo "backup created successfully"
+
ls -al $backup;
−
  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
 
 
</source>
 
</source>
  
Line 80: Line 45:
 
For all databases on a host
 
For all databases on a host
 
<source lang="bash">
 
<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/'
+
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
 
# 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 99: Line 59:
  
 
</source>
 
</source>
 +
  
 
[[Category:Database]]
 
[[Category:Database]]
 
[[Category:Bash]]
 
[[Category:Bash]]
−
[[Category:System Administration]]
 

Revision as of 07:20, 8 April 2015

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. 

[client]
user=root
password=SuperSecretSauce

Backup Script[edit | edit source]

Here's a quick recipe using mysqldump


cat ./backup.db.sh 

#!/bin/sh

DB=wiki
backupdir="$HOME/backups";
if [ ! -d "$backupdir" ]; then
  mkdir -p $backupdir;
fi
backup="$backupdir/dump-$(date +%F).$(hostname)-$DB.sql";
# increment the filename if it already exists
# http://www.gnu.org/software/bash/manual/html_node/Shell-Parameter-Expansion.html#Shell-Parameter-Expansion
i=1
filename=$(basename "$backup") # foo.txt
extension=${filename##*.}      # .txt
file=${filename%.*}            # foo
while [ -f $backup ]; do
  backup="$backupdir/${file}.${i}.${extension}"
  i=$(( i+1 ))   # increments $i
done
/usr/bin/mysqldump $DB > $backup;
ls -al $backup;


Backup One-liner[edit | edit source]

For times when you need to enter a password 

db=MYDATABASE;
mysqldump -u db_user $db -p > ./tmp/dump-$(date +%F).$(hostname)-$db.sql

For all databases on a host 

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

Restore[edit | edit source]

mysql $DB < $backup

Using process substitution and zcat, you don't even need to uncompress your gzipped backups first. 

mysql -p -u db_user db < <(zcat ./scheduled/eQualityTechnology-2015-03-15T23-11-50.mysql.gz)
Retrieved from "https://wiki.freephile.org/wiki/index.php?title=Mysqldump&oldid=5442"