Introduction In this article, we are going to see how we can implement an audit logging mechanism using MySQL database triggers to store the old and new row states in JSON column types. Database tables Let’s assume we have a library application that has the following two tables: The book table stores all the books that are found in our library, and the book_audit_log table stores the CDC (Change Data Capture) events that happened to a given book record via an INSERT, UPDATE, or DELETE DML statement. The book_audit_log table is created... Read More
10 Older Entries »
Long months of the pandemic lockdown have brought to life many great online events enabling the MySQL community to get together and stay informed about the very recent developments and innovations available to MySQL users. It isn’t over yet! Next Thursday, June 4th, Percona & ProxySQL are co-hosting the ProxySQL Tech Talks with Percona virtual meetup covering ProxySQL, MySQL and Percona XtraDB Cluster.
The attendees are invited to participate in the two-hour deep-dive event with plenty of time for questions and answers (we will have two 40-minute sessions + 20 minutes allocated for Q&A). Get prepared, come with your burning questions and true war stories – we’ll have our speakers answer and comment on them! And here come the speakers:
- René Cannaò, ProxySQL author and CEO of ProxySQL …
MySQL does not limit the number of slaves that you can connect to the master server in a replication topology. However, as the number of slaves increases, they will have a toll on the master resources because the binary logs will need to be served to different slaves working at different speeds. If the data churn on the master is high, the serving of binary logs alone could saturate the network interface of the master.
A classic solution for this problem is to deploy a binlog server – an intermediate proxy server that sits between the master and its slaves. The binlog server is set up as a slave to the master, and in turn, acts as a master to the original set of slaves. It receives binary log events from the master, does not apply these events, but serves them to all the other slaves. This way, the load on the master is tremendously reduced, and at the same time, the binlog server serves …[Read more]
In one of the latest post, I discussed how to transform a large MySQL table to JSON using Apache Spark. Well, that approach works for any tables with any volume. But it’s an overkill for simple and small tables. The good news is MySQL by default has a feature for …
Then secretary of Defense Donald Rumsfeld popularized the terms: “known knowns”, “known unknowns”, and “unknown unknowns.” With the ever-increasing number of data breaches and vulnerabilities, database operation teams have to account for every possibility. Visualizing your audit data allows you to look for the “unknowns”, those access patterns or connections that you’d otherwise overlook.
Although enabling an audit log and shipping it off to a vault may meet security and regulatory requirements, you will lose an important opportunity to protect your customer and employee information.
The following dashboard demonstrates the type of information that audit logs can reveal:
- Who is connecting to my database (IP address, location, username..)
- Who is trying to connect to my database but getting access errors?
- Which tables are being accessed and by whom? …
This blog post illustrates ” How to setup SSL enabled replication”
By default, mysql package installation creates SSL file in the data directory at the time of installation. If you would like to use different self-signed certificates then create them as described here.
Add SSL setting to my.cnf on all servers.
ssl=on ssl-ca=/etc/sslcerts/ca.pem ssl-cert=/etc/sslcerts/server-cert.pem ssl-key=/etc/sslcerts/server-key.pem
Restart mysql server and verify the settings.
Example: client connections using SSL
#mysql -urpluser -p -P22403 --host 127.0.0.1 --ssl-cert=/etc/sslcerts/client-cert.pem --ssl-key=/etc/sslcerts/client-key.pem -e '\s' Enter password: -------------- Connection id: 5 Current database: Current user: rpluser@localhost SSL: Cipher in use is DHE-RSA-AES256-SHA Current …[Read more]
In a MySQL master-slave high availability (HA) setup, it is important to continuously monitor the health of the master and slave servers so you can detect potential issues and take corrective actions. In this blog post, we explain some basic health checks you can do on your MySQL master and slave nodes to ensure your setup is healthy. The monitoring program or script must alert the high availability framework in case any of the health checks fails, enabling the high availability framework to take corrective actions in order to ensure service availability.
MySQL Master Server Health Checks
We recommended that your MySQL master monitoring program or scripts runs at frequent intervals. Assuming that the monitoring script is running on the same server as your …[Read more]
Introduction In this article, we are going to see how the SQL EXISTS operator works and when you should use it. Although the EXISTS operator has been available since SQL:86, the very first edition of the SQL Standard, I found that there are still many application developers who don’t realize how powerful SQL subquery expressions really are when it comes to filtering a given table based on a condition evaluated on a different table. Database table model Let’s assume we have the following two tables in our database, that form a one-to-many... Read More
Monitoring your MySQL database performance in real-time helps you immediately identify problems and other factors that could be causing issues now or in the future. It’s also a good way to determine which components of the database can be enhanced or optimized to increase your efficiency and performance. This is usually done through monitoring software and tools either built-in to the database management software or installed from third-party providers.
Prometheus is an open-source software application used for event monitoring and alerting. It can be used along with a visualization tool like Grafana to easily create and edit dashboards, query, visualize, alert on, and understand your metrics. ScaleGrid provides full admin access to your MySQL deployments – this makes it …[Read more]
Change-Data-Capture (CDC) allows capturing committed changes from a database in real-time and propagating those changes to downstream consumers . CDC is becoming increasingly popular for use cases that require keeping multiple heterogeneous datastores in sync (like MySQL and ElasticSearch) and addresses challenges that exist with traditional techniques like dual-writes and distributed transactions .
In databases like MySQL and PostgreSQL, transaction logs are the source of CDC events. As transaction logs typically have limited retention, they aren’t guaranteed to contain the full history of changes. Therefore, dumps are needed to capture the full state of a source. There are several open source CDC projects, …[Read more]
10 Older Entries »