工作笔记

1、mysql更新content_hit时间表的命令：
update content_hit set update_time1='2013-02-18 15:41:18' , create_time='2013-02-18 15:41:18' where ID= 1751;

2、mysql更新热度统计值，命令：
update content_hit set visit_count = '13'

3、mysql中提取某段时间内的outer_id，命令：
select outer_id from physical_file where update_time >='2013-02-21 12:00:00' and update_time <='2013-02-21 12:00:00'
4、数据库查询内容的状态，命令：
SELECT id, delete_flag,recycle_flag FROM physical_file WHERE outer_id ='XXXXX'

5、按内容的ID查询状态：
select * from content_storage_mapping where file_id in (58945,59070)

6、按ISA的ID查询ISA的信息：
select * from equipment where id = 2872

7、按内容的ID查询内容的删除信息：
select id, delete_flag,recycle_flag from physical_file where id = 59070

8、按内容ID修改内容的删除状态：
UPDATE PHYSICAL_FILE SET recycle_flag = 0 WHERE ID = 59070
SELECT * FROM content_hit
SELECT  visit_count from content_hit
UPDATE content_hit set visit_count = '13'
UPDATE content_hit set update_time = '2013-02-26 15:31:38',create_time = '2013-02-26 15:41:38'
SELECT * from business_strategy
update business_strategy set update_time = '2013-02-26                                                                                                                                                                                        15:43:38'
SELECT visit_count from content_hit where outer_id='QingniuN/PhysicalFile00000000000000095274'
SELECT * from content_hit where visit_count >='10'


9.删除存储记录语句：
update physical_file set delete_flag=1
update content_storage_mapping set delete_flag=1 where storage_id='1178'

10.提取outer_id，还存在删除过的记录，命令：
select outer_id  from physical_file where id  in (select file_id from content_storage_mapping where storage_id=1178 and delete_flag=0) and delete_flag=0

11.模糊查询语句：
select * from content_hit_stataistics_hour where outer_id lide "QingniuN"

12.查询所有点播内容的outer_id和时长
select outer_id,duration from physical_file where file_type = 1

13、查询文件大小和码率
select * from physical_file where file_size=0 and transfer_rate=0 and delete_flag=0;
统计点播内容时长为0
select * from physical_file where file_type=1 and duration like '%2000-01-01 00:00:00%'

13、查询点播的份数
select outer_id,count(csm.id) from physical_file pf inner join content_storage_mapping csm on pf.id = csm.file_id and csm.delete_flag=0 where pf.delete_flag=0 and pf.file_type=1 group by outer_id order by count(csm.id) desc

select outer_id,count(csm.id) from physical_file pf left join content_storage_mapping csm on pf.id = csm.file_id and csm.delete_flag=0 where pf.delete_flag=0 group by outer_id

14、创建
create table a4883 as select id,relative_path from physical_file where file_type = 1 and delete_flag=0 and file_size=0 and id in(select file_id from content_storage_mapping where
STORAGE_id=4883)

select CONCAT(id,'|',relative_path) from a4883;

select * from physical_file LIMIT 1;

15、查询注入的内容
SELECT *FROM physical_file WHERE file_type=1 AND delete_flag=0 ORDER BY update_time DESC LIMIT 10;

16、删除点播内容的2和4倍数文件
-- 备份物理文件表
CREATE TABLE physical_file_bak20131010 AS SELECT * FROM physical_file;


-- 获取要更新的记录
CREATE TABLE physical_update AS SELECT id FROM physical_file_bak20131010 WHERE INSTR(subsidiary_media_files,'bw2')>0 AND delete_flag=0 AND file_type=1;


-- 删除bw2记录
UPDATE physical_file SET subsidiary_media_files =
  REPLACE(subsidiary_media_files,SUBSTR(subsidiary_media_files,INSTR(subsidiary_media_files,'|bw2'),INSTR(subsidiary_media_files,'bw2.ts')+6-INSTR(subsidiary_media_files,'|bw2')),'')
WHERE id IN (SELECT id FROM physical_update)

