MySQL User Management


— One thing to note with MySQL is that there is no locking of accounts after a number of unsuccessful attempts like in other databases.

MySQL does a little different to other database servers, a user in MySQL is a username and a host string (hostname, IP address, fully qualified domain name or netmask), for example

[email protected]
[email protected]
[email protected].%
each of the above users can have different passwords, a user could have multiple entries, MySQL will try and match the most specific username. You can use wildcards % and _ in host strings here are some examples

192.168.1.% matches 192.168.1.[0-255]
192.168._.% matches 192.168.[0-9].[0-255] matches any host ending in “
To create, drop or rename a user see below

display users select * from mysql.user;
display who you are logged in as select user();
select current_user();
create user create user ‘ops’@’192.168.2.%’ identified by ‘password’;
delete user drop user ‘ops’@’192.168.2.%’;
rename user rename user ‘ops’@’192.168.2.%’ to ‘support’@’192.168.2.%’;
reset password set password for ‘ops’@’192.168.2.%’ = password(‘new password’);

Resetting the root password

There are times when the root password is lost or you have started a new job and no-one knows what the root password is, the process of resetting the root password will require a restart of the MySQL server. The first option involves using the skip-grants-table option, the second option involves creating a SQL file and using the init-file option to call this SQL apon startup, here are both options

option one
This process involves using the skip-grants-table option, which is less secure than option two as it allows anyone for a brief period of time to access the MySQL server, as when the server is restarted it is “wide open” with anyone able to log in with all privileges without specifying a username

Edit the configuration file (mysql.ini) and the line skip-grants-table
Restart the server
connect to the server using mysql
change the root password

update mysql.user set password=PASSWORD(‘new password’) where user=’root’;
exit the client and stop the database, remove the skip-grants-table and restart
option two
The second option invokes creating a SQL file that will be executed when the database starts, this is the more secure option

create a sql file, call it what you like, the contents should be

update mysql.user set password=PASSWORD(‘new password’) where user=’root’; FLUSH privileges;
once the file has been saved make a note of the full path and filename
edit the configuration file (mysql.ini) and add the below line in the mysqld section

stop and restart the mysql server
check that the root password as been reset
remove the line from task 3 from the configuration file