MySQL 解压版安装(Windows Server 2016 x64)

1. 本文说明

本文主要讲述 MySQL解压版在 Windows Server2016 x64 服务器上的安装过程,用于记录自己在安装MySQL过程中遇到的坑和基本 的使用方法。理论上这些内筒也适用于Windows10和Window7用户。

2. 软件下载

这里,下载MySQL最新社区版。我下载的版本是:mysql-5.7.18-winx64.zip

软件下载

继续阅读“MySQL 解压版安装(Windows Server 2016 x64)”

SQL Server跨服务器访问实例

SQL Server 跨服务器访问实例功能非常好用,访问其他服务器可以像访问同一个数据库一样便捷。

SQL跨服务器访问设置:

EXEC master.dbo.sp_addlinkedserver
@server = N'TEST74', --被访问的服务器别名
@srvproduct = N' ',
@provider = N'SQLNCLI',
@datasrc = N'10.191.3.74'; --要访问的服务器

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'TEST74',--被访问的服务器别名
@useself = N'False',
@locallogin = NULL,
@rmtuser = N'sa', --账户
@rmtpassword = '@@@@000'; --密码

GO

查询语句前要添加服务器表名和数据库名:

--查询测试语句

SELECT * FROM [TEST74].TESTDB.dbo.EquipmentList

SQL Server 数据库还原

数据库恢复大多是让人很头痛的部分。比如我,经常需要把生成环境的数据备份下来还原到测试环境中进行新功能的测试。

带我的前辈告诉我的方式是在SSMS中直接右键还原源选择恢复数据的来源数据库,目标选择要还原的数据库。

SQL Server 数据库还原

这种方式的成功率很低!经常会数据库使用中的错误提示:Exclusive access could not be obtained because the database is in use

后来找了两种方式来保证还原时数据库没被使用:

方法一:变成单用户模式

--变成单用户模式
USE master
GO
ALTER DATABASE test3 --换成你的数据库名
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

--恢复多用户模式
USE master
GO
ALTER DATABASE test3 --换成你的数据库名
SET MULTI_USER
Go

方法二:脱机

---开始脱机
ALTER DATABASE test --你的数据库名
SET OFFLINE WITH ROLLBACK IMMEDIATE;

--恢复联机
ALTER DATABASE test --你的数据库名
SET ONLINE WITH ROLLBACK IMMEDIATE;

这样恢复成功的概率增加了很多,不过依然有一些莫名奇妙的状况导致死活恢复不了。

后来,经过多种查找和尝试,终于找到了比较稳定的方式:

--返回由备份集内包含的数据库和日志文件列表组成的结果集。
--获得逻辑文件名
USE master
RESTORE FILELISTONLY
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\test.bak'

GO

--test和test_log是上面查到的逻辑名称
----注意:当还原数据库时,可以改变数据库的名称以及它的物理文件名称,但是不可以改变它的逻辑名称,所以,当我们将一个数据库还原成为另外一个数据库时,首先要利用RESTORE FILELISTONLY命令得到它的原始逻辑名称
USE master
RESTORE DATABASE test2
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\test.bak'

   WITH MOVE 'test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test2.mdf',

              MOVE 'test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test_log2.ldf',
   STATS = 10, --每完成10%显示一条记录,实时显示在Messages里面
   REPLACE ----加上 REPLACE 参数,则在现有数据库基础上强制还原
GO

效果展示:

总结,从备份到还原完整流程整理如下:

/*
       从备份生成数据库bak文件到还原到另一个数据库的完整流程
*/
USE master
BACKUP DATABASE backDB
  TO DISK = 'D:\Backup\test.bak'

--
RESTORE FILELISTONLY
  FROM DISK = 'D:\Backup\test.bak'

--
RESTORE DATABASE restoreDB
  FROM DISK = 'D:\Backup\test.bak'
  WITH MOVE 'DBTest' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test2.mdf',
  MOVE 'DBTest_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test_log2.ldf',
  STATS = 10,REPLACE
