# MySQL command cheat sheet

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 the most important set of
commands.

## Log in to MySQL shell

The MySQL shell can be accesses with the `mysql` command. Here as the user
`root`:

```shell
$ mysql -u root -p
```

`-u`, `--user`

The MySQL user name to use when connecting to the server

`-p`, `--password`

The password to use when connecting to the server. If you use the short
    option form (`-p`), you cannot have a space between the option
    and the password. If you omit the password value following the
    `--password` or `-p` option on the command line, mysql
    prompts for one.

## Database related commands

Once you’re in [hacker voice], list all databases:

```sql
SHOW DATABASES;
```

List all users:

```sql
SELECT User FROM mysql.user;
```

Create DB:

```sql
CREATE DATABASE database_name;
```

Delete database:

```sql
DROP DATABASE database_name;
```

## MySQL user related commands

Create user:

```sql
CREATE USER username@localhost;
```

Remove user:

```sql
DROP USER 'bob'@'localhost';
```

Set password for the user:

```sql
SET PASSWORD FOR bob@localhost= PASSWORD("password_here");
```

Grant privileges for the user to the newly created DB:

```sql
GRANT ALL PRIVILEGES ON database_name.* TO bob@localhost IDENTIFIED BY 'password
```

## Misc MySQL commands

Refresh MySQL:

```sql
FLUSH PRIVILEGES;
```

## Importing and exporting databases

Import DB:

```shell
$ 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:

```shell
--default-character-set=utf8 db_name < ~/path/db_filename.sql
```

Export (aka dump) a MySQL database using the `mysqldump` command:

```shell
$ 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:

```sql
$ sudo mysqldump -h 127.0.0.1 -u root -p db_name > ./sql_dumps/db_name.sql
```

## Conclusions

This what’s needed for very simple database operations, like setting up a new
WordPress DB or stuff like that.

Hope this was helpful, thanks for reading.