手动删除Piwik历史日志(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;
“`

使用php实现mysql转sqlite语句

在写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;
}
“`

MySQL之concat函数的使用

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

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

Table ‘xxx’is marked as crashed and last (automatic) repair failed

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

然后通过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
“`

MySQL找回root密码的方法

方法一(通用):

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
“`

MySQL复制不重复记录到新表

**NSERT IGNORE**与**INSERT INTO**的区别就是前者会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据,达到在间隙中插入数据的目的。

本条语句主要用于从DiscuzX备份表恢复一些缺失的数据。

“`
INSERT IGNORE INTO `discuz`.`ar_forum_post`
SELECT * FROM `discuz_bak`.`ar_forum_post`
“`

修改MySQL记录物理顺序

出于某种特殊需要,可能必须把数据按照某字段重新进行物理位置排序。另外这样也可以达到压缩空闲空间的目的。

可使用如下SQL语句进行操作:

“`
alter table `表名` order by `字段` asc;
“`

实战,DiscuzX数据库中可使用如下语句整理记录顺序:

“`
ALTER TABLE `xc_ucenter_members` ORDER BY `uid` ASC;
ALTER TABLE `xc_forum_post` ORDER BY `tid` ASC, `first` DESC, `pid` ASC;
“`

MyISAM几个容易忽视的配置选项

MyISAM在读操作占主导的情况下是很高效的。可一旦出现大量的读写并发,同InnoDB相比,MyISAM的效率就会直线下降,而且,MyISAM和InnoDB的数据存储方式也有显著不同:通常,在MyISAM里,新数据会被附加到数据文件的结尾,可如果时常做一些UPDATE,DELETE操作之后,数据文件就不再是连续的,形象一点来说,就是数据文件里出现了很多洞洞,此时再插入新数据时,按缺省设置会先看这些洞洞的大小是否可以容纳下新数据,如果可以,则直接把新数据保存到洞洞里,反之,则把新数据保存到数据文件的结尾。之所以这样做是为了减少数据文件的大小,降低文件碎片的产生。但InnoDB里则不是这样,在InnoDB里,由于主键是cluster的,所以,数据文件始终是按照主键排序的,如果使用自增ID做主键,则新数据始终是位于数据文件的结尾。

了解了这些基础知识,下面说说MyISAM几个容易忽视的配置选项:

**concurrent_insert:**

通常来说,在MyISAM里读写操作是串行的,但当对同一个表进行查询和插入操作时,为了降低锁竞争的频率,根据concurrent_insert的设置,MyISAM是可以并行处理查询和插入的:

当concurrent_insert=0时,不允许并发插入功能。
当concurrent_insert=1时,允许对没有洞洞的表使用并发插入,新数据位于数据文件结尾(缺省)。
当concurrent_insert=2时,不管表有没有洞洞,都允许在数据文件结尾并发插入。

这样看来,把concurrent_insert设置为2是很划算的,至于由此产生的文件碎片,可以定期使用OPTIMIZE TABLE语法优化。

**max_write_lock_count:**

缺省情况下,写操作的优先级要高于读操作的优先级,即便是先发送的读请求,后发送的写请求,此时也会优先处理写请求,然后再处理读请求。这就造成一个问题:一旦我发出若干个写请求,就会堵塞所有的读请求,直到写请求全都处理完,才有机会处理读请求。此时可以考虑使用max_write_lock_count:

max_write_lock_count=1

有了这样的设置,当系统处理一个写操作后,就会暂停写操作,给读操作执行的机会。

**low-priority-updates:**

我们还可以更干脆点,直接降低写操作的优先级,给读操作更高的优先级。

low-priority-updates=1

综合来看,concurrent_insert=2是绝对推荐的,至于max_write_lock_count=1和low-priority-updates=1,则视情况而定,如果可以降低写操作的优先级,则使用low-priority-updates=1,否则使用max_write_lock_count=1。

MySQL性能优化技巧

打开**/etc/my.cnf**文件,修改以下设置,如果没有,可手动添加。调整设置时,请量力而行,这与你的服务器的配置有关,特别是内存大小。以下设置比较适合于1-2G内存的服务器,但并不绝对。

**back_log = 200**

要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。默认数值是50

**max_allowed_packet = 4M**

一个包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节,但是可在需要时增加到max_allowed_packet个字节。缺省地,该值太小必能捕捉大的(可能错误)包。如果你正在使用大的BLOB列,你必须增加该值。它应该象你想要使用的最大BLOB的那么大。

**max_connections = 1024**

max_used_connections / max_connections ≈ 85%

允许的同时客户的数量。增加该值增加 mysqld要求的文件描述符的数量。这个数字应该增加,否则,你将经常看到 链接过多,请联系空间商 错误。 默认数值是100

**table_open_cache = 512**

open_tables / opened_tables >= 85%
open_tables / table_open_cache <= 95% 指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。检查峰值时间的状态值,如果open_tables等于table_open_cache,并且opened_tables在不断增长,那么就需要增加table_open_cache的值。注意,不能盲目地把table_open_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。 **tmp_table_size = 64M** **max_heap_table_size = 32M** created_tmp_disk_tables / created_tmp_tables <= 25% 当临时内存表的大小达到一定限制的时候,MySQL就会将临时内存表写入到磁盘,变为临时磁盘表。这个限制由 tmp_table_size 和 max_heap_table_size 这两个变量中的最小值确定。 **key_buffer = 384M** 指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。 比例key_reads/key_read_requests应该尽可能的低,至少是1:100,1:1000更好。在专有的数据库服务器上,该值可设置为 RAM * 1/4。 key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。 **sort_buffer_size = 4M** 每个线程排序所需的缓冲。如果状态值sort_merge_passes(排序算法使用归并的次数)很大,则应该把sort_buffer_size的值变大。 **read_buffer_size = 4M** 当一个查询不断地扫描某一个表,MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果你认为连续扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。 表扫描率 = handler_read_rnd_next / com_select 如果表扫描率超过 4000,说明进行了太多表扫描,很有可能索引没有建好,增加 read_buffer_size 值会有一些好处,但最好不要超过8mb。 **read_rnd_buffer_size = 8M** 加速排序操作后的读数据,提高读分类行的速度。如果正对远远大于可用内存的表执行GROUP BY或ORDER BY操作,应增加read_rnd_buffer_size的值以加速排序操作后面的行读取。仍然不明白这个选项的用处…… **thread_cache_size = 128** 可以复用的保存在中的线程的数量。如果有,新的线程从缓存中取得,当断开连接的时候如果有空间,客户的线置在缓存中。如果有很多新的线程,为了提高性能可以这个变量值。如果threads_created/connections很大,则应该把thread_cache_size的值变大。 **query_cache_size = 32M** 查询结果缓存。第一次执行某条SELECT语句的时候,服务器记住该查询的文本内容和它返回的结果。服务器下一次碰到这个语句的时候,它不会再次执行该语句。作为代替,它直接从查询缓存中的得到结果并把结果返回给客户端。 **thread_concurrency = 2** 最大并发线程数,cpu数量*2 **wait_timeout = 120** 设置超时时间,能避免长连接 **skip-innodb** **skip-bdb** 关闭不需要的表类型,如果你需要,就不要加上这个 **MySQL5.6参数变化:** http://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html **相关说法:** **1、**handler_read_rnd 根据固定位置读取行的请求数。如果你执行很多需要排序的查询,该值会很高。你可能有很多需要完整表扫描的查询,或者你使用了不正确的索引用来多表查询。 **2、**handler_read_rnd_next 从数据文件中读取行的请求数。如果你在扫描很多表,该值会很大。通常情况下这意味着你的表没有做好索引,或者你的查询语句没有使用好索引字段。 **3、**table_locks_waited 无法立即获得锁定表而必须等待的次数。如果该值很高,且您遇到了性能方面的问题,则应该首先检查您的查询语句,然后使用复制操作来分开表。如果 table_locks_immediate / table_locks_waited > 5000,最好采用innodb引擎,因为innodb是行锁而myisam是表锁,对于高并发写入的应用innodb效果会好些。

**扩展阅读:**
有人提起过依据key_reads/key_read_requests调整key_buffer_size有失偏颇,如果感兴趣,不妨移步这里看看:http://www.baidu.com/s?wd=key_reads+%2f+uptime

解决MySQL出现UnauthenticatedUser的问题

发现MySQL在上午的时候忽然发飙,不断的挂掉。看MySQL的error.log,只能看到类似如下的信息:
Forcing close of thread 12232 user: ‘root’

“`
mysqladmin -uroot -p******** status -i 1
“`

使用上述命令,发现Queries per second avg只有200左右,可以说很低,但是Threads确非常不稳定,居然会瞬间升至200以上,一般情况下这个线程这个值都是不会高于5的个位数。

“`
mysqladmin -uroot -p******** processlist
“`

使用上述命令,发现居然有大量的unauthenticated user进程。Google了一下,发现这算**MySQL的一个BUG**,不管连接是通过hosts还是ip的方式,MySQL都会对DNS做反查,IP到DNS,由于反查的接续速度过慢,大量的查询就难以应付,线程不够用就使劲增加线程,但是却得不到释放,所以MySQL会“假死”。

**解决方案**,结束这个反查的过程,禁止任何解析,打开mysql的配置文件(my.cnf),在[mysqld]下面增加一行:

“`
skip-name-resolve
“`

重新载入配置文件或者重启MySQL服务即可。