CS469/569 - Linux and Unix Administration and Networking

Spring 2022

SQL

SQL stands for Structured Query Language and is a data query language to Create/ Read / Update or Delete (CRUD) data, structured around tables that are composed of rows sub-divided into columns of various typed data, almost exactly analogous to a spreadsheet.

SQL databases are often used as back-end data stores for many services, such as web-services, so an administrator often needs to be able to deal with the basics of administrating an SQL server, some of the most common of which are MySQL / MariaDB.

MySQL/MariaDB administration:

MySQL or MariaDB (which is a fork derived from the original MySQL) are common free database servers that an administrator might have to deal with. Learning to manage the basics with MySQL/MariaDB often translates well to other database servers such as PostgreSQL, or other SQL based database servers.

Installing the DB:
# mysql_install_db --user=mysql

Sets up the initial database.
User set the "home" directory for the database system. The MySQL daemon runs as the given user (typically 'mysql'.)

# chmod 755 /etc/rc.d/rc.mysqld

Makes mysqld start on boot.
Edit the rc file to enable networking (outside clients.), generally considered a bad idea. Use the firewall to restrict connections to trusted hosts.

Start the database, then run mysql_secure_installation to complete the installation. Be sure to put a password on the "root" account and disable guest accounts for a secure installation. It is the case that the systems super- user no longer needs a password to access the MariaDB root account regardless of whether there is one or not, though normal users will be required to enter it (probably to prevent an administrator from being locked out of the database from forgetting / losing the password.)

~/.my.cnf MySQL client configuration file:

[client]
user=<username>
password=<password>
database=<database>

Defines the optional user, password and/or database that the mysql client command uses to connect to by default.

# chmod 600 ~/.my.cnf

Makes sure that the config file is not readable by anyone other than the user to whom it belongs.

> mysql [-u user] [-h host] [-p] [database]

The MySQL client command. Allows one to connect to the MySQL database server from the command line and issue MySQL

Option

-u user Selects the user to connect to the database as.

-h host Selects host (localhost by default) to connect to.

-p Ask for password.

database Database to use upon connecting.

Common MySQL/MariaDB administration commands:

Create / delete databases:

create database databasename;

Creates a database, no associated user initially.

use database;

Use the specified database. (ex: mysql)

drop database database;
drop user user@localhost;

Deletes database or users.

Managing users:

create user username@hostname [identified by 'password'];

Creates a user, w/o an associated database or databases.
'%' for hostname means any host, normally 'localhost'. The hostname represents the host the user is allowed to connect from.

set password [for user] = password('password')

Set or change the password (default current logged in user.) Requires "root" (the MySQL administrator) privilege to use the for user syntax to change some other users password.

Grants:

Grants are how permissions are applied to databases for specific users. A user is identified by their username + hostname combination. The hostname is the host they connect from. Grants can be applied at the global, database, table, column and routine level.

Syntax:

grant [all privileges | SQL commands ] on database.table to username@hostname [identified by 'password'] [with grant option]

Grant specifier Privilege:

*.* Global (all databases)

database.* All tables in a specific database

database.table A specific table in a specific database

grant all privileges on database.* to username@localhost identified by 'password' with grant option;

Grants user (mostly) full access to database.tables, creating the user and setting the password, allowing the user to issue grants for others.

grant create routine on database.* to user@localhost;
grant alter routine on database.* to user@localhost;
grant execute on database.* to user@localhost;

Additional example grants not typically assigned by default.

grant select,lock on database.* to username@localhost;

Allows a user to use only specific SQL commands on the given database. Above gives effectively read-only access to a database.

show grants for user@localhost;

Show the grants given to a particular user.

flush privileges;

Updates permissions (tells running server to re-read mysql tables.) Important to do after adding/removing users and updating grants.

To change user`@localhost` to user`@'%'`:

use mysql;
update user set host='%' where user='user' and host='localhost';

Backing up MySQL databases:

# mysqldump --all-databases | gzip > /some/place/all-dbs.gz

Dumps all databases to standard output as SQL commands

> mysqldump db_name > backup.sql

Restoring:

> mysql db_name < backup.sql

Integrity checking:

# mysqlcheck [--all-databases | db_name]

Checks, repairs, optimizes or analyzes tables.
Should probably be run after upgrades.

Misc:

Get list of all databases.

echo "show databases;' | mysql --batch
mysql -B -e 'show databases;'
mysql -r -s -e 'show databases;'

Same as the above.

Backup all databases individually:

#!/bin/bash

db_dir="/some/path";

for db in $(mysql -B -e 'show databases;'); do
  echo "mysqldump $db | gzip > $db_dir/$db.sql";
done