使用xtrabackup備份mysql數據庫

本文主要記錄下使用percona xtrabackup 2.4來備份mysql數據。最終效果是實現mysql所在主機定時進行全量備份和增量備份並自動刪除舊備份文件。另有一台數據存儲機,定時通過ssh抓取各個mysql主機上的備份文件,保存更長時間後刪除舊文件。

用到的軟件和技術有:xtrabackup(免費的mysql備份程序),qpress(xtrabackup壓縮需要的依賴,據說壓縮比很高),python3.5(ssh的遠程抓取,因為我不會bash)。

mkdir -p /root/42/script
cd /root/42/script
#安裝qpress
wget http://www.quicklz.com/qpress-11-linux-x64.tar
tar xf qpress-11-linux-x64.tar
mv qpress /usr/local/bin/
#centos6安裝xtrabackup
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
yum install libev -y
yum install percona-xtrabackup-24 -y
#新建backup.sh(來源未知)內容如下:
#!/bin/bash

PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
 
BACKUP_BASE_DIR="/data/backup/xtrabackup"
INC_BASE_LIST="${BACKUP_BASE_DIR}/inc_list.txt"
XTRABACKUP_PATH="/usr/bin/innobackupex"
 
MYSQL_CNF="/etc/my.cnf"
MYSQL_HOSTNAME=127.0.0.1
MYSQL_USERNAME=root
MYSQL_PASSWORD=""
 
LOCK_FILE="/tmp/innobackupex.lock"
THREAD=3
 
mkdir -p ${BACKUP_BASE_DIR}
CURRENT_BACKUP_PATH="${BACKUP_BASE_DIR}/$(date +%F_%H-%M)"
[[ -d ${CURRENT_BACKUP_PATH} ]] && CURRENT_BACKUP_PATH="${BACKUP_BASE_DIR}/$(date +%F_%H-%M-%S)"
 
print_help(){
    echo "--------------------------------------------------------------"
    echo "Usage: $0 full | inc | help               "
    echo "--------------------------------------------------------------"
    exit 1
}
 
[[ $# -lt 1 || "$1" == "help" ]] && print_help
 
[[ -f "$LOCK_FILE" ]] && echo -e "Usage: rm -f $LOCK_FILE\nUsage: chattr -i $LOCK_FILE && rm -f $LOCK_FILE" && exit 1
 
FullBackup(){
    touch $LOCK_FILE
    chattr +i $LOCK_FILE
    local rc=0
    ${XTRABACKUP_PATH} \
    --defaults-file=${MYSQL_CNF} \
    --user=${MYSQL_USERNAME} \
    --password=${MYSQL_PASSWORD} \
    --host=${MYSQL_HOSTNAME} \
    --compress \
    --compress-threads=${THREAD} \
    --parallel=${THREAD} \
    --no-timestamp ${CURRENT_BACKUP_PATH} > ${CURRENT_BACKUP_PATH}_full.log 2>&1
    grep ".*\ completed\ OK\!" ${CURRENT_BACKUP_PATH}_full.log > /dev/null 2>&1
    if [ $? -ne 0 ];then
        rc=1
        [[ -d ${CURRENT_BACKUP_PATH} && $(pwd) != "/" ]] && rm -rf ${CURRENT_BACKUP_PATH}
    else
        echo "NULL|${CURRENT_BACKUP_PATH}|full" >> ${INC_BASE_LIST}
        [[ -d ${CURRENT_BACKUP_PATH} && $(pwd) != "/" ]] && chattr +i ${CURRENT_BACKUP_PATH} || rc=1
    fi
    chattr -i ${LOCK_FILE}
    rm -f $LOCK_FILE
    chattr +a ${INC_BASE_LIST}
    return $rc
}
 
IncBackup(){
    touch $LOCK_FILE
    chattr +i $LOCK_FILE
    local rc=0
    PREV_BACKUP_DIR=$(sed '/^$/d' ${INC_BASE_LIST} | tail -1 | awk -F '|' '{print $2}')
    ${XTRABACKUP_PATH} \
    --defaults-file=${MYSQL_CNF} \
    --user=${MYSQL_USERNAME} \
    --password=${MYSQL_PASSWORD} \
    --host=${MYSQL_HOSTNAME} \
    --compress \
    --compress-threads=${THREAD} \
    --no-timestamp --incremental ${CURRENT_BACKUP_PATH} \
    --incremental-basedir=${PREV_BACKUP_DIR} > ${CURRENT_BACKUP_PATH}_inc.log 2>&1
    grep ".*\ completed\ OK\!" ${CURRENT_BACKUP_PATH}_inc.log > /dev/null 2>&1
    if [ $? -ne 0 ];then
        rc=1
        [[ -d ${CURRENT_BACKUP_PATH} && $(pwd) != "/" ]] && rm -rf ${CURRENT_BACKUP_PATH}
    else
        echo "${PREV_BACKUP_DIR}|${CURRENT_BACKUP_PATH}|inc" >> ${INC_BASE_LIST}
        [[ -d ${CURRENT_BACKUP_PATH} && $(pwd) != "/" ]] && chattr +i ${CURRENT_BACKUP_PATH} || rc=1
    fi
    chattr -i ${LOCK_FILE}
    rm -f $LOCK_FILE
    chattr +a ${INC_BASE_LIST}
    return $rc
}
 
## 全量備份
if [ "$1" == "full" ];then
    FullBackup
fi
 
## 增量備份
if [ "$1" == "inc" ];then
    ## 若全量備份不存在,則執行全量備份
    if [[ ! -f ${INC_BASE_LIST} || $(sed '/^$/d' ${INC_BASE_LIST} | wc -l) -eq 0 ]];then
        FullBackup
    else
        IncBackup
    fi
fi
 
## 刪除22天前的備份
if [[ -d ${BACKUP_BASE_DIR} && $(pwd) != "/" ]];then
    find ${BACKUP_BASE_DIR} -name "$(date -d '22 days ago' +'%F')_*" | xargs chattr -i
    find ${BACKUP_BASE_DIR} -name "$(date -d '22 days ago' +'%F')_*" | xargs rm -rf
fi

#可能需要修改backup.sh中的如下參數
BACKUP_BASE_DIR="/data/backup/xtrabackup"
XTRABACKUP_PATH="/usr/bin/innobackupex"
 
MYSQL_CNF="/etc/my.cnf"
MYSQL_HOSTNAME=127.0.0.1
MYSQL_USERNAME=root
MYSQL_PASSWORD=""
#給腳本添加執行權限
chmod 755 /root/42/script/backup.sh
#执行一次全量备份
/root/42/script/backup.sh full
#如果沒有錯誤信息,就可以看到BACKUP_BASE_DIR下生成了備份文件
#檢查下cron是否啟動
service crond status
#如果未啟動則將cron設置為開機啟動並手動啟動下
chkconfig crond on
service crond start
#添加定時任務
crontab -e
#在底部新增內容
## 每周六凌晨3:30一次全量备份
## 每周二、四、日的凌晨3:30点执行增量备份
30 3 * * 6 /root/42/script/backup.sh full
30 3 * * 2,4,7 /root/42/script/backup.sh inc

mysql tricks

查看數據庫中各表的大小

SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,
TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2), 'MB') 
as data FROM TABLES WHERE TABLE_SCHEMA='dbname';

