1. Deploying MySQL on Linux with Docker

1.1. Starting a MySQL Server Instance

To start a new Docker container for a MySQL Server, use the following command:

docker run --name=container_name [--restart on-failure] -d -e MYSQL_RANDOM_ROOT_PASSWORD=yes [-e MYSQL_ONETIME_PASSWORD=yes] image_name:tag

$ docker run --name mysql8 -d -e MYSQL_RANDOM_ROOT_PASSWORD=yes mysql:8
b4b86043b5562bb9069d82a663e2beae7fb417d6c3a005ade1a9b48ced6048f5

$ docker logs mysql8 2>&1 | grep GENERATED
2021-12-29 08:22:09+00:00 [Note] [Entrypoint]: GENERATED ROOT PASSWORD: aetheQuihaichoReiziejeey3Echoovi
$ docker logs mysql8
2021-12-29 08:21:58+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.
2021-12-29 08:21:59+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2021-12-29 08:21:59+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.
2021-12-29 08:21:59+00:00 [Note] [Entrypoint]: Initializing database files
...
2021-12-29 08:22:05+00:00 [Note] [Entrypoint]: Starting temporary server
...
2021-12-29 08:22:09+00:00 [Note] [Entrypoint]: GENERATED ROOT PASSWORD: aetheQuihaichoReiziejeey3Echoovi
...

2021-12-29 08:22:11+00:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.

2021-12-29T08:22:12.185059Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.27) starting as process 1
2021-12-29T08:22:12.193270Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.

2021-12-29T08:22:12.948002Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.27'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.

1.2. Using a custom MySQL configuration file

docker run \
    --name some-mysql \
    -v /my/custom:/etc/mysql/conf.d \ (1)
    -v /my/own/datadir:/var/lib/mysql \ (2)
    -e MYSQL_ROOT_PASSWORD=my-secret-pw \
    -d \
    mysql:tag
1 The default configuration for MySQL can be found in /etc/mysql/my.cnf, which may !includedir additional directories such as /etc/mysql/conf.d or /etc/mysql/mysql.conf.d.

If /my/custom/config-file.cnf is the path and name of your custom configuration file, you can start your mysql container like this (note that only the directory path of the custom config file is used in this command):

This will start a new container some-mysql where the MySQL instance uses the combined startup settings from /etc/mysql/my.cnf and /etc/mysql/conf.d/config-file.cnf, with settings from the latter taking precedence.
2 The -v /my/own/datadir:/var/lib/mysql part of the command mounts the /my/own/datadir directory from the underlying host system as /var/lib/mysql inside the container, where MySQL by default will write its data files.

1.3. Creating database dumps

docker exec some-mysql \
    sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /some/path/on/your/host/all-databases.sql

1.4. Restoring data from dump files

docker exec -i some-mysql \
    sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /some/path/on/your/host/all-databases.sql

1.5. Connecting to and Disconnecting from the Server

To connect to the server, you usually need to provide a MySQL user name when you invoke mysql and, most likely, a password. If the server runs on a machine other than the one where you log in, you must also specify a host name.

$> mysql -h host -u user -p
Enter password: ********

host and user represent the host name where your MySQL server is running and the user name of your MySQL account. Substitute appropriate values for your setup. The ******** represents your password; enter it when mysql displays the Enter password: prompt.

$ docker exec -it mysql8 mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \q
Bye

2. Entering Queries

Here is a simple query that asks the server to tell you its version number and the current date. Type it in as shown here following the mysql> prompt and press Enter:

$ docker exec -it mysql8 mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT VERSION(), CURRENT_DATE;
+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 8.0.27    | 2021-12-29   |
+-----------+--------------+
1 row in set (0.00 sec)

This query illustrates several things about mysql:

  • A query normally consists of an SQL statement followed by a semicolon.

  • When you issue a query, mysql sends it to the server for execution and displays the results, then prints another mysql> prompt to indicate that it is ready for another query.

  • mysql displays query output in tabular form (rows and columns).

    The first row contains labels for the columns.

    The rows following are the query results.

  • mysql shows how many rows were returned and how long the query took to execute, which gives you a rough idea of server performance.

    These values are imprecise because they represent wall clock time (not CPU or machine time), and because they are affected by factors such as server load and network latency.

Keywords may be entered in any lettercase. The following queries are equivalent:

mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;

Here is another query. It demonstrates that you can use mysql as a simple calculator:

mysql> SELECT SIN(PI()/4), (4+1)*5;
+-------------------------+---------+
| SIN(PI()/4)             | (4+1)*5 |
+-------------------------+---------+
|      0.7071067811865475 |      25 |
+-------------------------+---------+
1 row in set (0.00 sec)

You can even enter multiple statements on a single line. Just end each one with a semicolon:

mysql> SELECT VERSION(); SELECT NOW();
+-----------+
| VERSION() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

+---------------------+
| NOW()               |
+---------------------+
| 2021-12-29 09:18:28 |
+---------------------+
1 row in set (0.00 sec)

Here is a simple multiple-line statement:

mysql> SELECT
    -> USER()
    -> ,
    -> CURRENT_DATE
    -> ;
+----------------+----------------+
| USER()         | CURRENT_DATE   |
+----------------+----------------+
| root@localhost | 2021-12-29     |
+----------------+----------------+
1 row in set (0.00 sec)

3. Creating and Using a Database

Use the SHOW statement to find out what databases currently exist on the server:

mysql> SHOW DATABASES;
+------------------------------------------------------------------+
| Database                                                         |
+------------------------------------------------------------------+
| information_schema                                               |
| mysql                                                            |
| performance_schema                                               |
| sys                                                              |
+------------------------------------------------------------------+
4 rows in set (0.01 sec)

The list of databases displayed by the statement may be different on your machine; SHOW DATABASES does not show databases that you have no privileges for if you do not have the SHOW DATABASES privilege.

3.1. Creating and Selecting a Database

If the administrator creates your database for you when setting up your permissions, you can begin using it. Otherwise, you need to create it yourself:

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.02 sec)

Under Unix, database names are case-sensitive (unlike SQL keywords), so you must always refer to your database as test, not as Test, TEST, or some other variant. This is also true for table names.

Creating a database does not select it for use; you must do that explicitly. To make menagerie the current database, use this statement:

mysql> SELECT DATABASE();
+------------------------------------+
| DATABASE()                         |
+------------------------------------+
| NULL                               |
+------------------------------------+
1 row in set (0.00 sec)

mysql> USE test
Database changed

mysql> SELECT DATABASE();
+------------------------------------+
| DATABASE()                         |
+------------------------------------+
| test                               |
+------------------------------------+
1 row in set (0.00 sec)

Your database needs to be created only once, but you must select it for use each time you begin a mysql session. You can do this by issuing a USE statement as shown in the example. Alternatively, you can select the database on the command line when you invoke mysql. Just specify its name after any connection parameters that you might need to provide. For example:

$ docker exec -it mysql8 mysql -u root -p test
Enter password:
...
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

mysql>

3.2. Creating a Table

Creating the database is the easy part, but at this point it is empty, as SHOW TABLES tells you:

mysql> SHOW TABLES;
Empty set (0.01 sec)

Use a CREATE TABLE statement to specify the layout of your table:

mysql> CREATE TABLE pet (
    ->     name VARCHAR(20),
    ->     owner VARCHAR(20),
    ->     species VARCHAR(20),
    ->     sex CHAR(1),
    ->     birth DATE,
    ->     death DATE
    -> );
Query OK, 0 rows affected (0.03 sec)

If you make a poor choice and it turns out later that you need a longer field, MySQL provides an ALTER TABLE statement.

Once you have created a table, SHOW TABLES should produce some output:

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| pet            |
+----------------+
1 row in set (0.01 sec)

mysql> SHOW TABLES IN test;
+----------------+
| Tables_in_test |
+----------------+
| pet            |
+----------------+
1 row in set (0.00 sec)

To verify that your table was created the way you expected, use a DESCRIBE statement:

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.07 sec)

3.3. Loading Data into a Table

After creating your table, you need to populate it. The LOAD DATA and INSERT statements are useful for this.

name owner species sex birth death

Fluffy

Harold

cat

f

1993-02-04

Claws

Gwen

cat

m

1994-03-17

Buffy

Harold

dog

f

1989-05-13

Fang

Benny

dog

m

1990-08-27

Bowser

Diane

dog

m

1979-08-31

1995-07-29

Chirpy

Gwen

bird

f

1998-09-11

Whistler

Gwen

bird

1997-12-09

You could create a text file pet.txt containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values (such as unknown sexes or death dates for animals that are still living), you can use NULL values. To represent these in your text file, use \N (backslash, capital-N). For example, the record for Whistler the bird would look like this (where the whitespace between values is a single tab character):

Whistler        Gwen    bird    \N      1997-12-09      \N

To load the text file pet.txt into the pet table, use this statement:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

When you want to add new records one at a time, the INSERT statement is useful. In its simplest form, you supply values for each column, in the order in which the columns were listed in the CREATE TABLE statement. Suppose that Diane gets a new hamster named “Puffball.” You could add a new record using an INSERT statement like this:

mysql> INSERT INTO pet
    -> VALUES (
    ->         'Puffball',
    ->         'Diane',
    ->         'hamster',
    ->         'f',
    ->         '1999-03-30',
    ->         NULL
    ->     );
