MySQL¶

gardendb

Install¶

sudo apt install mysql-server -y

Run the mysql Secure Installation Program¶

sudo mysql_secure_installation

Establish Password Validity Policy
You can use more secure authentication means like keys rather than passwords.
MySQL has an anonymous user capability for testing, but it should be removed for security.
Root user should only be allowed to connect from localhost.
Remove the test database.
Reload the privileges table.

Enable the Firewall¶

sudo ufw enable

sudo ufw allow mysql

Check to ensure it's listening:

netstat -ant

You should see the 127.0.0.1:3306 (3306 indicating the default port for mySQL) The database server will eventually need to talk to other systems on the network.

Set Up¶

Configuration¶

cd /etc/mysql/mysql.conf.d && ll

mysql.cnf configures the command line's behavior
mysqld.cnf configures the server's behavior

vi mysqld.cnf

This is where you can change the default port. The bind address is the address of the local system you are binding to

After making changes, be sure to restart the service.

systemctl restart mysql

Check to see changes were applied.

netstat -ant

If you've changed the bind address to 0.0.0.0, the server will be exposed to the broader network. DON'T DO THAT IF YOU DON'T NEED TO.

Use¶

Connecting to Database¶

sudo mysql -u root -p

Basic Commands¶

Capitalization of verb and object are for ease of reading, but not required for function.

Creating a Basic Database¶

See what databases are in the server:

SHOW DATABASE;

Create a database:

CREATE DATABASE gardendb;

Select the database you want to use:

USE gardendb;

Create a table in gardendb called gardeners:

CREATE TABLE gardeners (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
    );

See the names of the tables in gardendb:

SHOW TABLES

See the content of the gardeners table:

DESCRIBE gardeners;

Fill table with content:

INSERT INTO gardeners (name) VALUES
    ('chacho'),('chirp'),('monkey');

Viewing the Data¶

Now to see various aspects of the content:

SELECT * FROM gardeners;

SELECT name FROM gardeners;

SELECT id FROM gardeners;

SELECT name FROM gardeners WHERE id>1;

SELECT id FROM gardeners WHERE name = "chacho";

Using a More Complex Database¶

Play with sample databases: Download Sample Employee Databasefrom GitHub

git clone https://github.com/datacharmer/test_db

Installation Instructions

sudo mysql -u root -p -t < employees.sql

Check to see that "employees" is now listed among the databases:

sudo mysql -u root -p

Select the "employees" database:

USE employees;

Show the tables in the "employees" database:

SHOW TABLES;

DESCRIBE employees;

Viewing Specific Data¶

Get a sample of what the employees data looks like:

SELECT * FROM employees LIMIT 20;

Establishing Example:

SELECT first_name FROM employees WHERE emp_no = 10010;

DESCRIBE salaries;

SELECT * FROM salaries WHERE emp_no = 10010;

SELECT * FROM titles WHERE title LIKE '%Senior%';

Modifying Records¶

Add an email address column:

ALTER TABLE employees ADD email VARCHAR(255);

Now an email address can be added to a record:

UPDATE employees SET email = "chirp@impossibleemailaddress.com" WHERE emp_no = 10001;

Display the changes:

SELECT * FROM employees WHERE emp_no = 10001;

It is EASY to make huge mistakes using CLI to modify database records. If "WHERE..." was deleted, it would have replaced all employee email addresses with chirp@impossibleemailaddress.com!

Setting Up a GUI for the Database¶

Set Up a Web Server with php¶

sudo apt install apache2 php php-mysql -y

Get a copy of Adminer for mysql (phpMyAdmin is another option) by copying the link address. Then run wget from the /var/www/html directory:

cd /var/www/html && sudo wget https://github.com/vrana/adminer/releases/download/v4.8.1/adminer-4.8.1-mysql-en.php

Rename for ease of access:

mv adminer-4.8.1-mysql-en.php db.php

Start up Apache

sudo systemctl start apache2

In a web browser, visit:

127.0.0.1/db.php

Now that the webserver is hosting the database, a user needs to be created to be able to authenticate into it for access.

Back in mysql:

sudo mysql -u root -p

In this instance, the user is HR and they are only allowed to access the database from the specified IP

CREATE USER 'HR'@'localhost' IDENTIFIED BY 'Password123!';

Give the user access to only what they need (in this case, the employees database)

GRANT ALL PRIVILEGES ON employees.* TO 'HR'@'localhost';

Show the permissions for the user we just created

SHOW GRANTS FOR 'HR'@'localhost';

Rather than restart the server we can do a flush of privileges to recognize the changes

FLUSH PRIVILEGES;

When attempting to login via the browser, if an error is thrown, check the log file

tail /var/log/mysql/error.log

If you must use a native password authentication plugin

CREATE USER 'HR'@'10.4.5.6' IDENTIFIED WITH mysql_native_password BY 'Password123!';

Once the user can authenticate into the Adminer GUI, the database can be viewed, exported for backup, etc.

Exporting Database through CLI¶

Create a backup

mysqldump --user user --password database_name --result-file=path/to/file.sql

Backup a specific table redirecting the output to a file

mysqldump --user user --password database_name table_name > path/to/file.sql

Backup all databases redirecting the output to a file

mysqldump --user user --password --all-databases > path/to/file.sql

Backup all databases from a remote host, redirecting the output to a fil

mysqldump --host=ip_or_hostname --user user --password --all-databases > path/to/file.sql