Monday 31 January 2022

How to connect to MySQL database from Ubuntu command line

We first need to install MySQL shell.
 
I'm running my test db from within a Docker container as:
 
$ docker run \
--name my-mysql \
--rm \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=root \
-v mysql-volume:/var/lib/mysql \
-d mysql:latest \
--sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
 
 
To test that DB is up and running we can use telnet:
 
$ telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
J
8.0.27NwogIs�,
              2)IqzZ%caching_sha2_password2#08S01Got timeout reading communication packetsConnection closed by foreign host.
 
 
To connect MySQL shell running directly on my local host:
 
$ mysqlsh --mysql -u root -proot -h127.0.0.1
MySQL Shell 8.0.28

Copyright (c) 2016, 2022, 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 '\?' for help; '\quit' to exit.
WARNING: Using a password on the command line interface can be insecure.
Creating a Classic session to 'root@127.0.0.1'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 64
Server version: 8.0.27 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  127.0.0.1:3306 ssl  JS >
 
As we can see above, the default execution mode is set to JS (JavaScript). If we want to execute SQL commands, we need to change execution mode to SQL which can be done with \sql command (see all mysqlsh commands here):
 
MySQL  127.0.0.1:3306 ssl  JS > \sql
Switching to SQL mode... Commands end with ;
MySQL  127.0.0.1:3306 ssl  SQL >
 
Alternatively, we can pass --sql argument when running mysqlsh in order to start it in SQL mode from the very beginning (connection protocol, classic or X protocol is autodetected):
 
mysqlsh --sql ...
 
We can now execute SQL queries: 
 
MySQL  127.0.0.1:3306 ssl  SQL > show databases;
+--------------------+
| Database           |
+--------------------+
| my_custom_schema    |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.0019 sec)
 MySQL  127.0.0.1:3306 ssl  SQL >


 
 MySQL  127.0.0.1:3306 ssl  SQL > use mysql
Default schema set to `mysql`.
Fetching table and column names from `mysql` for auto-completion... Press ^C to stop.

 MySQL  127.0.0.1:3306 ssl  mysql  SQL > show tables;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+
37 rows in set (0.0040 sec)

 MySQL  127.0.0.1:3306 ssl  mysql  SQL > describe 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                     |       |
| Reload_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv      | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv   | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type                 | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher               | blob                              | NO   |     | NULL                  |       |
| x509_issuer              | blob                              | NO   |     | NULL                  |       |
| x509_subject             | blob                              | NO   |     | NULL                  |       |
| max_questions            | int unsigned                      | NO   |     | 0                     |       |
| max_updates              | int unsigned                      | NO   |     | 0                     |       |
| max_connections          | int unsigned                      | NO   |     | 0                     |       |
| max_user_connections     | int unsigned                      | NO   |     | 0                     |       |
| plugin                   | char(64)                          | NO   |     | caching_sha2_password |       |
| authentication_string    | text                              | YES  |     | NULL                  |       |
| password_expired         | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed    | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime        | smallint unsigned                 | YES  |     | NULL                  |       |
| account_locked           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_role_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_role_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Password_reuse_history   | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_reuse_time      | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_require_current | enum('N','Y')                     | YES  |     | NULL                  |       |
| User_attributes          | json                              | YES  |     | NULL                  |       |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.0046 sec)

 MySQL  127.0.0.1:3306 ssl  mysql  SQL > select User, Host from user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.0035 sec)


If we know in advance the schema (database) that we want to use, we can pass its name to --schema or --database argument. Also, if we want to save the output of all commands from the interactive session into a file, we can specify --pager command (e.g. tee):

$ mysqlsh --host=127.0.0.1 --user=root --password=root --sql --schema=sys --pager="tee mysqlsh.out"
MySQL Shell 8.0.28

Copyright (c) 2016, 2022, 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 '\?' for help; '\quit' to exit.
WARNING: Using a password on the command line interface can be insecure.
Creating a session to 'root@127.0.0.1/sys'
Fetching schema names for autocompletion... Press ^C to stop.
Fetching table and column names from `sys` for auto-completion... Press ^C to stop.
Your MySQL connection id is 66
Server version: 8.0.27 MySQL Community Server - GPL
Default schema set to `sys`.
 MySQL  127.0.0.1:3306 ssl  sys  SQL > show tables;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
