标签 mysql 下的文章

不知不觉间,Piwik数据的容量已经达到500GB,虽然并没有感觉到性能有什么影响,但也是时候考虑如何清理其最占空间的访问记录数据了。于是决定拿备份库演练一番:

首先想到的是使用后台的自动清理功能,但是发现其每次10000条的删除能力,对于此等量级的数据可谓是杯水车薪。最后只能翻看源码,得出最基本的两条删除语句。

DELETE FROM `wk_log_visit` WHERE visit_last_action_time < '2018-11-05 00:00:00';
DELETE FROM `wk_log_link_visit_action` WHERE server_time < '2018-11-05 00:00:00';

至于 wk_log_action 表一则也就10G多些,无伤大雅;二则这个表可以根据上面的两个表对其引用情况执行自动清理。

https://your.domain.com/index.php?action=executeDataPurge&date=today&format=html&idSite=1&module=PrivacyManager&period=day

至此,我们将遇到第二个问题:

如果要从InnoDB大表中删除许多行,则可能会超出表的锁定表大小InnoDB。也许删除500G的数据需要8个小时左右。

为了避免这个问题,或者只是为了最小化表保持锁定的时间,官方给出以下策略(根本不使用 DELETE):

  • 选择不要删除的行到与原始表具有相同结构的空表中

    INSERT INTO t_copy SELECT * FROM t WHERE ... ;
  • 使用RENAME TABLE以原子移动原始表的方式进行,并重新命名拷贝到原来的名称

    RENAME TABLE t TO t_old, t_copy TO t;
  • 删除原始表
    DROP TABLE t_old;

最终,我们优化查询如下

CREATE TABLE wk_log_visit_copy LIKE wk_log_visit;
INSERT INTO wk_log_visit_copy SELECT * FROM wk_log_visit WHERE visit_last_action_time >= '2018-11-05 00:00:00';
RENAME TABLE wk_log_visit TO wk_log_visit_old, wk_log_visit_copy TO wk_log_visit;

CREATE TABLE wk_log_link_visit_action_copy LIKE wk_log_link_visit_action;
INSERT INTO wk_log_link_visit_action_copy SELECT * FROM wk_log_link_visit_action WHERE server_time >= '2018-11-05 00:00:00';
RENAME TABLE wk_log_link_visit_action TO wk_log_link_visit_action_old, wk_log_link_visit_action_copy TO wk_log_link_visit_action;

DROP TABLE wk_log_visit_old, wk_log_link_visit_action_old;

在写ArkPlus框架过程中一直使用的基于PDO驱动的MySQL,因为项目需求,要转一个SQLite版本,于是写了这个简单的转换函数,实现MySQL建表语句到SQLite的平滑转换,有需要的童鞋们可以拿去。

/**
 * mysql(ctreate_table)转sqlite语句
 * @author anrip[mail@anrip.com]
 * @version 2.1, 2013-01-18 17:02
 * @link http://www.anrip.com/?arkplus
 */
function ark_table_mysql2sqlite($sql) {
  $expr = array(
    '/`(\w+)`\s/' => '[$1] ',
    '/\s+UNSIGNED/i' => '',
    '/\s+[A-Z]*INT(\([0-9]+\))/i' => ' INTEGER$1',
    '/\s+INTEGER\(\d+\)(.+AUTO_INCREMENT)/i' => ' INTEGER$1',
    '/\s+AUTO_INCREMENT(?!=)/i' => ' PRIMARY KEY AUTOINCREMENT',
    '/\s+ENUM\([^)]+\)/i' => ' VARCHAR(255)',
    '/\s+ON\s+UPDATE\s+[^,]*/i' => ' ',
    '/\s+COMMENT\s+(["\']).+\1/iU' => ' ',
    '/[\r\n]+\s+PRIMARY\s+KEY\s+[^\r\n]+/i' => '',
    '/[\r\n]+\s+UNIQUE\s+KEY\s+[^\r\n]+/i' => '',
    '/[\r\n]+\s+KEY\s+[^\r\n]+/i' => '',
    '/,([\s\r\n])+\)/i' => '$1)',
    '/\s+ENGINE\s*=\s*\w+/i' => ' ',
    '/\s+CHARSET\s*=\s*\w+/i' => ' ',
    '/\s+AUTO_INCREMENT\s*=\s*\d+/i' => ' ',
    '/\s+DEFAULT\s+;/i' => ';',
    '/\)([\s\r\n])+;/i' => ');',
  );
  $sql = preg_replace(array_keys($expr), array_values($expr), $sql);
  return $sql === null ? '{table_mysql2sqlite_error}' : $sql;
}

concat作为MySQL函数中的代表函数,concat函数返回结果为连接参数产生的字符串,该函数可以可以连接一个或者多个字符串,极具实用价值。

-- 链接字符串
SELECT CONCAT('fie', 'ld') FROM table;
-- 批量添加前缀
UPDATE table SET field=CONCAT('pre_', field);
-- 批量添加后缀
UPDATE table SET field=CONCAT(field, '_suf');

维护着一个大型门户网站,数据量相当大,最近搬到云服务器环境,突然发现帖子无法打开。第一反应是想着是不是数据被破坏了,丢了一些数据,检查后发现并不是。

然后通过adminer查看forum_post表的时候提示:
Table 'forum_post' is marked as crashed and last (automatic) repair failed

这意味着该表被标记为跌宕了。于是想着修复:repair table forum_post,修复却失败了。

只好通过mysql命令行来执行了。

#停止mysql服务;
service mysqld stop
#切换到该表目录下(注意:不切换过来会总提示myisamchk一些错误导致失败)
cd /var/lib/mysql/tbl
#如果还是提示错误,就多加一个参数-f强制进行
myisamchk -r forum_post.MYI

方法一(通用):

1.在my.cnf或my.ini中的[mysqld]字段加入skip-grant-tables,免认证登录

2.重启mysql服务,进入CLI模式

#使用root用户
mysql -uroot

3.在mysql命令行修改root密码

use mysql;
update user set authentication_string=password('newpassword') where user='root';
flush privileges;

# mysql old version
#update user set Password=password('newpassword') where user='root';

4.去掉my.cnf或my.ini中的skip-grant-tables,重启mysqld

方法二(Debian系统):

类Debian系统可直接使用/etc/mysql/debian.cnf中[client]节提供的密码登录

#查看系统保留密码
cat /etc/mysql/debian.cnf
#使用系统用户登录
mysql -udebian-sys-maint -p