用outfile的方式備份所有table資料

table schema需另外備份
MySQL Store Procedure：


DELIMITER $$

DROP PROCEDURE IF EXISTS `DBName`.`sp_u_backupdata` $$
CREATE DEFINER=`peijuan`@`%` PROCEDURE `sp_u_backupdata`()
BEGIN

DECLARE done INT DEFAULT 0;
Declare DBNAME VARCHAR(20);
Declare TABLENAME VARCHAR(20); /* table name */
Declare MD VARCHAR(4);   /* date-mmdd */
Declare out_file VARCHAR(100);
Declare dir VARCHAR(100);
Declare outdir VARCHAR(200);

Declare cur CURSOR FOR
SELECT table_schema, table_name FROM information_schema.`TABLES` where table_schema not in ('information_schema','mysql','test');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SELECT DATE_FORMAT(now(),'%m%d') INTO MD;  /* mmdd */

SET dir = concat('/data/mysql_dump/',MD,'/');  /* /data/mysql_dump/mmdd/  */

OPEN cur;

REPEAT
FETCH cur INTO DBNAME,TABLENAME;

IF NOT done THEN

SET out_file = concat(DBNAME,'.',TABLENAME);  /* db.table */

SET outdir = concat(dir,out_file,'.txt'); /* /data/mysql_dump/mmdd/xxx.xxx.txt */

/* out file data */
SET @dyn_sql = concat('select * into outfile''',outdir,'''',
'fields terminated by ''<|&|>''',
'lines terminated by ''<|^|>''',
'from ',out_file);

PREPARE s1 from @dyn_sql;

EXECUTE s1;

END IF;
UNTIL done END REPEAT;

CLOSE cur;

END $$
DELIMITER ;

SH：

#!/bin/sh

WORKDIR='/data/mysql_dump/'`date '+%m%d'`
NOW=`date '+%m%d'`

#若資料夾不存在，則建立資料夾 -p指不出現錯誤訊息 -m770指給770的權限
test -d $WORKDIR || mkdir -p -m770 $WORKDIR

#呼叫store procedure
/usr/local/bin/mysql -uxxx -pxxx -e "CALL DBName.storeprocedure_name()"

cd /data/mysql_dump/

#壓縮資料
tar pzcf $NOW.tar.gz $NOW

#刪除資料
rm -rf $NOW