GO

差异备份还原:

如果要还原差异备份,需要先还原在差异备份时间点之前的一个完整备份,在还原完整备份时要加上NORECOVERY参数。

还原差异备份时,如果只有一个差异备份文件需要还原,无需使用NORECOVERY参数,如果有多个差异备份文件需要还原,除了最后一个差异备份文件,其他都需要加上NORECOVERY参数。

--Datebase_backup_2016_08_29_000002_6047304 --完整备份文件
--Datebase_backup_2016_08_31_000002_2090321 --差异备份文件

--查看备份文件的逻辑名称
RESTORE FILELISTONLY
  FROM DISK = 'R:\MSSQL11.MSSQLSERVER\MSSQL\Backup\Datebase_backup_2016_08_31_000002_2090321.bak' 

---开始脱机
ALTER DATABASE RestoreDB
SET OFFLINE WITH ROLLBACK IMMEDIATE;

--恢复联机
ALTER DATABASE RestoreDB
SET ONLINE WITH ROLLBACK IMMEDIATE;

USE master
RESTORE DATABASE RestoreDB
  FROM DISK = 'R:\MSSQL11.MSSQLSERVER\MSSQL\Backup\Datebase_backup_2016_08_29_000002_6047304.bak'
  WITH MOVE 'SKTMES_BPS_1_0_0' TO 'S:\MSSQL11.MSSQLSERVER\MSSQL\DATA\SKTMES_BPS_1_0_0.mdf',
  MOVE 'SKTMES_BPS_1_0_0_log' TO 'S:\MSSQL11.MSSQLSERVER\MSSQL\DATA\SKTMES_BPS_1_0_0_log.LDF',
  STATS = 10,REPLACE, --使用强制还原
  NORECOVERY 
GO

RESTORE DATABASE RestoreDB
  FROM DISK = 'R:\MSSQL11.MSSQLSERVER\MSSQL\Backup\Datebase_backup_2016_08_31_000002_2090321.bak'
  WITH MOVE 'SKTMES_BPS_1_0_0' TO 'S:\MSSQL11.MSSQLSERVER\MSSQL\DATA\SKTMES_BPS_1_0_0.mdf',
  MOVE 'SKTMES_BPS_1_0_0_log' TO 'S:\MSSQL11.MSSQLSERVER\MSSQL\DATA\SKTMES_BPS_1_0_0_log.LDF',
  STATS = 10   --最后一个差异备份文件不需要添加 NORECOVERY
GO

参考:

  1. 使用bak文件还原SQL Server
  2. SQL Server 数据库备份和还原认识和总结(一)
  3. SQL Server 数据库备份和还原认识和总结(二)
  4. RESTORE DATABASE命令还原SQLServer 2005 数据库
  5. SQL Server的差异备份还原

开启SQL Server 2012 远程调试功能

入职这个公司以来,每天写的最多的就是 SQL Server 存储过程。作为一个非专业开发出身的人员,几乎每天都在摸索着前进。

写庞大存储过程的痛苦,逼得我经常去尝试一些SSMS的替代工具。昨天在尝试一款替代工具时发现 SQL 语句竟然也可以逐步调试。于是回想起SSMS也有调试菜单啊,说不定微软就给我们提供了一大杀器呢!

调试工具:

在未使用工具的蛮荒时代,我们调试存储过程都是使用 Print 语句把各种变量打印出来。

本文使用工具的是:SQL Server 2016 Management Studio 同时在 SQL Server 2012 Management Studio 测试正常。

连接的数据库版本是:SQL Server 2012

本机调试:

如果SQL Server 和SSMS工具安装在同一台服务器上,那么不用进行多余的配置直接可以使用Windows 账户或sa账户进行调试。

远程调试:

远程调试开启的前提:

