# Managing MySQL databases, users, and tables from the command line

> Create and delete MySQL databases, users and tables and run SQL script files from the command line when you have SSH access.

Source: https://helpcenter.ruachost.com/developers/databases/managing-mysql-databases-users-and-tables-from-the-command-line/

**Managing MySQL Databases, Users, and Tables from the Command Line**\
Learn how to create, manage, and delete MySQL databases, users, and tables directly from the command line.

> **Note:**
If your account includes cPanel, it is recommended to use it instead for managing MySQL databases and users.

## Creating Users and Databases

1. Log in to MySQL as the root user:

```
mysql -u root -p
```

2. Enter the MySQL root password.
3. To create a database user:

```
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
```

> This grants all permissions on all databases. To limit permissions, use specific privileges:

```
GRANT SELECT ON *.* TO 'username'@'localhost';
```

Or for a single database:

```
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';
```

4. Exit MySQL:

```
\q
```

5. Log in as the new user:

```
mysql -u username -p
```

```
\
Create a new database:
```

```
CREATE DATABASE dbname;
USE dbname;
```

7. Create a table and insert data:

```
CREATE TABLE example (
  id smallint unsigned NOT NULL AUTO_INCREMENT,
  name varchar(20) NOT NULL,
  CONSTRAINT pk_example PRIMARY KEY (id)
);
INSERT INTO example (id, name) VALUES (NULL, 'Sample data');
```

## Using SQL Script Files

1. Create a user as shown above.
2. Create a file `example.sql` and include:

```
CREATE DATABASE dbname;
USE dbname;
CREATE TABLE tablename (
  id smallint unsigned NOT NULL AUTO_INCREMENT,
  name varchar(20) NOT NULL,
  CONSTRAINT pk_example PRIMARY KEY (id)
);
INSERT INTO tablename (id, name) VALUES (NULL, 'Sample data');
```

> Replace `dbname` and `tablename` with your preferred names.

3. Run the script:

```
mysql -u username -p < example.sql
```

## Deleting Tables and Databases

- Delete a table:

```
DROP TABLE tablename;
```

- Delete a database:

```
DROP DATABASE dbname;
```

> Warning: MySQL does not confirm deletion; all data will be lost immediately.

## Deleting Users

- View all users:

```
SELECT user FROM mysql.user GROUP BY user;
```

- Delete a user:

```
DELETE FROM mysql.user WHERE user = 'username';
```

## More Information

For full MySQL documentation, visit [MySQL Reference Manual](https://dev.mysql.com/doc/refman/5.5/en/index.html).