This is a quick cheat sheet of MySQL commands related to creating, importing, and exporting databases and database users.
I rarely need to work with MySQL databases anymore, but when I do, I always need to look up the commands. Might as well document a small set of commands here.
The MySQL shell can be accesses with the
mysql command. Here as the user
$ mysql -u root -p
-p), you cannot have a space between the option and the password. If you omit the password value following the
-poption on the command line, mysql prompts for one.
Once you’re in [hacker voice], list all MySQL databases:
List all MySQL users:
SELECT User FROM mysql.user;
Create MySQL DB:
CREATE DATABASE database_name;
Delete MySQL database:
DROP DATABASE database_name;
Create MySQL user:
CREATE USER username@localhost;
Remove MySQL user:
DROP USER 'bob'@'localhost';
Set password for the MySQL user:
SET PASSWORD FOR bob@localhost= PASSWORD("passwordhere");
Grant privileges for the user to the newly created DB:
GRANT ALL PRIVILEGES ON database_name.* TO bob@localhost IDENTIFIED BY 'password
Import MySQL DB:
$ mysql -u root -p --default-character-set=utf8 db_name < ~/path/db_filename.sql
Or if you do it from the MySQL shell, get rid of the login thing the beginning:
--default-character-set=utf8 db_name < ~/path/db_filename.sql
Export (aka dump) a MySQL database using the
$ sudo mysqldump –u root –p db_name > ./sql_dumps/dump_file.sql
If you’re not prompted for a password, and get an error like this:
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
It might be that you need to define the host in the export command. I’ve had this issues and usually it’s been solved by using the local IP:
$ sudo mysqldump -h 127.0.0.1 -u root -p db_name > ./sql_dumps/db_name.sql
This what’s needed for very simple database operations, like setting up a new WordPress DB or stuff like that.
Comments would go here, but the commenting system isn’t ready yet, sorry. Tweet me @hiljaa if you want to make a correction etc.