Query OK, 1 row affected (0.03 sec)

String and date values are specified as quoted strings here. Also, with INSERT, you can insert NULL directly to represent a missing value. You do not use \N like you do with LOAD DATA.

3.4. Retrieving Information from a Table

  • Selecting All Data

    mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

  • Selecting Particular Rows

    mysql> SELECT * FROM pet WHERE name='Browser';
Empty set (0.00 sec)

mysql> SELECT * FROM pet WHERE name='Bowser';
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
1 row in set (0.00 sec)

  • Selecting Particular Columns

    mysql> SELECT name,birth FROM pet;
+----------+------------+
| name     | birth      |
+----------+------------+
| Puffball | 1999-03-30 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Bowser   | 1979-08-31 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
+----------+------------+
9 rows in set (0.00 sec)

  • Sorting Rows

    mysql> SELECT name,birth FROM pet ORDER BY birth;
+----------+------------+
| name     | birth      |
+----------+------------+
| Bowser   | 1979-08-31 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy   | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+
9 rows in set (0.00 sec)

  • Date Calculations

    mysql> SELECT name,
    ->     birth,
    ->     CURDATE(),
    ->     TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age
    -> FROM pet;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Puffball | 1999-03-30 | 2021-12-29 |   22 |
| Fluffy   | 1993-02-04 | 2021-12-29 |   28 |
| Claws    | 1994-03-17 | 2021-12-29 |   27 |
| Buffy    | 1989-05-13 | 2021-12-29 |   32 |
| Fang     | 1990-08-27 | 2021-12-29 |   31 |
| Bowser   | 1979-08-31 | 2021-12-29 |   42 |
| Chirpy   | 1998-09-11 | 2021-12-29 |   23 |
| Whistler | 1997-12-09 | 2021-12-29 |   24 |
| Slim     | 1996-04-29 | 2021-12-29 |   25 |
+----------+------------+------------+------+
9 rows in set (0.01 sec)

mysql> SELECT name,
    ->     birth,
    ->     death,
    ->     TIMESTAMPDIFF(YEAR, birth, death) AS age
    -> FROM pet
    -> WHERE death IS NOT NULL
    -> ORDER BY age;
+--------+------------+------------+------+
| name   | birth      | death      | age  |
+--------+------------+------------+------+
| Bowser | 1979-08-31 | 1995-07-29 |   15 |
+--------+------------+------------+------+
1 row in set (0.00 sec)

mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name     | birth      | MONTH(birth) |
+----------+------------+--------------+
| Puffball | 1999-03-30 |            3 |
| Fluffy   | 1993-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1990-08-27 |            8 |
| Bowser   | 1979-08-31 |            8 |
| Chirpy   | 1998-09-11 |            9 |
| Whistler | 1997-12-09 |           12 |
| Slim     | 1996-04-29 |            4 |
+----------+------------+--------------+
9 rows in set (0.00 sec)

    mysql>  SELECT '2018-10-31' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-31' + INTERVAL 1 DAY |
+-------------------------------+
| 2018-11-01                    |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT '2018-10-32' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-32' + INTERVAL 1 DAY |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2018-10-32' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

  • Working with NULL Values

    mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+
1 row in set (0.00 sec)

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+
1 row in set (0.00 sec)

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
|         0 |             1 |          0 |              1 |
+-----------+---------------+------------+----------------+
1 row in set (0.00 sec)

  • Pattern Matching

    mysql> SELECT * FROM pet WHERE name LIKE 'b%';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM pet WHERE name LIKE '%fy';
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM pet WHERE name LIKE '%w%';
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM pet WHERE name LIKE '_____';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

    mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$');
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

  • Counting Rows

    mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Diane  |        2 |
| Harold |        2 |
| Gwen   |        3 |
| Benny  |        2 |
+--------+----------+
4 rows in set (0.00 sec)

    mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.pet.owner'; this is incompatible with sql_mode=only_full_group_by
mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT owner, COUNT(*) FROM pet;
+-------+----------+
| owner | COUNT(*) |
+-------+----------+
| Diane |        9 |
+-------+----------+
1 row in set (0.00 sec)

  • Using More Than one Table

    event.sql
    CREATE TABLE event (
    name VARCHAR(20),
    date DATE,
    type VARCHAR(15),
    remark VARCHAR(255)
);

