Posts tagged ‘select into outfile’

select into outfile access deny问题

为应用建立了rnd的帐号,专门为他们查询线上数据库用的,当然,只有他们上了生产网络以后才能连上数据库,安全方面我们还是很注意的,呵呵。
授权的语句如下:
grant select on armory.* to rnd;
flush privileges;

select查询数据没有问题,但是有的用户有了更多的需求,他想把数据导出来,简单的处理的话,可以用select into outfile导出来。自己指定字段的分隔,行分隔等等。
但是用户一查询就报:access deny的错误,权限不对。
rnd@localhost : armory 09:26:31> select * into outfile ‘/tmp/1.txt’ from os limit 5;
ERROR 1045 (28000): Access denied for user ‘rnd’@’%’ (using password: NO)
郁闷的是MySQL没有说缺少了那个权限。

在本机测试了一下,
grant all on armory.* to rnd;
flush privileges;
给rnd所有的权限以后,还是报权限错误。这个就奇怪了,所有的权限都给它了,还报错?不可理喻阿。
实在搞不定,最后让用户:
mysql -urnd -p -e ‘select * from os limit 5;’ >1.txt
的变通方法。

一直被这个纠结着,突然后来有一天,查询了一下MySQL的文档,找到是file的权限没有加上去,但是当时MySQL对应的库的所有权限我都加上去了阿。灵光一闪,file是全局的权限,在MySQL中对单个库是没有这个权限概念的,所以就算我把库上的所有权限给了rnd,file的权限其实还是没有附权给它的。不信的话,我们这就试试:
root@localhost : (none) 09:55:14> grant file on armory.* to rnd;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
果然,File权限是GLOBAL权限,不能附权给数据库。
GLOBAL FILE附权以后
root@localhost : mysql 09:58:21> grant file on *.* to rnd;
Query OK, 0 rows affected (0.00 sec)
select查询就可以执行了:
rnd@localhost : armory 10:00:42> select * into outfile ‘/tmp/1.txt’ from os limit 5;
Query OK, 5 rows affected (0.00 sec)

其实MySQL的权限可能比较拗,让我们一下子适应不过来。MySQL的权限可以精细到列,权限判断是根据GLOBAL,DB,TABLE,COLUMN来授权的,可以简单的理解为他们对应到mysql库中的四个表:user,db,tables_priv,columns_priv这几个表。当然,MySQL没有这么简单拉。有兴趣的话可以好好看一下MySQL的reference或者其他介绍。举个例子:
rnd@localhost : armory 10:00:43> show grants for rnd;
+————————————————-+
| Grants for rnd@%                                |
+————————————————-+
| GRANT FILE ON *.* TO ‘rnd’@’%’                  |
| GRANT ALL PRIVILEGES ON armory.* TO ‘rnd’@’%’ |
+————————————————-+
2 rows in set (0.00 sec)
grant对同一个用户就分了两行,分别对应着user和db里面的两行:
root@localhost : mysql 10:18:34> select * from mysql.user where user=’rnd’\G
*************************** 1. row ***************************
Host: %
User: rnd
Password:
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: Y
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
1 row in set (0.00 sec)

root@localhost : mysql 10:18:41> select * from mysql.db where user=’rnd’\G
*************************** 1. row ***************************
Host: %
Db: armory
User: rnd
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Execute_priv: Y
Event_priv: Y
Trigger_priv: Y
1 row in set (0.00 sec)
MySQL的权限检查也是通过检查这两个表来进行判断的。

附上tables_priv和columns_priv的两个表的字段,和user和db还是有点不同的,用到了set类型。
root@localhost : mysql 10:18:58> desc tables_priv;
+————-+———————————————————————————————————————————–+——+—–+——————-+—————————–+
| Field       | Type                                                                                                                              | Null | Key | Default           | Extra                       |
+————-+———————————————————————————————————————————–+——+—–+——————-+—————————–+
| Host        | char(60)                                                                                                                          | NO   | PRI |                   |                             |
| Db          | char(64)                                                                                                                          | NO   | PRI |                   |                             |
| User        | char(16)                                                                                                                          | NO   | PRI |                   |                             |
| Table_name  | char(64)                                                                                                                          | NO   | PRI |                   |                             |
| Grantor     | char(77)                                                                                                                          | NO   | MUL |                   |                             |
| Timestamp   | timestamp                                                                                                                         | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Table_priv  | set(‘Select’,’Insert’,’Update’,’Delete’,’Create’,’Drop’,’Grant’,’References’,’Index’,’Alter’,’Create View’,’Show view’,’Trigger’) | NO   |     |                   |                             |
| Column_priv | set(‘Select’,’Insert’,’Update’,’References’)                                                                                      | NO   |     |                   |                             |
+————-+———————————————————————————————————————————–+——+—–+——————-+—————————–+
8 rows in set (0.00 sec)

