MySQL Password Reset, User Grant/Revoke, Backup & Restore, and phpMyAdmin Deployment Guide

1. Password Recovery and Setting

The lab requires resetting the MySQL root password and then setting it normally. First stop MySQL, start it with --skip-grant-tables (three possible methods), connect without a password, update the mysql.user table, flush privileges, and finally restart MySQL in normal mode.

Step 1 – Stop MySQL:

[root@dbsvr1 ~]# service mysql stop

Step 2 – Start MySQL skipping grant tables (choose one):

[root@dbsvr1 ~]# service mysql start --skip-grant-tables

or

[root@dbsvr1 ~]# mysqld_safe --user=mysql --skip-grant-tables &

or edit /etc/my.cnf and add skip_grant_tables=1 then start MySQL.

Step 3 – Connect and reset password:

[root@dbsvr1 ~]# mysql -u root

mysql> UPDATE mysql.user SET password=PASSWORD('1234567') WHERE user='root' AND host='localhost';

mysql> FLUSH PRIVILEGES;

Step 4 – Restart MySQL normally (remove the skip‑grant option) and verify the new password.

2. User Grant and Revocation

Grant root full access from the 192.168.4.0/24 network, create a management account dba007 with full privileges, revoke root's local access, and later restore it. Also grant webuser full rights on webdb , then revoke and change to read‑only.

Examples:

mysql> GRANT all ON *.* TO root@'192.168.4.%' IDENTIFIED BY '123';

mysql> GRANT all ON *.* TO dba007@localhost IDENTIFIED BY '1234567' WITH GRANT OPTION;

mysql> REVOKE all ON *.* FROM root@localhost;

mysql> GRANT all ON webdb.* TO webuser@'%' IDENTIFIED BY '888888';

mysql> REVOKE all ON webdb.* FROM webuser@'%';

mysql> GRANT SELECT ON webdb.* TO webuser@'%';

3. Data Backup and Recovery

Use mysqldump for logical backups and mysql to restore. The lab covers backing up all databases, a single database, and multiple selected databases, then restoring into a new database.

Backup all databases:

[root@dbsvr1 ~]# mysqldump -u root -p --all-databases > /root/alldb.sql

Backup a single database (e.g., userdb ):

[root@dbsvr1 ~]# mysqldump -u root -p userdb > userdb.sql

Backup multiple databases:

[root@dbsvr1 ~]# mysqldump -u root -p -B mysql test userdb > mysql+test+userdb.sql

Restore (example for userdb ):

[root@dbsvr1 ~]# mysql -u root -p userdb2 < /root/userdb.sql

After restoration, verify the data and optionally drop the old database.

4. MySQL Management Tool – phpMyAdmin Deployment

Deploy phpMyAdmin on a LAMP platform to manage MySQL via a web interface. Install Apache, MySQL client, PHP, and required extensions, then download and extract phpMyAdmin, configure the server IP, and access it from a browser.

Install required packages:

[root@dbsvr ~]# yum -y install httpd mysql php php-mysql

Install missing php-mbstring RPM with --nodeps if needed.

Configure Apache (e.g., set ServerName and DirectoryIndex ) and restart:

[root@dbsvr ~]# service httpd restart

Deploy phpMyAdmin:

[root@dbsvr ~]# unzip phpMyAdmin-4.1.2-all-languages.zip

[root@dbsvr ~]# mv phpMyAdmin-4.1.2-all-languages /var/www/html/pma

Copy and edit the config file to point to the MySQL server IP (e.g., 192.168.4.10 ).

Access the interface from a client browser at http://192.168.4.6/pma/index.php , log in with the MySQL credentials, and manage databases within the granted scope.