INSERT INTO event VALUES('Fluffy','1995-05-15','litter','4 kittens, 3 female, 1 male');
INSERT INTO event VALUES('Buffy','1993-06-23','litter','5 puppies, 2 female, 3 male');
INSERT INTO event VALUES('Buffy','1994-06-19','litter','3 puppies, 3 female');
INSERT INTO event VALUES('Chirpy','1999-03-21','vet','needed beak straightened');
INSERT INTO event VALUES('Slim','1997-08-03','vet','broken rib');
INSERT INTO event VALUES('Bowser','1991-10-12','kennel',NULL);
INSERT INTO event VALUES('Fang','1991-10-12','kennel',NULL);
INSERT INTO event VALUES('Fang','1998-08-28','birthday','Gave him a new chew toy');
INSERT INTO event VALUES('Claws','1998-03-17','birthday','Gave him a new flea collar');
INSERT INTO event values('Whistler','1998-12-09','birthday','First birthday');
    $ docker exec -it mysql8 sh -c "mysql -u root -p test < event.sql"
Enter password: *****
    mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| event          |
| pet            |
+----------------+
2 rows in set (0.02 sec)

mysql> DESCRIBE event;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| name   | varchar(20)  | YES  |     | NULL    |       |
| date   | date         | YES  |     | NULL    |       |
| type   | varchar(15)  | YES  |     | NULL    |       |
| remark | varchar(255) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

    mysql> SELECT pet.name,
    ->     TIMESTAMPDIFF(YEAR, birth, date) AS age,
    ->     remark
    -> FROM pet
    ->     INNER JOIN event ON pet.name = event.name
    -> WHERE event.type = 'litter';
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |    5 | 3 puppies, 3 female         |
| Buffy  |    4 | 5 puppies, 2 female, 3 male |
+--------+------+-----------------------------+
3 rows in set (0.00 sec)

4. Security

When thinking about security within a MySQL installation, you should consider a wide range of possible topics and how they affect the security of your MySQL server and related applications:

  • General factors that affect security.

    These include choosing good passwords, not granting unnecessary privileges to users, ensuring application security by preventing SQL injections and data corruption, and others.

  • Security of the installation itself.

    The data files, log files, and the all the application files of your installation should be protected to ensure that they are not readable or writable by unauthorized parties.

  • Access control and security within the database system itself, including the users and databases granted with access to the databases, views and stored programs in use within the database.

  • The features offered by security-related plugins.

  • Network security of MySQL and your system.

    The security is related to the grants for individual users, but you may also wish to restrict MySQL so that it is available only locally on the MySQL server host, or to a limited set of other hosts.

  • Ensure that you have adequate and appropriate backups of your database files, configuration and log files.

    Also be sure that you have a recovery solution in place and test that you are able to successfully recover the information from your backups.

4.1. Security Guidelines

Anyone using MySQL on a computer connected to the Internet should read this section to avoid the most common security mistakes.

In discussing security, it is necessary to consider fully protecting the entire server host (not just the MySQL server) against all types of applicable attacks: eavesdropping, altering, playback, and denial of service.

MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that users can attempt to perform. There is also support for SSL-encrypted connections between MySQL clients and servers. Many of the concepts discussed here are not specific to MySQL at all; the same general ideas apply to almost all applications.

When running MySQL, follow these guidelines:

  • Do not ever give anyone (except MySQL root accounts) access to the user table in the mysql system database! This is critical.

  • Learn how the MySQL access privilege system works.

    • Use the GRANT and REVOKE statements to control access to MySQL.

    • Do not grant more privileges than necessary.

    • Never grant privileges to all hosts.

  • Do not store cleartext passwords in your database.

    If your computer becomes compromised, the intruder can take the full list of passwords and use them. Instead, use SHA2() or some other one-way hashing function and store the hash value.

    To prevent password recovery using rainbow tables, do not use these functions on a plain password; instead, choose some string to be used as a salt, and use hash(hash(password)+salt) values.

  • Invest in a firewall. This protects you from at least 50% of all types of exploits in any software. Put MySQL behind the firewall or in a demilitarized zone (DMZ).

  • Applications that access MySQL should not trust any data entered by users, and should be written using proper defensive programming techniques.

  • Do not transmit plain (unencrypted) data over the Internet.

  • Learn to use the tcpdump and strings utilities.

    In most cases, you can check whether MySQL data streams are unencrypted by issuing a command like the following:

    $ tcpdump -l -i eth0 -w - src or dst port 3306 | strings

4.2. Access Control and Account Management

MySQL enables the creation of accounts that permit client users to connect to the server and access data managed by the server. The primary function of the MySQL privilege system is to authenticate a user who connects from a given host and to associate that user with privileges on a database such as SELECT, INSERT, UPDATE, and DELETE. Additional functionality includes the ability to grant privileges for administrative operations.

To control which users can connect, each account can be assigned authentication credentials such as a password. The user interface to MySQL accounts consists of SQL statements such as CREATE USER, GRANT, and REVOKE.

The MySQL privilege system ensures that all users may perform only the operations permitted to them.

As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the user name you specify.

When you issue requests after connecting, the system grants privileges according to your identity and what you want to do.