...
| x$waits_by_user_by_latency                    |
| x$waits_global_by_latency                     |
+-----------------------------------------------+
101 rows in set (0.0060 sec)
 MySQL  127.0.0.1:3306 ssl  sys  SQL > \quit
Bye!

Let's check the output file now:


$ cat mysqlsh.out
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
...
| x$waits_by_user_by_latency                    |
| x$waits_global_by_latency                     |
+-----------------------------------------------+
101 rows in set (0.0060 sec)


 

To execute multiple commands in a single session and store their outputs in order of execution into a (text) file we need first to write a .sql file which lists all commands.

mysql-queries.sql:

select now() as '';
select 'This is a mysqlsh session output' as'';
show tables;

Then we can set it as a source file and redirect output into a file:

$ mysqlsh --host=127.0.0.1 --user=root --password=root --sql --schema=sys --file=mysql-queries.sql > mysqlsh.out
WARNING: Using a password on the command line interface can be insecure.

Let's check the output file:

$ cat mysqlsh.out

2022-01-31 16:36:49

This is a mysqlsh session output
Tables_in_sys
host_summary
host_summary_by_file_io
host_summary_by_file_io_type
...
x$waits_by_user_by_latency
x$waits_global_by_latency

Another way to execute multiple commands and pipe output into a file is by listing SQL commands one by another, separated by semicolon.
 
# mysqlsh \
--host=$MYSQL_DB_HOST \
--port=$MYSQL_DB_PORT \
--user=root \
--password=root \
--sql \
--execute='use mysql; select User from user;' > ./out/$SQL_FILE.out

The queries above are equal to:

select User from mysql.user;


MySQL DB ports and connection protocols

 
It is possible to check on which port DB listens to:
 
$ docker container inspect mysql-demo | jq .[].NetworkSettings
{
  "Bridge": "",
  "SandboxID": "ad84f18f89bb51dd6c694357346633aae3350e7333f5eadd4296aa8a81fba96a",
  "HairpinMode": false,
  "LinkLocalIPv6Address": "",
  "LinkLocalIPv6PrefixLen": 0,
  "Ports": {
    "3306/tcp": [
      {
        "HostIp": "0.0.0.0",
        "HostPort": "3307"
      }
    ],
    "33060/tcp": null
  },
  "SandboxKey": "/var/run/docker/netns/ad84f18f89bb",
  "SecondaryIPAddresses": null,
  "SecondaryIPv6Addresses": null,
  "EndpointID": "246dcd5e7ab59333868ebdeda32f8580309a9b5daeac5648f369f53a62c5c461",
  "Gateway": "172.17.0.1",
  "GlobalIPv6Address": "",
  "GlobalIPv6PrefixLen": 0,
  "IPAddress": "172.17.0.3",
  "IPPrefixLen": 16,
  "IPv6Gateway": "",
  "MacAddress": "02:42:ac:11:00:03",
  "Networks": {
    "bridge": {
      "IPAMConfig": null,
      "Links": null,
      "Aliases": null,
      "NetworkID": "7a20c87648fe59ba77848c398b3c69e0073a294f018c9c563242302cddcba16c",
      "EndpointID": "246dcd5e7ab59333868ebdeda32f8580309a9b5daeac5648f369f53a62c5c461",
      "Gateway": "172.17.0.1",
      "IPAddress": "172.17.0.3",
      "IPPrefixLen": 16,
      "IPv6Gateway": "",
      "GlobalIPv6Address": "",
      "GlobalIPv6PrefixLen": 0,
      "MacAddress": "02:42:ac:11:00:03",
      "DriverOpts": null
    }
  }
}


Default classic MySQL protocol port is usually 3306
Default X Protocol port is usually 33060

From the output above, we can see that our DB server supports only classic MySQL protocol.


Exporting Tables

How to export tables from MySQL Database | My Public Notepad


Importing Tables



How to install MySQL shell on Ubuntu

