Skip to content

How to use the MySQL command line

I have a clean Linux OS with a MySQL database installed. An easy way of administering the database engine is installing ‘phpmyadmin’ which is a graphical database administration tool written in PHP. But phpmyadmin is always a weak spot in your production webserver environment. That is why I have phpmyadmin running on a separate development client and I am using the command line mysql utility on the production server. The following list are simple command line examples to administer your MySQL databases.

Login to your MySQL database:

mysql -u database_user -p

You will be prompted to enter the password for user database_user.

Show all the known databases on the mysql server:

mysql> show databases;

Change the prompt to the database you want to administer. You will see a message: Database changed:

mysql> \u mydb

Show all the tables inside the database ‘mydb’:

mysql> show tables;

Show the blueprint of the table ‘tbl_example’ inside database ‘mydb’:

mysql> describe tbl_example;

Show all the records inside the table tbl_example:

mysql> select * from tbl_example;

Delete all records from the table and reset the autoincrement. (Under the hood the table is re-created from scratch):

mysql> TRUNCATE TABLE tbl_example;

Reset the auto increment and start the first record with id 500:

ALTER TABLE tbl_example AUTO_INCREMENT = 500;

Removes the Foreign Key Constraint from the table tbl_example:

mysql> alter table tbl_example drop foreign key tbl_example_ibfk_1;

Create a backup of a specific database:

mysqldump mydb > mydb.sql -u root -p

Restore a backup into mysql:

mysql mydb < mydb.sql -u root -p

Create a new user with a password:

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Add standard access rights for the user to access the mydb database and all tables within. This will give the user ALL PRIVILEGES!:

mysql> GRANT ALL PRIVILEGES ON mydb.* TO 'database_user'@'localhost';

Re-read all permissions so they become active:

mysql> FLUSH PRIVILEGES;

Set specific permission on tbl_table. Choose from the following permissions:

ALL PRIVILEGES – allow a MySQL user full access
CREATE – allows them to create new tables or databases
DROP – allows them to them to delete tables or databases
DELETE – allows them to delete rows from tables
INSERT – allows them to insert rows into tables
SELECT – allows them to use the SELECT command to read through databases
UPDATE – allow them to update table rows
GRANT OPTION – allows them to grant or remove other users’ privileges

Example:

mysql> GRANT type_of_permission ON mydb.tbl_table TO ‘database_user’@'localhost’;

 

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *