MySQL Database and User

Sometimes you need to add an user to a database with proper permission.
You can follow below steps to create database and user and grant permissions.

1. First login to mysql with root user. Say mysql root user password is root123.
   
   mysql -u root -p
   password: root123
2. Assume we will create database my_database
3. Assume we will create user my_user with password as my_password
4. Delete the database if exists
   
   drop database if exists my_database;
5. Delete the user if exists
   
   drop user 'my_user'@'localhost';
   drop user 'my_user'@'%';

   The % above means user at any host.

6. Now create the user with permissions both for local machine and remote access of DB
   
   CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'my_password';
   CREATE USER 'my_user'@'%' IDENTIFIED BY 'my_password';
7. Now grant the permission
   
   GRANT ALL ON *.* TO 'my_user'@'localhost';
   GRANT ALL ON *.* TO 'my_user'@'%';
8. Create database
   
   create database my_database;
9. Lets allow user my_user to connect to the server from localhost using the password my_password.
   
   grant usage on *.* to my_user@'localhost' identified by 'my_password';
   grant usage on *.* to my_user@'%' identified by 'my_password';
10. And finally we grant all privileges on the my_database to my_user.
    
    grant all privileges on my_database.* to my_user@'localhost' ;
    grant all privileges on my_database.* to my_user@'%' ;

Thats all. Now you have created database my_database, user my_user with password as my_password.

Say you want to create database lm_database with user as lm_user and password as lm_password.
Lets combine all the steps given above, so one you need execute below bunch of commands at mysql prompt.

From command line you can easily backup and restore the database. From phpMyAdmin or cpanel, importing database of higher size involves bit complexity, however from terminal you can do it easily.

1. Assume we will backup database my_database to a file my_backup_database.sql
2. Execute
   
   mysqldump -u root -p my_database > my_backup_database.sql
3. Lets say we want to restore that database to system
4. Execute
   
   mysql -u root -p my_database < my_backup_database.sql

Importing and exporting database is so easy from terminal. This is frequently used for Drupal install.

Say you have a database, and you have 100s of tables. You want to just delete all the tables, and keep database, user and password intact. Use below comamnd to reset the database.