Day 6 - Database Services: From Installing MySQL/MariaDB to Backup and Recovery

📌 Key Takeaways

• A "user" is not a single name but a username@host pair. 'web'@'localhost' and 'web'@'%' are different accounts even with the same name, and their privileges are separate.
• Whether a connection succeeds comes down to that pair, plus bind-address + firewall port 3306.
• The package is mariadb-server, and the service name is mariadb. If you only start and forget enable, it will not come up after a reboot.
• -p is password, -P is Port. Backup is mysqldump ... > file, restore is mysql ... < file — the arrow direction is the key.
• A single dump given only a DB name has no CREATE DATABASE. You must create the DB before restoring (--databases and --all-databases include it).

MySQL and MariaDB — Why Are There Two Names?

MySQL was a product of Sweden's MySQL AB, passed through Sun, and went to Oracle in 2010. When a commercial DB company acquired the flagship open-source DB, the original developer Monty Widenius forked it, and the result is MariaDB (named after his daughter). That is why the default distribution DB of the RHEL family was switched to MariaDB. The client commands (mysql, mysqldump, mysqladmin) and the basic SQL are compatible, so what follows applies to both.

Installation and Starting the Daemon

• Depending on the distribution family, package names and service names diverge, and this difference comes up as short-answer questions.

yum install mariadb-server mariadb   # RHEL/CentOS/Rocky (server + client)
apt install mariadb-server           # Debian/Ubuntu (or mysql-server)
 
systemctl start mariadb        # Start
systemctl enable mariadb       # Register auto-start at boot
systemctl status mariadb       # Check status
systemctl restart mariadb      # Restart after a config change

⚠️ Trap: The package name is mariadb-server but the service name is mariadb. A choice that uses the package name as-is for the service name, like systemctl start mariadb-server, appears as a wrong answer. Other traps: installing the MySQL community package makes the service name mysqld, and if you only start and forget enable, the DB does not come up after a reboot.

mysql_secure_installation — The Essential Security Step Right After Installation

• A freshly installed DB leaves a root with no password, anonymous users, and a test DB wide open.
• The interactive script that cleans these up in one go is mysql_secure_installation.

The DB's root is a completely separate account from the Linux system's root. Only the name is the same; the credentials and privilege system live separately inside the DB, and changing the Linux root password leaves the DB root password unchanged. The "command that sets the DB administrator account's password" is mysql_secure_installation or mysqladmin -u root password 'new-password'.

mysqladmin -u root password 'NewP@ssw0rd'   # Set the root password non-interactively
mysqladmin -u root -p ping                  # Check the daemon is alive

Connecting and Basic SQL

mysql -u root -p                          # Local connection (password entered at the prompt)
mysql -u web -p webdb                     # Select webdb at the same time as connecting
mysql -h 192.168.1.50 -P 3306 -u web -p   # Connect to a remote server
mysql -u root -p -e "SHOW DATABASES;"     # Run one line of SQL and exit

⚠️ Trap: -p (lowercase) is the password and -P (uppercase) is the port. There are choices that swap the cases. When attaching the password after -p, you must write it with no space, as -pMyPass (with a space, MyPass is interpreted as the DB name). A password on the command line is left in ps and the shell history, so in practice you use just -p and type it at the prompt.

• The basic SQL used after connecting is below. An SQL statement must end with a semicolon (;) to run.

-- Query
SHOW DATABASES;                 -- List of databases
USE webdb;                      -- Select the database to use
SHOW TABLES;                    -- List of tables in the current DB
DESC members;                   -- Check table structure (same as DESCRIBE)
 
-- Create
CREATE DATABASE webdb DEFAULT CHARACTER SET utf8mb4;
CREATE TABLE members (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    name   VARCHAR(30) NOT NULL,
    email  VARCHAR(60),
    joined DATE
);
 
-- Manipulate
INSERT INTO members (name, email, joined) VALUES ('John Smith', 'john@ex.com', '2026-08-01');
SELECT * FROM members WHERE name = 'John Smith';
UPDATE members SET email = 'new@ex.com' WHERE id = 1;
DELETE FROM members WHERE id = 1;
 
-- Delete / exit
DROP TABLE members;   DROP DATABASE webdb;
EXIT;                           -- QUIT; and \q are the same

DELETE is DML, so it picks rows with WHERE and can be undone via a transaction, whereas TRUNCATE is DDL, empties the whole table, also resets AUTO_INCREMENT, and generally cannot be rolled back.

Users and Privileges — The 'user'@'host' Pair

