维护杂记

 

功能	语句	备注
完全备份	BACKUP DATABASE [tianyaportal] TO  DISK = N'F:\backup\tianyaportal' WITH  INIT ,  NOUNLOAD ,  NAME = N'tianyaportal backup',  NOSKIP ,  STATS = 10,  NOFORMAT
差异备份	BACKUP DATABASE [tianyauser]  TO  DISK = N'f:\backup_diff\tianyauser_diff'  WITH  DIFFERENTIAL, INIT ,  NOUNLOAD ,  NAME =N'tianyauser diff',  NOSKIP ,  STATS = 10,  NOFORMAT
完全还原	RESTORE DATABASE tianyawap    FROM disk='d:\backup\tianyawap'    WITH NORECOVERY,       MOVE 'tywap_data' TO 'd:\sqldata\tianyawap_data.mdf',   ---主数据文件名       MOVE 'tywap_log' TO 'd:\sqldata\tianyawap_log.ldf', --日志文件名   file=1---这里输入完全备份的文件号
差异还原	RESTORE DATABASE tianyauser    FROM disk='d:\backup\tianyauser_diff_Monday'    WITH RECOVERY,       MOVE 'tianyauser_data' TO 'e:\sqlData\tianyauser_data.mdf',   ---主数据文件名       MOVE 'tianyauser_log' TO 'f:\sqllog\tianyauser_lgo.ldf', --日志文件名    file=1---这里输入差异备份的文件号
删除用户	ALTER   AUTHORIZATION   ON   SCHEMA::db_owner   TO   dbo;
	use tianlang go declare @aa varchar(255) declare @bb varchar(255) DECLARE @OutCrsr CURSOR SET @OutCrsr = CURSOR FOR select name from sysobjects where uid<>1 and (xtype='P' or xtype ='V' or xtype='U') OPEN @OutCrsr FETCH next FROM @OutCrsr into @aa WHILE (@@FETCH_STATUS <> -1) BEGIN select @bb='datauser2.'+@aa   exec sp_changeobjectowner @bb,dbo    FETCH NEXT FROM @OutCrsr into @aa END CLOSE @OutCrsr go sp_dropuser datauser2 go sp_adduser 'datauser2','datauser2','db_datareader' sp_adduser 'datauser2','datauser2','db_datawriter'
添加登录	sp_addlogin [ @loginame = ] 'login'     [ , [ @passwd = ] 'password' ]     [ , [ @defdb = ] 'database' ]     [ , [ @deflanguage = ] 'language' ]     [ , [ @sid = ] sid ]     [ , [ @encryptopt= ] 'encryption_option' ]
添加用户	sp_adduser [ @loginame = ] 'login'         [ , [ @name_in_db = ] 'user' ]         [ , [ @grpname = ] 'role' ]
更改用户权限	grant select on object to user
链接服务器	sp_addlinkedserver   [   @server   =   ]   'server'             [   ,   [   @srvproduct   =   ]   'product_name'   ]             [   ,   [   @provider   =   ]   'provider_name'   ]             [   ,   [   @datasrc   =   ]   'data_source'   ]             [   ,   [   @location   =   ]   'location'   ]             [   ,   [   @provstr   =   ]   'provider_string'   ]             [   ,   [   @catalog   =   ]   'catalog'   ]
	{ODBC;Driver=SQL Server;Server=19.2.168.49;}
	exec sp_addlinkedserver  'TIANYAUSER','','SQLOLEDB','19.2.168.27'      exec sp_addlinkedsrvlogin 'TIANYAUSER','false',null,'tianyauser_link','KNF93nJYRujtPNHJS*$93nfd'    exec sp_serveroption 'TIANYAUSER','rpc out','true'
