log备份以及恢复示例,用一条mysql语句插入多条数据

应用MySQL总计页面访问及排行

总结访问页面数量,以分辨率进行排名

 SELECT CONCAT(`height` , '*', `width`) AS `resolution` , COUNT(CONCAT(`height`, '*', `width`)) AS `total` 
 FROM `wifi_status_page` 
 GROUP BY CONCAT(`height`, '*', `width`) 
 ORDER BY `total` DESC 
 LIMIT 0 , 30

近年来7天页面访问量,天天总括数据,以日期为序

 SELECT td AS showdate, COALESCE( totalcount, 0 ) AS totalcount 
 FROM (
  SELECT CURDATE( ) AS td
  UNION ALL SELECT DATE_ADD( CURDATE( ) , INTERVAL -1 DAY ) 
  UNION ALL SELECT DATE_ADD( CURDATE( ) , INTERVAL -2 DAY ) 
  UNION ALL SELECT DATE_ADD( CURDATE( ) , INTERVAL -3 DAY ) 
  UNION ALL SELECT DATE_ADD( CURDATE( ) , INTERVAL -4 DAY ) 
  UNION ALL SELECT DATE_ADD( CURDATE( ) , INTERVAL -5 DAY ) 
  UNION ALL SELECT DATE_ADD( CURDATE( ) , INTERVAL -6 DAY ) 
  ORDER BY td
 )a
 LEFT JOIN (
  SELECT FROM_UNIXTIME(add_time, '%Y-%m-%d') add_data, COUNT( id ) AS totalcount
  FROM wifi_status_page
  WHERE FROM_UNIXTIME(add_time, '%Y-%m-%d') BETWEEN DATE_ADD(CURDATE(), INTERVAL -6 DAY) AND CURDATE() 
  GROUP BY add_data
 )b ON a.td = b.add_data

这段日子30天页面访问量,每一天总括数据,以日期为序

SELECT td AS showdate, COALESCE( totalcount, 0 ) AS totalcount 
 FROM (
  SELECT CURDATE( ) AS td
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -1 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -2 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -3 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -4 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -5 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -6 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -7 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -8 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -9 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -10 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -11 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -12 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -13 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -14 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -15 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -16 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -17 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -18 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -19 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -20 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -21 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -22 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -23 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -24 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -25 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -26 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -27 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -28 DAY)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -29 DAY)
  ORDER BY td
 )a
 LEFT JOIN (
  SELECT FROM_UNIXTIME(add_time, '%Y-%m-%d') add_data, COUNT( id ) AS totalcount
  FROM wifi_status_page
  WHERE FROM_UNIXTIME(add_time, '%Y-%m-%d') BETWEEN DATE_ADD(CURDATE(), INTERVAL -29 DAY) AND CURDATE() 
  GROUP BY add_data
 )b ON a.td = b.add_data

后天二四钟头分时

 SELECT td AS showhour
 FROM (
  SELECT DATE_ADD(CURDATE(), INTERVAL -1 HOUR) as td
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -2 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -3 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -4 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -5 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -6 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -7 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -8 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -9 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -10 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -11 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -12 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -13 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -14 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -15 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -16 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -17 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -18 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -19 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -20 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -21 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -22 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -23 HOUR)
  UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -24 HOUR)
  ORDER BY td
 )a

前几日病故的10个钟头

 SELECT td AS showhour
 FROM (
  SELECT NOW() AS td
  UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -1 HOUR)
  UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -2 HOUR)
  UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -3 HOUR)
  UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -4 HOUR)
  UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -5 HOUR)
  UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -6 HOUR)
  UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -7 HOUR)
  UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -8 HOUR)
  UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -9 HOUR)
  UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -10 HOUR)
  UNION ALL SELECT DATE_ADD(NOW(), INTERVAL -11 HOUR)
  ORDER BY td
 )a

