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.

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.