> ## Documentation Index
> Fetch the complete documentation index at: https://kb.hosting.com/llms.txt
> Use this file to discover all available pages before exploring further.

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

> Create a MySQL Database with this concise command line guide, featuring detailed instructions, code snippets, and related article links.

This article describes how to do common MySQL database administration tasks from the command line using the *mysql* program. These common tasks include creating and deleting databases, users, and tables.

<Warning>
  **Important**

  If your account includes cPanel, you should use it instead to manage MySQL databases and users. For information about how to do this, please see [this article](/docs/managing-mysql-databases).
</Warning>

## Creating users and databases

To create a MySQL database and user, follow these steps:

1. At the command line, log in to MySQL as the root user:

   ```bash theme={null}
   mysql -u root -p
   ```

2. Type the MySQL root password, and then press Enter.

3. To create a database user, type the following command. Replace *username* with the user you want to create, and replace *password* with the user's password:

   ```sql theme={null}
   GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
   ```

   > 📘 Note
   >
   > The previous command grants the user all permissions on all databases. However, you can grant specific permissions to maintain precise control over database access. For example, to explicitly grant only the **SELECT** permission for the specified user, you would use the following command:
   >
   > ```sql theme={null}
   > GRANT SELECT ON *.* TO 'username'@'localhost';
   > ```
   >
   > To grant the user all permissions only on the database named *dbname*, you would use the following command:
   >
   > ```sql theme={null}
   > GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';
   > ```
   >
   > For more information about setting MySQL database permissions, please visit [https://dev.mysql.com/doc/refman/5.5/en/grant.html](https://dev.mysql.com/doc/refman/5.5/en/grant.html).

4. Type **\q** to exit the *mysql* program.

5. To log in to MySQL as the user you just created, type the following command. Replace *username* with the name of the user you created in step 3:

   ```bash theme={null}
   mysql -u username -p
   ```

6. Type the user's password, and then press Enter.

7. To create a database, type the following command. Replace *dbname* with the name of the database that you want to create:

   ```sql theme={null}
   CREATE DATABASE dbname;
   ```

8. To work with the new database, type the following command. Replace *dbname* with the name of the database you created in step 7:

   ```sql theme={null}
   USE dbname;
   ```

9. You can now work with the database. For example, the following commands demonstrate how to create a basic table named *example*, and how to insert some data into it:

   ```sql theme={null}
   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

The previous procedure demonstrates how to create and populate a MySQL database by typing each command interactively with the *mysql* program. However, you can streamline the process by combining commands into a SQL script file.

The following procedure demonstrates how to use a SQL script file to create and populate a database:

1. As in the previous procedure, you should first create a user for the database. To do this, type the following commands:

   ```bash theme={null}
   mysql -u root -p
   GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
   \q
   ```

2. Create a file named *example.sql* and open it in your preferred text editor. Copy and paste the following text into the file:

   ```sql theme={null}
   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' );
   ```

3. Replace *dbname* with the name of the database that you want to create, and *tablename* with the name of the table that you want to create.

   > 📘 Note
   >
   > You can modify the sample script file to create multiple databases and tables all at once. Additionally, the sample script creates a very simple table. You will likely have additional data requirements for your tables.

4. Save the changes to the *example.sql* file and exit the text editor.

5. To process the SQL script, type the following command. Replace *username* with the name of the user you created in step 1:

   ```bash theme={null}
   mysql -u username -p < example.sql
   ```

   The *mysql* program processes the script file statement by statement. When it finishes, the database and table are created, and the table contains the data you specified in the **INSERT** statements.

## Deleting tables and databases

To delete a table, type the following command from the **mysql>** prompt. Replace *tablename* with the name of the table that you want to delete:

```sql theme={null}
DROP TABLE tablename;
```

<Note>
  This command assumes that you have already selected a database by using the **USE** statement.
</Note>

Similarly, to delete an entire database, type the following command from the **mysql>** prompt. Replace *dbname* with the name of the database that you want to delete:

```sql theme={null}
DROP DATABASE dbname;
```

<Warning>
  **️ Warning**

  The *mysql* program does not ask for confirmation when you use this command. As soon as you press Enter, MySQL deletes the database and all of the data it contains.
</Warning>

## Deleting users

To view a list of all users, type the following command from the **mysql>** prompt:

```sql theme={null}
SELECT user FROM mysql.user GROUP BY user;
```

To delete a specific user, type the following command from the **mysql>** prompt. Replace *username* with the name of the user that you want to delete:

```sql theme={null}
DELETE FROM mysql.user WHERE user = 'username';
```

## More information

To view the official MySQL documentation and learn more about how to create databases, users, and tables, please visit [https://dev.mysql.com/doc/refman/5.5/en/index.html](https://dev.mysql.com/doc/refman/5.5/en/index.html).

## Related articles

* [Connecting to MySQL from the command line](/docs/connect-to-mysql-from-the-command-line)

* [Resetting the MySQL root password](/docs/reset-mysql-root-password)

* [Configuring remote MySQL access](/docs/remote-mysql-access)

* [Managing MySQL databases and users](/docs/managing-mysql-databases)

* [MySQL user privileges on shared servers](/docs/mysql-user-privileges-on-shared-servers)