My FAQ for routine MySQL admin tasks:
Set the root password:
$ mysqladmin -u root password NEWPASSWORD
Change the root password:
$ mysqladmin -u root -p ‘oldpassword’ password ‘newpassword’
Recover a lost or forgotton MySQL root password:
1. Stop the MySQL server:
(For RedHat, Fedora, CentOS, Oracle Linux)
# service mysqld stop
(For other *nix varieties you can look for the .pid file that contains the server’s process ID. The location of this varies between distros, hostnames and configurations. Usually it is called either mysqld.pid or hostname.pid. You can stop the server by sending a kill signal to the mysqld process by using the the path name of the .pid file passed as an argument like this:
# kill `cat /mysql-data-directory/host_name.pid’
2. Create a text file containing the following statments to update the root password. Type the two statement one on each line.
UPDATE mysql.user SET Password=PASSWORD(‘NewPassword’) WHERE User=’root’;
FLUSH PRIVILEGES;
Save this file and make sure the mysql user can read the file. This example uses the name mysql-reset-file as the saved filename.
chown mysql:mysql mysql-reset-file
3. Start the MySQL server with the –init-file start-up option:
# mysqld_safe –init-file=/home/user/mysql-reset-file &
The server should start and execute the query in the mysql-reset-file at startup changing the root password. After the server starts, delete the mysql-reset-file you created.
Create a database:
$ mysql -u <useridwithcreatepriv> -p
login with your password then
mysql> create <databasename>;
Create a user:
CREATE USER mark IDENTIFIED BY ‘password’;
Delete a user:
DROP USER mark;
grant privileges to a user:
SHOW GRANTS for mark;
GRANT SELECT ON userdb.* TO mark;
REVOKE SELECT ON userdb.* FROM mark;
change a user’s password:
SET PASSWORD FOR mark = Password(‘password’);
mysql> drop database <dbname>;
Simple!
Related articles
