Mysql – How to rename MySQL users without logging into MySQL


I wanted to rename the default Linux username on a Lubuntu 16.04 VM (from osgeo live) from user to newuser. I followed answers in this question to rename the Linux user. But after renaming, I cannot log into mysql, and mysql generates the following error:

mysql ERROR 1045 (28000): Access denied for user 'newuser'@'localhost' (using password: NO)

My guess is that there is a default MySQL user created by the VM creators which is the same as the Linux user (i.e. user). My question is:

How can I change the MySQL username to newuser) to be consistent (now that I cannot log in)?

— Update —

I executed sudo service mysql restart --skip-grant-tables --skip-networking as suggested (after deleting /var/log/mysql/error.log) and the log has the following diagnosis:

2017-01-12T02:25:23.664857Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-01-12T02:25:23.664951Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2017-01-12T02:25:23.804276Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-01-12T02:25:23.805216Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.16-0ubuntu0.16.04.1) starting as process 2574 ...
2017-01-12T02:25:23.808154Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-01-12T02:25:23.808182Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-01-12T02:25:23.808186Z 0 [Note] InnoDB: Uses event mutexes
2017-01-12T02:25:23.808189Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-01-12T02:25:23.808192Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2017-01-12T02:25:23.808194Z 0 [Note] InnoDB: Using Linux native AIO
2017-01-12T02:25:23.808365Z 0 [Note] InnoDB: Number of pools: 1
2017-01-12T02:25:23.808440Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-01-12T02:25:23.809445Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-01-12T02:25:23.815490Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-01-12T02:25:23.816764Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-01-12T02:25:23.828144Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-01-12T02:25:23.860141Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-01-12T02:25:23.860201Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-01-12T02:25:23.903728Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-01-12T02:25:23.904430Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-01-12T02:25:23.904442Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-01-12T02:25:23.904815Z 0 [Note] InnoDB: Waiting for purge to start
2017-01-12T02:25:23.955251Z 0 [Note] InnoDB: 5.7.16 started; log sequence number 2632377
2017-01-12T02:25:23.956218Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-01-12T02:25:23.963845Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-01-12T02:25:23.968294Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170112  3:25:23
2017-01-12T02:25:23.972653Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-01-12T02:25:23.972715Z 0 [Note] Server hostname (bind-address): ''; port: 3306
2017-01-12T02:25:23.972741Z 0 [Note]   - '' resolves to '';
2017-01-12T02:25:23.972799Z 0 [Note] Server socket created on IP: ''.
2017-01-12T02:25:24.006451Z 0 [Note] Event Scheduler: Loaded 0 events
2017-01-12T02:25:24.007120Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.16-0ubuntu0.16.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2017-01-12T02:25:24.686565Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)

Best Answer

You will have to due it the sneaky way

STEP 01 : Restart mysqld without grants and with no one being able to login behind you

$ service mysql restart --skip-grant-tables --skip-networking

STEP 02 : Login to mysql passwordless

$ mysql

STEP 03 : Locate all users in mysql you want to change

EXAMPLE: Suppose the user in question is olduser and you want to changed to newuser

Run this

mysql> SELECT user,host FROM mysql.user WHERE user='olduser';

to user all users run this

mysql> SELECT user,host FROM mysql.user;

You can change the username with the following

mysql> UPDATE mysql.user SET user='newuser' WHERE user='olduser';

STEP 04 : Leave mysql when you have changed all users you needed to change

mysql> exit

STEP 05 : Restart mysqld normally

$ service mysql restart