①、SQL Server 服务器端配置防火墙规则。
②、SSMS运行的电脑 Windows 账户必须是SQL Server 服务器上的一个域成员,并且此成员要拥有 sysadmin 角色。

开启的步骤(参考微软官方文档 设置成功,备注地址):

①、服务器端打开“高级安全Windows防火墙”

快捷键 Win+R 输入 wf.msc 打开

开启SQL Server 2012 远程调试功能

②、建立sqlserver.exe的入站规则

开启SQL Server 2012 远程调试功能

* 在 “规则类型” 对话框中,选择 “程序”,然后单击 “下一步”。
* 在 “程序” 对话框中,选择 “此程序路径:” ,然后输入指向此实例的 sqlservr.exe 的完整路径。 默认情况下,sqlservr.exe 安装在 C:\Program Files\Microsoft SQL Server\MSSQL13.InstanceName\MSSQL\Binn 中,其中,InstanceName 对于默认实例为 MSSQLSERVER,对于任何指定的实例则为相应实例的名称。
* 在 “操作” 对话框中,选择 “允许连接”,然后单击 “下一步”。
* 在 “配置文件” 对话框中,选择在您想要打开针对该实例的调试会话时描述计算机连接环境的任何配置文件,然后单击 “下一步”。
* 在 “名称” 对话框中,键入针对此规则的名称和说明,然后单击 “完成”。
* 在 “入站规则” 列表中,右键单击您创建的规则,然后在操作窗格中选择 “属性” 。
* 选择 “协议和端口” 选项卡。
* 在 “协议类型:” 框中选择 “TCP” ,在 “本地端口:” 框中选择 “RPC 动态端口” ,单击 “应用”,然后单击 “确定”。

③、建立svchost.exe的入站规则

* 在“高级安全 Windows 防火墙”的左窗格中,右键单击“入站规则”,然后在操作窗格中选择“新建规则”。
* 在 “规则类型” 对话框中,选择 “程序”,然后单击 “下一步”。
* 在 “程序” 对话框中,选择 “此程序路径:” ,然后输入指向 svchost.exe 的完整路径。 默认情况下,svchost.exe 安装在 %systemroot%\System32\svchost.exe 中。
* 在 “操作” 对话框中,选择 “允许连接”,然后单击 “下一步”。
* 在 “配置文件” 对话框中,选择在您想要打开针对该实例的调试会话时描述计算机连接环境的任何配置文件,然后单击 “下一步”。
* 在 “名称” 对话框中,键入针对此规则的名称和说明,然后单击 “完成”。
* 在 “入站规则” 列表中,右键单击您创建的规则,然后在操作窗格中选择 “属性” 。
* 选择 “协议和端口” 选项卡。
* 在 “协议类型:” 框中选择 “TCP” ,在 “本地端口:” 框中选择 “RPC 端点映射程序” ,单击“应用”,然后单击 “确定”。

④、把要使用的登陆用户添加到sysadmin角色中

开启SQL Server 2012 远程调试功能

这样在客户端就可以使用 Windows 身份验证或者 sa 账户等属于sysadmin角色的账户登录SSMS进行调试了。

调试步骤:

SSMS调试的方法和Visual Studio中的用法极为相似。

大体分为:设置断点、逐步或逐过程运行

开启SQL Server 2012 远程调试功能


参考:

  1. 运行 TSQL 调试器之前配置防火墙规则
  2. 运行 Transact-SQL 调试器

SQL Server 数据加密与解密

前段时间在客户公司,有一个很活泼的程序员给我展示他写的SQL Server 加密功能。由于测试库和正式库在同一台服务器里,于是一个不小心把正式库的所有存储过程、函数、视图全加密了。还好加密的方式只是在 AS 位置前增加了 WITH ENCRYPTION ,虽然不能查看内容但不影响程序的运行。

即使是这种简单的加密,解密过程也很复杂。为了缓解他焦灼的心情,于是我接下了这个解密的任务。在各种查找和尝试后,问题解决了。顺便也整理了全套加密和解密的方法,以便不时之需。