cmd连接	C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isql.exe
查锁、堵塞	use master go declare @spid int,@bl int DECLARE s_cur CURSOR FOR select  0 ,blocked from (select * from sysprocesses where  blocked>0 ) a where not exists(select * from (select * from sysprocesses where blocked>0 ) b where a.blocked=spid) union select spid,blocked from sysprocesses where  blocked>0 OPEN s_cur FETCH NEXT FROM s_cur INTO @spid,@bl WHILE @@FETCH_STATUS = 0 begin if @spid =0  select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' else select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl ) FETCH NEXT FROM s_cur INTO @spid,@bl end CLOSE s_cur DEALLOCATE s_cur exec sp_who2
删除系统扩展存储过程	sp_dropextendedproc 'xp_cmdshell'
添加系统扩展存储过程	sp_addextendedproc 'xp_cmdshell'
日志管理	backup log hainanec with no_log --截断日志 exec sp_dboption 'hainanec','autoshrink','true' --数据库设置为自动收缩 dbcc shrinkdatabase(hainanec,10) --收缩数据库，保留10％的空闲空间 backup log tianyatempdata with no_log --截断日志 exec sp_dboption 'tianyatempdata','autoshrink','true' --数据库设置为自动收缩 DBCC SHRINKFILE  (TianyaTempData_Log,TRUNCATEONLY) --收缩LOG文件
删除过期数据	delete from TechForum_response_deny where dtResponseDatetime <=dateadd(minute,-7,getdate());
删除复制项目	sp_droparticle @publication = N'tianyauser', @article = N'tianyaadsum', @force_invalidate_snapshot = 1 sp_dropsubscription @publication = N'tianyauser', @article = N'tianyaadsum',@subscriber=N'TYDB17', @destination_db = N'tianyauser'
添加固定数据库角色	sp_addrolemember   'db_datawriter','test'
数据库附加	USE [master] GO CREATE DATABASE [database_name] ON ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\<database name>.mdf' ), ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\<database name>.ldf' )  FOR ATTACH ; GO
删除临时表	if   exists(select   1   from   tempdb..sysobjects   where   id=object_id('tempdb..#queries_staticstics_groupbydb'))     drop   table   #queries_staticstics_groupbydb     go
修复用户	sp_change_users_login 'auto_fix','Tenya@developer'
修改密码	ALTER LOGIN tylogin@tydatauser WITH PASSWORD='dfDS87y39(2';
连接数	select connectnum=count(distinct net_address)-1 from master..sysprocesses
跟踪触发器	################################################################################
标识复制日志	EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1
mysql
联查	select  i_wip,i_locate,server_hardware.h_manufacturer,server_hardware.h_bodytype from server_info INNER JOIN server_hardware on server_hardware.H_serverserial=server_info.i_serverserial   order by i_wip
SQL获取IP地址，网卡，计算机名	SQL获取IP地址，网卡，计算机名 新一篇: SQL计算两个日期之间的工作天数   --网卡: create table #tb(id int identity(1,1),hostname nchar(128),loginname nchar(128),net_address nchar(12),net_ip nvarchar(15)) insert into #tb(hostname,net_address,loginname) select distinct hostname,net_address,loginame from master..sysprocesses where hostname<>'' declare @id int,@sql varchar(500) select @id=max(id) from #tb create table #ip(id int,a varchar(8000)) while @id>0 begin     select @sql='ping '+hostname from #tb where id=@id     insert #ip(a) exec master..xp_cmdshell @sql     update #ip set id=@id where id is null     set @id=@id-1 end update #tb set net_ip=left(a,patindex('%:%',a)-1) from #tb a inner join ( select id,a=substring(a,patindex('Ping statistics for %:%',a)+20,20) from #ip     where a like 'Ping statistics for %:%') b on a.id=b.id select * from #tb drop table #tb,#ip ------------------------------------- declare @ip varchar(15),@sql varchar(1000) --得到ip地址 create table #ip(a varchar(200)) set @sql='ping '+host_name()+' -a -n 1 -l 1' insert into #ip exec master..xp_cmdshell @sql select @ip=left(a,patindex('%:%',a)-1) from( select a=substring(a,patindex('Ping statistics for %:%',a)+20,20) from #ip where a like 'Ping statistics for %:%') a --显示结果 select 用户计算机名=host_name(),ip地址=@ip drop table #ip /* 用户计算机名                                                                                                                           ip地址            -------------------------------------------------------------------------------------------------------------------------------- --------------- FLYSTONE                                                                                                                         192.168.0.76 */ ------------------------------------ --网卡ID DECLARE @NetID VARCHAR(32) EXEC master..xp_by02 @NetID OUTPUT select @NetID ------ --显示芯片信息 exec xp_msver 'Platform'--可参照联机帮助，有几项选择 --处理器类型 exec xp_msver 'ProcessorType'
删除用户架构	ALTER AUTHORIZATION ON SCHEMA::ty_no20@link TO dbo
重建索引	DBCC   DBREINDEX('database_name.owner.table_name',index_name,fillfactor)/DBCC   CHECKDB   （'pubs',repair_rebuild）
	DBCC UPDATEUSAGE
