Manage MySQL Database
This guide describes how to do common MySQL database administration tasks using the command line with the mysql program. These tasks include how to create and delete databases, users, and tables.
Add users and databases
- Firstly, log in to MySQL as the root user at the command line:
mysql -u root -p
- Then, type the MySQL root password, and press Enter.
To create a database user, type the following command.
Replace username and password with the user you want to create and the user’s password:GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
This command grants the user all permissions. However, you can specify permissions to maintain precise control over database access. For instance, to explicitly grant the SELECT permission, you can use the following command:
GRANT SELECT ON *.* TO 'username'@'localhost';
For more information about setting MySQL database permissions, kindly visit https://dev.mysql.com/doc/refman/5.5/en/grant.html.
- After that, 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
- Next, 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, use 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 show how to create a basic table named table, and how to insert some data into it:
CREATE TABLE table( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_table primary key (id) ); INSERT INTO table( id, name ) VALUES ( null, 'Sample data' );
Remove Tables and Databases
To delete a table, use the following command from the mysql> prompt. Replace tablename with the name of the table that you want to delete:
DROP TABLE tablename;
Similarly, to delete an entire database, use the following command from the mysql> prompt. Replace dbname with the name of the database that you want to delete:
DROP DATABASE dbname;
Removing Database 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';
We hope this article helped you to learn how to manage MySQL database. For more articles, please go to Knowledge Base.
