MySQL在线增量备份

    增量备份的好处是可以节省备份的时间和占用的磁盘空间，可以根据业务和数据的特性，指定不同的全备和增量备份策略。
  
    备份最好是在slave上面进行，这样可以不对主库造成任何影响。使用xtrabackup这个工具能实现在线全备和在线增量备份，步骤如下：

    1、先做一个全备份
xtrabackup --backup --datadir=/data/iscsi/db/ --target-dir=/data/backups/mysql/

    2、每天增量备份

xtrabackup --backup --target-dir=/data/backups/inc/tue/   --incremental-basedir=/data/backups/mysql/       #Suppose the full backup is on Monday，Create an incremental backup on Tuesday

xtrabackup --backup --target-dir=/data/backups/inc/wed/   --incremental-basedir=/data/backups/inc/tue/     #Create an incremental backup on Wednesday

发生异常需要恢复时：

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/mysql/                                               #Prepare the base backup from Monday

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/mysql/  --incremental-dir=/data/backups/inc/tue/     #Roll Monday's data forward to the state on Tuesday

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/mysql/  --incremental-dir=/data/backups/inc/wed/     #Roll forward again to the state on Wednesday

    你可以用脚本程序和crontab一起来控制全备和增量备份的策略，将备份放在远程的服务器上面（可以在数据库服务器上面连接盘柜），达到数据安全的目的。

在线做MySQL slave

    之前给MySQL master做slave的时候，为了保证数据一致性，一般选择read lock或者停机，使用直接拷贝文件或者mysqldump的方法来实现。这种方法会使master在一段时间内不可写，影响业务的正常使用。

    有没有更好的方法，可以在master可读写的情况下，生成一个数据没有差错的slave呢？用innobackupex可以实现。具体的原理可以去看文档。实现步骤如下：

    1. on slave：
          nc -l 9999 | cat - > /data/backups/backup.tar
    2. on master：
          innobackupex --stream=tar --slave-info ./ | nc desthost 9999
    3. on slave:
          cd /data/backups/
          tar -ixvf /data/backups/backup.tar
          innobackupex --apply-log  --use-memory 5000M /data/backups

  

MySQL profile中'Sending data'很慢的问题

这几天，对MySQL（5.0.77）数据库中一个几百行记录的表（用于任务分配，读写比较频繁），进行的一个简单查询，从1s逐渐增大到15s。

mysql> select id from t_task limit 1;

+----------+
| id       |
+----------+
| 22865305 |
+----------+
1 row in set (15.24 sec)

Profiling的结果是：

 mysql> show profile for query 1;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000014 |
| checking query cache for query |  0.000032 |
| Opening tables                 |  0.000012 |
| System lock                    |  0.000003 |
| Table lock                     |  0.000006 |
| init                           |  0.000010 |
| optimizing                     |  0.000003 |
| statistics                     |  0.000006 |
| preparing                      |  0.000004 |
| executing                      |  0.000003 |
| Sending data                   | 15.242734 |
| end                            |  0.000011 |
| query end                      |  0.000002 |
| freeing items                  |  0.000013 |
| logging slow query             |  0.000003 |
| logging slow query             |  0.000002 |
| cleaning up                    |  0.000002 |
+--------------------------------+-----------+
17 rows in set (0.00 sec)

从字面意思看，是慢在‘传输数据’这一步。

‘Sending data’的意义查不到具体的说明，这里http://forums.mysql.com/read.php?24,241461,242012#msg-242012有人说是：

Each time means the time elapsed between the previous event and the new event. So, the line:
| Sending data | 0.00016800 |
means that 0.00016800 seconds elapsed between "executing" and "Sending data". It is, it takes 0.00016800 seconds to execute the query.

可以在sql_select.cc里面看到源代码（在里面搜索Sending data），可以看到do_select的时间也算在‘sending data‘里面了。


所以这个‘sending data‘并不只是数据传输的时间，也包含select的时间。有人就这个语义含糊的问题给MySQL报过bug：http://bugs.mysql.com/bug.php?id=52492，但是不了了之。

这个问题最初怀疑是网卡有问题（这台机器之前网卡有问题，更新过驱动），但是新建一张表结构一样，数据量一样的表，做同样的查询，速度又很快。又怀疑是磁盘问题，因为根据pk的查询都很快（因为簇集索引所以都缓存在内存里面），vmstat和iostat都没有发现异常。