-- Create accounts
CREATE USER 'web'@'localhost' IDENTIFIED BY 'WebP@ss1';    -- Local only
CREATE USER 'web'@'%' IDENTIFIED BY 'WebP@ss1';            -- All hosts
CREATE USER 'web'@'192.168.1.%' IDENTIFIED BY 'WebP@ss1';  -- Specific range only

• Privileges are given with GRANT and taken back with REVOKE.

GRANT ALL PRIVILEGES ON webdb.* TO 'web'@'localhost';            -- Full privileges
GRANT SELECT, INSERT, UPDATE ON webdb.* TO 'app'@'192.168.1.%';  -- Limited grant
REVOKE INSERT, UPDATE ON webdb.* FROM 'app'@'192.168.1.%';       -- Revoke
 
SHOW GRANTS FOR 'web'@'localhost';          -- Check privileges
SELECT user, host FROM mysql.user;          -- Account list
ALTER USER 'web'@'localhost' IDENTIFIED BY 'NewP@ss2';   -- Change password
DROP USER 'web'@'localhost';                -- Delete account
FLUSH PRIVILEGES;                           -- Reload privilege tables

• There are three target notations after ON.
  • *.* : all tables of all DBs
  • webdb.* : all tables of webdb
  • webdb.members : just that one table
• The practical principle is to give minimum privileges only on the DBs needed.

FLUSH PRIVILEGES is the command that re-reads the in-memory privilege information when you have touched privilege tables such as mysql.user directly with UPDATE/INSERT. If you went through the proper path with GRANT, REVOKE, or CREATE USER, the server reflects it automatically, so in principle it is not needed. It can be the answer to a question asking for "the command that applies privilege changes immediately".

'web'@'localhost' and 'web'@'%' are different accounts even with the same name. If you grant privileges only to the localhost account and connect remotely, Access denied appears. As a side note, -h localhost connects via the Unix socket (/var/lib/mysql/mysql.sock) while -h 127.0.0.1 connects via TCP port 3306, so the two can give different results.

Configuration File — my.cnf and bind-address

• Server behavior is controlled by my.cnf (the option file). The exam point is that the location differs by family.

• The file is read in units of sections enclosed in square brackets.
• Server daemon settings go in the [mysqld] section, and mysql client settings go in the [client] section.

[mysqld]
port         = 3306                        # Service port (default 3306/TCP)
bind-address = 0.0.0.0                     # Listening interface
datadir      = /var/lib/mysql              # Path where data files are stored
socket       = /var/lib/mysql/mysql.sock   # Socket file for local connections
log_error    = /var/log/mariadb/mariadb.log
max_connections      = 200                 # Upper limit of concurrent connections
character-set-server = utf8mb4             # Server default character set

The Debian family has a default of bind-address = 127.0.0.1, which blocks remote connections. If you created the account as 'user'@'%' and even opened the firewall but it still fails, nine times out of ten this is it. Conversely, if you open it to 0.0.0.0 and do not lock the firewall, the DB is exposed as-is. One more thing: after editing the configuration you must systemctl restart for it to take effect.

# Ask the server directly for the values actually in effect
mysql -u root -p -e "SHOW VARIABLES LIKE 'bind_address';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'datadir';"

Backup and Restore — mysqldump

• The standard tool for DB backup is mysqldump.
• As the name says, it is a tool that dumps the contents of a database as a text file of SQL statements (CREATE TABLE, INSERT ...).

mysqldump -u root -p webdb > /backup/webdb.sql             # Single DB
mysqldump -u root -p webdb members > /backup/members.sql   # Specific table only
mysqldump -u root -p --databases webdb blogdb > /backup/two.sql   # Multiple DBs
mysqldump -u root -p --all-databases > /backup/all.sql     # Entire server

• Restore is not a separate command — it is feeding the dumped SQL file into the mysql client with input redirection.

mysql -u root -p -e "CREATE DATABASE webdb;"     # Create the target DB first, then
mysql -u root -p webdb < /backup/webdb.sql       # Feed in the dump to restore
 
mysql -u root -p < /backup/all.sql               # An --all-databases dump goes without a DB name

Backup is > (output redirection), restore is < (input redirection), and if you use the wrong direction the backup file gets emptied. One more decisive point — a single dump given only a DB name does not contain a CREATE DATABASE statement. You must create the target DB before restoring, and if you do not, you get an Unknown database error. A dump made with --databases or --all-databases includes DB creation statements, so you feed it in as-is without a DB name.

mysqldump is a logical backup whose output is SQL text, so it is easy to move to another server. To take a consistent backup without stopping the service, use --single-transaction on InnoDB tables. On the other hand, a physical backup that bundles /var/lib/mysql with tar must be done only after stopping the daemon — copying while it is running yields broken files.

