MySQL/MariaDB: most used commands with examples

Please note – this is absolutely not a complete guide about all MySQL commands usage – but just examples of the most commonly used ones.

mysql> at the begin of the line means it’s executed from the MySQL console.

Symbols # or $ means it’s executed from the Linux shell.

UPD 03.03.2019 This post originally was posted at the 2012 year and became the most widely read in this blog – 198737 views at this moment. Today it was translated to English, updated with the new formatting and Table of contents sorting to make it easier to find necessary information.

Contents

General commands

To check MySQL status run:

systemctl status mysql

To connect to a MySQL server running on the same host:

mysql -u username -p

To connect to a MySQL server running on the remote host db1.example.com:

mysql -u username -p -h db1.example.com

Working with databases and tables

Working with databases

To create a database:

mysql> CREATE DATABASE [databasename];

List all databases on the current MySQL server:

mysql> SHOW DATABASES;

Connect to a database to start working with it:

mysql> USE [db name];

To delete database:

mysql> DROP DATABASE [database name];

Working with tables

List all tables in a current database:

mysql> SHOW TABLES;

Display table’s columns types and descriptions:

mysql> DESCRIBE [table name];

Display all table’s content:

mysql> SELECT * FROM [table name];

Display overall table’s lines:

mysql> SELECT COUNT(*) FROM [table name];

Count columns in a table:

mysql> SELECT SUM(*) FROM [table name];

Drop column from a table:

mysql> alter table [table name] DROP INDEX [column name];

Delete the whole table:

mysql> DROP TABLE [table name];

Working with columns

Add a new column to the table:

mysql> ALTER TABLE [table name] ADD COLUMN [new column name] varchar (20);

Change the column’s name:

mysql> ALTER TABLE [table name] CHANGE [old column name] [new column name] varchar (50);

Create a column with an unique name to avoid duplicates:

mysql> ALTER TABLE [table name] ADD UNIQUE ([column name]);

Change the column’s size:

mysql> ALTER TABLE [table name] MODIFY [column name] VARCHAR(3);

Selecting data

Display all table’s content:

mysql> SELECT * FROM [table name];

Display all columns and their content from a table:

mysql> SHOW COLUMNS FROM [table name];

Display all records from a table with the “whatever“:

mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

Find all records with the “Bob” in the name column and “3444444 in the phone_number column:

mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

Find all records withOUT the “Bob” in the name column and “3444444 in thephone_numbercolumn sorting them by the phone_number column:

mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

Display all records starting from the ‘bob‘ and ‘3444444′ phone in a specific table:

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

Display all records starting from the ‘bob‘ and ‘3444444′ phone in a specific table limiting results from 1 to 5:

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

Show all unique records:

mysql> SELECT DISTINCT [column name] FROM [table name];

Display selected records sorting the by decreasing order:

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Using regular expressions

You can use the regex (“REGEXP BINARY”) in your filters.

For example to find all records starting with the “A” using case-independent regex search:

mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";

Importing and exporting data

To load data from a CSV file into a table:

mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' (field1,field2,field3);

MySQL users, passwords

Adding a new user: connect to a server as root, connect to a database, adding a user, updating privileges:

mysql -u root -p

mysql> USE mysql;

mysql> INSERT INTO user (Host,User,Password) VALUES('%','username', PASSWORD('password'));

mysql> flush privileges;

To change a user’s password on a remote host db1.example.org using the mysqladmin tool:

mysqladmin -u username -h db1.example.org -p password 'new-password'

To change user’s password from the MySQL CLI:

mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

mysql> flush privileges;

MySQL root reset

To reset MySQL root’s password: stop the server, start it without privileges table, log in as root, set new password, exit and start MySQL in the normal way:

systemctl stop mysql

mysqld_safe --skip-grant-tables &

mysql -u root

mysql> use mysql;

mysql> update user set password=PASSWORD("newrootpassword") where User='root';

mysql> flush privileges;

mysql> quit

systemctl start mysql

To update root‘s password: using mysqladmin:

mysqladmin -u root -p oldpassword newpassword

Grant permissions for remote login from the localhost host with the passwd password:

mysql -u root -p

mysql> use mysql;

mysql> grant usage on *.* to bob@localhost identified by 'passwd';

mysql> flush privileges;

Grant specific permissions for user:

mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;

Or just grant everything:

mysql> grant all privileges on databasename.* to username@localhost;

mysql> flush privileges;

Setting specific permissions for a user per specific table:

mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

Do not forget update privileges:

mysql> flush privileges;

Backup, restore

Creating backups

Create a backup (dump) from all databases into the alldatabases.sql file:

mysqldump -u root -p password --opt >/tmp/alldatabases.sql

Create a backup (dump) from a database with the databasename name into the databasename.sql file:

mysqldump -u username -p password --databases databasename >/tmp/databasename.sql

Create one table’s backup into the databasename.tablename.sql file:

mysqldump -c -u username -p password databasename tablename > /tmp/databasename.tablename.sql

Restore from a backup

To restore database(s) or table(s) from a backup:

mysql -u username -p password databasename < /tmp/databasename.sql

Tables creation examples

italic: columns name;
In CAPITAL letters: types and attributes for columns;
in (brackets) – column’s values.

Example 1:

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35), suffix VARCHAR(3), officeid VARCHAR(10), userid VARCHAR(15), username VARCHAR(8), email VARCHAR(35), phone VARCHAR(25), groups VARCHAR(15), datestamp DATE, timestamp TIME, pgpemail VARCHAR(255));

Example 2:

mysql> create table [table name] (personid INT(50) NOT NULL AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(35), middlename VARCHAR(50), lastname VARCHAR(50) default 'bato');

More examples – in the Простая работа с MySQL в простых примерах #1 — работа с базами, создание таблиц and Простая работа с MySQL в простых примерах #2 — наполнение таблиц.