brent Posted May 8, 2020 Share Posted May 8, 2020 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 [email protected] | +---------------------------------------------------------------------------+ | 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 More sharing options...
Recommended Posts