Logs and Troubleshooting

• When the DB does not come up or you cannot connect, look at the error log first.
• Paths differ by distribution and package, so it is more reliable to check the setting value directly.

mysql -u root -p -e "SHOW VARIABLES LIKE 'log_error';"   # Check the path directly
tail -f /var/log/mariadb/mariadb.log     # MariaDB on the RHEL family
tail -f /var/log/mysqld.log              # MySQL on the RHEL family
tail -f /var/log/mysql/error.log         # Debian family
journalctl -u mariadb -n 50              # When the daemon does not come up at all

• Remote connection failures have a fixed set of causes. Narrowing down in the order below catches most of them.

ss -tlnp | grep 3306                                # Is the port open and listening
firewall-cmd --permanent --add-port=3306/tcp && firewall-cmd --reload

Telling the two connection-failure messages apart cuts diagnosis in half. Access denied for user ... means it reached the server but was rejected on account, password, or privileges (an inside-the-DB problem), and Can't connect to MySQL server ... means the connection itself did not reach (an outside problem such as daemon not running, firewall, or bind-address).

Memory Tips — Sorting Out the Confusing Pairs

Try It Yourself — A Full Lap from Installation to Backup

# 1. Install, start, initial security
yum install -y mariadb-server mariadb
systemctl start mariadb && systemctl enable mariadb
mysql_secure_installation
 
# 2. Create a DB and an account
mysql -u root -p -e "CREATE DATABASE webdb DEFAULT CHARACTER SET utf8mb4;
  CREATE USER 'web'@'localhost' IDENTIFIED BY 'WebP@ss1';
  GRANT ALL PRIVILEGES ON webdb.* TO 'web'@'localhost';
  SHOW GRANTS FOR 'web'@'localhost';"
 
# 3. Verify by connecting with the new account and creating a table
mysql -u web -p webdb -e "CREATE TABLE members (id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL, joined DATE);
  INSERT INTO members VALUES (1,'John Smith','2026-08-01'); SELECT * FROM members;"
 
# 4. Backup → delete → restore drill
mysqldump -u root -p webdb > /backup/webdb.sql
mysql -u root -p -e "DROP DATABASE webdb;"
mysql -u root -p -e "CREATE DATABASE webdb;"
mysql -u root -p webdb < /backup/webdb.sql
mysql -u root -p webdb -e "SELECT * FROM members;"

• If you do step 4's "backup → DROP → restore" by hand even once, the fact that a single dump has no CREATE DATABASE stays with you.
• This feel turns directly into points in the practical exam's hands-on tasks.

In the next session we wrap up with a comprehensive review that ties together this week's services (DNS, DHCP/NTP, Apache, Nginx, DB).

📖 Glossary

• MariaDB : The branch forked by the original developer after Oracle acquired MySQL. Commands and SQL are compatible, and it is the default DB of the RHEL family.
• DB root : A database-only administrator account completely separate from the Linux system's root.
• mysql_secure_installation : An interactive script that cleans up the root password, anonymous accounts, remote root, and test DB in one go right after installation.
• -p vs -P : Lowercase is password (prompt), uppercase is Port. When attaching the password after -p, write it with no space.
• DDL / DML / DCL : Definition language (CREATE, ALTER, DROP) / manipulation language (SELECT, INSERT, UPDATE, DELETE) / control language (GRANT, REVOKE, COMMIT).
• DELETE vs TRUNCATE : DML that picks rows with WHERE and can be undone / DDL that empties the whole table and even resets AUTO_INCREMENT.
• 'user'@'host' : The identifying unit of an account. Even with the same name, a different host means a different account.
• % (wildcard) : "From anywhere" in the host slot. A range can also be specified, as in 'web'@'192.168.1.%'.
• GRANT target notation : *.* (everything) · webdb.* (all of that DB) · webdb.members (one table).
• FLUSH PRIVILEGES : A command that makes the server re-read in-memory privilege info when the privilege tables were touched directly.
• bind-address : The interface to accept connections on. 127.0.0.1 means local only, 0.0.0.0 means all interfaces.
• datadir : The path where the actual database files accumulate. Usually /var/lib/mysql.
• Logical vs physical backup : mysqldump, which dumps SQL text and is easy to move / bundling the whole data directory (daemon stop required).
• --single-transaction : An option for taking a consistent backup on InnoDB without stopping the service.
• Access denied vs Can't connect : Reached the server but rejected on account or privileges / the connection itself did not reach (daemon, firewall, bind-address).