wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== MariaDB / MySQL command line ======

===== LIST / SHOW =====

==== Databases =====

List all databases
<cli prompt='#'>
[root@rh73 ~]# mysqlshow 
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
</cli>

Or
<cli prompt='#'>
[root@rh73 ~]# mysql -u root -p mysql
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 10.0.29-MariaDB-0ubuntu0.16.10.1 Ubuntu 16.10

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
</cli>

Connect to a database:
<cli prompt='#'>
[root@rh73 ~]# mysql -u root
MariaDB [(none)]> use mysql ;
Database changed
MariaDB [mysql]> 
</cli>

List the creation commands for a table:
<cli prompt='>'>
MariaDB [mysql]> show create table <table>;
</cli>

==== Tables =====

Show tables of a database:
<cli prompt='>'>
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
.....
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
30 rows in set (0.00 sec)
</cli>

Show table structure
<cli prompt='>'>
MariaDB [mysql]> DESCRIBE user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(80)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
...
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
...
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
...
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11)                           | NO   |     | 0       |       |
| plugin                 | char(64)                          | NO   |     |         |       |
| authentication_string  | text                              | NO   |     | NULL    |       |
| password_expired       | enum('N','Y')                     | NO   |     | N       |       |
| is_role                | enum('N','Y')                     | NO   |     | N       |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
44 rows in set (0.00 sec)
</cli>

<cli prompt='>'>
MariaDB [information_schema]> select TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME from COLUMNS where TABLE_SCHEMA = 'storage' order by TABLE_NAME;
+--------------+------------------+----------------+-------------+-----------+--------------------------+-------------------+
| TABLE_NAME   | COLUMN_NAME      | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | COLLATION_NAME    |
+--------------+------------------+----------------+-------------+-----------+--------------------------+-------------------+
| svc_copy     | date             | NULL           | NO          | date      |                     NULL | NULL              |
| svc_copy     | vdisk_id         | NULL           | NO          | int       |                     NULL | NULL              |
| svc_copy     | vdisk_name       | NULL           | YES         | varchar   |                     48 | latin1_general_ci |
| svc_copy     | copy_id          | NULL           | NO          | tinyint   |                     NULL | NULL              |
| svc_copy     | status           | NULL           | NO          | varchar   |                     16 | latin1_general_ci |
| svc_copy     | sync             | NULL           | NO          | enum      |                     3 | latin1_general_ci |

</cli>

Show table content
<cli prompt='>'>
mysql> select * from user;
+-----------+------+----------+-------------+-------------+-
| Host      | User | Password | Select_priv | Insert_priv | 
+-----------+------+----------+-------------+-------------+
| localhost | root |          | Y           | Y           | Y           | 
| labotest     | root |          | Y           | Y           | Y           | 
| 127.0.0.1 | root |          | Y           | Y           | Y           | 
| localhost |      |          | N           | N           | N           |
| labotest     |      |          | N           | N           | N           | 
+-----------+------+----------+-------------+-------------+-------------+
5 rows in set (0.00 sec)
</cli>

DB status
<cli prompt='>'>
MariaDB [mysql]> status;
--------------
mysql  Ver 15.1 Distrib 10.0.29-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:		42
Current database:	mysql
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.0.29-MariaDB-0ubuntu0.16.10.1 Ubuntu 16.10
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			4 hours 14 min 42 sec

Threads: 1  Questions: 233  Slow queries: 0  Opens: 19  Flush tables: 1  Open tables: 82  Queries per second avg: 0.015
</cli>

===== CREATE =====

==== Databases =====

Create a new database:
<cli prompt='#'>
[root@rh73 ~]# mysql -u root
CREATE DATABASE IF NOT EXISTS 
MariaDB [mysql]> CREATE DATABASE IF NOT EXISTS TOTODB;
</cli>

==== Tables =====

Show tables of a database:
<cli prompt='>'>
MariaDB [(none)]> use toto;
Database changed
MariaDB [toto]> CREATE TABLE IF NOT EXISTS tasks (
    ->   task_id INT(11) NOT NULL AUTO_INCREMENT,
    ->   subject VARCHAR(45) DEFAULT NULL,
    ->   start_date DATE DEFAULT NULL,
    ->   end_date DATE DEFAULT NULL,
    ->   description VARCHAR(200) DEFAULT NULL,
    ->   PRIMARY KEY (task_id)
    -> );
Query OK, 0 rows affected (0.06 sec)

MariaDB [toto]> describe tasks;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| task_id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| subject     | varchar(45)  | YES  |     | NULL    |                |
| start_date  | date         | YES  |     | NULL    |                |
| end_date    | date         | YES  |     | NULL    |                |
| description | varchar(200) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
</cli>

===== ALTER / CHANGE =====

==== Column =====

Add a column in a table:
  ALTER TABLE <table_name> ADD <column_name> <type>
  
  ALTER TABLE utilisateur ADD adresse_rue VARCHAR(255)
  
Suppress a column in a table:
  ALTER TABLE <table_name> DROP COLUMN <column_name>

Modify a column in a table: 
  ALTER TABLE <table_name> MODIFY <column_name> <type>

Rename a column in a table: 
  ALTER TABLE <table_name> CHANGE <old_column_name> <new_column_name> <type>

===== INSERT DATA =====

<cli prompt='#'>
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> INSERT INTO tutorials_tbl 
     ->(tutorial_title, tutorial_author, submission_date)
     ->VALUES
     ->("Learn PHP", "John Poul", NOW());
Query OK, 1 row affected (0.01 sec)
</cli>

===== DROP / DELETE =====

==== Databases =====

Delete a database:
<cli prompt='#'>
[root@rh73 ~]# mysqladmin -u root -p drop TUTORIALS
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'TUTORIALS' database [y/N] y
Database "TUTORIALS" dropped
</cli>

==== Tables =====

Delete a table in the database TUTORIALS:
<cli prompt='#'>
[root@rh73 ~]# mysql -u root -p
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> DROP TABLE tutorials_tbl
Query OK, 0 rows affected (0.8 sec)
</cli>

===== query content of tables =====

Export to a file
<cli prompt='$'>
[user@lnx01 ~]$ mysql -user -pass -e "select cols from table where cols not null" > /tmp/output
</cli>

===== BACKUP / RESTORE =====

To get the whole database structure as a set of CREATE TABLE statements, use mysqldump:
<cli prompt='#'>
[root@rh73 ~]# mysqldump -u<root> -p<password> <dbname> --compact --no-data > /dump/mysql_<dbname>_struct.dmp
</cli>

To get the whole database structure and data:
<cli prompt='#'>
[root@rh73 ~]# mysqldump -u<root> -p<password> <dbname> --compact > /dump/mysql_<dbname>.dmp
</cli>

To restore your database:
<cli prompt='#'>
[root@rh73 ~]# mysql -u <user> -p < db_backup.dump
</cli>
If the dump is of a single database you may have to add a line at the top of the file:
  USE <database-name-here>;
  
Or 
<cli prompt='#'>
[root@rh73 ~]# mysql -p -u[user] [database] < db_backup.dump
</cli>
wiki

User Tools

Site Tools

Page Tools