更改数据选项(单用户)	Sp_dboption 'tianyawap', 'single user', 'true'
重复记录	1. 获取某字段或几个字段有重复的数据，可限定重复几条       select field1,field2 from table group by field1,field2 having count(field1)>1    2. 获取某字段不重复的最新记录       select top * from table a where id in(select max(id) from table b group by field) order by id desc    3. 获取某字段的重复数       select count(field) from table group by field having count(field)>1    4. 获取不重复的记录       select field1,field2 from table group by field1,field2    5. 删除重复记录       delete from from table a where id not in(select max(id) from table b group by field)
事务
执行成本	set statistics io on
删除订阅	SP_DROPSUBSCRIPTION @publication='tyuser_login',@article='all',@subscriber='tyuser_login'
进程信息	with tb as ( select blocking_session_id, session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests  a CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) ), tb1 as ( select a.*,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage*8 as 'memory_usage(KB)', total_scheduled_time,reads,writes,logical_reads from tb a inner join master.sys.dm_exec_sessions b on a.session_id=b.session_id ) select a.*,connect_time,client_tcp_port,client_net_address from tb1 a inner join master.sys.dm_exec_connections b on a.session_id=b.session_id
SQL2005字符集的设置	SQL2005字符集的设置： alter    datebase     数据库名  COLLATE    chinese_prc_ci_as 使用如下命令，可以获得更多的SQL排序规则：  select  *    from  ::fn_helpcollations()
SQL2005数据库库批量更改对象所属架构	declare @name sysname declare csr1 cursor for    select TABLE_NAME from INFORMATION_SCHEMA.TABLES open csr1 FETCH NEXT FROM csr1 INTO @name while (@@FETCH_STATUS=0)    BEGIN SET @name='原架构名.'+@name          EXEC SP_ChangeObjectOwner @name, '新架构名' fetch next from csr1 into @name    END CLOSE csr1 DEALLOCATE csr1
sql截断问题	监控触发器问题
导出SQL
vpn	route -p add 10.1.0.0 mask 255.255.0.0 19.2.172.5
drop distribution	use master go alter database distribution set offline; drop database distribution;
修改文件增长属性	USE [master] GO ALTER DATABASE [YouDBName] MODIFY FILE ( NAME = N’FileLogicalName’, FILEGROWTH = 100%) GO
A. 返回在最近 N 天内修改过的所有对象	A. 返回在最近 N 天内修改过的所有对象 运行以下查询之前，请使用有效值替换 <database_name> 和 <n_days>。 复制  USE <database_name>; GO SELECT name AS object_name   ,SCHEMA_NAME(schema_id) AS schema_name   ,type_desc   ,create_date   ,modify_date FROM sys.objects WHERE modify_date > GETDATE() - <n_days> ORDER BY modify_date; GO
Grant VIEW SERVER STATE to [LoginUser]
复制出错     IO vmstat 5 > vmstat.txt 中文支持 time tcpdump -i eth1 -s 1500 src host 192.168.2.10 -w 20081119-db-traffic-01.dmp time tcpdump -i eth1 -s 1500 -w 20081119-db-traffic-01.dmp strings 20081119-db-traffic-01.dmp |grep -i 'insert'| awk '{printf("%s %s %s %s\n",$1,$2,$3,$4);}'|sort|uniq -c| awk '{printf("%06ld %s %s %s %s\n",$1,$2,$3,$4,$5);}'|sort 监控 wget http://19.2.168.171/install/Servf/install_64x.sh && chmod u+x install_64x.sh && ./install_64.sh 删除日志 30 5 * * * find /data/mysql/data -type f -mtime +5 -name mysql-bin.00*|tee /home/dellog.txt|xargs rm -f NTP安装 cront 程序中也要使用绝对路径 B tree算法 ssh ssh  -1c des  -l tianyas 26.13.1.66 不写到日志中 set SQL_LOG_BIN = 0 select写到日志中 set global general_log = on show variables like '%general%' 补一下补知道root密码情况下修改root密码 mysqld启动的时候加上–skip-grant-tables，然后马上修改密码，修改后去掉–skip-grant-tables，然后就OK了 create event 查看mysql编译参数 cat /usr/local/mysql/bin/mysqlbug |grep ./configure show profile cpu,memory for query 1; 复制状态正常，数据不同步 查看数据名称是否一样，server-id是否正常 复制偿试 expire_logs_days  终端满 mstsc / v:ip:port /console[admin] mysqlreport mysqlreport-3.5/mysqlreport --host 19.2.168.67 --user dbmonitor --password mysqlsla mysqlsla -lt slow -percent 100 -top 1000 slowquerylog > 91slowresult innotop /usr/bin/innotop -h19.2.168.67 -uinnotop -p 963214785 -d 1 -m Q  转码  iconv -f unicode -t utf-8  tianyacity5.csv > tianyacity6.csv 平均负载 select servername as 服务器,avg(cast(`rrqms` as SIGNED )) as 平均IO读,avg(cast(`wrqms` as SIGNED)) as 平均IO写,(avg(cast(`insert` as SIGNED))+avg(cast(`update` as SIGNED))+avg(cast(`read` as SIGNED))) as 平均SQL数,avg(cast(`ioutil` as SIGNED)) as 平均IO使用率 from SQL_mysql where servername='19.2.168.68' and MonitorTime>'2010-11-14 00:00:00' \G; event MySQL 获取数据库实际的大小 SELECT 2 CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2))," MB") AS Size  3 FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like '%YOUR_DATABASE_NAME_HERE%' ; mysql库表索引大小 浅析MySQL数据碎片的产生 源码下载 http://mysql.ntu.edu.tw/Downloads/MySQL-5.1/mysql-5.1.56.tar.gz 支持字符集 show character set warning: Setting locale failed."的解决 vi /root/.bashrc 再最底部加上 export LC_ALL=C 或者直接运行 echo "export LC_ALL=C" >> /root/.bashrc 然后执行一下： source /root/.bashrc 查看锁信息 前一天数据 select count(*) from Users_Logined_Log_201109 where loginTime>date_sub(curdate(),interval 1 day) and loginTime<date_sub(curdate(),interval 0 day); 动态SQL set @vartime=left(curdate()+0,6); set @sql=concat('select * from Users_Logined_Log_',@vartime); set @sql=concat(@sql,' where loginTime>=''2011-09-06'' and loginTime <''2011-09-07'' limit 5'); prepare sqlstr from @sql; execute sqlstr; 触发器权限问题 grant veiw server state to [user] 一月前 delete from table where 时间字段 < date_add(curdate(),INTERVAL -1 month) 统计连接 select USER,substring(HOST,1,12) as host, count(*) from information_schema.PROCESSLIST group by USER,substring(HOST,1,12); 统计昨天 SELECT COUNT(id),SUM(pv)     FROM tb_wap_log_stat_mobileno_dis     WHERE domainid=1 AND stardate >=date_sub(curdate(),interval 1 day)     AND stardate<date_sub(curdate(),interval 0 day)