— 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]
%.hostname.com matches any host ending in “hostname.com
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
init-file=/
stop and restart the mysql server
check that the root password as been reset
remove the line from task 3 from the configuration file
