Ellis Giles

So, I imported an old database to completely replace all existing databases and user tables in a new MySQL install.
Basically a dump of all tables (including user tables) from mysqldump will dump your user table too.

mysqldump --all-databases > dump.sql


So, you have a dump including all users too, and if you import them into a new mysql install it can override the new required mysql users.

My debian-sys-maint account got removed from a new MySQL 5.7 install and the following error was showing up in my log file:

/etc/cron.daily/logrotate:
error: error running shared postrotate script for ‘/var/log/mysql.log /var/log/mysql/*log ‘
run-parts: /etc/cron.daily/logrotate exited with return code 1

So, to recreate the debian-sys-maint users, you can connect to the mysql daemon and create the user directly.
Note that with 5.7, there is no password field, so you must use the newer authentication_string instead.

First, find the password your scripts are trying to use by:

# more /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = YOURPASSWORD
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = YOURPASSWORD
socket = /var/run/mysqld/mysqld.sock


Make a note of the password field.  Next, connect to mysql, using mysql -p, and create the user, make sure to replace the YOURPASSWORD with the password copied from above.


INSERT INTO `mysql`.`user` ( `Host`, `User`, `authentication_string`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`
) VALUES (
'localhost', 'debian-sys-maint',
password('YOURPASSWORD'),
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0);
FLUSH PRIVILEGES;


Hopefully, your log update will run without problem.

Filed under: Uncategorized