====== MariaDB / MySQL command line ====== ===== LIST / SHOW ===== ==== Databases ===== List all databases [root@rh73 ~]# mysqlshow +--------------------+ | Databases | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ Or [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) Connect to a database: [root@rh73 ~]# mysql -u root MariaDB [(none)]> use mysql ; Database changed MariaDB [mysql]> List the creation commands for a table: MariaDB [mysql]> show create table ; ==== Tables ===== Show tables of a database: 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) Show table structure 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) 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 | Show table content 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) DB status 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 ===== CREATE ===== ==== Databases ===== Create a new database: [root@rh73 ~]# mysql -u root CREATE DATABASE IF NOT EXISTS MariaDB [mysql]> CREATE DATABASE IF NOT EXISTS TOTODB; ==== Tables ===== Show tables of a database: 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) ===== ALTER / CHANGE ===== ==== Column ===== Add a column in a table: ALTER TABLE ADD ALTER TABLE utilisateur ADD adresse_rue VARCHAR(255) Suppress a column in a table: ALTER TABLE DROP COLUMN Modify a column in a table: ALTER TABLE MODIFY Rename a column in a table: ALTER TABLE CHANGE ===== INSERT DATA ===== 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) ===== DROP / DELETE ===== ==== Databases ===== Delete a database: [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 ==== Tables ===== Delete a table in the database TUTORIALS: [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) ===== query content of tables ===== Export to a file [user@lnx01 ~]$ mysql -user -pass -e "select cols from table where cols not null" > /tmp/output ===== BACKUP / RESTORE ===== To get the whole database structure as a set of CREATE TABLE statements, use mysqldump: [root@rh73 ~]# mysqldump -u -p --compact --no-data > /dump/mysql__struct.dmp To get the whole database structure and data: [root@rh73 ~]# mysqldump -u -p --compact > /dump/mysql_.dmp To restore your database: [root@rh73 ~]# mysql -u -p < db_backup.dump If the dump is of a single database you may have to add a line at the top of the file: USE ; Or [root@rh73 ~]# mysql -p -u[user] [database] < db_backup.dump