Using SSIS to build MySQL monitoring tools

Tools for batch monitoring MySQL db layers in Linux are relatively scarce, and SSIS is used for DIY.

Use the SSIS package to read the configuration table information to monitor MySQL and send feedback to DBA by email according to the actual situation.

1. sort out a global information table, including ip addresses and monitoring dimensions.

The table creation statement is as follows:

 
 

  
  
CREATE TABLE [dbo].[all_host_info](  
  
  
 [ip] [varchar](20) NULL,  
  
  
 [isactive] [tinyint] NULL,  
  
  
 [ishost] [tinyint] NULL,  
  
  
 [isslave] [tinyint] NULL,  
  
  
 [isjob] [tinyint] NULL,  
  
  
 [isobject] [tinyint] NULL,  
  
  
 [isprivileges] [tinyint] NULL 
  
  
) ON [PRIMARY] 
 
 

2. Create an ssis package named mysqlmonitor and create the following variables.

3. Create the TSQL_SLAVE SQL task component in the control flow

Link oledb to the SQL server where all_host_info is located. Enter the following code in sqlsourcetype:

 
 

  
  
select count(1) as j from all_host_info where isslave=1;  
 
 

Select a single row in result set.

Enter the following result name in the result set and specify the variable j.

4. Create a TSQL_SLAVE_ID tsql task component.

Link to the ole link used by the TSQL_SLAVE component

5. The content of the new SCRIPT component is as follows:

6. Create a FOR loop as follows:

7. Drag a data stream in the FOR loop to write the read ip information to the object variable as follows.

8. Drag a FOReach_VAR foreach component into the for component. The content of the component is as follows:

9 drag data flow components

10 mail

The final components of package 11 are as follows:

12. Load the package into the job and send emails one hour. The monitoring principles of other dimensions are the same.

Practice one skill and practice a hundred arts, and become a natural.

Edit recommendations]