MySQL Guide

System requirements

MySQL

Tested on 8.4 LTS (MySQL community RPMs). The service name in the commands is mysqld.

Database settings

Server and connection character set utf8mb4

Database server OS

Linux x86_64, glibc 2.28 or later (for example RHEL or Rocky Linux 8 or later)

Build tools

gcc, make, mysql-community-devel of the same version as the server, curl, tar, sha256sum (coreutils 8.25 or later)

Privileges

An OS account with sudo on the database server, and the database root account

MySQL 8.0 and 9.x, Debian and Ubuntu packages, a custom plugin directory, replication, and SELinux enforcing configurations were not tested. For MariaDB, see the MariaDB Guide.

Where to run each step

mysqld loads the UDF library mysql_strcrypt.so, and the UDFs call libstrcrypt.so in the same plugin directory. The libraries, key file, and settings all live on the database server. Nothing needs to be installed on the application server. UDFs are registered server-wide, so they are not installed per database.

1. Pre-checks

ldd --version | head -1
mysqld --version
command -v mysql_config gcc make curl sha256sum tar sudo
systemctl is-active mysqld
mysql -u root -p -e "SELECT @@version, @@plugin_dir, @@character_set_server"

This prints the glibc version, the MySQL version, the installed tools, and the service state; the last line asks for the root password and prints the version, plugin directory, and server character set (utf8mb4). command -v prints only the tools that are installed. If @@plugin_dir differs from mysql_config --plugindir, set PLUGIN_DIR= in step 2 to the @@plugin_dir value.

Common errors

2. Build and install the libraries

Only if the build tools are missing

sudo dnf install -y gcc make mysql-community-devel-$(rpm -q --qf '%{VERSION}' mysql-community-server)

This installs the development package pinned to the server version. Without the version, the latest one is installed.

Download, verify, build, and install

mkdir -p ~/strcrypt-mysql && cd ~/strcrypt-mysql
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/libstrcrypt.so
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/libstrcrypt.so.hmac
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/strcrypt-mysql-0.3.1.tar.gz
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/SHA256SUMS
sha256sum -c --ignore-missing SHA256SUMS
tar xzf strcrypt-mysql-0.3.1.tar.gz && cd strcrypt-mysql-0.3.1
PLUGIN_DIR=$(mysql_config --plugindir)
sudo install -m 755 ../libstrcrypt.so "$PLUGIN_DIR"/
sudo install -m 644 ../libstrcrypt.so.hmac "$PLUGIN_DIR"/
make STRCRYPT_LIB_DIR="$PLUGIN_DIR" STRCRYPT_INCLUDE_DIR=.
sudo install -m 755 mysql_strcrypt.so "$PLUGIN_DIR"/
ldd "$PLUGIN_DIR/mysql_strcrypt.so" | grep strcrypt

The UDFs have to be built against the MySQL headers on the database server, so they ship as source. The build runs under ~/strcrypt-mysql, and all three libraries go to the plugin directory. Installing libstrcrypt.so into the plugin directory first and building with that path keeps the UDF library from referring to a home directory path. The step succeeded if the last line prints libstrcrypt.so => /usr/lib64/mysql/plugin/libstrcrypt.so.

Common errors

3. Generate the key file

sudo install -d -o mysql -g mysql -m 700 /etc/strcrypt
head -c 24 /dev/urandom | base64 | head -c 32 | sudo -u mysql tee /etc/strcrypt/strcrypt.key > /dev/null
sudo chmod 600 /etc/strcrypt/strcrypt.key
sudo -u mysql wc -c /etc/strcrypt/strcrypt.key

This creates a 32-byte key in a file only the mysql account can read. The last line should print 32 /etc/strcrypt/strcrypt.key. The second line overwrites an existing key file, so do not run it again once a key exists. The key is never passed through SQL arguments or tables. If you lose the key, the ciphertext cannot be recovered, so keep a separate backup.

Common errors

