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

​

Creating users and databases

1. At the command line, log in to MySQL as the root user:
   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:
   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:

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

   To grant the user all permissions only on the database named dbname, you would use the following command:

   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.

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:
   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:
   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:
   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:
   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. As in the previous procedure, you should first create a user for the database. To do this, type the following commands:
   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:
   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:
   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

DROP TABLE tablename;

DROP DATABASE dbname;

​

Deleting users

SELECT user FROM mysql.user GROUP BY user;

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

​

More information

​

Related articles

• Connecting to MySQL from the command line
• Resetting the MySQL root password
• Configuring remote MySQL access
• Managing MySQL databases and users
• MySQL user privileges on shared servers