How To Import and Export Databases in MySql

How to Import and Export Databases

Export

To Export a database, open up terminal, making sure that you are not logged into MySQL and type,

mysqldump -u [username] -p [database name] > [database name].sql

The database that you selected in the command will now be exported to your droplet.

Import

To import a database, first create a new blank database in the MySQL shell to serve as a destination for your data.

CREATE DATABASE newdatabase;

Then log out of the MySQL shell and type the following on the command line:

mysql -u [username] -p newdatabase < [database name].sql

With that, your chosen database has been imported into your destination database in MySQL.

Create a database user

login to mysql

mysql -u root -p

run the following command

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';

Replace newuser with the new user name, and user_password with the user password.

Grant Privileges to a MySQL User Account

• ALL PRIVILEGES – Grants all privileges to a user account.
• CREATE – The user account is allowed to create databases and tables.
• DROP - The user account is allowed to drop databases and tables.
• DELETE - The user account is allowed to delete rows from a specific table.
• INSERT - The user account is allowed to insert rows into a specific table.
• SELECT – The user account is allowed to read a database.
• UPDATE - The user account is allowed to update table rows.

To grant specific privileges to a user account, use the following syntax:

• Grand all privileges to a user account over a specific database:

  mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';

   

• Grand all privileges to a user account on all databases:

  mysql> GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';

   

• Grand all privileges to a user account over a specific table from a database:

  mysql> GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';

   

• Grant multiple privileges to a user account over a specific database:

  mysql> GRANT SELECT, INSERT, DELETE ON database_name.* TO database_user@'localhost';

  Display MySQL User Account Privileges

  To find the privilege(s) granted to a specific MySQL user account, use the SHOW GRANTS statement:

  mysql> SHOW GRANTS FOR 'database_user'@'localhost';

  The output will look something like below:

  +---------------------------------------------------------------------------+
  | Grants for database_user@localhost                                       |
  +---------------------------------------------------------------------------+
  | GRANT USAGE ON *.* TO 'database_user'@'localhost'                        |
  | GRANT ALL PRIVILEGES ON `database_name`.* TO 'database_user'@'localhost' |
  +---------------------------------------------------------------------------+
  2 rows in set (0.00 sec)
  

  Revoke Privileges from a MySQL User Account

  The syntax to revoke one or more privileges from a user account is almost identical as when granting privileges.

  To revoke all privileges from a user account over a specific database, run the following command:

  mysql> REVOKE ALL PRIVILEGES ON database_name.* FROM 'database_user'@'localhost';

  Remove an Existing MySQL User Account

  To delete a MySQL user account use the DROP USER statement:

  mysql> DROP USER 'user'@'localhost'