root@localhost : mysql 10:21:24> desc columns_priv;
+————-+———————————————-+——+—–+——————-+—————————–+
| Field       | Type                                         | Null | Key | Default           | Extra                       |
+————-+———————————————-+——+—–+——————-+—————————–+
| Host        | char(60)                                     | NO   | PRI |                   |                             |
| Db          | char(64)                                     | NO   | PRI |                   |                             |
| User        | char(16)                                     | NO   | PRI |                   |                             |
| Table_name  | char(64)                                     | NO   | PRI |                   |                             |
| Column_name | char(64)                                     | NO   | PRI |                   |                             |
| Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set(‘Select’,’Insert’,’Update’,’References’) | NO   |     |                   |                             |
+————-+———————————————-+——+—–+——————-+—————————–+
7 rows in set (0.00 sec)

may your success.

索引问题导致mysql复制lock timeout

遇到一个非常郁闷的问题。MySQL复制备机SQL线程执行一个语句报错:
Error ‘Lock wait timeout exceeded; try restarting transaction’ on query. Default database: ‘brmms2’. Query: ‘UPDATE brmms_user SET gmt_modified = now() , last_login_ip = ‘58.20.77.164’ , last_login_time = now() WHERE member_id = ‘yanyazhang”
这个语句就算我单独提出来执行也会超时。show innodb status的状态如下:
root@localhost : (none) 08:12:46> show innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
100501 20:12:51 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 8 seconds
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 22, signal count 22
Mutex spin waits 0, rounds 239, OS waits 8
RW-shared spins 20, OS waits 10; RW-excl spins 3, OS waits 3
————
TRANSACTIONS
————
Trx id counter 0 1835507099
Purge done for trx’s n:o < 0 1835505963 undo n:o < 0 0
History list length 44
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 0 0, not started, OS thread id 12
MySQL thread id 801, query id 3585 localhost root
show innodb status
—TRANSACTION 0 1835507098, ACTIVE 64 sec, OS thread id 17 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1216, 2 row lock(s)
MySQL thread id 1214, query id 3536 Updating
UPDATE brmms_user SET gmt_modified = now() , last_login_ip = ‘58.20.77.164’ , last_login_time = now() WHERE member_id = ‘yanyazhang’
——- TRX HAS BEEN WAITING 64 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10344 page no 3281 n bits 192 index PRIMARY of table brmms2.brmms_user trx id 0 1835507098 lock_mode X locks rec but not gap waiting
Record lock, heap no 121 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
0: len 8; hex 0000000005fd14a7; asc ;; 1: len 6; hex 00006d6795f9; asc mg ;; 2: len 7; hex 00000000c30c2f; asc /;; 3: len 10; hex 79616e79617a68616e67; asc yanyazhang;; 4: len 4; hex 00000000; asc ;; 5: len 4; hex 00000000; asc ;; 6: len 4; hex 00000000; asc ;; 7: len 4; hex 00000000; asc ;; 8: len 3; hex 8fb48e; asc ;; 9: len 12; hex 35382e32302e37372e313634; asc 58.20.77.164;; 10: len 4; hex 4bc59f9c; asc K ;; 11: len 4; hex 4bc59f8f; asc K ;; 12: len 4; hex 4bc59f9c; asc K ;; 13: len 4; hex 80000000; asc ;; 14: len 4; hex 80000000; asc ;; 15: len 4; hex 80000000; asc ;; 16: SQL NULL;

——————
—TRANSACTION 0 1835505145, ACTIVE (PREPARED) 5527 sec, OS thread id 0
1 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
——–
FILE I/O
——–
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0
Pending flushes (fsync) log: 0; buffer pool: 0
16300 OS file reads, 336 OS file writes, 268 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
————————————-
INSERT BUFFER AND ADAPTIVE HASH INDEX
————————————-
Ibuf: size 1, free list len 104, seg size 106,
27 inserts, 27 merged recs, 27 merges
Hash table size 35401603, node heap has 7 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

