完全备份
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'
日志管理
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）
重复记录
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)
删除订阅
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截断问题
监控触发器问题
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]
time tcpdump -i eth1 -s 1500 src host 192.168.2.10 -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安装
mysqld启动的时候加上–skip-grant-tables，然后马上修改密码，修改后去掉–skip-grant-tables，然后就OK了
create event
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%' ;
源码下载
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)
该贴由system转至本版2014-9-9 23:13:27
当前位置：
论坛首页 »
自由讨论区 »
VMware, Unix及操作系统讨论区
»
发起投票
技术讨论
加好友
发消息
2014-9-9 12:07:29 |
赞(
操作
