Database Systems

These notes address database applications pertaining to mySQL systems but particularly to the server and client utilities of the MariaDB project, which Fedora favors.

mysql command

Package mariadb provides several commands for querying an SQL server. The most general is mysql, which opens a CLI for interacting with an SQL server:

-> mysql --user root --password
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
...
MariaDB [(none)]> ...
...
MariaDB [(none)]> quit
Bye

mysql preserves your command history in file .mysql_history, but you can specify another file by setting MYSQL_HISTFILE accordingly. See the man page for some privacy and security concerns about this history and what to do about them.

The MariaDB package includes a few other commands, including mysqldump to backup or export a database and mysqlimport to import data from a file.

MariaDB/mySQL server

Fedora uses the SQL server from the MariaDB project (package mariadb-server) instead of the server from Oracle's mySQL project. To install and start the server:

-> yum install mariadb mariadb-server
...
-> systemctl start mysqld.service

Then run mysql_secure_installation to tighten up the fresh installation:

-> mysql_secure_installation
...

This utility presents a short sequence of annotated prompts offering to set a password for the root accounts, restrict root connections to the local host, remove anonymous access, and delete the test database. The server has its own notions of user accounts; its root and other accounts are independent of the host system's accounts.

Here's the clean slate:

 -> mysql --user root --password --database mysql
Enter password: 
...
MariaDB [mysql]> select user,host,password from user;
+------+-----------+-------------------------------------------+
| User | Host      | Password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *550032BCA2E149816690E1B12A94B293B31A9749 |
| root | 127.0.0.1 | *550032BCA2E149816690E1B12A94B293B31A9749 |
| root | ::1       | *550032BCA2E149816690E1B12A94B293B31A9749 |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

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

MariaDB [mysql]> quit;
Bye

The base configuration file is /etc/my.cnf. It incorporates additional configuration files (under /etc/my.cnf.d). Database files reside under /var/lib/mysql unless /etc/my.cnf relocates them.

To see the status of the server:

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

Connection id:		2
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		5.5.38-MariaDB MariaDB Server
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			9 min 32 sec

Threads: 1  Questions: 19  Slow queries: 0  Opens: 4  Flush tables: 2  Open tables: 30  Queries per second avg: 0.033
--------------

To see what storage engines the server supports:

MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+

Or, you can be more selective:

MariaDB [(none)]> select engine,support,comment from information_schema.engines;
...

You can use mysqladmin to query and administer a running server. For example:

-> mysqladmin --user root --password status
Enter password: 
Uptime: 1557  Threads: 1  Questions: 25  Slow queries: 0  Opens: 0  Flush tables: 2  Open tables: 26  Queries per second avg: 0.016

To see what directories and files the server looks to:

-> mysqladmin -u root -p variables | grep dir | cut -d\| -f 2,3 --out = | perl -pe 's/ +/ /g'
Enter password: 
 aria_sync_log_dir = NEWFILE 
 basedir = /usr 
 binlog_direct_non_transactional_updates = OFF 
 character_sets_dir = /usr/share/mysql/charsets/ 
 datadir = /var/lib/mysql/ 
 ignore_db_dirs = 
 innodb_data_home_dir = 
 innodb_log_group_home_dir = ./ 
 innodb_max_dirty_pages_pct = 75 
 lc_messages_dir = 
 plugin_dir = /usr/lib64/mysql/plugin/ 
 slave_load_tmpdir = /var/tmp 
 tmpdir = /var/tmp 

phpMyAdmin

phpMyAdmin is a browser-based GUI for interacting with a mySQL server. It offers an alternative to the command-line interface of mysql.

To use phpMyAdmin to access a mySQL server running on the local host, open URL http://localhost/phpMyAdmin from your favorite web browser; lower-case "phpmyadmin" works too. [To be verified: You can also use phpMyAdmin to access a remote mySQL server provided that the server allows connections from your IP address (and provided that the remote host offers phpMyAdmin). To access the mySQL server on host example.com, say, open URL http://example.com/phpMyAdmin.] You will be asked to enter a username and password that the underlying mySQL server accepts.

Once logged in to the server, note the small icons under the phpMyAdmin logo at the top of the database-navigation panel to the left (unless minimized), wherein lies the log-out button, among others.

To use phpMyAdmin locally, install the eponymous package and tell Apache to reload its configuration files:

-> yum install phpmyadmin
-> systemctl reload httpd.service

(This installs /etc/httpd/conf.d/phpMyAdmin.conf for Apache. phpMyAdmin assumes that Apache is equipped to run PHP.)