Mysqldump: Difference between revisions
add single transaction option |
No edit summary |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
[[File:Database-mysql.svg|thumb]] | |||
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. | ||
< | <syntaxhighlight lang="bash"> | ||
file=~/.my.cnf | file=~/.my.cnf | ||
touch $file | touch $file | ||
| Line 10: | Line 11: | ||
EOF | EOF | ||
</ | </syntaxhighlight> | ||
== Backup Script == | == Backup Script == | ||
| Line 17: | Line 18: | ||
<code>cat ./backup.db.sh</code> | <code>cat ./backup.db.sh</code> | ||
< | <syntaxhighlight lang="bash"> | ||
#!/bin/sh | #!/bin/sh | ||
# @author Greg Rundlett <info@eQuality-Tech.com> | # @author Greg Rundlett <info@eQuality-Tech.com> | ||
| Line 58: | Line 59: | ||
# note that i is naked because $(( expression )) is arithmetic expansion in bash | # note that i is naked because $(( expression )) is arithmetic expansion in bash | ||
done | done | ||
if /usr/bin/mysqldump "$DB" > "$backup"; then | if /usr/bin/mysqldump --single-transaction "$DB" > "$backup"; then | ||
echo "backup created successfully" | echo "backup created successfully" | ||
ls -al "$backup"; | ls -al "$backup"; | ||
| Line 68: | Line 69: | ||
exit 1 | exit 1 | ||
fi | fi | ||
</ | </syntaxhighlight> | ||
== Backup One-liner == | == Backup One-liner == | ||
For times when you need to enter a password | For times when you need to enter a password | ||
< | <syntaxhighlight lang="bash"> | ||
db=MYDATABASE; | db=MYDATABASE; | ||
mysqldump -u db_user $db -p > ./tmp/dump-$(date +%F).$(hostname)-$db.sql | mysqldump -u db_user $db -p > ./tmp/dump-$(date +%F).$(hostname)-$db.sql | ||
</ | </syntaxhighlight> | ||
For all databases on a host | For all databases on a host | ||
< | <syntaxhighlight 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 --single-transaction \1 > \1.'$(date +"%Y%m%d")'.sql/' | ||
# Then just redo the command piped to sh | # Then just redo the command piped to sh | ||
</ | </syntaxhighlight> | ||
One of many ways to dump a database from one machine to another | One of many ways to dump a database from one machine to another | ||
< | <syntaxhighlight lang="bash"> | ||
sudo mysqldump mydb | gzip -c | ssh ubuntu@10.0.50.53 'cat > /home/ubuntu/mydb.dump.sql.gz' | sudo mysqldump --single-transaction mydb | gzip -c | ssh ubuntu@10.0.50.53 'cat > /home/ubuntu/mydb.dump.sql.gz' | ||
</ | </syntaxhighlight> | ||
== Restore == | == Restore == | ||
< | <syntaxhighlight lang="bash"> | ||
mysql $DB < $backup | mysql $DB < $backup | ||
</ | </syntaxhighlight> | ||
Using process substitution and <code>zcat</code>, you don't even need to uncompress your gzipped backups first. | Using process substitution and <code>zcat</code>, you don't even need to uncompress your gzipped backups first. | ||
< | <syntaxhighlight lang="bash"> | ||
mysql -p -u db_user db < <(zcat ./scheduled/eQualityTechnology-2015-03-15T23-11-50.mysql.gz) | mysql -p -u db_user db < <(zcat ./scheduled/eQualityTechnology-2015-03-15T23-11-50.mysql.gz) | ||
</ | </syntaxhighlight> | ||
[[Category:Database]] | [[Category:Database]] | ||
[[Category:Bash]] | [[Category:Bash]] | ||
[[Category:System Administration]] | [[Category:System Administration]] | ||