MySQL 사용법

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"

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

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

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

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

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

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';

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

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;

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;

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

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

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;

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 }'