$ locate ibd | less /var/lib/mysql/testDB/testTB.ibd /var/lib/mysql/sys/sys_config.ibd ... $ cat /var/lib/mysql/testDB/testTB.ibd | head -n 20 mysql> alter instance rotate InnoDB master key; [mysqld] early-plugin-load=keyring_file.so keyring_file_data=/var/lib/mysql/keyring-data/keyring [mysqld] innodb_file_per_table=ON $ sudo service mysql restart mysql> create table testTB (c1 INT) encryption='y'; mysql> alter table testTB encryption='y'; Query OK, 1 row affected (0.33 sec) Records: 1 Duplicates: 0 Warnings: 0 $ strings /var/lib/mysql/testDB/testTB.ibd | head -n 20 $ mysql -u user -p -h $ tshark -i any > mysql_plaintext.pcap $ mkdir /var/lib/mysql/new_certs && cd /var/lib/mysql/new_certs $ openssl genrsa 2048 > ca-key.pem $ openssl req -new -x509 -nodes -days 3500 -key ca-key.pem -out ca.pem $ openssl genrsa 2048 > server-key.pem $ openssl req -new -key server-key.pem -out server-req.pem $ openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem $ openssl genrsa 2048 > client-key.pem $ openssl req -new -key client-key.pem -out client-req.pem $ openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem $ openssl verify -CAfile ca.pem server-cert.pem client-cert.pem server-cert.pem: OK client-cert.pem: OK $ vim /etc/mysql/mysql.conf.d/mysqld.cnf [mysqld] ssl_ca= /var/lib/mysql/new_certs/ca.pem ssl_cert=/var/lib/mysql/new_certs/ server-cert.pem ssl_key=/var/lib/mysql/new_certs/server-key.pem require_secure_transport=ON $ chown -R mysql:mysql /var/lib/mysql/new_certs/ $ chmod 600 client-key.pem server-key.pem ca-key.pem $ sudo service mysql restart $ mysql -u user -p -h ERROR 1045 (28000): Access denied for user 'user'@'%' (using password: YES) $ mkdir ~/certs $ scp user@[IP_Address]:/var/lib/mysql/new_certs/ca-cert.pem ~/certs/ $ scp user@[IP_Address]:/var/lib/mysql/new_certs/client-cert.pem ~/certs/ $ scp user@[IP_Address]:/var/lib/mysql/new_certs/client-key.pem ~/certs/ mysql> alter user 'user'@'client_ip' require X509; mysql> flush privileges; $ mysql -u user -p -h -ssl-ca= ~/certs/ca.pem -ssl-cert=~/certs/client-cert.pem -ssl-key=under ~/certs/client-key.pem mysql> grant insert on database.* to 'user'@'localhost'; mysql> grant select(coulmn_name) on database.Clients to 'user'@'localhost'; mysql> flush privileges; mysql> create user 'user'@'localhost' identified by 'password' - > with max_queries_per_hour 15 - > max_updates_per_hour 12 - > max_connections_per_hour 4 - > max_user_connections 3; mysql> alter user 'user'@'localhost' with max_queries_per_hour 25; mysql> select load_file('/etc/passwd'); mysql> select load_file('/etc/passwd'); +--------------------------+ | load_file('/etc/passwd') | +--------------------------+ | NULL | +--------------------------+ 1 row in set (0.000 sec) mysql> select 'Hello2' into outfile '/tmp/hello.txt'; $ cat /tmp/hello.txt Hello2 ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES) $ cat /tmp/world Hello world! mysql> select 'Hello world!' into dumpfile '/tmp/world'; Query OK, 1 row affected (0.001 sec) mysql> revoke FILE on *.* from 'user'@'localhost'; $ vim /etc/mysql/my.cnf user=mysql $ sudo service mysql restart mysql> delete from mysql.user where user='root' and host not in ('localhost', '127.0.0.1', '::1'); mysql> flush privileges; [mysqld] port=XXXX skip-networking sudo service mysql restart mysql> create user 'user'@'192.168.100.0/255.255.255.0';