[MariaDB]: Four key client commands

It’s time to get to know four of the most commonly used administration commands for your MariaDB server: mysql,mysqladmin, mysqldump, and mysqlimport.

The examples run these commands on the server rather than over the network. You’ll be prompted for your password; if you want to save a few keystrokes you can record your password in a configuration file, which also solves the problem of how to pass a password if you want to use any of these commands in an unattended script. Doubtless you’re aware of how to pass in your password directly in your commands, but doing this creates a security hole because it records your passwords in your shell history, and in status applications such as ps. It is safer to be prompted, or to use a configuration file.

Let’s look at a few example commands, in which I’ll use my own username, example database names like db1 and db2, and example table names like table1 and table2.

You’ll use mysql a lot for administering your MariaDB server. mysql is an interactive command shell for all database operations: managing users and tables; maintaining databases; manipulating data; administering plugins, transactions, and slave servers; and all other administration tasks. When you are logged in to a mysql session, run help contentsfor detailed help and command options:

$ mysql -u carla -p
MariaDB [(none)]> help contents
You asked for help about help category: "Contents"
For more information, type 'help ', where  is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

mysql keeps a command history for the current session, so you can see and reuse your previous commands. Navigate your history with the up and down arrow keys.

What are some interesting ways to use mysql to get information about your databases? Every table can use a different storage engine, and this command shows which tables use which storage engines:

MariaDB [(none)]> select table_name, engine from information_schema.tables where table_schema = 'db1';
+--------------------+--------+
| TABLE_NAME         | ENGINE |
+--------------------+--------+
| table1             | MyISAM |
| table2             | InnoDB |
| table3             | MyISAM |
| table4             | MyISAM |
| table5             | CSV    |
| table6             | InnoDB |
+--------------------+--------+

What is the current server connection status?

MariaDB [(none)]> status;
--------------
mysql  Ver 15.1 Distrib 5.5.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1

Connection id:          76
Current database:       mysql
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         5.5.38-MariaDB-1~trusty mariadb.org binary distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 2 days 16 hours 5 min 33 sec

Threads: 1  Questions: 329  Slow queries: 0  Opens: 208  Flush tables: 2  Open tables: 41  Queries per second avg: 0.001

This command shows how to view a table index:

MariaDB [(none)]> use db1;
MariaDB [db1]> show index from table4;
+---------+----------+--------------+-------------+------------+
| Table   | Key_name | Seq_in_index | Column_name | Index_type | 
+-------  +------------+------------+-------------++-----------+
| table4  |        0 | PRIMARY      |           1 | BTREE      | 
| table4  |        0 | PRIMARY      |           2 | BTREE      |
+---------+------------+------------+-------------+------------+
2 rows in set (0.00 sec)

mysqladmin

mysqladmin has a lot of overlap with mysql; you can manage tables and databases, start and stop the server, manage users, manage slave servers, check server status, and most other administration asks. Use it in scripted operations like cron jobs, and for running one or two commands without having to log in to the mysql shell. Run it with no options to generate a help page.

This mysqladmin command shows how to change a user’s password, Replace newpassword with the user’s real new password:

$ mysqladmin -u carla -p newpassword

Is the server alive? Use the command’s ping option to find out:

$ mysqladmin -u carla -p ping
mysqld is alive

mysqldump

mysqldump dumps copies of your databases into files that you can then use for backups, setting up slave servers, or making a copy of your production server for testing and experimentation. This example dumps all of your databases into a single file, which in this example is named /db-dumps/dump1:

$ mysqldump -u carla -p --all-databases > /db-dumps/dump1

The output file can be named anything you want. It is a plain-text file that contains all the SQL statements needed for a complete restoration, so all you do to make a restoration, or copy on another machine, is reload the file in your mysqlshell:

MariaDB [mysql]> source /db-dumps/dump1

Typically, you do this kind of restoration when the databases you are restoring do not already exist. Use the--databases option to dump specific databases, or a single database:

$ mysqldump -u carla -p --databases db1 db3  > /db-dumps/db1-3.sql

This example replicates an empty copy of a database by exporting only the schema, but not the data:

$ mysqldump -u carla -p --no-data db2 > /db-dumps/db2-schema

A neat trick is outputting to a delimited format such as CSV (comma-separated values), which is great for importing your data into a spreadsheet. This example exports a single table to the directory table-dumps. Be aware that with the-Toption it is the MariaDB server that writes out the data, so choose a directory that the server can write to.mysqldump automatically creates two files, table4.sql and table4.txt; the former is the same kind of backup we created earlier, while the latter contains the same data in CSV format:

$ mysqldump -u carla -p db2 table4 -T /table-dumps/ --fields-terminated-by=','

mysqlimport

mysqlimport is a fast way to import delimited dump files (which we created in the previous example with mysqldumpusing the -T option) into existing databases and tables. The base names of the data file and the table must be the same, though the extensions can be different, so table4.txt, table4.crazyextension, and table4 are all valid files to import into table4:

$ mysqlimport -u carla -p db2 /table-dumps/table4.txt

You can import multiple tables with a single command:

$ mysqlimport -u carla -p db1 /table-dumps/table3.sql /table-dumps/table5.txt

Or a database:

$ mysqlimport -u carla -p db1 /db-dumps/db1.sql

All of these commands have many more mighty powers and options, so please follow the links to each command’s detailed command reference page to learn more, and also refer to the man pages for each command.

Ask Question