After upgrading the mysql-server to 5.7 and enabling GTIDs, the mysql-backup script started spewing errors.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. (...repeated for every database schema...) mysqldump: Couldn't execute 'SHOW FIELDS FROM `host_summary`': View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
That's two errors for the price of one.
The first was easily fixed by doing as suggested:
- Add
--set-gtid-purged=OFFto the per-schema mysqldump. - Add a tailing dump for ALL; this is only viable if your databases
are small or few. (If they are few, you could consider skipping the
per-schema dump.)
mysqldump $mysqlargs --quick --all-databases --triggers --routines \ --events >"$dstpath/ALL.$day.sql" && rm -f "$dstpath/ALL.$day.sql.bz2" && bzip2 "$dstpath/ALL.$day.sql"
The second error was a bit more strange. For some reason the mysql upgrade had created the tables, but not the triggers and the functions. Or they got lost during a dump restore. In any case, debugging went like this:
# /usr/local/bin/mysql-backup
mysqldump: Couldn't execute 'SHOW FIELDS FROM `host_summary`':
View 'sys.host_summary' references invalid table(s) or column(s) or function(s)
or definer/invoker of view lack rights to use them (1356)
# mysql --defaults-file=/etc/mysql/debian.cnf sys
...
mysql> show create view host_summary\G
...
*************************** 1. row ***************************
View: host_summary
Create View: CREATE ALGORITHM=TEMPTABLE DEFINER=`mysql.sys`@`localhost`
SQL SECURITY INVOKER VIEW `host_summary` AS
select if(isnull(`performance_schema`.`accounts`.`HOST`)
...
mysql> select * from host_summary;
ERROR 1356 (HY000): View 'sys.host_summary' references invalid table(s) or
column(s) or function(s) or definer/invoker of view lack rights to use them
mysql> select if(isnull(`performance_schema`.`accounts`.`HOST`),'background',...
...
ERROR 1305 (42000): FUNCTION sys.format_time does not exist
A-ha, a missing function.
# dpkg -S /usr/share/mysql/mysql_sys_schema.sql mysql-server-5.7: /usr/share/mysql/mysql_sys_schema.sql # mysql --defaults-file=/etc/mysql/debian.cnf < /usr/share/mysql/mysql_sys_schema.sql ERROR 1064 (42000) at line 43: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
Invalid SQL? Nah, just inline semi-colons within statements. Since MySQL doesn't mind multiple statements at once, the fix was to surround the entire SQL with a new delimiter:
# ( cat /usr/share/mysql/mysql_sys_schema.sql; echo '####' ) |
mysql --defaults-file=/etc/mysql/debian.cnf --delimiter='####' sys
Fixed! Now, what is that sys database anyway?
It's a collection of views, functions and procedures to help MySQL administrators get insight into MySQL Database usage, according to the MySQL sys schema.
That might come in handy later...