MySQL considers both your host name and user name in identifying you because there is no reason to assume that a given user name belongs to the same person on all hosts.

To see what privileges a given account has, use the SHOW GRANTS statement. For example:

SHOW GRANTS FOR 'joe'@'office.example.com';
SHOW GRANTS FOR 'joe'@'home.example.com';

MySQL access control involves two stages when you run a client program that connects to the server:

  • Stage 1: The server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password.

  • Stage 2: Assuming that you can connect, the server checks each statement you issue to determine whether you have sufficient privileges to perform it.

4.3. Account User Names and Passwords

MySQL stores accounts in the user table of the mysql system database. An account is defined in terms of a user name and the client host or hosts from which the user can connect to the server.

mysql> DESCRIBE mysql.user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                    | Type                              | Null | Key | Default               | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| User                     | char(32)                          | NO   | PRI |                       |       |
| Select_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv                | enum('N','Y')                     | NO   |     | N                     |       |

...

An account may also have authentication credentials such as a password. The credentials are handled by the account authentication plugin. MySQL supports multiple authentication plugins. Some of them use built-in authentication methods, whereas others enable authentication using external authentication methods.

mysql> SELECT Host,User FROM mysql.user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

4.4. Privileges Provided by MySQL

The privileges granted to a MySQL account determine which operations the account can perform. MySQL privileges differ in the contexts in which they apply and at different levels of operation:

  • Administrative privileges enable users to manage operation of the MySQL server.

    These privileges are global because they are not specific to a particular database.

  • Database privileges apply to a database and to all objects within it.

    These privileges can be granted for specific databases, or globally so that they apply to all databases.

  • Privileges for database objects such as tables, indexes, views, and stored routines can be granted for specific objects within a database, for all objects of a given type within a database (for example, all tables in a database), or globally for all objects of a given type in all databases.

4.5. Grant Tables

The mysql system database includes several grant tables that contain information about user accounts and the privileges held by them.

However, normally you do not modify the grant tables directly. Modifications occur indirectly when you use account-management statements such as CREATE USER, GRANT, and REVOKE to set up accounts and control the privileges available to each one. When you use such statements to perform account manipulations, the server modifies the grant tables on your behalf.

These mysql database tables contain grant information:

  • user: User accounts, static global privileges, and other nonprivilege columns.

  • global_grants: Dynamic global privileges.

  • db: Database-level privileges.

  • tables_priv: Table-level privileges.

  • columns_priv: Column-level privileges.

  • procs_priv: Stored procedure and function privileges.

  • proxies_priv: Proxy-user privileges.

  • default_roles: Default user roles.

  • role_edges: Edges for role subgraphs.

  • password_history: Password change history.

To check the privileges for a given account, use the SHOW GRANTS statement.

SHOW GRANTS FOR 'bob'@'pc84.example.com';

To display nonprivilege properties of an account, use SHOW CREATE USER:

SHOW CREATE USER 'bob'@'pc84.example.com';

4.6. Specifying Account Names

MySQL account names consist of a user name and a host name, which enables creation of distinct accounts for users with the same user name who connect from different hosts.