-- 删除bw4记录
UPDATE physical_file SET subsidiary_media_files =
  REPLACE(subsidiary_media_files,SUBSTR(subsidiary_media_files,INSTR(subsidiary_media_files,'|bw4'),INSTR(subsidiary_media_files,'bw4.ts')+6-INSTR(subsidiary_media_files,'|bw4')),'')
WHERE id IN (SELECT id FROM physical_update)

-- 删除fw2记录
UPDATE physical_file SET subsidiary_media_files =
  REPLACE(subsidiary_media_files,SUBSTR(subsidiary_media_files,INSTR(subsidiary_media_files,'|fw2'),INSTR(subsidiary_media_files,'fw2.ts')+6-INSTR(subsidiary_media_files,'|fw2')),'')
WHERE  id IN (SELECT id FROM physical_update)

-- 删除fw4记录 同时更新update_time

UPDATE physical_file SET  update_time = NOW(), subsidiary_media_files =
  REPLACE(subsidiary_media_files,SUBSTR(subsidiary_media_files,INSTR(subsidiary_media_files,'|fw4'),INSTR(subsidiary_media_files,'fw4.ts')+6-INSTR(subsidiary_media_files,'|fw4')),'')
WHERE id IN (SELECT id FROM physical_update);

17.按outer_id查询属于的ISA
SELECT c.storage_id FROM physical_file p JOIN content_storage_mapping c ON p.id=c.file_id WHERE p.outer_id LIKE '%0000001929042%'

18.更新数据库的命令：
SET GLOBAL log_bin_trust_function_creators=1/ON;
source XXX.sql

19、数据重置密码
修改/etc/my.cnf配置文件，在[mysqld]下添加skip-grant-tables
      重启mysql服务  service mysql start，登录mysql无需密码
就可以不需要密码就进入 MySQL 了。
mysql>use mysql
mysql>update user set password =password("gmuser") where user="root" and host="localhost";
mysql>flush privileges;
grant all privileges on iptv_data.* to 'iptv_data'@'localhost' identified by 'gmuser' with grant option;
grant all privileges on iptv_data.* to 'iptv_data'@'%' identified by 'gmuser' with grant option;
重新杀 MySQL ，用正常方法启动 MySQL

1.》停掉mysql服务。a.使用net stop mysql. b.控制面板->管理工具->服务，找到mysql，停掉。c.Ctrl+Alt+Del，结束和mysql相关的进程。比如mysql.exe,mysqld.exe.（主要是怕你在看这篇帖之前用过其他方法。以上三种方式可以结合使用，总之就是以把mysql服务给STOP掉为最终目的。）

2.》新建一个txt文件，名字随意，比如：mysql-init.txt。写入如下命令，并保存，再建议放到c盘根目录下。

update mysql.user set password=password('newpassword') where user='root';

flush privileges;

将红色部分替换成你的新密码。

3.》起一个cmd.exe，cd到mysql的安装目录下的bin文件夹。输入命令：mysqld --init-file=c:\\mysql-init.txt 回车

mysql5.5 对应的是mysqld，如果你看见其他的比如：mysql-nt等，这是版本的区别，所以寻找与你版本对应的解决方法。

4.》稍等片刻，另起一个cmd.exe.要用到的命令：

net start mysql

等待启动...

mysql -u root -p

20、cmd界面查看数据库表
show full processlist;

21、查询一个内容所在存储：
select * from equipment where id in (select csm.storage_id from physical_file pf, content_storage_mapping csm where pf.id = csm.file_id and pf.delete_flag = 0 and csm.delete_flag = 0 and pf.outer_id ='')

22、查询一个存储所有的回看内容：
SELECT pf.outer_id FROM content_storage_mapping csm,physical_file pf
WHERE csm.storage_id=1102 AND csm.delete_flag =1 AND csm.file_id=pf.id AND pf.file_type=21 AND  pf.create_time>'2013-09-18 00:00:00' AND pf.create_time<'2013-09-19 00:00:00'; 

23、查询时长为0的所有内容
SELECT * FROM physical_file WHERE file_type=1 AND duration LIKE '%00:00:00%' AND delete_flag=1

