Upgrading MySQL to 8.0.12 with Audit plugin.

As a spin-off from the previous post, https://mysqlmed.wordpress.com/2018/08/23/get-the-auditors-in/, I thought that it would be good to see how well the Audit plugin upgrades to MySQL 8. The big change in auditing is that the tables change from MyISAM to InnoDB, so keep your eyes open.

I’m using the previously used instance in version 5.7.18.

Preparation

Before we do anything, let’s make sure auditing will be in place when we restart the instance with 8.0.12:

Uncomment the plugin-load & audit-log params we had originally commented out. After all, this is something we should have done in the last post (apologies!):

vi my_audit.cnf:
  ..
  [mysqld]
  plugin-load =audit_log.so
  audit-log =FORCE_PLUS_PERMANENT
  ..

Restart the 5.7 instance so we upgrade from a rebooted / ‘as real as can be expected’ scenario:

bin/mysqladmin --defaults-file=my_audit.cnf -uroot shutdown
bin/mysqld --defaults-file=my_audit.cnf &

With the tail of the audit.log still running, login again as the audit_test_user:

bin/mysql -uaudit_test_user -paudittest123 -S /opt/mysql/audit/mysql_audit.sock

Observe that the login is being audited with out audit.log tail:

SELECT * from mysql.audit_log_user;
SELECT * from mysql.audit_log_filter;
SELECT @@audit_log_filter_id;

Lets check the instance with the upgrade checks:

https://dev.mysql.com/doc/refman/8.0/en/upgrading-strategies.html

Run the check-upgrade script:

mysqlcheck -S /opt/mysql/audit/mysql_audit.sock -uroot --all-databases --check-upgrade

Check for partitioned tables:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE NOT IN ('innodb', 'ndbcluster')
AND CREATE_OPTIONS LIKE '%partitioned%';

Check for table names:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE LOWER(TABLE_SCHEMA) = 'mysql'
and LOWER(TABLE_NAME) IN
(
'catalogs',
'character_sets',
'collations',
'column_statistics',
'column_type_elements',
'columns',
'dd_properties',
'events',
'foreign_key_column_usage',
'foreign_keys',
'index_column_usage',
'index_partitions',
'index_stats',
'indexes',
'parameter_type_elements',
'parameters',
'resource_groups',
'routines',
'schemata',
'st_spatial_reference_systems',
'table_partition_values',
'table_partitions',
'table_stats',
'tables',
'tablespace_files',
'tablespaces',
'triggers',
'view_routine_usage',
'view_table_usage'
);

If you find anything, then now’s the moment to change the names, partitions, etc.

Shutdown the instance:

bin/mysqladmin --defaults-file=my_audit.cnf -uroot shutdown

 

MySQL 8

Change to 8.0.12 binaries.

Copy & edit the my.cnf.

cp /usr/local/mysql/mysql-advanced-5.7.18-linux-glibc2.5-x86_64/my_audit.cnf /usr/local/mysql/mysql-commercial-8.0.12-linux-glibc2.12-x86_64/my_audit80.cnf

Get rid of query_cache & sql_mode params from the my.cnf, and adjust the audit log params:

vi my_audit80.cnf
   ..
   port=3380
   ..
   [mysqld]
   plugin-load =audit_log.so
   audit-log =FORCE_PLUS_PERMANENT
   ..
   basedir =/usr/local/mysql/mysql-commercial-8.0.12-linux-glibc2.12-x86_64
   ..

 

Start the instance with the 8.0.12 binaries:

bin/mysqld --defaults-file=my_audit80.cnf &

And time to upgrade:

bin/mysql_upgrade -S /opt/mysql/audit/mysql_audit.sock -uroot
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading system table data.
Checking system database.
mysql.audit_log_filter OK
mysql.audit_log_user OK
mysql.columns_priv OK
mysql.component OK
mysql.db OK
mysql.default_roles OK
mysql.engine_cost OK
mysql.func OK
mysql.general_log OK
mysql.global_grants OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.password_history OK
mysql.plugin OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.role_edges OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
Checking databases.
nexus.replicant OK
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.

Ok, all ok.

Double checking:

ls -lrt /opt/mysql/audit/data/*upg*
-rw-rw-r-- 1 khollman khollman 6 ago 22 19:58 /opt/mysql/audit/data/mysql_upgrade_info

cat /opt/mysql/audit/data/mysql_upgrade_info
8.0.12

 

Let’s check the audit plugin status:

bin/mysql -uroot -S /opt/mysql/audit/mysql_audit.sock

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'audit%';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| audit_log | ACTIVE |
+-------------+---------------+

SELECT @@audit_log_filter_id;

SELECT * from mysql.audit_log_user;
+-----------------+-----------+----------------+
| USER            | HOST      | FILTERNAME     |
+-----------------+-----------+----------------+
| %               |           | log_connection |
| audit_test_user | localhost | log_connIUD    |
| root            | localhost | log_nothing    |
+-----------------+-----------+----------------+

SELECT * from mysql.audit_log_filter;

Here we see the table is now InnoDB:

show create table mysql.audit_log_user;

So now we see everything is as it was, we suddenly remember we saw something about upgrading in the install section of the manual:

https://dev.mysql.com/doc/refman/8.0/en/audit-log-installation.html

Note

As of MySQL 8.0.12, for new MySQL installations, the USER and HOST columns in the audit_log_user table used by MySQL Enterprise Audit have definitions that better correspond to the definitions of the User and Host columns in the mysql.user system table. For upgrades to an installation for which MySQL Enterprise Audit is already installed, it is recommended that you alter the table definitions as follows:

So we run the following commands:

ALTER TABLE mysql.audit_log_user
DROP FOREIGN KEY audit_log_user_ibfk_1;
ALTER TABLE mysql.audit_log_filter
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci;
ALTER TABLE mysql.audit_log_user
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci;
ALTER TABLE mysql.audit_log_user
MODIFY COLUMN USER VARCHAR(32);
ALTER TABLE mysql.audit_log_user
ADD FOREIGN KEY (FILTERNAME) REFERENCES mysql.audit_log_filter(NAME);

Lets do the tail of the audit.log that hasn’t had anything written to it since we started the upgrade.

tail -100f /opt/mysql/audit/data/audit.log

Now to login as our audit_test_user user.

bin/mysql -uaudit_test_user -paudittest123 -S /opt/mysql/audit/mysql_audit.sock nexus

And run some I/U/D:

DELETE FROM replicant where `First name`='Rick';
UPDATE replicant set `Replicant` = 'No' where `First name` = 'Harrison';
INSERT INTO replicant (`First name`,`Last name`,`Replicant`) VALUES ('Rick','Ford','No');
UPDATE replicant set `Replicant` = 'Yes' where `First name` = 'Harrison';

exit, and watch it all being logged, except the SELECT’s.

A “status;” shows us:

--------------
bin/mysql Ver 8.0.12-commercial for linux-glibc2.12 on x86_64 (MySQL Enterprise Server - Commercial)

Connection id: 18
Current database: 
Current user: audit_test_user@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.12-commercial MySQL Enterprise Server - Commercial
...

 

Upgraded! With Auditing in place.