Account names appear in SQL statements such as CREATE USER, GRANT, and SET PASSWORD and follow these rules:

  • Account name syntax is 'user_name'@'host_name'.

  • The @'host_name' part is optional.

    An account name consisting only of a user name is equivalent to 'user_name'@'%'. For example, 'me' is equivalent to 'me'@'%'.

  • The user name and host name need not be quoted if they are legal as unquoted identifiers.

    Quotes must be used if a user_name string contains special characters (such as space or -), or a host_name string contains special characters or wildcard characters (such as . or %).

    For example, in the account name 'test-user'@'%.com', both the user name and host name parts require quotes.

  • Quote user names and host names as identifiers or as strings, using either backticks (`), single quotation marks ('), or double quotation marks (").

  • The user name and host name parts, if quoted, must be quoted separately. That is, write 'me'@'localhost', not 'me@localhost'. The latter is actually equivalent to 'me@localhost'@'%'.

  • A reference to the CURRENT_USER or CURRENT_USER() function is equivalent to specifying the current client’s user name and host name literally.

    mysql> SELECT CURRENT_USER;
+----------------+
| CURRENT_USER   |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)

MySQL stores account names in grant tables in the mysql system database using separate columns for the user name and host name parts:

  • The user table contains one row for each account.

    • The User and Host columns store the user name and host name.

    • This table also indicates which global privileges the account has.

  • Other grant tables indicate privileges an account has for databases and objects within databases.

    • These tables have User and Host columns to store the account name.

    • Each row in these tables associates with the account in the user table that has the same User and Host values.

  • For access-checking purposes,

    • comparisons of User values are case-sensitive;

    • comparisons of Host values are not case-sensitive.

User names and host names have certain special values or wildcard conventions, as described following.

An account with a blank user name is an anonymous user. To specify an anonymous user in SQL statements, use a quoted empty user name part, such as ''@'localhost'.

The host name part of an account name can take many forms, and wildcards are permitted:

  • A host value can be a host name or an IP address (IPv4 or IPv6).

    • The name 'localhost' indicates the local host.

    • The IP address '127.0.0.1' indicates the IPv4 loopback interface.

    • The IP address '::1' indicates the IPv6 loopback interface.

  • The % and _ wildcard characters are permitted in host name or IP address values. These have the same meaning as for pattern-matching operations performed with the LIKE operator.

    • A host value of '%' matches any host name, whereas a value of '%.mysql.com' matches any host in the mysql.com domain.

    • '198.51.100.%' matches any host in the 198.51.100 class C network.

  • MySQL does not perform matching on host names that start with digits and a dot.

  • For a host value specified as an IPv4 address, a netmask can be given to indicate how many address bits to use for the network number. Netmask notation cannot be used for IPv6 addresses.

    The syntax is host_ip/netmask. For example:

    CREATE USER 'david'@'198.51.100.0/255.255.255.0';

  • As of MySQL 8.0.23, a host value specified as an IPv4 address can be written using CIDR notation, such as 198.51.100.44/24.

4.7. Specifying Role Names

MySQL role names refer to roles, which are named collections of privileges.

Role names have syntax and semantics similar to account names. As stored in the grant tables, they have the same properties as account names.

Role names differ from account names in these respects:

  • The user part of role names cannot be blank. Thus, there is no “anonymous role” analogous to the concept of “anonymous user.”

  • As for an account name, omitting the host part of a role name results in a host part of '%‘. But unlike ’%' in an account name, a host part of '%' in a role name has no wildcard properties. For example, for a name 'me'@'%' used as a role name, the host part (’%'`) is just a literal value; it has no “any host” matching property.

  • Netmask notation in the host part of a role name has no significance.

  • An account name is permitted to be CURRENT_USER() in several contexts. A role name is not.

4.8. Access Control, Stage 1: Connection Verification

When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on these conditions:

  • Your identity and whether you can verify it by supplying the proper credentials.

  • Whether your account is locked or unlocked.

The server checks credentials first, then account locking state. A failure at either step causes the server to deny access to you completely. Otherwise, the server accepts the connection, and then enters Stage 2 and waits for requests.

The server performs identity and credentials checking using columns in the user table, accepting the connection only if these conditions are satisfied:

  • The client host name and user name match the Host and User columns in some user table row.

  • The client supplies the credentials specified in the row (for example, a password), as indicated by the authentication_string column. Credentials are interpreted using the authentication plugin named in the plugin column.

  • The row indicates that the account is unlocked. Locking state is recorded in the account_locked column, which must have a value of 'N'. Account locking can be set or changed with the CREATE USER or ALTER USER statement.

The authentication_string column can be blank. This is not a wildcard and does not mean that any password matches. It means that the user must connect without specifying a password. The authentication method implemented by the plugin that authenticates the client may or may not use the password in the authentication_string column. In this case, it is possible that an external password is also used to authenticate to the MySQL server.

From MySQL’s point of view, the encrypted password is the real password, so you should never give anyone access to it. In particular, do not give nonadministrative users read access to tables in the mysql system database.

4.9. Access Control, Stage 2: Request Verification

After the server accepts a connection, it enters Stage 2 of access control. For each request that you issue through the connection, the server determines what operation you want to perform, then checks whether your privileges are sufficient. This is where the privilege columns in the grant tables come into play. These privileges can come from any of the user, global_grants, db, tables_priv, columns_priv, or procs_priv tables.

The user and global_grants tables grant global privileges for a given account indicate the account privileges that apply on a global basis no matter what the default database is.

It is wise to grant privileges in the user table only to people who need them, such as database administrators.

For other users, leave all privileges in the user table set to 'N' and grant privileges at more specific levels only (for particular databases, tables, columns, or routines).

4.10. Adding Accounts, Assigning Privileges, and Dropping Accounts

To manage MySQL accounts, use the SQL statements intended for that purpose:

  • CREATE USER and DROP USER create and remove accounts.

  • GRANT and REVOKE assign privileges to and revoke privileges from accounts.

  • SHOW GRANTS displays account privilege assignments.

4.10.1. Creating Accounts and Granting Privileges

The following example uses CREATE USER and GRANT statements to set up four accounts (where you see 'password', substitute an appropriate password):

CREATE USER 'finley' @'localhost' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'finley' @'localhost' WITH
GRANT OPTION;

CREATE USER 'finley' @'%.example.com' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'finley' @'%.example.com' WITH
GRANT OPTION;