准备晚上升级MySQL，并使用HP BL460刀片机替换这台出过硬件故障的IBM x3650M2。

动态控制MySQL慢查询日志的设置

To control the slow log at runtime, use the global slow_query_log and slow_query_log_file system variables. Set slow_query_log to 0 (or OFF) to disable the log or to 1 (or ON) to enable it. Set slow_query_log_file to specify the name of the log file. If a log file already is open, it is closed and the new file is opened.Set long_query_time to specify the seconds。
设置完成后，你可以通过select @@slow_query_log来确认是否设置成功。

使用maatkit来迁移MySQL数据库

   迁移MySQL一般会用两种方法：1、停掉数据库，直接拷贝数据文件。这种方法比较简单，合适用在迁移到更好的服务器上面，MySQL的版本没有大的变化。2、锁住数据库，将数据dump出来，再重新导入新的数据库。

   在第二种方法中，一般会用mysqldump来导出，用mysql来导入。这两个工具都是单线程的，所以在迁移表比较多的情况下，会影响速度。你可以使用Maatkit  里面的mk-parallel-dump和mk-parallel-restore来导出导入数据。这两个工具都是多线程，并且更好用。

    步骤如下：

1. 锁住需要导出数据的数据库：flush tables with read lock;
2. 导出数据： ./mk-parallel-dump -udeploy -p123 -h172.28.29.209 --databases  cloud --tables t_test --chunk-size 100000 --threads 8 --base-dir /data/dump_data  --charset binary --no-gzip   。其中的chunk-size 100000是指每次导出的记录数为10w，那么一个1000w的表，就会执行100次SQL语句，生成100个数据文件； threads 8是指用8个线程来导出数据，优先导出大表；charset binary指定用二进制来导出数据，可以避免乱码的问题。
3. 在要导入的数据库上面修改允许接收的最大包大小为100M，防止导入SQL语句被这个限制住而报错：set global max_allowed_packet=104857600; 
4. 导入数据：./mk-parallel-restore -udeploy -p123 -h172.28.28.90 --databases    cloud --fast-index  --threads 8   /sandata/cloud 。其中fast-index适用于innodb plugin和XtraDB，可以提高创建索引的速度（先插入数据，再创建索引。如果先创建索引，再插入数据，那么对于几千万表来说，后面的插入速度会很慢）。 
5. 解锁原有数据库，max_allowed_packet改回正常值。迁移完成。 

    ps:把数据导入innodb plugin 或者XtraDB时，可能会报错errno: 1478。解释在这里：http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-compression-syntax-warnings.html，因为my.cnf里面指定了 innodb_strict_mode=1

MySQL enterprise Monitor的安装和使用

  上次内部分享MySQL经验的时候，西山居的DBA建议试用一下MySQL enterprise Monitor，游戏部门已经购买，并且评价很高。
  
  抽空安装试用了一下，感觉挺好的，可以发现不少数据库存在的风险，推荐大家使用。

  官方的文档挺全的，这里记录一下安装的过程。
   1、到https://enterprise.mysql.com/去申请一个试用版。
   2、安装监控访问管理端的程序（service manager and dashboard），这个我是安装在windows下的。一路next就行
  3、增加agent的MySQL权限：
            grant select,replication client,show databases,super,process on *.* to ‘agent’@'127.0.0.1′ identified by ‘password’;
           grant create,insert on mysql.* to ‘agent’@'127.0.0.1′ identified by ‘password’;
 4、安装agent端程序（mysqlmonitoragent），：
       rpm -qa | grep -i lsb 看redhat-lsb-3.1-12.3.EL是否存在（数据库操作系统为centos5.4）
       直接执行 mysqlmonitoragent-2.2.0.1705-linux-glibc2.3-x86-64bit-installer.bin --mode text即可。如果不需要agent采集SQL语句来分析，就不用开启MySQL Enterprise Agent Proxy Service。
5、/etc/init.d/mysql-monitor-agent start 启动agent。可以查看/opt/mysql/enterprise/agent/mysql-monitor-agent.log来看是否正常。
6、没问题的话，在server端的18080端口就可以看到数据库的监控情况了。
     

MySQL Query Browser的SQL语句耗时提示

在MySQL Query Browser中执行一条SQL语句，会发现左下角有一行提示：

N row(s) fetched in Time1 s(Time2 s)


