语言:C# 开发工具:Visual Studio 2017 问题场景介绍:
nuget引用的MySql.Data版本是6.10.5,MySQL的数据库连接串如下: “`
Server=xxx;Database=xxx;User=xxx;Password=xxx;Port=3306;CharSet=utf8;Allow
User Variables=True “` 可以正常访问MySQL数据库
更新nuget,MySql.Data版本是8.0.11,报错`The host xxx does not support
SSL connections.` 解决方案:连接字符串添加`澳门微尼斯人手机版,SslMode=none;`
修改后的数据库连接串如下: “`
Server=xxx;Database=xxx;User=xxx;Password=xxx;Port=3306;CharSet=utf8;Allow
User Variables=True;SslMode=none; “`

2,查看当前正在运行的Job

 

SQL Server将Job的信息存放在msdb中,Schema是dbo,表名以“sysjob”开头。

 

select js.job_id,js.step_id,js.step_name,
    js.subsystem,js.command,
    js.last_run_outcome,--Last Run Result
    js.last_run_duration,
    js.last_run_date,
    js.last_run_time,
    js.last_run_retries
from msdb.dbo.sysjobsteps js with(nolock)
where js.job_id=N'DF0C68ED-7C76-4571-A72D-CD6161EFFC04'

-v,
–verbose      
Reconstruct pseudo-SQL statements out of row events. -v  -v adds comments
on column data types.

select j.job_id,j.name,j.enabled,j.description,
    j.start_step_id,j.date_created,j.date_modified
from msdb.dbo.sysjobs j with(nolock)
where name =N'xxx'

 

参考文档:

MySQL
mysqlbinlog解析出的SQL语句被注释是怎么回事

select jh.instance_id,jh.job_id,jh.step_id,jh.step_name,jh.sql_message_id,jh.sql_severity,
    jh.message,
    case jh.run_status
        when 0 then 'failed'
        when 1 then 'Succeeded'
        when 2 then 'Retry'
        when 3 then 'Canceled'
    end as run_status,
    jh.run_date,jh.run_time,jh.run_duration
from msdb.dbo.sysjobhistory jh with(nolock)
where job_id=N'07A53839-E012-4C80-9227-15594165B013'
order by instance_id desc

 

 

默认情况下只能看到一些经过base-64编码的信息,如下所示:

Records current SQL Server Agent job
activity and status. The column
last_executed_step_id is the id of the last step completed.  If the
job is on the first step it’s NULL.  So getting the current step is a
simple formula of ISNULL(last_executed_step_id,0)+1.

 

1,基础表

mysqlbinlog:
/usr/bin/mysqlbinlog /usr/share/man/man1/mysqlbinlog.1.gz

3,Job History的查询

mysqlbinlog有一个参数–verbose(或-v),将自动生成带注释的SQL语句(在行事件中重构伪SQL语句),其实这个并非原始SQL语句,而是伪SQL,如果使用这个参数两次(如-v
-v),则输出列的描述信息,会生成字段的类型、长度、是否为null等属性信息:

5,查看每个Job最后一次执行的状态和该job最后一个Step的执行信息。

[root@DB-Server
~]# /usr/bin/mysqlbinlog 
–base64-output=DECODE-ROWS  -v -v
/data/mysql/DB-Server-bin.000026

use msdb
GO

;with cte_job as
(
select jh.server,
    j.name,
    j.enabled ,
    jh.job_id,
    jh.run_status,
    jh.run_date,
    jh.run_time,
    jh.run_duration,
    ROW_NUMBER() OVER(PARTITION by jh.job_id order by jh.run_date desc,jh.run_time desc ) as rid
from msdb.dbo.sysjobs  j with(nolock)
inner join msdb.dbo.sysjobhistory jh with(nolock)
    on j.job_id=jh.job_id
where j.category_id=0
    and jh.step_id=0
)
SELECT j.name,
    j.enabled,
    case j.run_status 
        when 0 then 'Failed' 
        when 1 then 'Successed' 
        when 2 then 'Retry' 
        when 3 then 'Canceled' 
        else '' 
    end as [Status],
    cast(STUFF(STUFF(str(j.run_date,8),7,0,N'-'),5,0,N'-') + N' ' + 
         STUFF(STUFF(REPLACE(STR(j.run_time,6,0),N' ',N'0'),5,0,N':'),3,0,N':') 
         AS DATETIME) as [StartTime],
    stuff(stuff(replace(str(j.run_duration,6),N' ',N'0'),5,0,N':'),3,0,N':')  as Duration,
    jh.step_id,
    jh.step_name ,
    case jh.run_status 
        when 0 then 'Failed' 
        when 1 then 'Successed' 
        when 2 then 'Retry' 
        when 3 then 'Canceled' 
        else N'' 
    end as StepStatus,
    jh.message as StepMessage
