mysql_step_by_step

mhabbyo

information_schema.tables  中含有所有的表 集中操作的时候从这里面取  group_contract函数很有用

select concat('drop table ',TABLE_SCHEMA,'.',table_name,'; ') from information_schema.tables where
table_schema='test';

mhabbyo

这个可怕的问题还没遇到过


在使用mysqldump恢复数据时遇到错误：
数据总量是15G的sql文件，mysql版本5.5.10，存储引擎innodb
问题的解决方法：

在my.ini或my.cnf中增加(或增大)
[mysqld]
max_allowed_packet = 16M

还需要重启数据库服务器

mhabbyo

加快 dump  的参数

MySQL导出的SQL语句在导入时有可能会非常非常慢，经历过导入仅12G大小的sql备份文件，竟用了近3个小时。在导出时合理使用几个参数，可以大大加快导入的速度。

-e 使用包括几个VALUES列表的多行INSERT语法;
--max_allowed_packet=XXX 客户端/服务器之间通信的缓存区的最大大小;
--net_buffer_length=XXX  TCP/IP和套接字通信缓冲区大小,创建长度达net_buffer_length的行。

mysql>show variables like 'max_allowed_packet'; --最好在16M以上
mysql>show variables like 'net_buffer_length';  --最好在1M以上

-- mysqldump导出备份的时候，后面跟随参数
mysqldump --max_allowed_packet=100M --net_buffer_length=16777216 -u xxxx -pxxxx test test_1 > test_1.sql

mhabbyo

分页查询的优化

再比如说在文章系统里分页显示的时候，一般的查询是这样的：
    SELECT id, title, content FROM article ORDER BY created DESC LIMIT 10000, 10;
   通常这样的查询会把索引建在created字段（其中id是主键），不过当LIMIT偏移很大时，查询效率仍然很低，改变一下查询：
   SELECT id, title, content FROM article
   INNER JOIN (
          SELECT id FROM article ORDER BY created DESC LIMIT 10000, 10
    ) AS page USING(id)

mhabbyo

Xtrabackup  拥有较快的备份速度，他会生成自己的日志来记录备份的位置

需要注意的是  在恢复前，一定要在备份的机器上进行 apply log，否则原有未提交的事务不能提交，导致恢复或者主从搭建的时候报错



下面是我改编的一个本分脚本，一周一全备，每天进行增量备份的脚本


#!/bin/bash
BEGINTIME=`date +"%Y-%m-%d %H:%M:%S"`
format_time=`date +"%Y-%m-%d_%H:%M:%S"`
port=3306
ip=127.0.0.1
backdir=/data/databackup/backup
file_cnf=/etc/my.cnf
user_name=root
password=password
out_log=$backdir/Xtrabackup_log_$format_time
time_cost=$backdir/Xtrabackup_time.txt


if [ -d "$backdir/rec5" ];then
        mv $backdir /backup/bak_eccoredb_$(date +%F_%H_%M_%S)
        mkdir $backdir
fi

#full
if [ ! -d "$backdir/full" ];then
        echo "#####start full backup at $BEGINTIME to directory full" >>$time_cost
        innobackupex --defaults-file=$file_cnf --socket=/data/mysql3306/mysql.sock --parallel=5 --no-timestamp --user=$user_name --password=$password   --host=$ip --port=$port  $backdir/full 1> $out_log 2>&1
        exit;
        elif [ ! -d "$backdir/rec0" ];then
        echo "#####start 0 incremental backup at $BEGINTIME to directory rec0" >>$time_cost
        innobackupex --defaults-file=$file_cnf --socket=/data/mysql3306/mysql.sock --parallel=5 --no-timestamp --user=$user_name --password=$password  --host=$ip --port=$port --incremental --incremental-basedir=$backdir/full $backdir/rec0 1>$out_log 2>&1
        elif [ ! -d "$backdir/rec1" ];then
        echo "#####start 1 incremental backup at $BEGINTIME to directory rec1" >>$time_cost
        innobackupex --defaults-file=$file_cnf --socket=/data/mysql3306/mysql.sock --parallel=5 --no-timestamp --user=$user_name --password=$password  --host=$ip --port=$port --incremental --incremental-basedir=$backdir/rec0 $backdir/rec1 1>$out_log 2>&1
        exit;
        elif [ ! -d "$backdir/rec2" ];then
        echo "#####start 2 incremental backup at $BEGINTIME to directory rec2" >>$time_cost
        innobackupex --defaults-file=$file_cnf --socket=/data/mysql3306/mysql.sock --parallel=5 --no-timestamp --user=$user_name --password=$password  --host=$ip --port=$port --incremental --incremental-basedir=$backdir/rec1 $backdir/rec2 1>$out_log 2>&1
        exit;
        elif [ ! -d "$backdir/rec3" ];then
        echo "#####start 3 incremental backup at $BEGINTIME to directory rec3" >>$time_cost
        innobackupex --defaults-file=$file_cnf --socket=/data/mysql3306/mysql.sock --parallel=5 --no-timestamp --user=$user_name --password=$password  --host=$ip --port=$port --incremental --incremental-basedir=$backdir/rec2 $backdir/rec3 1>$out_log 2>&1
        exit;
        elif [ ! -d "$backdir/rec4" ];then
        echo "#####start 4 incremental backup at $BEGINTIME to directory rec4" >>$time_cost
        innobackupex --defaults-file=$file_cnf --socket=/data/mysql3306/mysql.sock --parallel=5 --no-timestamp --user=$user_name --password=$password  --host=$ip --port=$port --incremental --incremental-basedir=$backdir/rec3 $backdir/rec4 1>$out_log 2>&1
        exit;
        elif [ ! -d "$backdir/rec5" ];then
        echo "#####start 5 incremental backup at $BEGINTIME to directory rec5" >>$time_cost
        innobackupex --defaults-file=$file_cnf --socket=/data/mysql3306/mysql.sock --parallel=5 --no-timestamp --user=$user_name --password=$password  --host=$ip --port=$port --incremental --incremental-basedir=$backdir/rec4 $backdir/rec5 1>$out_log 2>&1
        exit;
fi