总计访问页面数量,以分辨率举行排行 SELECT CONCAT(`height` , *,
`width`) AS `resolution` , COUNT(CONCAT(`height`, *,
`width`)…

用一条mysql语句插入多条数据,mysql语句插入多条

那篇文章主要介绍了在mysql中接纳一条sql语句插入多条数据,功效极高,不过原理其实很简短,希望对我们有着援救

 

  如果有一个数码表A:

id name title addtime

        假如须求插入n条数据 :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 $time= time(); $data = array(   array(    'name'=>'name1','title'=>'title1','addtime'=>$time;   ),   array(    'name'=>'name2','title'=>'title2','addtime'=>$time;   ),   array(    'name'=>'name3','title'=>'title3','addtime'=>$time;   ),   ...   array(    'name'=>'nameN','title'=>'titleN','addtime'=>$time;   ),     );

   以前作者的主见会是,通过数量构造多条插入语句,循环调用 。如:     

1 2 3 4 $sql1 = "INSERT INTO `A`(`name`,`title`,`addtime`)VALUES ('name1','title1','".$time."')"; $sql2 = "INSERT INTO `A`(`name`,`title`,`addtime`)VALUES ('name2','title2','".$time."')"; ...... $sqlN = "INSERT INTO `A`(`name`,`title`,`addtime`)VALUES ('nameN','titleN','".$time."')";

   之后发掘了sql的insert语句能够一回插入多条:

1 2 3 4 5 $sql = "INSERT INTO `A`(`name`,`title`,`addtime`)VALUES ('name1','title1','".$time."'),"; $sql .= "('name2','title2','".$time."'),"; $sql .= "('name3','title3','".$time."'),"; ..... $sql .= "('nameN','titleN','".$time."')";

      
通过”,”号将七个数据分隔开分离来,即恐怕过一条sql操作来消除四个数据的插入,在此以前试验时,在插入数据条数为30的情景下,一次插入与高频安顿一样数额的进程比较,壹次插入快了近玖倍。同临时候,因为插入操作唯有贰次,所以也类似事情操作,插入战败则全失利,插入成功则全成功,使数据的军管更加的便民。所以,如若有多条数据必要插入同三个表的,尽量用这种措施。

先前插入多条数据库记录时,常那样写:

1 2 3 4 5 6 7 8 9 $b = 14;   for($a=0;$a<100;$a++){      $sql = " INSERT INTO `roles` (`uid`,`rid`) VALUES (".$a.",".$b.")";      mysql_query($sql);   }

但这种写法效能低下,须要频仍实践sql语句。假如您用过phpmyadmin导入数据,其实您会开采,上边的语句其实能够这么写

1 2 INSERT INTO `roles` (`uid`,`rid`) VALUES     (534,14),(535,14),(536,14),(537,14),(539,14)

由此本来的代码能够这么改写

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 $b = 14;   for($a=0;$a<100;$a++){     if($a==0)        $sql = "INSERT INTO `roles` (`uid`,`rid`) VALUES (".$a.",".$b.")";     else       $sql. = ",(".$a.",".$b.")";   }   mysql_query($sql);

那篇小说首要介绍了在mysql中行使一条sql语句插入多条数据,作用非常高,但是原理其实很…

mysqldump和big-log备份以及恢复生机示例

mysql版本

[[email protected]~]#mysql-V
mysqlVer14.14Distrib5.6.28,forlinux-glibc2.5(x86_64)usingEditLinewrapper

一、mysqldump

mysqldump仅适用于数据集不大场地

用法:mysqldump[options][db_name[tbl_name...]]
主要选项解释:
--all-databases,-A:备份所有数据库
--databases,-B:要备份的数据库,可以同时备份多个,使用空格分隔
--flush-logs,-F:备份前、请求到锁之后滚动日志,要记录下复制时的二进制日志
--flush-privileges:通知数据库重读授权表
--host=host_name,-hhost_name:要备份的数据库的主机名,可以基于网络备份
--lock-all-tables,-x:请求锁定所有表之后再备份,对MyISAM,InnoDB,Aria做温备
--single-transaction:能够对InnoDB存储引擎实现热备
-uusename备份的用户名
-ppassword登陆数据库的密码
--events:备份事件调度器代码
--routines:备份存储过程和存储函数
--triggers:备份触发器
--master-date={0|1|2},0表示不记录,1表示距离为changemaster语句,2表示记录为注释的changemaster语句

贰、创造数据库以及表并插入数据

mysql>createdatabasezxl;
QueryOK,1rowaffected(0.00sec)
mysql>usezxl
Databasechanged
mysql>CREATETABLE`users`(
->`id`bigint(20)NOTNULLAUTO_INCREMENT,
->`name`varchar(255)DEFAULTNULL,
->PRIMARYKEY(`id`)
->)ENGINE=InnoDBAUTO_INCREMENT=7DEFAULTCHARSET=utf8;
QueryOK,0rowsaffected(0.02sec)

#那是开创表以及插入数据的身体力行,来自网络。。。

#------------------------------
#--Tablestructureforusers
#------------------------------
#DROPTABLEIFEXISTS`users`;
#CREATETABLE`users`(
#`id`bigint(20)NOTNULLAUTO_INCREMENT,
#`name`varchar(255)DEFAULTNULL,
#PRIMARYKEY(`id`)
#)ENGINE=InnoDBAUTO_INCREMENT=7DEFAULTCHARSET=utf8;
#
#------------------------------
#--Recordsofusers
#------------------------------
#INSERTINTO`users`VALUES('1','小明');
#INSERTINTO`users`VALUES('2','小虎');
#INSERTINTO`users`VALUES('3','小花');
#INSERTINTO`users`VALUES('4','小花');
#INSERTINTO`users`VALUES('5','小花');
#INSERTINTO`users`VALUES('6','小虎');

布置数据

mysql>INSERTINTO`users`VALUES('1','小明');
QueryOK,1rowaffected(0.00sec)
mysql>INSERTINTO`users`VALUES('2','小虎');
QueryOK,1rowaffected(0.00sec)
mysql>INSERTINTO`users`VALUES('3','小花');
QueryOK,1rowaffected(0.00sec)
mysql>INSERTINTO`users`VALUES('4','小花');
QueryOK,1rowaffected(0.00sec)
mysql>INSERTINTO`users`VALUES('5','小花');
QueryOK,1rowaffected(0.01sec)
mysql>INSERTINTO`users`VALUES('6','小虎');
QueryOK,1rowaffected(0.00sec)

查阅插入的数量

mysql>select*fromusers;
+----+--------+
|id|name|
+----+--------+
|1|小明|
|2|小虎|
|3|小花|
|4|小花|
|5|小花|
|6|小虎|
+----+--------+
6rowsinset(0.00sec)

叁、备份数据库以及演示怎么着回复

[[email protected]~]#mysqldump-uroot-p123456--databaseszxl--single-transaction--flush-logs--master-data=2>/tmp/zxl_users.sql

Warning: Using a password on the command line interface can be insecure.

注:提醒warning,因为5.六版本扩充安全机制,不允许在指令行中出现密码,具体没商讨,能够在my.cnf文件中加入[mysqldump]下参预用户以及密码就不会唤醒warning了。

备份数据库之后,再次插入新的数量。

mysql>INSERTINTO`users`VALUES('7','bob');
QueryOK,1rowaffected(0.01sec)
mysql>INSERTINTO`users`VALUES('8','tom');
QueryOK,1rowaffected(0.00sec)
mysql>INSERTINTO`users`VALUES('9','lili');
QueryOK,1rowaffected(0.00sec)

查阅新插入的数码

mysql>select*fromusers;
+----+--------+
|id|name|
+----+--------+
|1|小明|
|2|小虎|
|3|小花|
|4|小花|
|5|小花|
|6|小虎|
|7|bob|
|8|tom|
|9|lili|
+----+--------+
9rowsinset(0.00sec)

除去数据库zxl

mysql>dropdatabasezxl;
QueryOK,1rowaffected(0.01sec)
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|performance_schema|
|test|
+--------------------+
4rowsinset(0.00sec)

查阅mysqldump备份的sql文件中的pos节点地点以及对应的贰进制文件名

二进制文件以及pos节点如下:

— CHANGE MASTER TO MASTER_LOG_FILE=’master-bin.000060′,
MASTER_LOG_POS=120;

动用mysqlbiglog查看二进制文件,删除数据库zxl的at地点是778

[[email protected]]#mysqlbinlogmaster-bin.000060
#at778
#16012014:25:42serverid1end_log_pos867CRC320x014503a4Querythread_id=44exec_time=0error_code=0
SETTIMESTAMP=1453271142/*!*/;
dropdatabasezxl

备份二进制日志地方

[[email protected]~]#mysqlbinlog--start-position=120--stop-position=778/usr/local/mysql/data/master-bin.000060>/tmp/big_log.sql

复原数据库

[[email protected]~]#mysql-uroot-p

查阅恢复生机的数据库

关闭二进制日志

mysql>setsessionsql_log_bin=0;
QueryOK,0rowsaffected(0.00sec)
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|performance_schema|
|test|
|zxl|
+--------------------+
5rowsinset(0.00sec)

查看恢复的表
mysql>usezxl;
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A
Databasechanged
mysql>showtables;
+---------------+
|Tables_in_zxl|
+---------------+
|users|
+---------------+
1rowinset(0.00sec)
mysql>select*fromusers;
+----+--------+
|id|name|
+----+--------+
|1|小明|
|2|小虎|
|3|小花|
|4|小花|
|5|小花|
|6|小虎|
+----+--------+
6rowsinset(0.00sec)
注:后增加的数据没有恢复过来

苏醒二进制备份的big-log文件

[[email protected]~]#mysql-uroot-p

双重查看users表

mysql>select*fromusers;
+----+--------+
|id|name|
+----+--------+
|1|小明|
|2|小虎|
|3|小花|
|4|小花|
|5|小花|
|6|小虎|
|7|bob|
|8|tom|
|9|lili|
+----+--------+
9rowsinset(0.00sec)

开启big-log

mysql>setsessionsql_log_bin=1;
QueryOK,0rowsaffected(0.00sec)

注:关闭不倒闭贰进制唯有不开展其余操作就可以不小憩,原因你懂的。

注:在事实上复苏时最棒编辑my.cnf配置文件,增加如下项:

skip-networking //跳过网络作用来平复数据

mysql版本
[[email protected]~]#mysql-VmysqlVer14.14Distrib5.6.28,forlinux-glibc2.5(x86_64)usingEditLinewrapper
一、mysqldump mysqldump仅…

发表评论

电子邮件地址不会被公开。 必填项已用*标注

标签:
网站地图xml地图