CREATE USER 'admin' @'localhost' IDENTIFIED BY 'password';
GRANT RELOAD,
    PROCESS ON *.* TO 'admin' @'localhost';

CREATE USER 'dummy' @'localhost';

The previous example grants privileges at the global level. The next example creates three accounts and grants them access at lower levels; that is, to specific databases or objects within databases. Each account has a user name of custom, but the host name parts differ:

CREATE USER 'custom' @'localhost' IDENTIFIED BY 'password';
GRANT ALL ON bankaccount.* TO 'custom' @'localhost';

CREATE USER 'custom' @'host47.example.com' IDENTIFIED BY 'password';
GRANT SELECT,
    INSERT,
    UPDATE,
    DELETE,
    CREATE,
    DROP ON expenses.* TO 'custom' @'host47.example.com';

CREATE USER 'custom' @'%.example.com' IDENTIFIED BY 'password';
GRANT SELECT,
    INSERT,
    UPDATE,
    DELETE,
    CREATE,
    DROP ON customer.addresses TO 'custom' @'%.example.com';

4.10.2. Checking Account Privileges and Properties

mysql> SELECT User,Host FROM mysql.user;
+------------------+--------------------+
| User             | Host               |
+------------------+--------------------+
| root             | %                  |
| custom           | %.example.com      |
| finley           | %.example.com      |
| custom           | host47.example.com |
| admin            | localhost          |
| custom           | localhost          |
| dummy            | localhost          |
| finley           | localhost          |
| mysql.infoschema | localhost          |
| mysql.session    | localhost          |
| mysql.sys        | localhost          |
| root             | localhost          |
+------------------+--------------------+
12 rows in set (0.00 sec)

To see the privileges for an account, use SHOW GRANTS:

mysql> SHOW GRANTS FOR 'admin'@'localhost';
+-----------------------------------------------------+
| Grants for admin@localhost                          |
+-----------------------------------------------------+
| GRANT RELOAD, PROCESS ON *.* TO `admin`@`localhost` |
+-----------------------------------------------------+
1 row in set (0.00 sec)

To see nonprivilege properties for an account, use SHOW CREATE USER:

mysql> SHOW CREATE USER 'admin'@'localhost'\G
*************************** 1. row ***************************
CREATE USER for admin@localhost: CREATE USER `admin`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$d6zqX\nG\'S03)B8Ixmrn5EBoH1Fc4MFp5p07e1AIzknBpHaKaHwdaXpB' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.00 sec)

4.10.3. Revoking Account Privileges

To revoke account privileges, use the REVOKE statement. Privileges can be revoked at different levels, just as they can be granted at different levels.

  • Revoke global privileges:

    REVOKE ALL
  ON *.*
  FROM 'finley'@'%.example.com';

REVOKE RELOAD
  ON *.*
  FROM 'admin'@'localhost';

  • Revoke database-level privileges:

    REVOKE CREATE,DROP
  ON expenses.*
  FROM 'custom'@'host47.example.com';

  • Revoke table-level privileges:

    REVOKE INSERT,UPDATE,DELETE
  ON customer.addresses
  FROM 'custom'@'%.example.com';

To check the effect of privilege revocation, use SHOW GRANTS:

mysql> SHOW GRANTS FOR 'admin'@'localhost';
+---------------------------------------------+
| Grants for admin@localhost                  |
+---------------------------------------------+
| GRANT PROCESS ON *.* TO `admin`@`localhost` |
+---------------------------------------------+

4.10.4. Dropping Accounts

To remove an account, use the DROP USER statement. For example, to drop some of the accounts created previously:

DROP USER 'finley'@'localhost';
DROP USER 'finley'@'%.example.com';
DROP USER 'admin'@'localhost';
DROP USER 'dummy'@'localhost';

4.11. Assigning Account Passwords

To assign a password when you create a new account, use CREATE USER and include an IDENTIFIED BY clause:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

To assign or change a password for an existing account, use the ALTER USER statement with an IDENTIFIED BY clause:

ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

If you are not connected as an anonymous user, you can change your own password without naming your own account literally:

ALTER USER USER() IDENTIFIED BY 'password';

4.12. Account Locking

MySQL supports locking and unlocking user accounts using the ACCOUNT LOCK and ACCOUNT UNLOCK clauses for the CREATE USER and ALTER USER statements:

When used with CREATE USER, these clauses specify the initial locking state for a new account. In the absence of either clause, the account is created in an unlocked state.

When used with ALTER USER, these clauses specify the new locking state for an existing account. In the absence of either clause, the account locking state remains unchanged.

Account locking state is recorded in the account_locked column of the mysql.user system table. The output from SHOW CREATE USER indicates whether an account is locked or unlocked.