Key file problems appear after ERROR 1123 (HY000): Can't initialize function 'strcrypt_encrypt'; 환경 변수 오류: when a function is called in step 5 or later. After fixing the key file, restart mysqld for the change to apply.

4. Pass the key path to the server

sudo mkdir -p /etc/systemd/system/mysqld.service.d
printf '[Service]\nEnvironment=STRCRYPT_KEY_FILE=/etc/strcrypt/strcrypt.key\n' | sudo tee /etc/systemd/system/mysqld.service.d/strcrypt.conf
sudo systemctl daemon-reload
sudo systemctl restart mysqld
sudo systemctl show mysqld -p Environment

A systemd drop-in file passes the environment variable STRCRYPT_KEY_FILE to the mysqld service. It is applied when Environment= on the last line contains STRCRYPT_KEY_FILE=/etc/strcrypt/strcrypt.key. Database connections drop briefly during the restart. A value exported in your shell does not reach the server. To use algorithms outside the Korean standards, add the line Environment=STRCRYPT_MODE=non-approved.

Common errors

5. Register the UDFs

cd ~/strcrypt-mysql/strcrypt-mysql-0.3.1
mysql -u root -p < install.sql

install.sql registers six functions and runs an encryption and decryption test at the end. The step succeeded if the output shows [OK] and === 설치 완료 === (installation complete). Registration survives server restarts, and the script can be run again. The script drops the existing functions first, so running it while the library files are missing removes all of them.

Common errors

6. Restrict execute privileges

MySQL has no execute privilege for UDFs, so you cannot limit which users call the decryption function. Any account that can connect, even one with only USAGE, can decrypt a ciphertext string. Instead, restrict which accounts can access the ciphertext column and the key file.

Check accounts that can register or drop functions (root)

SELECT user, host FROM mysql.user WHERE Insert_priv = 'Y' OR Delete_priv = 'Y';
SELECT user, host, db FROM mysql.db WHERE db = 'mysql' AND (Insert_priv = 'Y' OR Delete_priv = 'Y');
SELECT user, host, table_name, table_priv FROM mysql.tables_priv WHERE db = 'mysql' AND table_name = 'func';

If the first query returns only root | localhost and the other two return nothing, only the administrator can change the functions. Revoke the privilege from any other account that appears.

7. Verify the installation

Character set and a basic round trip

SELECT @@character_set_client, @@character_set_connection;
SELECT CONVERT(strcrypt_decrypt(strcrypt_encrypt('test')) USING utf8mb4) AS result;

Round-trip test (1,000 rows; the test table is dropped at the end)

CREATE TABLE strcrypt_selftest (id INT PRIMARY KEY, val VARCHAR(100), enc VARCHAR(400));

INSERT INTO strcrypt_selftest (id, val)
WITH RECURSIVE seq (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1000)
SELECT n, CONCAT('row-', n, '-홍길동') FROM seq;

UPDATE strcrypt_selftest SET enc = strcrypt_encrypt_algo(val, 'aria256gcm');

SELECT COUNT(*) AS CHECKED,
       SUM(NOT (CAST(strcrypt_decrypt(enc) AS BINARY) <=> CAST(val AS BINARY))) AS MISMATCHES
FROM strcrypt_selftest;

DROP TABLE strcrypt_selftest;

Run these as the application user in the application database (for example mysql -u app_user -p appdb). The first query should return utf8mb4 | utf8mb4, the second test, and the round-trip test CHECKED 1000 | MISMATCHES 0. The round-trip test needs CREATE and DROP privileges.

Common errors

SQL functions