加密过程

可以参考这篇文章来创建加密的存储过程:(sp_EncryptObject)。此存储过程的方法是在存储过程、函数或视图的“As”位置前加上“with encryption”。如果是触发器的话就在“for”之前加“with encryption”。

具体实现代码如下:(SQL Server 2012 和 2016测试OK)

Use master
Go
if object_ID ( '[sp_EncryptObject]') is not null
    Drop Procedure [sp_EncryptObject]
Go
create procedure sp_EncryptObject
(
    @Object sysname= 'All'
)
as
/*
    当@Object=All的时候,对所有的函数,存储过程,视图和触发器进行加密
    调用方法:
    1. Execute sp_EncryptObject 'All'
    2. Execute sp_EncryptObject 'ObjectName'
*/
begin
    set nocount on

    if @Object <> 'All'
    begin
        if not exists(select 1 from sys. objects a where a .object_id = object_id( @Object ) And a .type in( 'P', 'V' ,'TR' , 'FN', 'IF' ,'TF' ))
        begin
            --SQL Server 2008
            --raiserror 50001 N'无效的加密对象!加密对象必须是函数,存储过程,视图或触发器。'

            --SQL Server 2012
            throw 50001, N'无效的加密对象!加密对象必须是函数,存储过程,视图或触发器。' , 1

            return
        end

        if exists( select 1 from sys. sql_modules a where a .object_id = object_id( @Object ) and a .definition is null)
        begin
            --SQL Server 2008
            --raiserror 50001 N'对象已经加密!'

            --SQL Server 2012
            throw 50001, N'对象已经加密!' ,1
            return
        end
    end

    declare @sql nvarchar (max ), @C1 nchar ( 1), @C2 nchar( 1 ),@type nvarchar (50 ),@Replace nvarchar (50 )
    set @C1 = nchar( 13 )
    set @C2 = nchar( 10 )


    declare cur_Object
        cursor for


            select object_name ( a. object_id ) As ObjectName , a. definition


                from sys . sql_modules a
                    inner join sys. objects b on b .object_id = a. object_id
                        and b. is_ms_shipped =0
                        and not exists(select 1
                                            from sys . extended_properties x
                                            where x. major_id =b .object_id
                                                and x. minor_id =0
                                                and x. class =1
                                                and x. name ='microsoft_database_tools_support'
                                        )
                where b. type in( 'P' ,'V' , 'TR', 'FN' ,'IF' , 'TF')
                    and ( b .name =@Object or @Object= 'All' )
                    and b. name <>'sp_EncryptObject'
                    and a. definition is not null
                order by Case

                            when b. type = 'V' then 1
                            when b. type = 'TR' then 2
                            when b. type in( 'FN' ,'IF' , 'TF') then 3
                            else 4 end , b. create_date ,b .object_id

    open cur_Object
    fetch next from cur_Object into @Object, @sql
    while @@fetch_status= 0
    begin

        Begin Try

            if objectproperty ( object_id( @Object ),'ExecIsAfterTrigger' )= 0 set @Replace ='As' ; else set @Replace= 'For ';

            if (patindex ( '%'+ @C1 +@C2 +@Replace + @C1+ @C2 +'%' , @sql)> 0 )
            begin
                set @sql= Replace (@sql ,@C1 +@C2 +@Replace + @C1+ @C2 ,@C1 +@C2 +'With Encryption'+ @C1+ @C2 +@Replace + @C1+ @C2 )
            end
            else if ( patindex( '%' +@C1 +@Replace + @C1+ '%' ,@sql )>0 )
            begin


                set @sql= Replace (@sql ,@C1 +@Replace + @C1, @C1 +'With Encryption' + @C1+ @Replace +@C1 )
            end
            else if ( patindex( '%' +@C2 +@Replace + @C2+ '%' ,@sql )>0 )
            begin


                set @sql= Replace (@sql ,@C2 +@Replace + @C2, @C2 +'With Encryption' + @C2+ @Replace +@C2 )
            end
            else if ( patindex( '%' +@C2 +@Replace + @C1+ '%' ,@sql )>0 )
            begin


                set @sql= Replace (@sql ,@C2 +@Replace + @C1, @C1 +'With Encryption' + @C2+ @Replace +@C1 )
            end
            else if ( patindex( '%' +@C1 +@C2 +@Replace + '%', @sql )>0 )
            begin


                set @sql= Replace (@sql ,@C1 +@C2 +@Replace , @C1+ @C2 +'With Encryption'+ @C1+ @C2 +@Replace )
            end
            else if ( patindex( '%' +@C1 +@Replace + '%', @sql )>0 )
            begin


                set @sql= Replace (@sql ,@C1 +@Replace , @C1+ 'With Encryption' +@C1 +@Replace )
            end
            else if ( patindex( '%' +@C2 +@Replace + '%', @sql )>0 )
            begin


                set @sql= Replace (@sql ,@C2 +@Replace , @C2+ 'With Encryption' +@C2 +@Replace )
            end

            set @type =
                case


                    when object_id ( @Object, 'P' )>0 then 'Proc'
                    when object_id ( @Object, 'V' )>0 then 'View'
                    when object_id ( @Object, 'TR' )>0  then 'Trigger'
                    when object_id ( @Object, 'FN' )>0 or object_id ( @Object, 'IF' )>0 or object_id ( @Object, 'TF' )>0 then 'Function'
                end
            set @sql= Replace (@sql ,'Create ' + @type, 'Alter ' +@type )

            Begin Transaction
            exec (@sql )
            print N'已完成加密对象(' + @type+ '):' +@Object
            Commit Transaction

        End Try
        Begin Catch
            Declare @Error nvarchar ( 2047)
            Set @Error= 'Object: ' +@Object + @C1+ @C2 +'Error: ' + Error_message()

            Rollback Transaction

            print @Error
            print @sql
        End Catch

        fetch next from cur_Object into @Object, @sql

    end

    close cur_Object
    deallocate cur_Object
