$ 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 <SSLsrvr_IPaddress>


$ 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 <MySQL_IPaddress>
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 <SSLServer_IPAddress>-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';