MySQL is an open-source Relational Database Management System, its data can be pulled either from MySQL dump or by mutual querying. MySQL deals with data from various data sources.
MySQL database is a reliable and industry standard for websites of middle-level data-based applications.
Sprinkle supports a wide range of data sources. On clicking the “+sign”, a list of data sources pops up. In this case, MySQL is selected. A new MySQL data source is named and created.
After naming the data source, the configure tab would require the user to fill in the credentials such as Username, Password, Driver version, JDBC URL and select between connecting via SSH Host or not before testing the connection and updating.
Optimising Incremental Ingestion in MySQL
Also users can select Yes or No to Optimize Incremental Ingestion. If optimize is Yes, all the datasets will undergo full ingestion on every Sunday or every night. If optimize is No, data will be ingesting incrementally and it never goes under complete ingestion.
In Add Tables, the user can add tables either by table or query method, in table method the user is required to apply a table name and filter clause could also be applied whenever required.
On selecting Query, the user must provide a table name and apply SQL Query before creating a table.
In the Run and Schedule tab, the concurrency (number of tables that can run in parallel, a maximum of 7) can be set preferentially before running the job. The status of the job will be updated in the tab below once it’s complete. The jobs can also be set to run automatically by enabling autorun. By default, the frequency is set to every night. Frequency can be changed by clicking on More --> Autorun-->Change Frequency.
Setup for Binlog Replication
- Create a user for sprinkle, choosing your own password
CREATE USER [email protected]'%' IDENTIFIED WITH mysql_native_password BY 'password'; GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO [email protected]'%';
Check if following lines are there in the mysql configuration file (my.cnf), otherwise add them:
[mysqld] binlog-format=ROW log-bin=mysql-binlog server-id=67835629 expire-logs-days=7 log-slave-updates=1
binlog-format must be ROW
No need to change log-bin entry if it is already there
No need to change if Server-id is already there. Otherwise choose any value between 1000 and 4294967295
expire-logs we recommend should be 7 days.
Restart Mysql server for new config to take effect.
(Optional) After restart you can check the variables using : show variables like '%binlog_format%';