Upgrading Percona Server from 8.0.44 to 8.4.7 on GTID based replication environments?

MySQL & MariaDB Percona Server for MySQL 8.0
1

We are planning to upgrade our Percona Server 8.0.44 to 8.4.7. Our environment is setup GTID based replication (replica is read only).

During the upgrade (after 8.4.7), we need to uninstall and install some plugins like uninstall deprecated audit log and validate_password and install component audit filter, component validate Password plugins.

Since uninstalling and installing plugins, the replica host needs to be in WRITE mode and Source/Primary only be upgraded after upgrading replica, is there any implications of uninstalling and installing plugins by temporarily enabling write operations on the replica during this task? After upgrading the source/primary, any impact on the GTID executions (as these plugins already installed in replica)?

What are the correct method of upgrading from 8.0.44 to 8.4.7 with GTID based replications which also includes uninstalling deprecated plugins and installing new component based plugins?

An early reply is highly appreciated.

Thank you

2

Please review the documentation for upgrading from 8.0 to 8.4.

3

Hi Wayne,

Yes we already reviewed the document. But we have a question on upgrading our readly replica (before upgrading its SOURCE).

Since uninstalling and installing plugins, the REPLICA needs to be in WRITE mode and Source/Primary only be upgraded after upgrading REPLICA, is there any implications of uninstalling and installing plugins by temporarily enabling write operations on the REPLICA during this task? After upgrading the source/primary, any impact on the GTID transactions as REPLICA already upgraded with new plugins?

Please advice

4

Running INSTALL COMPONENT on a replica is usually safe because it is a local, non-replicated administrative action. It won’t generate GTIDs, won’t hit the binlog, and won’t disturb replication by itself.

If you are concerned about potential errant GTID’s you can disable writes to the bin logs on the replica with this command:

SET session sql_log_bin=0;

Once you are done making your changes you can re-enable bin logs:

SET session sql_log_bin=1;

I HIGHLY recommend that you test these changes in your lower environments before touching your production. Better yet if you have a sandbox environment I would recommend testing there.

5

Thank you so much Wayne.

What about installing the audit_filter_log using the script “/usr/share/mysql/audit_log_filter_linux_install.sql”? Does this generate GTIDs?

6

Yes it will, since it creates two tables. I would suggest that you not run that script on your replica. Run that on your primary, then replication will take care of the 2 tables that will be created. Then go back to the replica and run:

INSTALL COMPONENT 'file://component_audit_log_filter';
7

Ok. I can exclude running the file “/usr/share/mysql/audit_log_filter_linux_install.sql” from REPLICA. But can I run INSTALL COMPONENT ‘file://component_audit_log_filter’; on REPLICA before upgrading the SOURCE/Primary (running the file “/usr/share/mysql/audit_log_filter_linux_install.sql” in SOURCE)?

We are trying automate the whole upgrade process using ansible.

Please advise.

8

Yes you should be able to run:

INSTALL COMPONENT ‘file://component_audit_log_filter’;

On your replica before you upgrade the Primary.

9

Thank you so much for your quick response. My other question is after running the statement on the REPLICA, can I set the following parameters in the REPLICA configuration file (my.cnf) and restart the server? Or these parameters needs the required audit_log_filter tables?

audit_log_filter.format=JSON
audit_log_filter.file=/var/log/mysql-audit/mysql.audit.log
audit_log_filter.rotate_on_size=0
audit_log_filter.max_size=0
audit_log_filter.prune_seconds=0

Please advise.

Thank you for your help.

10

That should not be an issue.

11

Thank you so much. Appreciate your great help. I will test it and let you know. After that I will close this topic.

Once again thank you much.

12

Hi Wayne,

Following steps will be used during the upgrade on the REPLICA side.

  1. Enable WRITE MODE with SET session sql_log_bin=0;

  2. Uninstall all deprecated PLUGINS using UNISTALL and INSTALL statements

  3. INSSTALL the PLUGINS using INSTALL PLUGIN (and NOT running any scripts)

  4. Update the following parameters

    audit_log_filter.format=JSON
    audit_log_filter.file=/var/log/mysql-audit/mysql.audit.log
    audit_log_filter.rotate_on_size=0
    audit_log_filter.max_size=0
    audit_log_filter.prune_seconds=0

  5. Restart the instance

Please let me know the above steps are correct or needs any changes.

Thanks a lot for your help as always.

13

Those steps covered what we have been discussing, you should be good to go.

14

Good morning Wayne. Forgot to include one more question. Since the audit filter tables available only after PRIMARY/SOURCE upgrade, I cannot run the following statements in REPLICA (before upgrading SOURCE)

SELECT audit_log_filter_set_filter

SELECT audit_log_filter_remove_user

SELECT audit_log_filter_set_user

SELECT audit_log_filter_flush();

Am I correct?

15

You are correct. Let replication handle the audit_log once you have the Source complete.