Improved version mysqldump to back up MYSQL database _ MySQL

Improved version mysqldump to back up MYSQL database mysqldump

My backup scripts are executed in the early morning, and I often see this information in the slow query log: select * from table1;
I have been wondering before, and finally I learned that it was the ghost of MYSQLDUMP.
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
#
# Created by david yeung.
#
#20080707.
#
# Backup mysql's full data.
#
DBNAME = $1
BACKUPDIR =/home/David _yeung/backup_new
USERNAME = backup_user
Passswd = 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.