I grew accustomed to using MySQL GUI tools, but found that they can be quite daunting to use, ESPECIALLY a big pain to use is the MySQL Workbench suite. If you MUST use a GUI interface, try SQLYog. Simple and to the point.
If you are looking for MySQL command line, here are a few that may come in handy:
Database commands
- create database ‘databasename’;
- drop database ‘databasename’;
- show databases;
- use ‘databasename’
Backing up databases
- mysqldump -u username -p ‘databasename’ > dumpfile.sql
Restoring databases
- 1st option (from MySQL path): mysqldump -u username -p ‘databasename’ < dumpfile.sql
- 2nd option (from MySQL prompt):
- use ‘databasename’
- source ‘pathtodumpfile.sql’;
Create and Permissioning
- Show all users: SELECT User, Host, Password FROM mysql.user;
- SHOW GRANTS [FOR user]
- GRANT ALL ON databasename.* to ‘databasename’@’localhost’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;
- NOTE: With any user changes, ALWAYS run: FLUSH PRIVILEGES; (this will force the changes to go into effect)