CREATE TABLE members (id INT PRIMARY KEY, ssn VARCHAR(20), ssn_enc VARCHAR(80));
INSERT INTO members (id, ssn, ssn_enc)
VALUES (1, '900101-1234567', strcrypt_encrypt_algo('900101-1234567', 'aria256gcm'));
INSERT INTO members (id, ssn) VALUES (2, '850505-2345678'), (3, NULL);
UPDATE members SET ssn_enc = strcrypt_encrypt_algo(ssn, 'aria256gcm')
WHERE ssn_enc IS NULL AND ssn IS NOT NULL;
SELECT id, CONVERT(strcrypt_decrypt(ssn_enc) USING utf8mb4) AS ssn FROM members ORDER BY id;
SELECT id, CONVERT(strcrypt_decrypt(strcrypt_encrypt_algo(CAST(CONCAT('EMP-', id) AS CHAR), 'seed128gcm')) USING utf8mb4) AS emp_code
FROM members ORDER BY id;
SELECT COUNT(*) AS failed_rows FROM members WHERE ssn_enc IS NOT NULL AND strcrypt_decrypt(ssn_enc) IS NULL;

Use them like ordinary functions in SELECT, INSERT, and UPDATE. NULL input gives NULL output. The result character set is binary, so wrap results in CONVERT(... USING utf8mb4) to read them as strings, and compare bytes with CAST(... AS BINARY). Name the algorithm with strcrypt_encrypt_algo when encrypting. Ciphertext from strcrypt_encrypt without an algorithm has no header, so it no longer decrypts if the server operating mode changes.

Wrap expressions that mix in numeric columns with CAST(... AS CHAR). On MySQL 8.4, passing an expression such as CONCAT('A-', id) straight to a UDF corrupts the argument from the second row on: Korean values become NULL and ASCII values are encrypted wrongly without any error. The emp_code query above shows the workaround.

When encryption or decryption of one row fails, the statement does not stop; only that row becomes NULL. Tampered ciphertext, a different key, and input that is not UTF-8 all end this way, and the result cannot be told apart from NULL input. Check by counting rows that have ciphertext but decrypt to NULL, as in the last query.

Ciphertext differs every time, even for the same value, so it cannot be indexed. For exact-match search, store strcrypt_search_index(value, 'table.column') in an indexed search column and compare it with the index of the search term. Do not use an unkeyed SHA-256 hash: guessable values such as national ID numbers can be recovered by hashing every candidate. A search column reveals which rows share a value, so add one only where search is needed, keeping in mind that UDFs have no per-function execute privilege, so any account that can connect can compute indexes for candidate values.

Bulk processing

MySQL has no UDF that takes many rows at once. UDFs are called once per row, so the number of calls cannot be reduced inside SQL. For many rows, query chunks in primary key order outside the database, encrypt or decrypt each chunk with the CLI in one call, and apply the results by primary key. Ciphertext from the CLI can be decrypted with the UDFs, and ciphertext from the UDFs can be decrypted with the CLI. This method needs neither the server's local_infile setting nor temporary files.

Prepare the CLI (in ~/strcrypt-mysql from step 2)

cd ~/strcrypt-mysql
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/strcrypt
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/strcrypt.hmac
sha256sum -c --ignore-missing SHA256SUMS
sudo install -m 755 strcrypt /usr/local/bin/strcrypt
sudo install -m 644 strcrypt.hmac /usr/local/bin/strcrypt.hmac
mysql_config_editor set --login-path=strcrypt_batch --host=localhost --user=app_user --password

The last line stores the application user's connection details in ~/.mylogin.cnf and asks for the password. The key file belongs to the mysql account, so the script below runs the CLI with sudo -u mysql. sudo drops /usr/local/bin from the path, so the CLI is called by its absolute path.

Encrypt an existing column in bulk