MySQL shell is MySQL DB client. mysqlsh (in Windows, that's mysqlsh.exe) is a successor of mysql client. [see more here]
 
In this tutorial I want to share my experience with installing it on my Ubuntu.  

Let's first check if it is installed already:

$ mysqlsh
bash: /usr/bin/mysqlsh: No such file or directory
 
We can now try to install it.

Update package information for the MySQL APT repository:

$ sudo apt-get update
...
 
Update the MySQL APT repository configuration package:

$ sudo apt-get install mysql-apt-config
Reading package lists... Done
Building dependency tree       
Reading state information... Done
E: Unable to locate package mysql-apt-config


This means that we don't have MySQL APT repository configuration package installed (MySQL APT repository is not in system's software repository list). 

We need to go to download page for the MySQL APT repository at https://dev.mysql.com/downloads/repo/apt/ and download it:



Now we can install the downloaded release package:

$ sudo dpkg -i mysql-apt-config_0.8.22-1_all.deb
Selecting previously unselected package mysql-apt-config.
(Reading database ... 341402 files and directories currently installed.)
Preparing to unpack mysql-apt-config_0.8.22-1_all.deb ...
Unpacking mysql-apt-config (0.8.22-1) ...
Setting up mysql-apt-config (0.8.22-1) ...
Warning: apt-key should not be used in scripts (called from postinst maintainerscript of the package mysql-apt-config)
OK

We can now re-try the initial steps:

 
$ sudo apt-get update
...
Get:4 http://repo.mysql.com/apt/ubuntu focal InRelease [12.9 kB]                                                                                                 
...
Reading package lists... Done
 
$ sudo apt-get install mysql-shell
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following NEW packages will be installed
  mysql-shell
0 to upgrade, 1 to newly install, 0 to remove and 25 not to upgrade.
Need to get 13.9 MB of archives.
After this operation, 109 MB of additional disk space will be used.
Get:1 http://repo.mysql.com/apt/ubuntu focal/mysql-tools amd64 mysql-shell amd64 8.0.28-1ubuntu20.04 [13.9 MB]
Fetched 13.9 MB in 5s (2,905 kB/s)      
Selecting previously unselected package mysql-shell:amd64.
(Reading database ... 341407 files and directories currently installed.)
Preparing to unpack .../mysql-shell_8.0.28-1ubuntu20.04_amd64.deb ...
Unpacking mysql-shell:amd64 (8.0.28-1ubuntu20.04) ...
Setting up mysql-shell:amd64 (8.0.28-1ubuntu20.04) ...
 
To verify that MySQL shall was successfully installed:
 
$ mysqlsh
MySQL Shell 8.0.28

Copyright (c) 2016, 2022, 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 '\?' for help; '\quit' to exit.
 MySQL  JS > \quit
Bye!

To check its version:

$ mysqlsh --version
mysqlsh   Ver 8.0.28 for Linux on x86_64 - for MySQL 8.0.28 (MySQL Community Server (GPL))



To uninstall it:
 
$ sudo apt-get remove mysql-shell
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages will be REMOVED
  mysql-shell
0 to upgrade, 0 to newly install, 1 to remove and 25 not to upgrade.
After this operation, 109 MB disk space will be freed.
Do you want to continue? [Y/n] y
(Reading database ... 348074 files and directories currently installed.)
Removing mysql-shell:amd64 (8.0.28-1ubuntu20.04) ...
 


Thursday 27 January 2022

MySQL Stored Procedures

Here is an example of SQL command which creates MySQL stored procedure named GetDiff which has 3 input and 1 output parameters:
 
 
DELIMITER $$