24、删除多余的表
DROP TABLE `content_result_20130716`;

DROP TABLE `content_storage_mapping_20131107`;

DROP TABLE `dispatch_task_20130716`;

DROP TABLE `movietask`;

DROP TABLE `physical_file_20130510`;

DROP TABLE `physical_file_20131107_fize0`;

DROP TABLE `physical_file_20131107_fize1`;

DROP TABLE `physical_file_20131107_fize_4357`;

DROP TABLE `physical_file_20131107_fize_4637`;

DROP TABLE `physical_file_20131107_fize_4642`;

DROP TABLE `physical_file_20131107_fize_4778`;

DROP TABLE `physical_file_20131107_fize_4784`;

DROP TABLE `physical_file_20131107_fize_4880`;

DROP TABLE `physical_file_20131107_fize_4883`;

DROP TABLE `physical_file_20131107_fize_4899`;

DROP TABLE `physical_file_20131107_fize_5101`;

DROP TABLE `physical_file_20131107_fize_5221`;

DROP TABLE `physical_file_20131107_fize_5236`;

DROP TABLE `physical_file_20131107_fize_7605`;

DROP TABLE `physical_file_20131107_fize_7608`;

DROP TABLE `physical_file_20131107_fize_9432`;

DROP TABLE `physical_file_20131107_fize_9806`;

DROP TABLE `physical_file_bak20131022`;

DROP TABLE `physical_update`;

DROP TABLE `schedule_record_2_bak_20130509`;

DROP TABLE `schedule_recored_2_20130515`;

DROP TABLE `synctask_201307102000`;

DROP TABLE `synctask_201307111620`;

DROP TABLE `synctask_20130716`;

DROP TABLE `synctask_bak`;

DROP TABLE `t1`;

DROP TABLE `t2`;

25、linux下安装mysql时，卸载时一直提示有关联关系，解决办法：
先rpm -e掉可以卸载的安装包，剩余的再rpm -e --nodeps XXX
26、在平台中更新数据库连接，出现：检测异常，客户端不允许连接mysql
原因是找不到匹配的表，需要重新分配用户权限执行以下语句：
grant all privileges on iptv_data.* to 'iptv_data'@'localhost' identified by 'gmuser' with grant option;
grant all privileges on iptv_data.* to 'iptv_data'@'%' identified by 'gmuser' with grant option;
执行时，如果报：ERROR 1133 (42000): Can't find any matching row in the user table
原因是：从mysql错误代码说明里，可以查到mysql错误1133是数据库用户名不存在，如下：
1133：MYSQL数据库用户不存在
如此，找到的最简单的办法就是在mysql命令行中执行FLUSH PRIVILEGES;这一语句即可。

mysql提示1133错误的原因是，变更了mysql.user表之后，没有使用FLUSH PRIVILEGES命令来更新权限表（grant tables）。

26、更新表中的ip
UPDATE equipment SET outer_ip = '19.16.18.10'
UPDATE equipment SET ip = '19.16.18.10'

27、Linux下密码mysql的root密码
1. KILL掉系统里的MySQL进程；

killall -TERM MySQLd

2. 用以下命令启动MySQL，以不检查权限的方式启动；

safe_MySQLd --skip-grant-tables &

3. 然后用空密码方式使用root用户登录 MySQL；

MySQL -u root

4. 修改MySQLroot用户的密码；

1.           MySQL> update MySQL.user set password=PASSWORD('gmuser') where User='root';  

2.           MySQL> flush privileges;  

3.           MySQL> quit 