以前没有在意里面两个时间的区别，最近数据库（IBM x3650）网卡故障，访问很慢，在排除软件问题的时候，发现这两个时间比较诡异，有时Time1比Time2大很多。google了下，这里可以看到sun数据库工程师的解释：

 query_time is the time the query needed to execute:

// Start query timer
timer_start(&timer);

if (sql)
r= myx_mysql_query(mysql, resultset->query->sql);
#if MYSQL_VERSION_ID >= 50000
else
r= mysql_next_result(mysql);
#endif
// Stop query timer
resultset->query_time= timer_stop(&timer);

and fetch_time is what it took to get the result set transferred to the client.

可以得知，Time1是服务器端把结果传回客户端的时间，Time2是数据库执行SQL语句的时间。

毒霸云查杀数据库架构和MySQL优化实践

想写这个PPT很久了，毒霸的数据库规模和应用最大的就是云查杀这边，两三年间遇到的问题也挺多，有必要总结一下。这次参加2010数据库大会回来，要做内部分享，就抽了时间写了下（PPT是个提纲，等内部分享完了放录音出来）

http://www.slideshare.net/moonbingbing/mercury-4148606

MySQL慢查询日志自动分割

MySQL的慢查询日志是揪出"数据库杀手"的好方法。美中不足的是，当我们解决了一个慢查询以后，通过mysqlsla再次分析慢查询日志，并不知道效果如何，因为默认是不会按照日期分割慢查询日志的。

怎么处理？如果不是经常有慢查询，手工做一下即可。MySQL的官方手册写的很清楚：

 For example, if the general and slow query logs are named mysql.log and mysql-slow.log, you can use a series of commands like this:

shell> cd mysql-data-directory shell> mv mysql.log mysql.old shell> mv mysql-slow.log mysql-slow.old shell> mysqladmin flush-logs 

At this point, you can make a backup of mysql.old and mysql-slow.log and then remove them from disk.

 如果你的数据库慢查询太多，囧，可以考虑自动的方法（就是用Logrotate来替你定时完成上面的几个命令，关键是怎么在执行mysqladmin不用输入数据库密码）,参见这里（很详细）

担心哪一天不能访问了,在这里备份一下:)

If you have the resources (CPU + RAM) available on your server then its can be a great troubleshooting tool if you enable MySQL logging which includes server messages, SQL query logs, and slow query logs. If you do not have the resources I would suggest only enable minimal logging such as only server messages and the slow query log since enabling all queries to be written to a file can become expensive rather quickly. Below I discuss enabling three different types of MySQL logging, adding a MySQL configuration file to logrotate, and configuring root to run mysqladmin commands without having to type the password out each time.

Enable MySQL Logging:

First we are going to enable the various types of MySQL logging. I would keep an eye on the query log just in case it becomes to resource intensive. Below I will display the configuration command to enable each type of logging including creating the file and directory that MySQL will log to.

1. Modify The my.cnf MySQL Config File: First lets add the statements to the MySQL configuration file which by default is typically /etc/my.cnf. There will probably be a minimal set of configuration items in this file if you have never modified it. Add the below lines which include comments above each one to explain what will be logged.

   Configure my.cnf To Log Queries, Errors, Slow Queries:

   view plaincopy to clipboardprint?

   1. # Will log MySQL Errors Including Startup Errors 
   2. log-error=/var/log/mysql/mysqld.log 
   3. # Will log MySQL queries, This log will grow quickly 
   4. log=/var/log/mysql/mysql.log 
   5. # Will log MySQL queries that take a long time (in this case over 1 second as specified below) 
   6. log-slow-queries = /var/log/mysql/mysql-slow.log 
   7. long_query_time = 1 

   The above configuration lines should go under the main section of MySQL which should be "[mysqld]" at the beginning of the file. The next section doesn't start until "[mysqld_safe]" so as you can see there are typically two sections and they are divided by these headings.

2. Create MySQL Log Directory: I like to try and split services up in the log directory so I can keep track of things easier so in this case we are going to do the same. In the command list below we will be creating a directory, creating blank files, and then making sure everything has the right permissions and is owned by the proper user which should be the "mysql".

   Create MySQL Log Directory & Log Files:

   view plaincopy to clipboardprint?

   1. [root@dev ~]# mkdir /var/log/mysql 
   2. [root@dev ~]# touch /var/log/mysql/mysql.log 
   3. [root@dev ~]# touch /var/log/mysql/mysqld.log 
   4. [root@dev ~]# touch /var/log/mysql/mysql-slow.log 
   5. [root@dev ~]# chown -R mysql.mysql /var/log/mysql 
   6. [root@dev ~]# chmod -R 664 /var/log/mysql/ 

   So notice that all of the new files will be owned the mysql user in the mysql group and they will allow reading from all users and writing from the mysql user and members of the mysql group.

