How to manage MySQL databases, users, and tables from the command line
Knowledge Base
How to manage MySQL databases, users, and tables from the command line
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.
This article only applies to the products listed in the Article Details sidebar. 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.
Table of Contents
Creating users and databases
To create a MySQL database and user, follow these steps:
- At the command line, log in to MySQL as the root user:
mysql -u root -p
- Type the MySQL root password, and then press Enter.
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';
This command grants the user all permissions. However, you can grant specific permissions to maintain precise control over database access. For example, to explicitly grant the SELECT permission, you would use the following command:
GRANT SELECT ON *.* TO 'username'@'localhost';
For more information about setting MySQL database permissions, please visit https://dev.mysql.com/doc/refman/5.5/en/grant.html.
- Type \q to exit the mysql program.
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
- Type the user's password, and then press Enter.
To create a database, type the following command. Replace dbname with the name of the database that you want to create:
CREATE DATABASE dbname;
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;
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
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:
- As in the previous procedure, you should first create a user for the database. To do this, type the following commands:
mysql -u root -pGRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';\q
Create a file named example.sql and open it in your preferred text edtior. 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' );
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.
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.
- Save the changes to the example.sql file and exit the text editor.
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
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:
DROP TABLE tablename;
This command assumes that you have already selected a database by using the USE statement.
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:
DROP DATABASE dbname;
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.
Deleting users
To view a list of all users, type the following command from the mysql> prompt:
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:
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.
- To learn more about hosting your MySQL database with A2 Hosting, click here.
Article Details
- Product: Cloud VPSUnmanaged Dedicated ServerUnmanaged VPS
- Level: Intermediate
Other Articles In This Category
- Connecting to MySQL from the command line
- Connecting to MySQL using PHP
- Connecting to MySQL using Python
- Connecting to MySQL using Node.js
- Connecting to MySQL using Perl
- Connecting to MySQL using Microsoft .NET
- Importing and exporting a MySQL database
- Remote MySQL connections
- MySQL client applications
- Managing MySQL databases, users, and tables from the command line
- Converting a MySQL database to UTF-8
- Converting the MySQL time zone
- MySQL database backups using cron jobs
- MySQL database backups using AutoMySQLBackup
- The information_schema database
- Resetting the MySQL root password
- MySQL runs out of memory or does not start
- MySQL user privileges on shared servers
- Creating and executing MySQL stored functions and procedures
- MySQL views
- MySQL triggers
- Working with MySQL database engines
- Checking MySQL database and table sizes
- Repairing MySQL databases and tables
- Enabling the slow query log in MySQL
- Renaming a MySQL database
- Using indexes to improve MySQL query performance
- Restricting MySQL port access
- Installing phpMyAdmin on managed hosting accounts
- Creating a MySQL installation for local testing
- Troubleshooting MySQL definer issues
- Using SELECT INTO OUTFILE in MySQL
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
Grow Your Web Business
Subscribe to receive weekly cutting edge tips, strategies, and news you need to grow your web business.
No charge. Unsubscribe anytime.
Did you find this article helpful? Then you'll love our support. Experience the A2 Hosting difference today and get a pre-secured, pre-optimized website. Check out our web hosting plans today.