Requirements
All operations must be executed from a MySQL account having administrator access/privileges.
mysql -u [username] -p
How to Create a New User
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
Grant Permissions For User
Grant access to all databases.
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
Grant access to one specified database.
GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';
Grant access to one specified database’s table.
GRANT ALL PRIVILEGES ON database_name.table_name TO 'newuser'@'localhost';
Grant specific privileges to a user.
- ALL PRIVILEGES – grants all privileges to the MySQL user
- CREATE – allows the user to create databases and tables
- DROP – allows the user to drop databases and tables
- DELETE – allows the user to delete rows from a specific MySQL table
- INSERT – allows the user to insert rows into a specific MySQL table
- SELECT – allows the user to read the database
- UPDATE – allows the user to update table rows
GRANT SELECT, UPDATE, INSERT ON database_name.* TO 'newuser'@'localhost';
Make The Changes Take Effect
FLUSH PRIVILEGES;
Change a User’s Password
ALTER USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';
Create a Database
CREATE DATABASE database_name;
Remove a MySQL User
DROP USER 'newuser'@'localhost';
Remove a Database
DROP DATABASE database_name;
Remove a Database Table
DROP TABLE table_name;
Remove All Data From a Table
TRUNCATE TABLE table_name;
Import a SQL File From CLI
mysql -u [username] -p database_name < file_path_of_SQL_file.sql;
Restore a single database from a file.
mysql --one-database -u [username] -p database_name < all_db_bak_file.sql
Creating Backups
Backup Single Database
mysqldump -u [username] -p database_name > database_name_bak_file.sql
Backup Multiple Database
mysqldump --databases -u [username] -p database_name1 database_name2 > multi_database_bak_file.sql
Backup All Databases
mysqldump --all-databases -u [username] -p database_name > all_db_bak_file.sql