So, basically what is Grandfather-father-son or GFS?
GFS backup is a common rotation scheme for backup, in which there are three or more backup cycles, such as daily, weekly, and monthly. Typically, It consists of daily backups (son, at fixed intervals of hours in a day), a weekly full backup (father, once a week), and monthly full backup (Grandfather, once a month).
Word “data” is very crucial since early 2000 and within a span of these 2 decades is it becoming more crucial. According to Forbes Google believe that in future every organisation will lead to becoming a data company. Well, when it comes to data, security is one of the major concerns that we have to face.
We have several common techniques to store data in today’s environment like MySql, Oracle, MsSql, Cassandra, Mongo etc and these techs will keep on changing in future. But according to DataAnyz, MySql Still has a 33% share of the market. So here we are with a technique to secure our MySQL data.
Before getting more into this article, let us know what are possible combined approaches to secure MySQL data
Mysql Server hardening
Mysql Application-level hardening
Mysql data encryption at transit
Mysql data at rest encryption
Mysql Disk Encryption
You may explore all the approaches but in this article, we will understand the concept of Mysql data at encryption and hands-on too.
The concept of “Data at Rest Encryption” in MySQL was introduced in Mysql 5.7 with the initial support of InnoDB storage engine only and with the period it has evolved significantly. So let’s understand about “Data at Rest Encryption” in MySQL
What is “Data at Rest Encryption” in MySql?
The concept of “data at rest encryption” uses two-tier encryption key architecture, which used below two keys
Tablespace keys: This is an encrypted key which is stored in the tablespace header
Master Key: the Master key is used to decrypt the tablespace keys
So let’s Understand its working
Let’s say we have a running MySQL with InnoDB storage engine and tablespace is encrypted using a key, referred as table space key. This key is then encrypted using a master key and stored in the tablespace header
Now when a request is made to access MySQL data, InnoDB use master key to decrypt tablespace key present tablespace header. After getting decrypted tablespace key, the tablespace is decrypted and make is available to perform read/write operations
Note: The decrypted version of a tablespace key never changes, but the master key can be rotated.
Data at rest encryption implemented using keyring file plugin to manage and encrypt the master key
After understanding the concept of encryption and decryption below are few Pros and Cons for using DRE
A strong Encryption of AES 256 is used to encrypt the InnoDB tables
It is transparent to all applications as we don’t need any application code, schema, or data type changes
Key management is not done by DBA.
Keys can be securely stored away from the data and key rotation is very simple.
Encrypts only InnoDB tables
Can’t encrypt binary logs, redo logs, relay logs on unencrypted slaves, slow log, error log, general log, and audit log
Though we can’t encrypt binary logs, redo logs, relay logs on Mysql 5.7 but MariaDB has implemented this with a mechanism to encrypt undo/redo logs, binary logs/relay logs, etc. by enabling few flags in MariaDB Config File
Let’s Discuss its problem/solutions and few solutions to them
Running MySQL on a host will have access from root user and the MySQL user and both of them may access key file(keyring file) present on the same system. For this problem, we may have our keys on mount/unmount drive which can be unmounted after restarting MySQL.
Data will not be in encrypted form when it will get loaded onto the RAM and can be dumped and read
If MySQL is restarted with skip-grant-tables then again it’s havoc but this can be eliminated using an unmounted drive for keyring
As tablespace key remains the same so our security relies on Master key rotation which can be used to save our master key
NOTE: Do not to lose the master key file, as we cant decrypt data and will suffer data loss
Doing Is Learning, so let’s try
As a prerequisite, we need a machine with MySQL server up and running Now for data at rest encryption to work we need to enable
Enable file per table on with the help of the configuration file.
[root@mysql ~]# vim /etc/my.cnf [mysqld]
Along with the above parameter, enable keyring plugin and keyring path. This parameter should always be on the top in configuration so that it will get load initially when MySQL starts up. Keyring plugin is already installed in MySQL server we just need to enable it.
[root@mysql ~]# vim /etc/my.cnf [mysqld] early-plugin-load=keyring_file.so keyring_file_data=/var/lib/mysql/keyring-data/keyring innodb_file_per_table=ON
And save the file with a restart to MySQL
[root@mysql ~]# systemctl restart mysql
We can check for the enabled plugin and verify our configuration.
mysql> SELECT plugin_name, plugin_status FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name LIKE 'keyring%'; +--------------+---------------+ | plugin_name | plugin_status | +--------------+---------------+ | keyring_file | ACTIVE | +--------------+---------------+ 1 rows in set (0.00 sec)
verify that we have a running keyring plugin and its location
mysql> show global variables like '%keyring%'; +--------------------+-------------------------------------+ | Variable_name | Value | +--------------------+-------------------------------------+ | keyring_file_data | /var/lib/mysql/keyring-data/keyring | | keyring_operations | ON | +--------------------+-------------------------------------+ 2 rows in set (0.00 sec)
Verify that we have enabled file per table
MariaDB [(none)]> show global variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.33 sec)
Now we will test our set up by creating a test DB with a table and insert some value to the table using below commands
mysql> CREATE DATABASE test_db; mysql> CREATE TABLE test_db.test_db_table (id int primary key auto_increment, payload varchar(256)) engine=innodb; mysql> INSERT INTO test_db.test_db_table(payload) VALUES('Confidential Data');
After successful test data creation, run below command from the Linux shell to check whether you’re able to read InnoDB file for your created table i.e. Before encryption
Along with that, we see that our keyring file is also empty before encryption is enabled
[root@mysql ~]# strings /var/lib/mysql/test_db/test_db_table.ibd infimum supremum Confidential DATA
At this point of time if we try to check our keyring file we will not find anything
You can encrypt data at rest by using keyring plugin and we can control and manage it by master key rotation. Creating an encrypted Mysql data file setup is as simple as firing a few simple commands. Using an encrypted system is also transparent to services, applications, and users with minimal impact of system resources. Further with Encryption of data at rest, we may also implement encryption in transit.
I hope you found this article informative and interesting. I’d really appreciate any and all feedback.
Recently, I got a requirement to facilitate backup for the data and a way to analyze it without using the main database. MySQL replication is a process that allows you to easily maintain multiple copies of MySQL data by having them copied automatically from a master to a slave database.
Everything was running smoothly in the night I configured it. But the joy didn’t last for long as the traffic hits in the morning, slave starts getting behind the master with few seconds which increases with the activity on the application. At the peak time, it was playing in thousands of second.
What now, I had to dig deep into MySQL Replication
How it works
What can probably cause the lag
An approach that minimizes or eliminates it
How MySQL Replication Works
On the master
First of all, master writes replication events to a special log called binary log. This is usually a very lightweight activity because writes are buffered and they are sequential. The binary log file stores data that replication slave will be reading later.
On the replica
When you start replication, two threads are started on the slave: 1. IO thread This process connects to a master, reads binary log events from the master as they come in and just copies them over to a local log file called relay log. Even though there’s only one thread reading the binary log from the master and one writing relay log on the slave, very rarely copying of replication events is a slower element of the replication. There could be a network delay, causing a steady delay of a few hundred milliseconds. If you want to see where IO thread currently is, check the following in “show slave status \G” Master_Log_File – last file copied from the master (most of the time it would be the same as last binary log written by a master) Read_Master_Log_Pos – binary log from the master is copied over to the relay log on the slave up until this position. And then you can compare it to the output of “show master status/G” from the master.
mysql> show master status\G;
*************************** 1. row ***************************
1 row in set (0.00 sec)
2. SQL thread The second process – SQL thread – reads events from a relay log stored locally on the replication slave (the file that was written by IO thread) and then applies them as fast as possible. Going back to “show slave status /G”, you can get the current status of SQL thread from the following variables: Relay_Master_Log_File – binary log from the master, that SQL thread is “working on” (in reality it is working on relay log, so it’s just a convenient way to display information) Exec_Master_Log_Pos – which position from the master binary log is being executed by SQL thread.
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Log_File: db01-binary-log.000032 Read_Master_Log_Pos: 1008768810
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
1 row in set (0.00 sec)
Why Replication Lag Occurred
Replication lag occurs when the slaves cannot keep up with the updates occurring on the master. Unapplied changes accumulate in the slave’s relay logs and the version of the database on the slaves becomes increasingly different from that of the master.
Caught The Culprit
Let me take you through my journey how I crossed the river. First, I took the reference to multiple blogs and started gobbling my mind with possible reasons suggesting
Hardware Faults (getting RAID in degraded mode)
MySQL Config Updates
updating slave_parallel_workers to a higher value
changing slave_parallel_type to support more parallel workers
But unfortunately, or say, it was my benightedness towards Database Administration that I was still searching for that twig which can help me from drowning. And finally, I found one, my DBA friend who suggested me to look for the Binary Log Format that I am using. Let’s see what it is
Binary Logging Formats
The server uses several logging formats to record information in the binary log. The exact format employed depends on the version of MySQL being used. There are three logging formats: STATEMENT: With statement-based replication, every SQL statement that could modify data is logged on the master. Then those SQL statements are replayed on the slaves against the same dataset and in the same context. There is always less data that is to be transferred between the master and the slave. But, the data inconsistency issue between the master and the slave that creeps up due to the way this kind of replication works. ROW: With row-based replication, every “row modification” is logged on the master and is then applied to the slave. With row-based replication, each and every change can be replicated and hence this is the safest form of replication. On a system that frequently UPDATE a large number of rows, it produces very large update logs and generates a lot of network traffic between the master and the slave. MIXED: A third option is also available: mixed logging. With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases.
Changing Binary Log Format
The Binary Log Format is updated on Master MySQL server and requires MySQL service restart to reflect. It can be done for Global, Runtime or Session.
set at runtime with –binlog-format=format
setting the global (with the SUPER privilege)
session value of the binlog_format server variable
mysql> SET GLOBAL binlog_format=MIXED;
mysql> SET SESSION binlog_format=ROW;
mysql> SET binlog_format=STATEMENT;
So, earlier I was using STATEMENT BinLog Format, which is default one. Since I switched to MIXED BinLog Format, I am very delighted to share the below stats. Current status of Master Read and Slave Execute position difference and Slave Lag (in sec), both are ZERO.
Replication Lag (in Seconds) graph for a month, powered by Prometheus-Grafana.
One thing that I love about Prometheus is that it has a multitude of Integration with different services, both officially supported and the third party supported.
Let’s see how can we monitor MySQL with Prometheus.
Those who are the starter or new to Prometheus can refer to our this blog.
MySQL is a popular opensource relational database system, which exposed a large number of metrics for monitoring but not in Prometheus format. For capturing that data in Prometheus format we use mysqld_exporter. I am assuming that you have already setup MySQL Server.
Configuration changes in MySQL
For setting up MySQL monitoring, we need a user with reading access on all databases which we can achieve by an existing user also but the good practice is that we should always create a new user in the database for new service.
CREATEUSER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'password' WITH MAX_USER_CONNECTIONS 3;
After creating a user we simply have to provide permission to that user.
GRANT PROCESS, REPLICATION CLIENT, SELECTON*.* TO 'mysqld_exporter'@'localhost';
Setting up MySQL Exporter
Download the mysqld_exporterfrom GitHub. We are downloading the 0.11.0 version as per latest release now, change the version in future if you want to download the latest version.
Then simply extract the tar file and move the binary file at the appropriate location.
tar -xvf mysqld_exporter-0.11.0.linux-amd64.tar.gz
mv mysqld_exporter /usr/bin/
Although we can execute the binary simply, but the best practice is to create service for every Third Party binary application. Also, we are assuming that systemd is already installed in your system. If you are using init then you have to create init service for the exporter.
For scrapping metrics from mysqld_exporter in Prometheus we have to make some configuration changes in Prometheus, the changes are not fancy, we just have to add another job for mysqld_exporter, like this:-
One of the important step during release is doing database update and rollback in case something goes wrong, usually people perform this operation manually. In this blog I’ll talk about how we can automate this process by following some convention.
Here I’m taking mysql database as an example we can have same conventions for other databases also
Convention to manage rollback/updates of a release
Each project codebase at it’s root will have a folder database_scripts
The database_scripts folder will contain folder for each release i.e Release1_1, Release2_0…
The database scripts release folder will in turn contains two folders update & rollback which will contain updates & rollbacks scripts for a release.
Automating the rollback/update
The update folder will have a source input file FileSequencer.txt. This file will point to all the update scripts in correct order that needs to be executed for the release
In the similar manner rollback folder will have a source input file FileSequencer.txt. This file will point to all the rollback scripts in correct order that needs to be executed for the release
At last we will have a utility shell script, this script will take db details and execute all the scripts referred in FileSequencer.txt using mysql command