Password recovery

1. Login as root on the system
2. Shutdown the running mysql daemon
#/etc/init.d/mysql stop
3. Start mysql server in save mode and skip loading the users
   # mysqld_safe --skip-grant-tables &
4. Now you can connect as root to the server (no password required)
   # mysql -u root
mysql>
mysql> use mysql;
5. To see which users are currently existing:
   mysql> select Host,User,Password from user
   To change the password of the root user connecting from the local machine
   mysql> UPDATE user SET Password=PASSWORD('newrootpassword') WHERE User='root' AND Host='localhost';
   Now you can disconnect from the mysql console
mysql> quit
6. After the password has been changed, simply restart the mysql server with the init script
   # /etc/init.d/mysql stop
# /etc/init.d/mysql start
7. Logging in with your just changed password must now work
# mysql -u root -p

Creating users

• Create a new administrator with all privileges
  mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'host' IDENTIFIED BY 'password' WITH GRANT OPTION;
• Create a new user with full privileges on a single database
  mysql> GRANT ALL PRIVILEGES ON database.* TO 'user'@'host' IDENTIFIED BY 'password';

Exploring the database server

• List all known databases on the server
mysql> SHOW DATABASES;
• Switch to a database
  mysql> USE databasename
• List all tables in a database
  mysql> SHOW TABLES;
• Show table structure (columns)
mysql> DESCRIBE tablename;