set -euo pipefail
last=0
while :; do
  rows=$(mysql --login-path=strcrypt_batch -N -B appdb -e "SELECT id, REPLACE(TO_BASE64(ssn), '\n', '') FROM customers WHERE id > $last AND ssn IS NOT NULL ORDER BY id LIMIT 1000")
  [ -n "$rows" ] || break
  enc=$(printf '%s\n' "$rows" | cut -f2 | sudo -u mysql env STRCRYPT_KEY_FILE=/etc/strcrypt/strcrypt.key /usr/local/bin/strcrypt encrypt-batch -a aria256gcm)
  [ "$(printf '%s\n' "$enc" | wc -l)" -eq "$(printf '%s\n' "$rows" | wc -l)" ]
  paste <(printf '%s\n' "$rows" | cut -f1) <(printf '%s\n' "$enc") |
    awk -F'\t' 'BEGIN { print "START TRANSACTION;" } { printf "UPDATE customers SET ssn_enc = '\''%s'\'' WHERE id = %s;\n", $2, $1 } END { print "COMMIT;" }' |
    mysql --login-path=strcrypt_batch appdb
  last=$(printf '%s\n' "$rows" | tail -n 1 | cut -f1)
  echo "updated up to id $last"
done

Save it to a file and run it with bash. Pasted into an interactive shell, set -e closes the shell on an error. Each pass queries 1,000 rows of plaintext as Base64, encrypts them with encrypt-batch, and sends primary key UPDATEs for the chunk in one transaction. Each chunk prints updated up to id .... On failure it stops before applying the chunk, and chunks already committed stay. Running it again re-encrypts every row from the start.

• TO_BASE64 inserts a line break every 76 characters. Without REPLACE removing them, long values split into several records.
• NULL comes out of the query as the string NULL, which also reads as Base64 and would encrypt the wrong value, so the ssn IS NOT NULL condition cannot be dropped.

Check the results (run as app_user)

SELECT COUNT(*) AS CHECKED,
       SUM(NOT (CAST(strcrypt_decrypt(ssn_enc) AS BINARY) <=> CAST(ssn AS BINARY))) AS MISMATCHES,
       SUM(ssn IS NOT NULL AND ssn_enc IS NULL) AS NOT_ENCRYPTED
FROM customers;

If MISMATCHES and NOT_ENCRYPTED are 0, every row was encrypted and decrypts with the UDF.

Decrypt ciphertext from the UDFs with the CLI

set -euo pipefail
rows=$(mysql --login-path=strcrypt_batch -N -B appdb -e "SELECT strcrypt_encrypt_algo(ssn, 'seed128gcm'), REPLACE(TO_BASE64(ssn), '\n', '') FROM customers WHERE ssn IS NOT NULL ORDER BY id LIMIT 1000")
dec=$(printf '%s\n' "$rows" | cut -f1 | sudo -u mysql env STRCRYPT_KEY_FILE=/etc/strcrypt/strcrypt.key /usr/local/bin/strcrypt decrypt-batch)
paste <(printf '%s\n' "$dec") <(printf '%s\n' "$rows" | cut -f2) | awk -F'\t' '$1 != $2 { bad++ } END { printf "CHECKED %d MISMATCHES %d\n", NR, bad + 0 }'

decrypt-batch writes plaintext as Base64, so it is compared with the Base64 of the original. The output should be CHECKED 1000 MISMATCHES 0.

Limitations

• UDF calls cannot be restricted per user. Protect data with read privileges on the ciphertext column.
• Plaintext must be UTF-8 without NUL bytes. Pass latin1 column values as CONVERT(value USING utf8mb4).
• Results sent to the client must be smaller than max_allowed_packet. Values stored into a table inside the server can exceed that limit.
• Changes to the key file or server environment variables apply after mysqld restarts.
• The same value encrypts to a different ciphertext each time, so you cannot search or index the ciphertext column. Keep a separate hash column if you need lookups.
• Plaintext passes through the memory of the mysqld process on the database server.

API reference

Encryption and decryption results use the binary character set. Key errors, library errors, and a disallowed algorithm given as a constant stop the whole statement with ERROR 1123; other per-row failures return NULL for that row only.

Test environments

Steps 1 to 7, the SQL function example, the bulk processing example (a 2,500-row round trip including NULL, empty strings, and Korean values), calls from an account with only USAGE, and blocking by column privileges were confirmed (2026-09-17).