end

Go
exec sp_ms_marksystemobject 'sp_EncryptObject' --标识为系统对象
go

执行加密

use test
go
exec sp_EncryptObject 'all' --加密所有内容,指定名称加密指定内容
go

解密过程

因为我所遇到的情况涉及的存储过程比较多,一个个解密不现实。于是在网上搜到了这个工具:dbForge SQL Decryptor 。这是一款免费软件和SSMS长得比较像。这款软件如果用常规账户连接的话解密速度特别慢,推荐使用DAC方式连接,速度飞快。

服务器默认DAC连接是关闭的,可以在服务器示例右键→Facets→外围应用配置器中启用远程DAC。

SQL Server 数据加密与解密

dbForge SQL Decryptor通过DAC连接服务器:

SQL Server 数据加密与解密

批量解密所有加密存储过程、函数、视图和触发器。

SQL Server 数据加密与解密

SSMS也可以使用DAC连接

SSMS通过DAC连接只能在文件→新建→数据库引擎查询处登录。其中服务器名称前要加 admin: ,登录完成默认只显示空白的编辑窗口。


参考文献:

  1. 批量解密SQLSERVER数据库中的各种对象的工具dbForge SQL Decryptor2.1.11
  2. 对存储过程进行加密和解密(SQL 2008/SQL 2012)
  3. MS SQL专用管理员连接DAC

SQL Server 系统存储过程 sp_executesql 使用方式

SQL Server 中执行存储过程的方式有两种:

使用 EXEC
使用系统存储过程 sp_executesql

此篇介绍使用 sp_executesql 执行SQL语句与存储过程。

sp_executesql 的基本模式是:

EXECUTE sp_executesql [SQL语句], [参数],[每个参数赋值|OUTPUT]

