Install Sys_Schema on MySQL – MariaDB

Posted by in Databases, Frameworks, MariaDB, MySQL

If you are a database administrator, it could be really useful to hava available good tools. When this blog started, I already wrote about frameworks for MySQL DBA, and I’ve read several entrances about common_schema, another useful framework under the MySQL-MariaDB ecosystem.

Today it’s time for Sys Schema, a collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage, so let’s install it.

Install SYS on Windows

The project is available to download on GitHub: https://github.com/MarkLeith/mysql-sys, so, I downloaded it and unzip on a directory.

Ficheros de sys_schema

sys_schema files

Everything is ready, so, let’s move to a MySQL GUI admin (I like heidiSQL, but I realice that I’ve never write about it on the blog … je je!!), and let’s open the file to install. In my case, working with versión 5.6, I load sys_56.sql:

Preparando sys_schema

Preparing sys_schema

If you execute it, you’ll receibe an error, because of the paths:

sys_schema error

sys_schema error

The thruth is that using SOURCE under Windows OS requires you to modify the script and change relative path to absolute paths. The original install script is:

source sys_schema

source sys_schema

But I changed the paths, and the new installer script is like this (you can do it using a textfile editor with the function replace text, using your path):

sys_schema modificado

sys_schema modified

Now, you have the install script ready, but it’s better to use the MySQL console:

consola MySQL

MySQL console

And, now on the console, write source your_path/sys_56.sql:

instalando sys_schema

install sys_schema

Y vemos cómo se ejecuta la instalación.

procesando sys_schema

procesing … sys_schema

SOURCE command under Windows OS

SOURCE is one of those commands used for installing (or executing) from SQL on text files, and I’ve neves used it before, but using the console, everything is perfect!.

First steps using SYS Schema

If you haven’t detected any problem on the install process, everything is ready for you to go with the sys_schema tol. Let’s move again to the GUI admin, refresh databases, and you can see all installed. The version I installed is 1.3.0, and you have a lot:

sys_schema

sys_schema

For your first steps, it could be desirable for you to read this entrance: https://opensourcedba.wordpress.com/2014/12/09/sys-schema-first-steps/.

As you can imagine, the name of the views are almost self explanatory, and you have 81 views to see how your server works!

This framework is NOT available for previous versions, so if you have a MySQL server versions 5.1 or 5.5, it could be great if you upgrade your software (of course, I’ve already written about it here how to do it in a 8 entrances series!)

I hope it can be useful for you.