Improved Version mysqldump in Linux to back up MySQL Databases

Source: Internet
Author: User

Because mysqldump adds a global lock for the entire database.
If mysqldump is used for full-database backup, the following three factors may be affected.
1. The server CPU is severely blocked.
2. the disk I/O line is increased.
3. All queries become slow queries.
My current website database is about 5 GB and increases every day.
The table structure is a mixture of MyISAM, InnoDB, and memory.
Therefore, it may be difficult to simply use the hotcopy tool. So today I just changed my last script about using OUTFILE to back up MySQL.
The three shortcomings mentioned above can be solved.

1. Backup script content

[David _yeung @ localhost ~] $ Cat fast_backup
#! /Bin/sh
#< br> # created by David Yeung.
#< BR ># 20080707.
# backup MySQL's full data.
#
dbname = $1
backupdir =/home/David _yeung/backup_new
username = backup_user
passwd = 123456
tarname =" $ backupdir "/backup" $1 "'date' + % Y % m % d'
# add your own database name here.
case "$1" in
my_site);
*) Exit;
esac
# Get all the tables 'name.
num = '/usr/local/MySQL/bin/MySQL-U $ username-p $ passwd-S-VV-e "show tables"-d $ dbname | WC -l'
headnum = 'expr $ {num}-3'
tailnum = 'expr $ {num}-7'
arr1 = '/usr/ local/MySQL/bin/MySQL-U $ username-p $ passwd-S-VV-e "show tables"-d $ dbname | head-n "$ headnum" | tail- N "$ tailnum" '
arr2 = ($ arr1)
I = 0
while ["$ I"-lt "$ {# arr2 [@]}"]
DO
tmpfilename =$ {arr2 [$ I]}
# The real dump process.
/usr/local/MySQL/bin/mysqldump-U $ username-P "$ passwd" "$ dbname" "$ tmpfilename"> "$ tarname"
let "I ++"
done

2. Because we have been using stored procedures, we have to back up them separately.

[David _yeung @ localhost ~] $ Cat fast_sp
#! /Bin/sh
# Created by David Yeung 20080122.
#
# Backup site's routine.
Tarname =/home/David _yeung/backup_new/spbackup "$1" 'date' + % Y % m % d''
/Usr/local/MySQL/bin/mysqldump-ubackup_user-p123456-n-t-d-r my_site> "$ tarname"

3. Drop it into the scheduled task.

[Root @ localhost backup_new] # crontab-l
0 01 ***/home/David _yeung/fast_backup my_site
0 0 ** 5/home/David _yeung/fast_sp

Data is backed up at every day and stored every Friday morning.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.