CREATE PROCEDURE GetDiff (
    IN  table_left VARCHAR(255),
    IN  table_right VARCHAR(255),
    IN  post_attribute VARCHAR(255),
    IN  post_type VARCHAR(255),
    OUT sql_statement VARCHAR(1000) -- used for debugging only
)
BEGIN
    SET @table_left = table_left;
    SET @table_right = table_right;
    SET @post_attribute = post_attribute;
    SET @post_type = post_type;

    SET @column_name_alias_a = CONCAT(post_attribute, '_a');
    SET @column_name_alias_b = CONCAT(post_attribute, '_b');

    SET @sql_text = CONCAT(
        'SELECT ',
            @table_left, '.ID, ',
            @table_left, '.', @post_attribute, ' AS ', @column_name_alias_a, ', ',
            @table_right, '.', @post_attribute, ' AS ', @column_name_alias_b, ' ',
            'FROM ', @table_left, ' ',
            'INNER JOIN ', @table_right, ' ON ', @table_left, '.ID = ', @table_right, '.ID ',
            'WHERE ', @table_left, '.', @post_attribute, ' != ', @table_right, '.', @post_attribute, ' ',
            'AND ', @table_left, '.post_type = \'', @post_type, '\';');
    
    SELECT @sql_text
    INTO sql_statement;
    
    PREPARE stmt FROM @sql_text;
    EXECUTE stmt;
    DROP PREPARE stmt;
    
END$$

DELIMITER ; 


To call it and see the table which is the result of sql query built inside the procedure:

call GetDiff('posts_a', 'posts_b', 'title', 'shipment', @sql_statement);
 
To call it and see/debug the sql query built inside the procedure:
 
call GetDiff('posts_a', 'posts_b', 'title', 'shipment', @sql_statement);
select @sql_statement;
 
The result might be like:
 
SELECT posts_a.ID, posts_a.title AS title_a, posts_b.title AS title_b FROM posts_a INNER JOIN posts_b ON posts_a.ID = posts_b.ID WHERE posts_a.title != posts_b.title AND posts_a.post_type = 'shipment';
 
 
---

Monday 24 January 2022

SQL Cheat Sheet

Statements

 

AS

Used to assign an alias name to a column or a table. Alias is temporary, it exists only during the execution of the query.
 
select CustomerName as customer from customers;
 
AS is optional but is recommended to use it explicitly. So this query is also valid:

select CustomerName customer from customers;

DROP

Deletes an object (database, table, index or view)
 
 
To drop a database:
 
> drop database northwind;

 
MySQL example how to verify that e.g. database was dropped: 

> show databases;

or 

> select schema_name from information_schema.schemata;


SELECT 


To return all unique last names:
 
SELECT DISTINCT LastName FROM Employees;



Clauses

ORDER BY

 
To sort the output by the LastName field, then by the FirstName field if LastName matches:
 
SELECT * FROM Employees ORDER BY LastName, FirstName;

 

JOIN 


 

...
 

https://www.db-fiddle.com/f/tPUgzPgpCwo1qKF9HUFn2v/0


CREATE TABLE Persons_dev (

    PersonID int,

      Name varchar(255)

);


CREATE TABLE Persons_beta (

    PersonID int,

      Name varchar(255)

);


INSERT INTO Persons_dev (PersonID, Name) VALUES (1,'Ana');

INSERT INTO Persons_dev (PersonID, Name) VALUES (2,'Bojan');

INSERT INTO Persons_dev (PersonID, Name) VALUES (3,'Ceca');

INSERT INTO Persons_dev (PersonID, Name) VALUES (4,'Dejan');

INSERT INTO Persons_dev (PersonID, Name) VALUES (5,'Erik');

INSERT INTO Persons_dev (PersonID, Name) VALUES (6,'Florian');


INSERT INTO Persons_beta (PersonID, Name) VALUES (0,'Zero');

INSERT INTO Persons_beta (PersonID, Name) VALUES (1,'Ana');

INSERT INTO Persons_beta (PersonID, Name) VALUES (2,'Bojan');

INSERT INTO Persons_beta (PersonID, Name) VALUES (3,'Ceca');

INSERT INTO Persons_beta (PersonID, Name) VALUES (4,'Deki');

INSERT INTO Persons_beta (PersonID, Name) VALUES (5,'Erik');



SELECT * FROM Persons_dev

WHERE PersonID NOT IN (SELECT PersonID FROM Persons_beta)

UNION 

SELECT * FROM Persons_beta

WHERE PersonID NOT IN (SELECT PersonID FROM Persons_dev)


SELECT Persons_dev.PersonID, Persons_dev.Name, Persons_beta.Name FROM Persons_dev 

INNER JOIN Persons_beta ON Persons_dev.PersonID=Persons_beta.PersonID