LOG

Log sequence number 102 3346391724
Log flushed up to 102 3346391724
Last checkpoint at 102 3346391724
0 pending log writes, 0 pending chkp writes
198 log i/o’s done, 0.00 log i/o’s/second
———————-
BUFFER POOL AND MEMORY
———————-
Total memory allocated 18653842407; in additional pool allocated 16776704
Dictionary memory allocated 4059160
Buffer pool size 1048576
Free buffers 1034004
Database pages 14565
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 14565, created 0, written 146
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
————–
ROW OPERATIONS
————–
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 9, state: waiting for server activity
Number of rows inserted 52, updated 36, deleted 3, read 364
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
—————————-
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.00 sec)
这里比较奇怪的地方在于:这个备机上就一个SQL线程在跑,其他的任何东西都没有跑,结果它还被锁超时杀掉了。上面的show innodb status显示
——————
—TRANSACTION 0 1835505145, ACTIVE (PREPARED) 5527 sec, OS thread id 0
1 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
这个线程是干吗的,难道是它lock住了update语句?

我先后试过如下的方法想去恢复:
1、重启数据库。SQL slave自己执行不过去。手工执行不过去。
2、重启数据库并修改为skip-slave-start模式,手工执行未果。
3、alter table brmms_user drop index idx_brmms_user_member_id, add index idx_brmms_user_Mem_id (member_id);报错lock timeout.
4、stop slave;想利用mysqldump -uroot -p –opt –add-drop-table –default-character-set=utf8 –master-data=2 –single-transaction –complete-insert –log-error=/tmp/mysqldump20100501.log brmms2 brmms_user>brmms_user.sql 或者mysqldump -uroot -p –default-character-set=utf8 brmms2 brmms_user>brmms_user.sql导出数据,然后重新导入。结果在mysqldump的时候就导不出来数据(能够导出表结构,错误日志里面也没有任何信息)
5、创建临时表tmp_pickup_brmms_user,然后insert into temp_brmms_user_pickup select * from brmms_user;。报错lock timeout.

最后通过select into outfile的方式解决了这个问题。步骤如下:
1、确认slave是停掉的。 并且修改SQL_LOG_BIN来避免下面的语句记录到binlog中(如果对应的主机没有这个问题,下面的这些操作完全没有必要通过replication传递到主机执行)。
stop slave;
SET SQL_LOG_BIN=0;
2、导出brmms_user表的所有数据。注意/tmp目录需要有足够的空间。
select * from brmms_user into outfile ‘/tmp/brmms_user.txt’;
3、创建临时表。该表的结构要求跟brmms_user表结构一样。
CREATE TABLE tmp_pickup_brmms_user ( … );
4、将brmms_user的数据导入到tmp_pickup_brmms_user。
load data local infile ‘/tmp/brmms_user.txt’ into table tmp_pickup_brmms_user;
5、改名。将brmms_user改到另外的名字,将临时表名字改为brmms_user
alter table brmms_user rename tmp_pickup_brmms_user_old;
alter table tmp_pickup_brmms_user rename brmms_user;
6、重新启动复制。
start slave;
7、收尾工作:必要的话,删除tmp_pickup_brmms_user_old表。SET SQL_LOG_BIN=1;来恢复session的binlog记录。
drop table tmp_pickup_brmms_user_old;
SET SQL_LOG_BIN=1;

may you success.

今天我们又检查了一下那个有问题的表。发现通过主键更新对应记录也是locktimeout。更新它之前和之后的主键都没有问题。突然回想起这台机器是通过另外一台机器的solaris的snapshot生产的快照,然后直接传到这台机器的。之后就直接启动起来用了。假设

这里在生成snapshot的时候,MySQL的数据(索引)在物理层面上是不一致的。这样的不一致是否是导致MySQL的这个问题的原因列。有待进一步的确认。

这里我们也知道了solaris的snapshot还是有风险的,如果以后还有备机的搭建,可以考虑先停止MySQL,然后才做snapshot。保证数据的物理一致性。

may you success