2019 Webinar: Oracle to MariaDB: Pragmatic Migration Strategies
It’s time to get to know four of the most commonly used administration commands for your MariaDB server:
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.
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 contents for 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 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.
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 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
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 is a fast way to import delimited dump files (which we created in the previous example with
mysqldump using 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.
2019 Webinar: Oracle to MariaDB: Pragmatic Migration Strategies
Download the latest MariaDB products today