You are here

MySQL Database and User

MySQL Database and User Creation

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.

Example

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.

CREATE USER 'lm_user'@'localhost' IDENTIFIED BY 'lm_password';
CREATE USER 'lm_user'@'%' IDENTIFIED BY 'lm_password';

GRANT ALL ON *.* TO 'lm_user'@'localhost';
GRANT ALL ON *.* TO 'lm_user'@'%';

create database lm_database;

grant usage on *.* to lm_user@'localhost' identified by 'lm_password';
grant usage on *.* to lm_user@'%' identified by 'lm_password';

grant all privileges on lm_database.* to lm_user@'localhost' ;
grant all privileges on lm_database.* to lm_user@'%' ;

MySQL Database Backup and Restore

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.

How to Reset a Database (MySQL)

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.

mysql --user=YOUR_USER --password=YOUR_PASSWORD -BNe "show tables" YOUR_DATABASE | tr '\n' ',' | sed -e 's/,$//' | awk '{print "SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS " $1 ";SET FOREIGN_KEY_CHECKS = 1;"}' | mysql --user=YOUR_USER --password=YOUR_PASSWORD YOUR_DATABASE