Recently I had to deal with a Directadmin VPS that had mysql installed, but for some reason there wasn’t a record for root in mysql user table. Here are the queries I used to add a root account, and to assign all the necessary privilegies.
First of all We need to create a record with username and password.
INSERT INTO user(user, password) values (‘root’, PASSWORD(‘PASSWORD_HERE’));
Then we need to add all the privileges to our root:
UPDATE `mysql`.`user` SET `Select_priv` = ‘Y’,
`Insert_priv` = ‘Y’,
`Update_priv` = ‘Y’,
`Delete_priv` = ‘Y’,
`Create_priv` = ‘Y’,
`Drop_priv` = ‘Y’,
`Reload_priv` = ‘Y’,
`Shutdown_priv` = ‘Y’,
`Process_priv` = ‘Y’,
`File_priv` = ‘Y’,
`Grant_priv` = ‘Y’,
`References_priv` = ‘Y’,
`Index_priv` = ‘Y’,
`Alter_priv` = ‘Y’,
`Show_db_priv` = ‘Y’,
`Super_priv` = ‘Y’,
`Create_tmp_table_priv` = ‘Y’,
`Lock_tables_priv` = ‘Y’,
`Execute_priv` = ‘Y’,
`Repl_slave_priv` = ‘Y’,
`Repl_client_priv` = ‘Y’,
`Create_view_priv` = ‘Y’,
`Show_view_priv` = ‘Y’,
`Create_routine_priv` = ‘Y’,
`Alter_routine_priv` = ‘Y’,
`Create_user_priv` = ‘Y’ WHERE `user`.`Host` = ” AND `user`.`User` = ‘root’ LIMIT 1 ;
And the last command:
GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY ‘PASSWORD_HERE’ WITH GRANT OPTION
Then you can do the same for da_admin mysql user:
GRANT ALL PRIVILEGES ON *.* TO da_admin@localhost IDENTIFIED BY ‘PASSWORD_HERE’ WITH GRANT OPTION;
Restart mysql and have fun.