3. Restart MySQL & Verify Logging: Now lets restart MySQL and verify the logging is operational. You can restart MySQL by issuing the below command.

   Restart MySQL:

   view plaincopy to clipboardprint?

   1. [root@dev ~]# /etc/init.d/mysqld restart 
   2. Stopping MySQL:  [  OK  ] 
   3. Starting MySQL:  [  OK  ] 
   4. [root@dev ~]# 

   If MySQL fails to restart and this is a live server quickly make a backup of my.cnf, remove the changes,and start MySQL so your downtime is minimal. If you follow the directions above you should be fine but I wanted to mention just in case there were any issues with the restart. Now visit some web pages on your site to generate some of the MySQL queries to the database and then view the log files to verify logging is functional. You can view the log files in real time by using the tail command as displayed below.

   View New MySQL Log Files In Real Time With Tail:

   view plaincopy to clipboardprint?

   1. [root@dev mysql]# tail -f mysql.log 
   2. FROM wp_posts WHERE MONTH(post_date) = '12' 
   3. AND YEAR(post_date) = '2009' 
   4. AND post_type = 'post' AND post_status = 'publish' 
   5. AND post_date < '2009-12-20 12:52:13' 
4. Configure Root User MySQL Access: Now in preparation for the logrotate mysql configuration file you want to add a file to the root users home directory so that user can flush the log files without having to use a username and password every single time. If you attempt to flush the MySQL logs before you add the below configuration file you will receive an error similar to the below.

   Error When Running mysqladmin flush-logs Without A Password:

   view plaincopy to clipboardprint?

   1. [root@dev ~]# /usr/bin/mysqladmin flush-logs 
   2. /usr/bin/mysqladmin: connect to server at 'localhost' failed 
   3. error: 'Access denied for user 'root'@'localhost' (using password: NO)' 

   Use your favorite editor such as vi as the root user  to create a file by the name of .my.cnf in /root/. Add the contents below to this file but of make sure to modify the username and password in the example below to include a MySQL username and password that has access to run "mysqladmin flush-logs".

   New /root/.my.cnf File:

   view plaincopy to clipboardprint?

   1. [mysqladmin] 
   2. user               = root 
   3. password        = changeme 

   Make sure that you set the proper permissions for the above file once you have created it so that no other users on the server can read the file contents and find out what the MySQL root password is. To set the proper permissions use the below command.

   Set Permissions On /root/.my.cnf:

   view plaincopy to clipboardprint?

   1. [root@dev ~]# chmod 600 /root/.my.cnf 

   Now you can run the "mysqladmin flush-logs" command as the root user without having to enter a username or password and without receiving an error.

5. Add Logrotate MySQL Configuration File: Now add the below configuration contents to a new file named "mysql" in the "/etc/logrotate.d/" directory.

   The mysql Logrotate Configuration File Contents:

   view plaincopy to clipboardprint?

   1. /var/log/mysql/*.log { 
   2. create 644 mysql mysql 
   3. notifempty 
   4. daily 
   5. rotate 5 
   6. missingok 
   7. nocompress 
   8. postrotate 
   9. # run if mysqld is running 
   10. if test -n "`ps acx|grep mysqld`"; then 
   11. /usr/bin/mysqladmin flush-logs 
   12. fi 
   13. endscript 
   14. } 

   The basis of the file is that it will rotate any files in the MySQL directory that end with ".log". Once the files are rotated the new files will be generated by the mysql user with permissions of 644. The files will not be rotated by Logrotate unless they have contents. The files will be rotated daily and five copies will be retained. Logrotate is not going to panic if there is a file missing and when all is said and done the root user will run "mysqladmin flush-logs" to flush out the log files so logs will continue to write to the proper files.

You are now able to troubleshoot MySQL, web applications, and many other things much easier. Remember to keep an eye on mysql.log to make sure that the file does grow out of hand. Also be sure to check back on the log files after a couple days just to make sure that logrotate is functioning properly. If you don't have logrotate installed and configured yet you can check out the manpage here on QD.