If a client attempts to connect to a locked account, the attempt fails. The server increments the Locked_connects status variable that indicates the number of attempts to connect to a locked account, returns an ER_ACCOUNT_HAS_BEEN_LOCKED error, and writes a message to the error log:

Access denied for user 'user_name'@'host_name'.
Account is locked.
mysql> CREATE USER test@localhost IDENTIFIED BY 'pwd';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL ON test.* TO test@localhost;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                      |
+---------+------+--------------------------------------------------------------------------------------------------------------+
| Warning | 1285 | MySQL is started in --skip-name-resolve mode; you must restart it without this switch for this grant to work |
+---------+------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> ALTER USER test@localhost ACCOUNT LOCK;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT User,Host,account_locked FROM mysql.user WHERE User='test';
+------+-----------+----------------+
| User | Host      | account_locked |
+------+-----------+----------------+
| test | localhost | Y              |
+------+-----------+----------------+
1 row in set (0.00 sec)

$ docker exec -it mysql8 mysql -u test -p
Enter password:
ERROR 3118 (HY000): Access denied for user 'test'@'localhost'. Account is locked.
mysql> DROP USER test@localhost;
Query OK, 0 rows affected (0.01 sec)

5. Backup and Recovery

It is important to back up your databases so that you can recover your data and be up and running again in case problems occur, such as system crashes, hardware failures, or users deleting data by mistake. Backups are also essential as a safeguard before upgrading a MySQL installation, and they can be used to transfer a MySQL installation to another system or to set up replica servers.

5.1. Using mysqldump for Backups

mysqldump produces two types of output, depending on whether the --tab option is given:

  • Without --tab, mysqldump writes SQL statements to the standard output.

    • This output consists of CREATE statements to create dumped objects (databases, tables, stored routines, and so forth), and INSERT statements to load data into tables.

    • The output can be saved in a file and reloaded later using mysql to recreate the dumped objects.

    • Options are available to modify the format of the SQL statements, and to control which objects are dumped.

  • With --tab, mysqldump produces two output files for each dumped table.

    • The server writes one file as tab-delimited text, one line per table row.

    • This file is named tbl_name.txt in the output directory.

    • The server also sends a CREATE TABLE statement for the table to mysqldump, which writes it as a file named tbl_name.sql in the output directory.

5.1.1. Dumping Data in SQL Format with mysqldump

By default, mysqldump writes information as SQL statements to the standard output. You can save the output in a file:

$ mysqldump [arguments] > file_name

To dump all databases, invoke mysqldump with the --all-databases option:

$ mysqldump --all-databases > dump.sql

To dump only specific databases, name them on the command line and use the --databases option:

$ mysqldump --databases db1 db2 db3 > dump.sql

The --databases option causes all names on the command line to be treated as database names. Without this option, mysqldump treats the first name as a database name and those following as table names.

With --all-databases or --databases, mysqldump writes CREATE DATABASE and USE statements prior to the dump output for each database. This ensures that when the dump file is reloaded, it creates each database if it does not exist and makes it the default database so database contents are loaded into the same database from which they came. If you want to cause the dump file to force a drop of each database before recreating it, use the --add-drop-database option as well. In this case, mysqldump writes a DROP DATABASE statement preceding each CREATE DATABASE statement.

To dump a single database, name it on the command line:

$> mysqldump --databases test > dump.sql

In the single-database case, it is permissible to omit the --databases option:

$> mysqldump test > dump.sql

The difference between the two preceding commands is that without --databases, the dump output contains no CREATE DATABASE or USE statements.

To dump only specific tables from a database, name them on the command line following the database name:

$> mysqldump test t1 t3 t7 > dump.sql
$ docker exec -it mysql8 sh -c "mysqldump -u root -p --databases test > /tmp/test.sql"
Enter password:

$ docker exec -it mysql8 cat /tmp/test.sql
-- MySQL dump 10.13  Distrib 8.0.27, for Linux (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	8.0.27
...
-- Dump completed on 2021-12-30  8:07:50

5.1.2. Reloading SQL-Format Backups

To reload a dump file written by mysqldump that consists of SQL statements, use it as input to the mysql client. If the dump file was created by mysqldump with the --all-databases or --databases option, it contains CREATE DATABASE and USE statements and it is not necessary to specify a default database into which to load the data:

$ mysql < dump.sql

Alternatively, from within mysql, use a source command:

mysql> source dump.sql

If the file is a single-database dump not containing CREATE DATABASE and USE statements, create the database first (if necessary):

$ mysqladmin create db1

Then specify the database name when you load the dump file:

$ mysql db1 < dump.sql

Alternatively, from within mysql, create the database, select it as the default database, and load the dump file:

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql

6. The InnoDB Storage Engine

innodb architecture

7. References