Administration of MySQL Database in Linux

Some common commands are introduced for MySQL database administration in Linux.

1. Connect to MySQL database server:
# mysql -h 'mysql-server' -u root -p'password'

2. Change the password for root user:
# mysqladmin -u root -p'password' password 'newpassword'

3. Check if the MySQL database service is running:
# mysqladmin -u root -p ping

4. Create a new database:
# mysqladmin create 'db-name'

5. Delete an existing database:
# mysqladmin drop 'db-name'

6. Create a user account:
# CREATE USER 'username'@'mysql-server' IDENTIFIED BY 'password'

7. Display all the running process/queries in the MySQL database:
# mysqladmin -u root -p'password' processlist

8. Shut down the MySQL server:
# mysqladmin -u root -ptmppassword shutdown

9. Back up a database:
# mysqldump -h 'mysql-server' -u 'user' (-p) 'db-name' (eg. --all-databases) > backup.sql

10. Restore a database:
# mysql -h 'mysql-server' -u 'user' (-p) 'db-name' < backup.sql

Free Web Hosting