重新启动MySQL，就可以使用新密码登录了
方法二：
1．首先确认服务器出于安全的状态，也就是没有人能够任意地连接MySQL数据库。
因为在重新设置MySQL的root密码的期间，MySQL数据库完全出于没有密码保护的
状态下，其他的用户也可以任意地登录和修改MySQL的信息。可以采用将MySQL对
外的端口封闭，并且停止Apache以及所有的用户进程的方法实现服务器的准安全
状态。最安全的状态是到服务器的Console上面操作，并且拔掉网线。
2．修改MySQL的登录设置：
# vi /etc/my.cnf
在[mysqld]的段中加上一句：skip-grant-tables
例如：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables
保存并且退出vi。
3．重新启动mysqld
# /etc/init.d/mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]
4．登录并修改MySQL的root密码
# /usr/bin/mysql
mysql> USE mysql ;
tabase changed
mysql> UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root' ; 
mysql> flush privileges ;
mysql> quit
5．将MySQL的登录设置修改回来
# vi /etc/my.cnf
将刚才在[mysqld]的段中加上的skip-grant-tables删除
保存并且退出vi。
6．重新启动mysqld
# /etc/init.d/mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

28、检查死锁的命令：
mysql -uroot -p -e"show engine innodb status\G" | grep -i deadlock

29、现网告警时间：
告警时间为服务产生告警的时间
在一分钟内向邮件服务器发送邮件告警
日志关键字：成功向维护人员
SELECT * FROM `alarm_message` where MONITOR_DESCRIPTION Like '%192.168.13.81心跳汇报超时%';

30、网管中查并发数的命令:
SELECT * FROM online_statistic_20140331 WHERE ies_id=7525 AND online_count=40
SELECT * FROM online_statistics_hours WHERE ies_id=7525 AND create_time>='2014-03-31 00:00:00' AND create_time<'2014-04-01 00:00:00'

31、添加Agent命令
INSERT INTO nmequipment (
`type_id`,
  `name` ,
  `ip` ,
  `outer_ip` ,
  `port` ,
  `url` ,
  `protocol` ,
  `description` ,
  `config`,
  `update_time` ,
  `extend_flag` ,
  `remark` ,
  `device_id` ,
  `service_id` ,
  `create_time` ,
  `ismonitor` ,
  `delete_flag` ,
  `join_type` ,
  `domain_id` ,
  `Is_enable` ,
  `version_number`
)
SELECT  `type_id`,
  `name` ,
  '1.25.203.201' ,
  '1.25.203.201' ,
  `port` ,
  `url` ,
  `protocol` ,
  `description` ,
  `config`,
   NOW() ,
  `extend_flag` ,
  `remark` ,
  `device_id` ,
  `service_id` ,
   NOW(),
  `ismonitor` ,
  `delete_flag` ,
  `join_type` ,
  `domain_id` ,
  `Is_enable` ,
  `version_number`
from  nmequipment WHERE type_id=38  LIMIT 1

32、主键冲突更新语句
1），查询出问题的语句
SELECT pf.id,pf.outer_id,pf.create_time,pf.update_time,csm.file_id FROM physical_file pf
LEFT JOIN content_storage_mapping csm ON csm.file_id=pf.id
WHERE pf.delete_flag=0 AND pf.file_type=1
AND csm.file_id IS NULL AND (csm.delete_flag=0 OR csm.delete_flag IS NULL)

2），查询correlateid语句
SELECT * FROM dispatch_task WHERE content_id
IN('00000001000000020000000002646830')

3），删除语句
UPDATE physical_file SET delete_flag=1,update_time=NOW() WHERE id IN(36009942,36009943,36009944,36009945,36010016,36010017,36010018,36010019,36010021,36010022,36010023,36010024)

SELECT outer_id,SUBSTRING_INDEX(outer_id,'/',-1) FROM physical_file WHERE id IN(35881214,358812115,35882804,35882805,35961137,35961138,35961139,35962181)

33、数据库切分命令：
vmstat -t -n 1 > sys.vmstat.log &

34、linux下查看cpu型号命令：
cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
http://www.oschina.net/question/12_30886?sort=time

35、查看device_id一致的命令：
select  device_id,count(*) from equipment_2 where delete_flag=0 and type_id=38
group by device_id

36、windows下mysql数据库备份
mysqldump -q -umysql -ppassword --add-drop-table --all-databases > c:/all.sql

