1. How to add the Integration
1.1 Use SSL Encryption
1.2 How to connect with MySQL 8.0
2. Cloud Applications recommendation
3. Bring the Data to Life
With our new function “SQL Data Warehouse” you can easily export all your stored infos from zistemo to a SQL database. This gives you the full control of your data and you can create every reports or chart you can imagine. Optimal for BI analysis with Tableau or Google Data Studio. This feature is exclusively for our “enterprise” users. We automatically update every hour the database informations. How the hourly sync works:
- Drop all tables from database
- Create tables
- Insert data into created tables
Important notes:
- Zistemo supports MySQL Version 5.6. or 5.7. See HERE, how to connect with MySQL 8.0;
- Host field value is valid IP address or domain name
- Setup your firewall. Add IP 159.100.250.182; 194.182.189.139; 194.182.188.162; 91.92.154.198; 91.92.155.52 to allowed list
If you have any issues with the permissions, please run this command:
GRANT ALL PRIVILEGES ON `yours_db_name`.* TO `yours_user_name`@`159.100.250.182`;
GRANT ALL PRIVILEGES ON `yours_db_name`.* TO `yours_user_name`@`194.182.189.139`;
GRANT ALL PRIVILEGES ON `yours_db_name`.* TO `yours_user_name`@`194.182.188.162`;
GRANT ALL PRIVILEGES ON `yours_db_name`.* TO `yours_user_name`@`91.92.154.198`;
GRANT ALL PRIVILEGES ON `yours_db_name`.* TO `yours_user_name`@`91.92.155.52`;If you use MySQL 8 then run this sql command:
ALTER USER `yours_user_name`@`159.100.250.182` IDENTIFIED WITH mysql_native_password BY ‘password’;
ALTER USER `yours_user_name`@`194.182.189.139` IDENTIFIED WITH mysql_native_password BY ‘password’;
ALTER USER `yours_user_name`@`194.182.188.162` IDENTIFIED WITH mysql_native_password BY ‘password’;
ALTER USER `yours_user_name`@`91.92.154.198` IDENTIFIED WITH mysql_native_password BY ‘password’;
ALTER USER `yours_user_name`@`91.92.155.52` IDENTIFIED WITH mysql_native_password BY ‘password’;
How to add the Integration
To start, go to the “Integration Tab”
Click “Add Integration”
and press “connect” at the Data Warehouse Integration
Insert your Host, Port, DB-Name, User Name and Password. These credentials you can create in your own MySQL Database or you can use any MySQL provider.
Use SSL Encryption
To use SSL encrypted connection to a database you just need to add the SSL certificates. To do so, activate “Use SSL”. After that you can upload your certificates.
How to connect with MySQL 8.0
By default, mysql 8 default plugin is auth_socket. Applications will most times expect to log in to your database using a password. If you have not yet already changed your mysql default authentication plugin, you can do so by:
- Log in as root to mysql
- Run this sql command: ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_passwordBY ‘password’;
Replace ‘password’ with your root password. In case your application does not log in to your database with the root user, replace the ‘root’ user in the above command with the user that your application uses.
Find also more infos HERE
Permissions for Users:
If you have any issues with the permissions, please run this command:GRANT ALL PRIVILEGES ON ‘zistemo’.* TO ‘user_name’@’159.100.250.182’;Change ‘zistemo’ to your given database name’user_name’ please change to your used username.
Set up your firewall and permissions
Make sure you add zistemo’s IP address to your firewall and add permission. You can run this command:
MySQL 5.7
GRANT ALL PRIVILEGES ON `yours_db_name`.* TO `yours_user_name`@`194.182.189.139`;
GRANT ALL PRIVILEGES ON `yours_db_name`.* TO `yours_user_name`@`194.182.188.162`;
MySQL 8.0
ALTER USER `yours_user_name`@`194.182.189.139` IDENTIFIED WITH mysql_native_password BY ‘password’;
ALTER USER `yours_user_name`@`194.182.188.162` IDENTIFIED WITH mysql_native_password BY ‘password’;
Cloud Application
We use Google Cloud Platform for our tutorials. If you don’t want to use it, feel free to use any Cloud Application you want to use. You can find here a step-by-step guide to connect your Google Cloud Platform to zistemo warehouse
Bring the Data to Life
The next step is to get the Data into an BI (Business Intelligence) Solution. We recommend Tableau or Google Data Studio. You can also do whatever you want with your data 🙂 This is just an recommendation how to use it.
Here is a quick setup on the software “Tableau”:
1. Choose “MySQL” at the sidebar
2. Input your MySQL credentials as shown above
3. Inout your User credentials
4. Before you Login, you have to download the “MySQL” Driver. Please follow the instructions on the site.
After the successful installation/login, you need to restart tableau and connect/login again. SSL is not supported. In the next step you need to choose your Database:
As soon as your Database is loaded, you are ready to go. You can create any reports or charts you wish.
Enjoy!