① 执行SQL语句:通过select语句获取执行结果

--定义所需的四个参数
DECLARE @SQLString NVARCHAR (500)      --需要执行的SQL语句
DECLARE @ParmDefinition NVARCHAR (500) --参数列表,需要用N''括起来
DECLARE @IntVariable INT               --给定的一个变量
DECLARE @Lastlname varchar (30)        --定义输出的变量
SET @SQLString = N'SELECT @LastlnameOUT = max(UserName) FROM dbo.UserInfo WHERE UserAge = @level'
SET @ParmDefinition = N'@level tinyint,@LastlnameOUT varchar(30) OUTPUT'  --名称与SQL语句中相同
SET @IntVariable = 25
EXECUTE sp_executesql @SQLString, @ParmDefinition,@level = @IntVariable,@LastlnameOUT= @Lastlname OUTPUT --参数后的每个变量都要参数列表中的顺序对应赋值,需要输出的增加OUTPUT
SELECT @Lastlname  --输出结果

备注 MAX() 函数:

--SQL MAX()、MIN() 函数返回一列中的最大值或最小值,NULL值不包括在计算中。
--也可用于文本列,获取按字母顺序排列的最高位或最低值
SELECT MAX (UserName) FROM dbo .UserInfo

② 执行存储过程:

CREATE PROCEDURE Myproc
    @parm VARCHAR( 10),
    @parm1OUT VARCHAR( 30) OUTPUT,
    @parm2OUT VARCHAR( 30) OUTPUT
    AS
      SELECT @parm1OUT ='parm 1' + @parm
     SELECT @parm2OUT ='parm 2' + @parm
GO

DECLARE @SQLString NVARCHAR (500)
DECLARE @ParmDefinition NVARCHAR (500)
DECLARE @parmIN VARCHAR (10)
DECLARE @parmRET1 VARCHAR (30)
DECLARE @parmRET2 VARCHAR (30)
SET @parmIN= ' returned'
SET @SQLString= N'EXEC Myproc @parm,@parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition= N'@parm varchar(10),@parm1OUT varchar(30) OUTPUT,@parm2OUT varchar(30) OUTPUT'

EXECUTE sp_executesql @SQLString, @ParmDefinition,@parm=@parmIN ,@parm1OUT= @parmRET1 OUTPUT,@parm2OUT =@parmRET2 OUTPUT

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
GO
DROP PROCEDURE Myproc

参考文献:

  1. How to specify output parameters when you use the sp_executesql stored procedure in SQL Server
  2. SQL MAX() 函数

SQL Server报表生成器入门

最近几周都在客户公司做项目,偶然的机会看到他们的主管在教怎么使用SQL Server报表生成器快速创建所需报表。感觉很有趣、很强大,于是跟着学了一些。

网上下载了:Microsoft SQL Server 2014 Report Builder 3.0,竟然是免费软件!

下面记录一下创建一个简单的报表流程:

初次使用

初次打开会弹出新建报表向导,默认提供了三种创建模式。

SQL Server报表生成器入门

在这里我们直接双击表或矩阵向导

SQL Server报表生成器入门

选择创建数据集。

SQL Server报表生成器入门

在数据源中可以创建新的数据连接。

SQL Server报表生成器入门

这里我们使用Microsoft SQL Server数据类型。

SQL Server报表生成器入门

输入服务器名,数据库用户验证信息及选择需要查询的数据库。

SQL Server报表生成器入门

创建完成可以同步测试连接效果。

SQL Server报表生成器入门SQL Server报表生成器入门

在设计查询部分,我们可以选择自己输入SQL 语言。
SQL Server报表生成器入门SQL Server报表生成器入门SQL Server报表生成器入门

这里我们选择所有字段的值。

SQL Server报表生成器入门SQL Server报表生成器入门

样式的部分随便选择。

SQL Server报表生成器入门SQL Server报表生成器入门SQL Server报表生成器入门