KlustronDB Log File Encryption Features and Usage
KlustronDB Log File Encryption Features and Usage
Note:
Unless otherwise specified, the version numbers in the text can be replaced with the version numbers of any released version. For all released versions, see: Release_notes.
Overview
The main content is first to introduce the features of database log file encryption. It explains how to install the log file encryption plugin in KlustronDB, how to enable the log file encryption configuration steps, then test the use of log file encryption. At the operating system level, the data content of encrypted log files and non-encrypted log files is compared directly. Finally, it also tests and verifies that log encryption does not affect data synchronization on Klustore instances, and that log encryption is transparent to data synchronization.
01 KlustronDB The Role and Characteristics of Log Files
KlustronDB's binary log (Binlog) is an important component used to record changes made to the database, and its functions and characteristics are as follows:
1.1 The Function of Binlog Logs
a) Data Recovery and Backup: Binlog records all changes in the database, including operations such as INSERT, UPDATE, and DELETE. This makes the Binlog a key tool for restoring the database to a previous state. By analyzing the Binlog, you can restore the database to a specific point in time, facilitating data recovery and backup.
b) Database Replication: Binlog plays a key role in database replication. The master database transmits Binlog events to the slave database, thereby achieving master-slave replication. This allows the slave database to stay synchronized with the master database and be used for purposes such as read operation load balancing, failover, and data distribution.
c) Data auditing and tracking: Binlog can be used to track changes in the database for data auditing and monitoring. It records who modified which data in the database and at what time.
d) Data Migration: Binlog can also be used to migrate data between different MySQL servers. By applying Binlog events to the target database, data can be copied from one server to another.
1.2 Binlog Log Features
a) Record all change operations: The Binlog records every change operation in the database in binary format, including SQL statements and the corresponding data. This makes it very powerful, allowing the database to be restored to any point in time.
b) Scalability: Binlog can be configured to record events in different ways, including complete statement events, row events, and mixed mode. This makes Binlog more flexible in different scenarios.
c) Asynchronous logging: Binlog is recorded asynchronously, which means it does not significantly affect the database's write performance. Write operations return immediately, while Binlog events are recorded asynchronously in the background.
d) Customizability: Administrators can configure parameters such as the size of the Binlog, retention period, and naming method to meet different needs and policies.
e) Security: Binlog files are usually located on the server and protected by access permissions to prevent unauthorized access.
f) Wide range of uses: Binlog is used not only for backup and recovery, but also for real-time replication, data synchronization, data migration, performance analysis, and data auditing, among other uses.
It should be noted that although Binlog is very useful, it also needs to be used and managed carefully. Proper configuration and maintenance of Binlog is a key step to ensure database stability and security.
02 KlustronDB binlog Features of Encryption
KlustronDB's binary logs (Binlog) are usually not encrypted in their raw form. The Binlog records changes to the database, including operations such as INSERT, UPDATE, and DELETE, to facilitate database replication, backup, and recovery. However, encrypting Binlog data is a useful security measure to prevent unauthorized access or the leakage of sensitive information.
Binlog encryption feature:
a) Data Protection: Binlog contains the critical change history of the database, so encryption can protect sensitive data from unauthorized access and leaks.
b) Security Improvement: Encrypting the Binlog can enhance the overall security of the database system. Even if an attacker is able to access the Binlog files, they cannot easily decrypt the contents.
c) Compliance requirements: For organizations that need to meet specific compliance requirements (such as GDPR, HIPAA, etc.), it may be necessary to encrypt Binlog data.
d) Performance Overhead: Encrypting and decrypting data may incur some overhead on system performance, especially under high load conditions. Therefore, it is necessary to balance the trade-off between security and performance.
e) Management and Maintenance: Implementing Binlog encryption requires managing keys and certificates, as well as ensuring the encryption components are correctly configured and running. This requires additional management and maintenance work.
03 KlustronDB Install Encryption Plugin and Enable Binlog Encryption
3.1 Connect to the Klustore instance and check whether the current database has Binlog file encryption enabled.
[root@kunlun2 ~]# su – Kunlun
[kunlun@kunlun2 ~]$ cd /kunlun/instance_binaries/storage/57005/kunlun-storage-1.2.1/dba_tools/
[kunlun@kunlun2 dba_tools]$ ./imysql.sh 57005
mysql> show variables like '%binlog_encryption%';
mysql> show binary logs;
The current Binlog log file is not encrypted.
3.2 Install the encryption plugin on the Klustore instance and create a directory to store the encryption keyring file: '/kunlun/mysql-keyring/'
[root@kunlun2 ~]# su - kunlun
[kunlun@kunlun2 ~]$ mkdir /kunlun/mysql-keyring/
3.3 Modify all Klustore instance configuration files and change the following items in the configuration files:
[kunlun@kunlun2 ~]$ vi /kunlun/storage_datadir/57003/data/57003.cnf
early-plugin-load=keyring_file.so
keyring_file_data=/kunlun/mysql-keyring/keyring
binlog_encryption=on
3.4 It is necessary to modify all Klustore instance configuration files, for example, here we also modify the following two files (operate according to step 3.3)
[kunlun@kunlun2 ~]$ vi /kunlun/storage_datadir/57005/data/57005.cnf
[kunlun@kunlun2 ~]$ vi /kunlun/storage_datadir/57007/data/57007.cnf
3.5 Restart the Klustore instance after modifying the configuration file
[kunlun@kunlun2 ~]$ cd /kunlun/instance_binaries/storage/57003/kunlun-storage-1.2.1/dba_tools
[kunlun@kunlun2 ~]$ ./stopmysql.sh 57003
[kunlun@kunlun2 ~]$ ./startmysql.sh 57003
[kunlun@kunlun2 ~]$ cd /kunlun/instance_binaries/storage/57005/kunlun-storage-1.2.1/dba_tools
[kunlun@kunlun2 ~]$ ./stopmysql.sh 57005
[kunlun@kunlun2 ~]$ ./startmysql.sh 57005
[kunlun@kunlun2 ~]$ cd /kunlun/instance_binaries/storage/57007/kunlun-storage-1.2.1/dba_tools
[kunlun@kunlun2 ~]$ ./stopmysql.sh 57007
[kunlun@kunlun2 ~]$ ./startmysql.sh 57007
3.6 Verify the successful installation of the encryption plugin after restarting the Klustore instance
[kunlun@kunlun2 ~]$ mysql -h 192.168.56.113 -P57003 -upgx -ppgx_pwd
mysql> select * from information_Schema.plugins where plugin_name like '%keyring_file%'\G
mysql> show global variables like '%keyring%';
3.7 Check that the keyring file has been generated and the encryption plugin installation is complete
[kunlun@kunlun2 ~]$ ls -l /kunlun/mysql-keyring
3.8 Check to confirm that the Binlog encryption setting has been successfully enabled
[root@kunlun2 ~]# su – Kunlun
[kunlun@kunlun2 ~]$ cd /kunlun/instance_binaries/storage/57005/kunlun-storage-1.2.1/dba_tools/
[kunlun@kunlun2 dba_tools]$ ./imysql.sh 57005
mysql> show variables like '%binlog_encryption%';
mysql> show binary logs;
The encryption plugin was successfully installed and enabled Binlog log file encryption.
04 Binlog Log File Encryption Test Case
This is a test case based on Binlog log file encryption. After enabling Binlog log file encryption, it compares the cases with and without Binlog log file encryption enabled, by directly viewing the file contents at the operating system level and comparing the two types of logs.
4.1 Connect to the Klustore instance and check whether the current database has Binlog file encryption enabled.
[root@kunlun2 ~]# su – Kunlun
[kunlun@kunlun2 ~]$ cd /kunlun/instance_binaries/storage/57005/kunlun-storage-1.2.1/dba_tools/
[kunlun@kunlun2 dba_tools]$ ./imysql.sh 57005
mysql> show variables like '%binlog_encryption%';
mysql> show binary logs;
4.2 By directly viewing the unencrypted binlog system files (unencrypted file: biglog.000012) from the operating system level, you can directly see the plaintext information of the data stored in the files.
[root@kunlun2 ~]# su - kunlun
[kunlun@kunlun2 ~]$ hexdump -C /kunlun/storage_logdir/57005/binlog/binlog.000012
4.3 By directly viewing the encrypted binlog system file (encrypted file: biglog.000014) from the operating system level, it was observed that the data stored in the file is encrypted, and the file's data information cannot be obtained.
[root@kunlun2 ~]# su - kunlun
[kunlun@kunlun2 ~]$ hexdump -C /kunlun/storage_logdir/57005/binlog/binlog.000014
The encrypted binlog log system file shows ciphertext, and the plaintext data information of the file cannot be viewed, ensuring the security of the binlog log data.
05 Binlog Log Encryption Transparent Case for Data Synchronization on Klustore instances
After the Binlog log is encrypted, on the Kluscomp instance, connect to the database and perform database access operations, such as creating tables and inserting data into the tables. Then check the tables on each Klustore instance to verify that after the Binlog log is encrypted, data synchronization between Klustore instances is not affected. The synchronization is transparent, and tables and data can be synchronized across the various database Klustore instances.
5.1 Connect to the database, create a database user, create a test database, and grant permissions to the user.
[root@kunlun1 ~]# su - kunlun
[kunlun@kunlun1 ~]$ psql -h 192.168.56.112 -p 47001 postgres
create user kunlun_user1 with password 'kunlun';
create database testdb_binlog;
grant all privileges on database testdb_binlog to kunlun_user1;
\c testdb_encypt kunlun_user1
5.2 Create the data table 'emp' and insert data into the table.
CREATE TABLE emp (
empid int,
empname varchar(50),
salary int
);
insert into emp (empid,empname,salary) values(1001,'test',5000);
insert into emp (empid,empname,salary) values(1002,'admin',8000);
insert into emp (empid,empname,salary) values(1003,'operator',10000);
insert into emp (empid,empname,salary) values(1004,'auditor',12000);
insert into emp (empid,empname,salary) values(1005,'viewer',9000);
insert into emp (empid,empname,salary) values(1006,'test2',6000);
5.3 Connect to the Klustore instance at 57005 to view the data in the emp table
[root@kunlun2 ~]# su - kunlun
[kunlun@kunlun2 ~]$ cd /kunlun/instance_binaries/storage/57005/kunlun-storage-1.2.1/dba_tools
[kunlun@kunlun2 dba_tools]$ ./imysql.sh 57005
mysql> use testdb_binlog_$$_public
mysql> select * from emp;
5.4 Connect to the Klustore instance at 57007 to view the data in the emp table
[root@kunlun2 ~]# su - kunlun
[kunlun@kunlun2 ~]$ cd /kunlun/instance_binaries/storage/57007/kunlun-storage-1.2.1/dba_tools
[kunlun@kunlun2 dba_tools]$ ./imysql.sh 57007
mysql> use testdb_binlog_$$_public
mysql> select * from emp;
By checking the tables and data on each Klustore instance, it can be verified that after Binlog log encryption, the data synchronization on the Klustore instances is not affected. The synchronization is transparent, and tables and data can be synchronized across various database Klustore instances.