Jump to content

How To Import and Export Databases in MySql


brent
 Share

Recommended Posts

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'

     

 

 

 

 

 

 

 

Link to comment
Share on other sites

 Share

×
×
  • Create New...