Data from PMM, Percona's open source database management tool, shows that 58 percent of MySQL and MariaDB (a MySQL fork) instances are running MySQL 8.0, while 18.8 percent are running 5.7, which went out of support in 2023.

While users might put off database migration because of the disruption involved, they should be aware that the upgrade from MySQL 8.0 to 8.4 – the most recent stable version – is not nearly so onerous as the upgrade from 5.7 to 8.0. "It was a very big and painful jump," Zaitsev told us.

ne way to do it:

Dump trigger definitions into a file

# mysqldump -uroot -p --triggers --add-drop-trigger --no-create-info \
      --no-data --no-create-db --skip-opt test > /tmp/triggers.sql

Open triggers.sql file in your favorite editor and use Find and Replace feature to change DEFINERs. Save updated file.

Recreate triggers from the file

 # mysql -u USER -p DATABASENAME < /tmp/triggers.sql

Quick and dirty PHP script to work around http://bugs.mysql.com/73894
"Can't easily change DEFINER on existing views, stored routines, triggers, events"

I have am having problems backing up my databases after an update. I have been poking around on my system trying to figure out why. One query I ran returned this result.

Got error: 1449: The user specified as a definer ('cittool'@'%') does not exist when using LOCK TABLES

After some investigation it appears that the definer for these views is an old developer account that has been purged from the system. The databases and views with this problem are used very infrequently, and most being kept around for archival purposes.

There is about 40 views with a definer that no longer exists. Is there an easy way to change the definer to a different account on everything at once? Is there a way to get mysqldump to simply dump all the views out to a file so I could edit that file and recreate the views?

The question seemed easy enough: We’ve dropped a user, now we want to change the DEFINER on all database objects that currently have it set to this dropped user?

This should be possible by checking the INFORMATION_SCHEMA tables of the appropriate object types (routines, triggers, views and events) and performing an ALTER on each of them that just modifies the DEFINER but nothing else, right?

Unfortunately it isn’t that easy, or at least not yet (see http://bugs.mysql.com/73894 and https://mariadb.atlassian.net/browse/MDEV-6731 ).