mysqldump -h 10.10.10.2 -uroot -proot dbName tableName --where "id in (select * from (select id from tableName limit 2,2) as a)" > a.sql这个where是为了把从第二条开始连着的2条记录dump出来, 但执行这句时候报错: Table 'tableNeme' was not locked with LOCK TABLES (1100)请教这是咋回事,搞定了再加188分。。
mysqldump -h 10.10.10.2 -uroot -proot dbName tableName -x --where "id in (select * from (select id from tableName limit 2,2) as a)" > a.sql
--lock-all-tables, -x Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables.
加入 -x参数即可 mysqldump -h 10.10.10.2 -uroot -proot dbName tableName -x --where "id in (select * from (select id from tableName limit 2,2) as a)" > a.sql
-x, --lock-all-tables Locks all tables across all databases. This is achieved by taking a global read lock for the duration of the whole dump. Automatically turns --single-transaction and --lock-tables off. -l, --lock-tables Lock all tables for read.mysqldump help 里写这几个参数都是 Lock All tables 的?它的意思是在mysqldump期间自动对所有的表进行lock,dump完了再unlock吗?如果我有其他线程需要对其他表进行读写操作,会不会出问题。。
如果你不想锁表,则不用mysqldum 直接用 mysql 脚本来执行 mysql -h 10.10.10.2 -u root -p123 dbName -e "select * from tableName where id in (select * from (select id from tableName limit 2,2) as a) INTO OUTFILE 'xxxx.dat'"以后用LOAD DATA INFILE 恢复。
Examples: --where="user='jimf'"
-w"userid>1"
-w"userid<1"
mysqldump -h 10.10.10.2 -uroot -proot dbName tableName --where "id in (select * from (select id from tableName limit 2,2) as a)" > a.sql这个where是为了把从第二条开始连着的2条记录dump出来, 但执行这句时候报错:
Table 'tableNeme' was not locked with LOCK TABLES (1100)请教这是咋回事,搞定了再加188分。。
mysqldump -h 10.10.10.2 -uroot -proot dbName tableName -x --where "id in (select * from (select id from tableName limit 2,2) as a)" > a.sql
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns --single-transaction and
--lock-tables off.
-l, --lock-tables Lock all tables for read.mysqldump help 里写这几个参数都是 Lock All tables 的?它的意思是在mysqldump期间自动对所有的表进行lock,dump完了再unlock吗?如果我有其他线程需要对其他表进行读写操作,会不会出问题。。
这时候用mysqldump tableA -x或 --lock-tables....., 此时是不是把tableB也lock了?文档很费解。。
mysqldump -h 10.10.10.2 -uroot -proot dbName tableName --where "id in (select * from (select id from tableName limit 2,2) as a)" > a.sql
如果用这2个where条件select数据的话,都不用lock
为什么dump时候,上面这句命令就不需要有lock ,下面这句就要呢。
这条语句只是让它导出 从第2条数据开始的2条数据,有没办法能只lock一个表,或者不lock
不知有没更好的办法。
如果你不想锁表,则不用mysqldum 直接用 mysql 脚本来执行
mysql -h 10.10.10.2 -u root -p123 dbName -e "select * from tableName where id in (select * from (select id from tableName limit 2,2) as a) INTO OUTFILE 'xxxx.dat'"以后用LOAD DATA INFILE 恢复。