linux下mysql备份
1》，备份命令：mysqldump -uroot -p123456 --single-transaction --flush-logs --quick --allow-keywords --add-locks --routines --triggers --events --add-drop-database --add-drop-table --all-databases > all_databases_neimeng_updates_201311121314.sql
2》、更新命令：mysql -uroot -p123456 < nmlt_0_updates.sql
3》、回滚命令：mysql -uroot -p123456 < all_databases_neimeng_updates_201311121314.sql

37、更新两个ID一致问题的命令
CREATE TABLE temp_3
AS
SELECT MAX(id) id FROM temp_2 GROUP BY outer_id

SELECT * FROM temp_3;

CREATE TABLE temp_4
AS
SELECT l.id  FROM temp_3 t3,temp_2 l WHERE t3.id=l.id AND l.delete_flag=0;

UPDATE  physical_file SET update_time=NOW() WHERE id IN(46201280,46342277）

38、中继新版本问题
select *from physical_channel where outer_id like '%00000001000000060000000000000033';

select*from physical_file where outer_id like '%00000001000000060000000000000033';

select id, outer_id  from physical_file where content_id=51901 and content_type=2 and file_type=20 and create_time>'2014-10-20'
order by create_time;
51091

select*from physical_file where id=47694191

39、RDP日志不够长时，解决办法：
在web.config中添加以下语句
<!--由于文件名称中增加节点ID后，导致查询请求URL长度超过IIS默认值2048，现将该值调整为4096-->
  <system.webServer>
    <security>
      <requestFiltering>
        <requestLimits maxQueryString="4096" />
      </requestFiltering>
      </security>
  </system.webServer>
 
  40、现网总点播数：
  SELECT COUNT(DISTINCT outer_id) FROM physical_file WHERE delete_flag=0 AND content_type=3

41、现网点播时长
SELECT  SUM(TIME_TO_SEC(duration))/3600 FROM physical_file WHERE file_type=1 AND delete_flag=0  

42，数据库content_storage_mapping插入表
insert into content_storage_mapping(file_opid,file_id,storage_id,update_time,create_time) values('ss01',file_id,storage_id,now(),now());

43、网管库备份
::
:: (C) 2010-2013 redcdn.cn Holding Limited.
::
:: This program is free software; you can redistribute it and/or
:: modify it under the terms of the GNU General Public License
:: version 2 as published by the Free Software Foundation.
::
:: Authors:  
:: [e-mail]  < http://weibo.com/mobi5 >
::
@echo off

set year=%DATE:~0,4%
set day=%DATE:~5,2%
set mnt=%DATE:~8,2%
set hr=%TIME:~0,2%
set min=%TIME:~3,2%

IF %day% LSS 10 SET day=0%day:~1,1%
IF %mnt% LSS 10 SET mnt=0%mnt:~1,1%
IF %hr% LSS 10 SET hr=0%hr:~1,1%
IF %min% LSS 10 SET min=0%min:~1,1%

set backuptime=%year%_%day%_%mnt%_%hr%_%min%

::::::::::::::::::::修改参数::::::::::::::::::::
:: 用户名
set db_user=root
:: 密码
set db_pass=gmuser
:: 备份目录
set bak_dir="D:\WangGuan_bak\IPTVHistoryData"
:: 日志文件
set log_file=%bak_dir%\mysql_backup_db.log
:: 命令目录
set bin_dir="D:\TOOL\mysql\bin"

:: 备份状态
set bak_status=1
rem 1- 为0时表示dump成功
set dump_status=0
set rar_status=0
::::::::::::::::::::修改参数::::::::::::::::::::

:: 创建备份目录
if not exist %bak_dir% md %bak_dir%
:: 备份数据为SQL文件
%bin_dir%\mysqldump.exe -hlocalhost -u%db_user% -p%db_pass% --single-transaction  --quick --allow-keywords --add-locks --routines --events --triggers --add-drop-database --add-drop-table --all-databases > %bak_dir%\%%i.%backuptime%.sql
if %errorlevel% == 0 set /a dump_status = %dump_status% + %errorlevel%

if %dump_status% == 0 set bak_status=%bak_status:1=0%
pause

44、热度统计


SELECT
                                            outer_id,
                                              SUM(visit_count)    visit_count
                                            FROM content_hit_statistics_hour
                                            WHERE update_time >= DATE_FORMAT('2015/7/24 4:00:00','%Y-%m-%d %H:%i:%s')
                                                AND domain_id IN(1149)
                                                AND is_hit = 1
                                            GROUP BY outer_id
                                            HAVING SUM(visit_count) > 5
                                            ORDER BY visit_count DESC

45、windows 下mysql查询表结构的命令：
SHOW VARIABLES LIKE '%per_table%';
OFF代表mysql是共享表空间

46、点播无法播放查询
a、先在physical_file中查到该内容状态是否为删除，记录id=A
b、然后根据A，在数据的sqlite中查询是否有数据。如果没有，即没有同步过来。
c、在physical_file中更新update_time时间，语句为
update physical_file set update_time = NOW() where id=A

47、Windows下mysql彻底卸载
在你从电脑里卸载旧的MYSQL数据库服务时，首先先在WINDOWS服务里停掉MYSQL的服务。再到控制面板里的增加删除程序内进行删除，但是你并不能完整的把MYSQL服务删除，那么就要在注册表里(regedit)清除你的MYSQL服务。有几个地方:
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL 目录删除;
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL 目录删除;
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL 目录删除;

48、查看大表的命令：
select DATA_LENGTH/1024/1024,INDEX_LENGTH/1024/1024,table_name,table_schema  from information_schema.TABLES  where DATA_LENGTH>1024*1024*50

49、mysql逻辑备份命令：
mysqldump -u root -p --all-databases > mysql-all.sql 

50、查看字符集
show variables like 'char%';

51、mysql双机热备
###mysql主主复制的验证

用mysql工具在主机和备机分别建立一个名为nps2的库
在mysql主机和备机上分别执行下列操作，并记录下MASTER_LOG_FILE和MASTER_LOG_POS的数值。
首先，在主机上执行：
FLUSH TABLES WITH READ LOCK; #锁表，让status不再进行变化
SHOW MASTER STATUS;
UNLOCK TABLES; #解锁
屏幕会显示MASTER_LOG_FILE和MASTER_LOG_POS的数值，请记录下来。

然后，在备机上执行：
FLUSH TABLES WITH READ LOCK; #锁表，让status不再进行变化
SHOW MASTER STATUS;
UNLOCK TABLES; #解锁
屏幕会显示MASTER_LOG_FILE和MASTER_LOG_POS的数值，请记录下来。

在mysql主机上面给主主复制的用户添加权限
grant all privileges on *.* to root@'%' identified by 'mysql';

在mysql备机上面给主主复制的用户添加权限
grant all privileges on *.* to root@'%' identified by 'mysql';

在mysql主机上面输入如下命令
> CHANGE MASTER TO    #输入完后会进行如下信息的填写
> MASTER_HOST='192.168.12.131', #备机的IP地址
> MASTER_USER='root', #备机的用户名
> MASTER_PASSWORD='mysql', #备机的密码
> MASTER_LOG_FILE='mysql-bin.000003', #备机的MASTER_LOG_FILE的号
> MASTER_LOG_POS=107; #备机的MASTER_LOG_POS号

在mysql备机上面输入如下命令
> CHANGE MASTER TO    #输入完后会进行如下信息的填写
> MASTER_HOST='192.168.101.15', #主机的IP地址
> MASTER_USER='root', #主机的用户名
> MASTER_PASSWORD='!QAZxsw2,./', #主机的密码
> MASTER_LOG_FILE='mysql-bin.000001', #主机的MASTER_LOG_FILE的号
> MASTER_LOG_POS=106; #主机的MASTER_LOG_POS号

在mysql主机和备机上面分别执行下述指令，使主主复制功能启动。
START SLAVE;

52、Keepalive
查看浮云IP命令：
ip add

53、查询mysql库已有表信息：
SELECT table_name, table_type, ENGINE
FROM information_schema.tables
WHERE table_type='base table'
ORDER BY table_name DESC;

54、查询mysql库已有触发器信息：
SELECT * FROM information_schema.`TRIGGERS`;