同数据库建表
CREATE TABLE Dataexp SELECT FROM Data WHERE Time>=1457528993 AND Time<=1457529475;
跨数据库建表
create table Data2 select from EarthQuake.Data where Time<=2011733997 and Time>=2011733543;
跨数据库插入
INSERT INTO EarthQuakeTest.Data2 SELECT FROM EarthQuake.Data WHERE Time<=1929606826 and Time>=1929606478;
mysql中把一个表的数据批量导入另一个表中
将一个表插入到另一个表中
INSERT INTO Data_test SELECT FROM Data_lost
UPDATE Data_Lost SET Time=Time+ 100 WHERE ;
drop table Data_exp1;删除表
delete from Data_exp;删除数据
phd -dbi 9 1457539200 1457625600
phd -dbi 10 1457539200 1457625600
还剩最后的10号没测。
1463819821
INSERT INTO Data(Time,Terminal,Length,Value) VALUES(1463819775,12,890
SET TIMESTAMP=1463819830/!/;
INSERT INTO Data(Time,Terminal,Length,Value) VALUES(1463819786,5,890
SET TIMESTAMP=1463819830/!/;
INSERT INTO Data(Time,Terminal,Length,Value) VALUES(1463819783,10,904,
SET TIMESTAMP=1463819835/!/;
INSERT INTO DataFinal(Time,Terminal,Version,V1,V2,V3,V4,V5,V6,V7,V8,V9,V10,V11,V12,V13,V14,V15,V16,V17,V18,V19,V20,V21,V22,V23,V24,V25,V26,V27,V28) VALUES(1463819700,6,3,628,1,6,50,132,81,61,163,99,56,95,78,637,1,6,617,1,24,668,1,6,0,96,146,325,4,49,17)
SET TIMESTAMP=1463819836/!/;
INSERT INTO Data(Time,Terminal,Length,Value) VALUES(1463819758,6,890,’2\0
SET TIMESTAMP=1463819841/!/;
INSERT INTO DataFinal(Time,Terminal,Version,V1,V2,V3,V4,V5,V6,V7,V8,V9,V10,V11,V12,V13,V14,V15,V16,V17,V18,V19,V20,V21,V22,V23,V24,V25,V26,V27,V28) VALUES(1463819760,9,3,-418,3,12,57,-91,28,68,70,-139,7,8,52,12,1,115,6,10,12,19,10,10,3,68,135,263,0,46,14)
查看 logbin里面具体进行了那些操作?
mysqlbinlog logbin.000012 | head -n 500 |grep -n ‘Version’|cut -c 1-175
mysqlbinlog logbin.000012 | head -n 500 |grep -n ‘Value’|cut -c 1-70
mysqlbinlog /Data/ftp/Binlog/logbin.000012 |mysql -uroot -p123456
delete from Data where Time>=1463819775 and Time<1929605416;
delete from DataFinal where Time>=1463819700;
delete from Log where Time>=1463819775;
50820 Connect pkuproject@106.5.196.93 on
50822 Connect pkuproject[@localhost ](/localhost ) on EarthQuake
50821 Connect pkuproject[@localhost ](/localhost ) on EarthQuake
50823 Connect pkuproject@106.5.196.93 on
50824 Connect pkuproject@106.5.196.93 on
[root[@localhost ](/localhost ) yong]# cat /var/lib/mysql/general.log |grep -v ‘INSERT’|grep ‘50820’
160701 10:33:39 50820 Connect pkuproject@106.5.196.93 on
50820 Query SET NAMES utf8
50820 Query SHOW VARIABLES LIKE ‘lower_case%’
50820 Query SHOW VARIABLES LIKE ‘profiling’
50820 Query SHOW DATABASES
[root_@_localhost yong]# cat /var/lib/mysql/general.log |grep -v ‘INSERT’|grep ‘50823’
50823 Connect pkuproject@106.5.196.93 on
50823 Query SET NAMES utf8
50823 Init DB EarthQuake
50823 Query SELECT @@characterset_database, @[@collation_database ](/collation_database )
160701 10:34:12 50823 Query SHOW FULL TABLES WHERE Table_type != ‘VIEW’
50823 Query SHOW CREATE TABLE Log
[root[@_localhost ](/localhost ) yong]# cat /var/lib/mysql/general.log |grep -v ‘INSERT’|grep ‘50824’
50824 Connect pkuproject@106.5.196.93 on
50824 Query SET NAMES utf8
50824 Init DB EarthQuake
50824 Query SHOW TABLE STATUS LIKE ‘Log’
160701 10:34:21 50824 Query SHOW ENGINES
50824 Query SELECT FROM Log
LIMIT 0, 1000
50824 Query SHOW COLUMNS FROM EarthQuake
.Log
160701 10:34:39 50824 Query SELECT FROM Log
ORDER BY Num
, Time
DESC LIMIT 0, 1000
160701 10:34:40 50824 Query SHOW COLUMNS FROM EarthQuake
.Log