WHERE Persons_dev.Name != Persons_beta.Name;



https://www.db-fiddle.com/f/tPUgzPgpCwo1qKF9HUFn2v/3


 



Operators

[NOT] IN


To return all published posts that are present in posts_dev table but not in posts_beta (id is the key):

SELECT * 
FROM posts_dev
WHERE post_status='publish' AND 
              id NOT IN (SELECT ID FROM posts_beta WHERE post_status='publish')
 

Comments

Comments (e.g. in .sql files) are:
 
-- This is a comment in one line

or 

/* This is a comment in one line */

or 

/* 
This is 
a comment 
in multiple lines 
*/

References:


Monday 17 January 2022

How to run MySQL Docker container

Docker hub contains MySQL images. To run the container with the latest version of MySQL on the local machine:
 
$ docker run \
--name my-test-mysql \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:latest

02179364ced0e65912ab0d23dd8451e42d9e4ca679507abe711a86d0ac564219

We are here naming this container my-test-mysql, opening default MySQL port 3306 on container (so we can connect to DB from outside the container), set the password (e.g. root) for the default MySQL user (root) and making a choice which MySQL version to run (the latest).
 
To check that this container is running:
 
$ docker container ls
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                  PORTS                                            NAMES
...
02179364ced0        mysql:latest        "docker-entrypoint.s…"   3 days ago          Up 3 days               0.0.0.0:3306->3306/tcp, 33060/tcp                my-test-mysql
...


---

If for some reason container won't run (it starts and then stops), the reason can be found in docker logs for that container:
 
$ docker container logs my-test-mysql
 
NOTE: make sure that container is preserved after docker run command - don't run it with --rm option.
---
 
 
We can test connectivity to this DB via MySQL workbench:
 
 
 
 

Setting MySQL configuration when creating the container

 
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.[source]

 
It is possible to set a variable in MySQL configuration file when starting the container. To see the full list of such variables we need to run:
 
$ docker run -it --rm mysql:latest --verbose --help
mysqld  Ver 8.0.27 for Linux on x86_64 (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.

Starts the MySQL database server.

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqld server mysqld-8.0
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file,
                        except for login file.
...

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}                             Value (after reading options)
------------------------------------------------------------ -------------
abort-slave-event-count                                      0
activate-all-roles-on-login                                  FALSE
...
sql-mode                                                     ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
...
windowing-use-high-precision                                 TRUE

To see what values a running MySQL server is using, type
'mysqladmin variables' instead of 'mysqld --verbose --help'.



Let's say we want to set sql_mode in order to suppress error invalid default value when setting '0000-00-00 00:00:00' as default value for column with datetime type. By default NO_ZERO_IN_DATE,NO_ZERO_DATE are included in sql_mode value in my.cnf but we want to exlude them:

sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
 
To set this variable when running the container:

$ docker run \
--name wp-admin-mysql \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:latest \
--sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

To verify that the value is correctly set we can connect to DB and execute the following command:

SELECT @@GLOBAL.sql_mode;



 

DB Config and Data Persistence



Each time we run the MySQL container it sets up DB from scratch. Once container is stopped all configurations, schemas, tables and data are lost forever. To persist this data between MySQL container sessions we can use:
  • Docker volume (preferred way)
  • directory on the host

Data Persistence with named Docker volume


Named Docker volume is like a blob that can be attached to a container and where container persists its data. 
 
We first need to create it:

$ docker volume create mysql-volume
mysql-volume

 
After it's created it will be listed among other Docker volumes:
 
$ docker volume ls
DRIVER              VOLUME NAME
local               mysql-volume



We can now mount it to /var/lib/mysql inside the container, where MySQL by default will write its data files: -v data-volume:/var/lib/mysql


$ docker run \
--name my-test-mysql \
--rm \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=root \
-v mysql-volume:/var/lib/mysql \ 
-d mysql:latest \
--sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Any change on the database from now will be persisted inside the volume and each subsequent run of MySQL database (from another MySQL container) will pick it up.

Stopping the container


$ docker container stop my-test-mysql
my-test-mysql

References:

Connect to MySQL running in Docker container from a local machine | by Md Kamaruzzaman | Towards Data Science