This ebook is not about SQL as such, but about performing some
typical task as a database administrator (instead of as a developer).
Most of the materials below uses the syntax of MySQL 5, which ships
with Ubuntu 18.04 LTS. Some has been update to cover MySQL 8, which
ships with Ubuntu 20.04 LTS and MariaDB. which ships with RHEL8.
Those sections that have been updated are marked [My5],
[My8]
and [Ma10].
Essential commands
On Ubuntu, the default security model [My5,My8] is to
only allow a superuser logged in on loclhost to log on as the database
administrator. This does not require a password. To do this, do the
following:
$ sudo mysql
Enter password: *****
…
mysql> quit
bye
The RHEL8 configuration [Ma10] requires a user name and a password:
$ mysql -u root -p
Enter password: *****
…
mysql> quit
bye
To log on on another host (by defult, Ubuntu's security model disallow this):
$ mysql -u root -p -h example.com
Enter password: *****
…
mysql> quit
bye
Commands that can be used by the database root to see what
databases exists, what tables a database have, who owns the databases,
what user names exist (with hashed passwords), current MySQL version,
current date and time:
SHOW databases;
SHOW database.tables;
SELECT db, user FROM mysql.db;
SELECT user, host, authentication_string FROM mysql.user;
SELECT version();
SELECT current_date;
SELECT now();
To set a database as current:
use database
To check for rogue persistent connections:
show processlist;
show full processlist;
Manage users
To list users:
SELECT db, user FROM mysql.db;
SELECT user, host, authentication_string FROM mysql.user;
The follow sequence of commands will create a new database user [My5,My8,Ma10]:
mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'username'@'localhost';
mysql> FLUSH PRIVILEGES;
Start mysqld_safe as a background process with the --skip-grant-tables and --skip-networking options so that it will not prompt for a password.
Connect to the database as the root user.
Set a new root password.
Stop and restart the database server.
Test the new passord.
# systemctl stop mariadb.service
# mysqld_safe --skip-grant-tables --skip-networking &
(hit return to get the command line prompt)
# mysql -u root
MariaDB [(none)]>
You're now interacting with the SQL monitor as the root user. Now you can set up a new root password.
***> use mysql
***> update user set password=PASSWORD("NewPass") where User='root';
***> flush privileges;
***> quit
A new password is now set. Stop and restart the MySQL server.
However, most times the service will not restart. If the cause is
another mysql process running. Use the follwing to verify
that this is the cause:
# journalctl -xe
…
--
-- Unit mariadb.service has begun starting up.
…: Socket file /var/lib/mysql/mysql.sock exists.
…: Is another MySQL daemon already running with the same unix socket?
…: mariadb.service: control process exited, code=exited status=1
…: Failed to start MariaDB database server.
-- Subject: Unit mariadb.service has failed
…
To kill it, first find its PID, kill the process and use mysqladmin to shut down.
Note that this also changes the root password into whatever the root password on the
old server used to be.
Back up a single database
To dump a database name mydb:
$ mysqldump -u root -p mydb > DUMPmydb.sql
To restore:
$ mysql -u root -p < DUMPmydb.sql
Rename database
There is no longer a built-in command to rename a data database (it
was removed in MySQL 5.1.23). Instead, you can use the
DUMP command to export the content of an old database
and import it into a new one.
mysql> SELECT db,user,host,select_priv FROM mysql.db;
mysql> RENAME USER 'olduser'@'localhost' TO 'newuser'@'localhost';
mysql> DROP USER 'user'@'localhost';
Dropping a user will clean up all the entries in the
various mysql.* tables that deal with user authentication,
such as mysql.db.
Delete database
To delete a database permanently, use the following pair of commands as root:
mysql> REVOKE ALL PRIVILEGES ON database.* FROM 'user'@'localhost';
mysql> DROP DATABASE database;
The DROP DATABASE-command will delete the database
files, but the database will not be purged from the mysql.db
table if there still is users associated with it. This why both
commands should be used.
To check that a database is really gone, do:
mysql> USE mysql;
mysql> SELECT db,user FROM db;
If you just want to drop all tables in order to empty the database,
you may use iterate over all tables using
“DROP TABLE tablename”, or you may drop and then
recreate the database:
mysql> DROP DATABASE database;
mysql> CREATE DATABASE database;