- MariaDB ``` [root@localhost ~]# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | > sudo bash -s -- --mariadb-server-version="mariadb-10.3" [root@localhost ~]# sudo yum install MariaDB-server MariaDB-client [root@localhost ~]# systemctl start mariadb [root@localhost ~]# systemctl status mariadb [root@localhost ~]# mysql -uroot Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.3.13-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> \s -------------- mysql Ver 15.1 Distrib 10.3.13-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 8 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.3.13-MariaDB MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 1 min 28 sec Threads: 7 Questions: 7 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 12 Queries per second avg: 0.079 -------------- MariaDB [(none)]> MariaDB [(none)]> MariaDB [(none)]> INSTALL PLUGIN server_audit SONAME 'server_audit.so'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> set global server_audit_logging=1; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> SHOW VARIABLES LIKE 'server_audit%'; +-------------------------------+-----------------------+ | Variable_name | Value | +-------------------------------+-----------------------+ | server_audit_events | | | server_audit_excl_users | | | server_audit_file_path | server_audit.log | | server_audit_file_rotate_now | OFF | | server_audit_file_rotate_size | 1000000 | | server_audit_file_rotations | 9 | | server_audit_incl_users | | | server_audit_logging | ON | | server_audit_mode | 0 | | server_audit_output_type | file | | server_audit_query_log_limit | 1024 | | server_audit_syslog_facility | LOG_USER | | server_audit_syslog_ident | mysql-server_auditing | | server_audit_syslog_info | | | server_audit_syslog_priority | LOG_INFO | +-------------------------------+-----------------------+ 15 rows in set (0.001 sec) MariaDB [(none)]> MariaDB [(none)]> MariaDB [(none)]> create schema if not exists sbtest; Query OK, 1 row affected (0.000 sec) MariaDB [(none)]> create user sbtest@'%' identified by 'sbtest'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> grant all on sbtest.* to sbtest@'%'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> \q Bye ``` - ProxySQL ``` [root@localhost ~]# cat < [proxysql_repo] > name= ProxySQL YUM repository > baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever > gpgcheck=1 > gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key > EOF [proxysql_repo] name= ProxySQL YUM repository baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/$releasever gpgcheck=1 gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key [root@localhost ~]# yum install -y proxysql-1.4.15 [root@localhost ~]# sudo systemctl start proxysql [root@localhost ~]# sudo systemctl status proxysql [root@localhost ~]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> ' Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. Admin> Admin> \s -------------- mysql Ver 15.1 Distrib 10.3.13-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 1 Current database: admin Current user: admin SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MySQL Server version: 5.5.30 (ProxySQL Admin Module) Protocol version: 10 Connection: 127.0.0.1 via TCP/IP Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 TCP port: 6032 Uptime: 24 sec Threads: 0 Questions: 0 Slow queries: 0 -------------- Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port,max_connections) VALUES (1,'192.168.154.100',3306,10); Query OK, 1 row affected (0.000 sec) Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port,max_connections) VALUES (2,'192.168.154.100',3306,10); Query OK, 1 row affected (0.000 sec) Admin> Admin> LOAD MYSQL SERVERS TO RUNTIME; Query OK, 0 rows affected (0.003 sec) Admin> SAVE MYSQL SERVERS TO DISK; Query OK, 0 rows affected (0.074 sec) Admin> insert into mysql_users(username,password,active,default_schema,transaction_persistent,max_connections,default_hostgroup) values ('sbtest','sbtest',1,'sbtest',1,10,1); Query OK, 1 row affected (0.000 sec) Admin> Admin> SAVE MYSQL USERS TO DISK; Query OK, 0 rows affected (0.047 sec) Admin> LOAD MYSQL USERS TO RUNTIME; Query OK, 0 rows affected (0.000 sec) Admin> \q Bye ``` - Trigger `CHANGEUSER` when we query user variables ``` [root@localhost ~]# mysql -usbtest -psbtest -h127.0.0.1 -P6033 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.30 (ProxySQL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> \s -------------- mysql Ver 15.1 Distrib 10.3.13-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 2 Current database: sbtest Current user: sbtest@192.168.154.100 SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MySQL Server version: 5.5.30 (ProxySQL) Protocol version: 10 Connection: 127.0.0.1 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 6033 Uptime: 1 min 44 sec Threads: 1 Questions: 3 Slow queries: 0 -------------- MySQL [(none)]> show tables; Empty set (0.000 sec) MySQL [(none)]> \r Connection id: 3 Current database: *** NONE *** MySQL [(none)]> \q Bye ``` - Log from `server_audit.log` ``` [root@localhost ~]# tail -f /var/lib/mysql/server_audit.log 20190320 21:53:54,localhost.localdomain,sbtest,192.168.154.100,20,0,CONNECT,sbtest,,0 20190320 21:53:54,localhost.localdomain,sbtest,192.168.154.100,20,23,QUERY,sbtest,'select DATABASE(), USER() limit 1',0 20190320 21:53:54,localhost.localdomain,sbtest,192.168.154.100,20,24,QUERY,sbtest,'select @@character_set_client, @@character_set_connection, @@character_set_server, @@character_set_database limit 1',0 20190320 21:53:58,localhost.localdomain,sbtest,192.168.154.100,20,25,QUERY,sbtest,'show tables',0 20190320 21:54:00,localhost.localdomain,OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO ^C ``` - Log that we expect ``` 20190320 21:53:18,localhost.localdomain,root,localhost,11,0,CHANGEUSER,,,0 ```