修改數據庫表文件的儲存位置(詳細步驟)

#登入數據庫
mysql -u root -p
#查看數據庫的存放位置,即datadir的值
show variables like '%dir%';
#退出數據庫
quit
#停止mysql,失敗的話用root權限
service mysqld stop
#創建新的數據儲存位置
mkdir /data/mysql
#將舊文件移動到新位置,第一個參數是前面datadir的值
mv /usr/local/mysql/data/* /data/mysql
#修改新位置的權限
chown mysql:mysql -R /data/mysql/
#修改數據庫配置,修改這一行datadir=/data/mysql
nano /etc/my.cnf
##修改數據庫配置,修改這一行datadir=/data/mysql
##這個我並沒有找到,所以不修改應該也可以
##nano /etc/init.d/mysqld
#啟動數據庫服務
service mysqld start

參考鏈接:mysql数据库目录存放位置更改

導入與導出mysql數據庫

#導出數據庫,前面是mysql的程序位置
/data/soft/mysql-5.5.30/client/mysqldump -u  root -p video > db_video.sql
#壓縮一下便於傳輸
tar -zcvf db_video.tar.gz db_video.sql
#導入
mysql -u zeng -p -h localhost video < db_video.sql

新增用戶及設置權限

转载自:MySQL新增用户以及数据库访问授权

mysql -u root -p
# 允许本地 IP 访问 localhost, 127.0.0.1
CREATE USER 'user42'@'localhost' IDENTIFIED BY '42@#42';
# 允许外网 IP 访问
CREATE USER 'user42'@'%' IDENTIFIED BY '42@#42';
# 刷新授权
flush privileges;
# 创建数据库
create database db42 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
# 授予用户通过外网IP对于该数据库的全部权限
GRANT ALL ON db42.* TO 'user42'@'%';
# 刷新权限
flush privileges;
# 退出 root 重新登录
\q
# 已新帐号 zhouz 登录,由于使用的是 % 任意IP连接,所以需要指定外部访问IP
mysql -u user42 -h 192.168.1.168 -p

新建一個庫一張表

CREATE DATABASE `djangoTask` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE TABLE `bonus` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userid` int(10) NOT NULL,
  `bonus` int(10) DEFAULT NULL COMMENT '奖励金',
  `level` int(3) DEFAULT NULL COMMENT '等级',
  `nowTimestamp` int(13) DEFAULT NULL COMMENT '插入时间戳',
  `nowDateTime` datetime DEFAULT NULL COMMENT '插入时间',
  `updateInfo` varchar(128) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=66156 DEFAULT CHARSET=utf8 COMMENT='bonus';

對某個時間段內某個值出現的次數做統計

SELECT info, count(info) FROM aso.aso_apple 
where nowTimestamp>'1489161600' GROUP BY info;

對某個時間段內的數據按天展示統計

SELECT COUNT(id) as 'sum', DATE_FORMAT(nowDatetime, '%Y-%m-%d') as 'day'
FROM aso.aso_apple WHERE nowTimestamp>'1488643200'
GROUP BY DATE_FORMAT(nowDatetime, '%Y-%m-%d');

使用inner join查看關聯數據

SELECT * FROM test.t_account a 
INNER JOIN test.t_client b 
ON a.cId = b.id WHERE b.sn like '%sz%';

刪除inner join查詢到的關聯數據

DELETE a.* FROM test.t_account a
INNER JOIN test.t_client b ON a.cId = b.id
WHERE (b.sn like '%sz%');

mysql添加一列

ALTER TABLE `locTest` ADD `user_id` INT NOT NULL AFTER `ID`

在ID列后添加非空新列user_id。用的INT数据类型,由于设置非空,之前的行会自动设置user_id为“0”。