from cte_job j
outer apply
(
select top 1 jh.step_id,jh.step_name,jh.run_status,jh.run_date,jh.run_time,jh.run_duration,jh.message
from msdb.dbo.sysjobhistory jh with(nolock)
where j.job_id=jh.job_id and jh.step_id>0 and jh.run_date>=j.run_date and jh.run_time>=j.run_time
order by jh.step_id desc
) as jh
where j.Rid=1 --Last Execution
    and j.run_status in(0,2)  --0 = Failed, 2=retry
order by j.name

如上所示,其实这里的SQL语句不是原始SQL语句,那么能否看到原始SQL语句呢?答案是可以,但是必须设置系统变量binlog_rows_query_log_events

二,查看Running jobs

mysql> show variables like 'binlog_format';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | ROW   |

+---------------+-------+

1 row in set (0.00 sec)

 

mysql> show master status;

+----------------------+----------+--------------+------------------+-------------------+

| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+----------------------+----------+--------------+------------------+-------------------+

| DB-Server-bin.000005 |      512 |              |                  |                   |

+----------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

 

mysql> 

 

 

 

mysql> drop table kkk;

Query OK, 0 rows affected (0.01 sec)

 

mysql> create table kkk (id int ,name varchar(32));

Query OK, 0 rows affected (0.02 sec)

 

mysql> insert into kkk

    -> select 100, 'name' union all

    -> select 200, 'kerry' union all

    -> select 300, 'k3';

Query OK, 3 rows affected (0.02 sec)

Records: 3  Duplicates: 0  Warnings: 0

 

mysql> 

Agent在运行时,会创建一个Session,并将current
SessionID存储在msdb.dbo.syssessions
中。Agent在执行每一个job时,都会将SessionID 和Job_ID 写入
msdb.dbo.sysjobactivity 中,因此 msdb.dbo.sysjobactivity 记录当前Agent
正在运行的每一个Job的信息(Job开始执行的时间,执行成功的最后一个StepID….),如果要查看Agent当前执行的Job,那么msdb.dbo.sysjobactivity的SessionID必须是当前Agent使用的SessionID。

use `MyDB`/*!*/;

SET TIMESTAMP=1530288296/*!*/;

DROP TABLE `kkk` /* generated by server */

/*!*/;

# at 628

#180629 12:05:14 server id 1  end_log_pos 745 CRC32 0xc6037a3f  Query   thread_id=3     exec_time=0     error_code=0

SET TIMESTAMP=1530288314/*!*/;

create table kkk (id int ,name varchar(32))

/*!*/;

# at 745

#180629 12:06:07 server id 1  end_log_pos 817 CRC32 0x74fd4efb  Query   thread_id=3     exec_time=0     error_code=0

SET TIMESTAMP=1530288367/*!*/;

BEGIN

/*!*/;

# at 817

#180629 12:06:07 server id 1  end_log_pos 866 CRC32 0xfb1391dd  Table_map: `MyDB`.`kkk` mapped to number 73

# at 866

#180629 12:06:07 server id 1  end_log_pos 930 CRC32 0xecb4e812  Write_rows: table id 73 flags: STMT_END_F

 

BINLOG '

71g2WxMBAAAAMQAAAGIDAAAAAEkAAAAAAAEABE15REIAA2trawACAw8CYAAD3ZET+w==

71g2Wx4BAAAAQAAAAKIDAAAAAEkAAAAAAAEAAgAC//xkAAAABG5hbWX8yAAAAAVrZXJyefwsAQAA

AmszEui07A==

'/*!*/;

# at 930

#180629 12:06:07 server id 1  end_log_pos 961 CRC32 0x7ca988e3  Xid = 37

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

发表评论

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