 
```
status
```

```
--------------
```

```
./bin/mysql Ver 8.0.13 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)
```

```
​
```

```
Connection id: 10
```

```
Current database:
```

```
Current user: root@localhost
```

```
SSL: Not in use
```

```
Current pager: stdout
```

```
Using outfile: ''
```

```
Using delimiter: ;
```

```
Server version: 8.0.13 MySQL Community Server - GPL
```

```
Protocol version: 10
```

```
Connection: Localhost via UNIX socket
```

```
Server characterset: utf8mb4
```

```
Db characterset: utf8mb4
```

```
Client characterset: utf8mb4
```

```
Conn. characterset: utf8mb4
```

```
UNIX socket: /tmp/mysql.sock
```

```
Uptime: 29 min 39 sec
```

```
​
```

```
Threads: 2 Questions: 22 Slow queries: 0 Opens: 136 Flush tables: 2 Open tables: 106 Queries per second avg: 0.012
```

```
--------------
```

 
```
pilou@lubuntu:~/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64$ mkdir ssl_keys
```

```
pilou@lubuntu:~/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64$ openssl genrsa 2048 > ./ssl_keys/ca-key.pem
```

```
Generating RSA private key, 2048 bit long modulus (2 primes)
```

```
.........................................................................+++++
```

```
...............................+++++
```

```
e is 65537 (0x010001)
```

```
pilou@lubuntu:~/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64$ openssl req -sha1 -new -x509 -nodes -days 3650 -key ./ssl_keys/ca-key.pem > ./ssl_keys/ca-cert.pem
```

 
```
openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout ./ssl_keys/server-key.pem > ./ssl_keys/server-req.pem
```

```
openssl x509 -sha1 -req -in ./ssl_keys/server-req.pem -days 3650 -CA ./ssl_keys/ca-cert.pem -CAkey ./ssl_keys/ca-key.pem -set_serial 01 > ./ssl_keys/server-cert.pem
```

```
openssl rsa -in ./ssl_keys/server-key.pem -out ./ssl_keys/server-key.pem
```

 
```
openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout ./ssl_keys/client-key.pem > ./ssl_keys/client-req.pem
```

```
openssl x509 -sha1 -req -in ./ssl_keys/client-req.pem -days 3650 -CA ./ssl_keys/ca-cert.pem -CAkey ./ssl_keys/ca-key.pem -set_serial 01 > ./ssl_keys/client-cert.pem
```

```
openssl rsa -in ./ssl_keys/client-key.pem -out ./ssl_keys/client-key.pem
```

pilou@lubuntu:~/mysql80/mysql-8.0.13-linux-glibc2.12-x86\_64$ ./ssl.sh Generating RSA private key, 2048 bit long modulus (2 primes) ...............................................+++++ ..............................+++++ e is 65537 (0x010001) You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) \[AU\]: State or Province Name (full name) \[Some-State\]: Locality Name (eg, city) \[\]: Organization Name (eg, company) \[Internet Widgits Pty Ltd\]: Organizational Unit Name (eg, section) \[\]: Common Name (e.g. server FQDN or YOUR name) \[\]:CA Email Address \[\]: Ignoring -days; not generating a certificate Generating a RSA private key ............+++++ ......................................+++++ writing new private key to '/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86\_64/ssl\_keys/server-key.pem' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) \[AU\]: State or Province Name (full name) \[Some-State\]: Locality Name (eg, city) \[\]: Organization Name (eg, company) \[Internet Widgits Pty Ltd\]: Organizational Unit Name (eg, section) \[\]: Common Name (e.g. server FQDN or YOUR name) \[\]:server Email Address \[\]: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password \[\]: An optional company name \[\]: Signature ok subject=C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = server Getting CA Private Key writing RSA key Ignoring -days; not generating a certificate Generating a RSA private key .................................+++++ .....................+++++ writing new private key to '/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86\_64/ssl\_keys/client-key.pem' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) \[AU\]: State or Province Name (full name) \[Some-State\]: Locality Name (eg, city) \[\]: Organization Name (eg, company) \[Internet Widgits Pty Ltd\]: Organizational Unit Name (eg, section) \[\]: Common Name (e.g. server FQDN or YOUR name) \[\]:client Email Address \[\]: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password \[\]: An optional company name \[\]: Signature ok subject=C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = client Getting CA Private Key writing RSA key

 
```
pilou@lubuntu:~/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64$ ./ssl.sh
```

```
Generating RSA private key, 2048 bit long modulus (2 primes)
```

```
...............................................+++++
```

```
..............................+++++
```

```
e is 65537 (0x010001)
```

```
You are about to be asked to enter information that will be incorporated
```

```
into your certificate request.
```

```
What you are about to enter is what is called a Distinguished Name or a DN.
```

```
There are quite a few fields but you can leave some blank
```

```
For some fields there will be a default value,
```

```
If you enter '.', the field will be left blank.
```

```
-----
```

```
Country Name (2 letter code) [AU]:
```

```
State or Province Name (full name) [Some-State]:
```

```
Locality Name (eg, city) []:
```

```
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
```

```
Organizational Unit Name (eg, section) []:
```

```
Common Name (e.g. server FQDN or YOUR name) []:CA
```

```
Email Address []:
```

```
Ignoring -days; not generating a certificate
```

```
Generating a RSA private key
```

```
............+++++
```

```
......................................+++++
```

```
writing new private key to '/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/server-key.pem'
```

```
-----
```

```
You are about to be asked to enter information that will be incorporated
```

```
into your certificate request.
```

```
What you are about to enter is what is called a Distinguished Name or a DN.
```

```
There are quite a few fields but you can leave some blank
```

```
For some fields there will be a default value,
```

```
If you enter '.', the field will be left blank.
```

```
-----
```

```
Country Name (2 letter code) [AU]:
```

```
State or Province Name (full name) [Some-State]:
```

```
Locality Name (eg, city) []:
```

```
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
```

```
Organizational Unit Name (eg, section) []:
```

```
Common Name (e.g. server FQDN or YOUR name) []:server
```

```
Email Address []:
```

```
​
```

```
Please enter the following 'extra' attributes
```

```
to be sent with your certificate request
```

```
A challenge password []:
```

```
An optional company name []:
```

```
Signature ok
```

```
subject=C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = server
```

```
Getting CA Private Key
```

```
writing RSA key
```

```
Ignoring -days; not generating a certificate
```

```
Generating a RSA private key
```

```
.................................+++++
```

```
.....................+++++
```

```
writing new private key to '/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/client-key.pem'
```

```
-----
```

```
You are about to be asked to enter information that will be incorporated
```

```
into your certificate request.
```

```
What you are about to enter is what is called a Distinguished Name or a DN.
```

```
There are quite a few fields but you can leave some blank
```

```
For some fields there will be a default value,
```

```
If you enter '.', the field will be left blank.
```

```
-----
```

```
Country Name (2 letter code) [AU]:
```

```
State or Province Name (full name) [Some-State]:
```

```
Locality Name (eg, city) []:
```

```
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
```

```
Organizational Unit Name (eg, section) []:
```

```
Common Name (e.g. server FQDN or YOUR name) []:client
```

```
Email Address []:
```

```
​
```

```
Please enter the following 'extra' attributes
```

```
to be sent with your certificate request
```

```
A challenge password []:
```

```
An optional company name []:
```

```
Signature ok
```

```
subject=C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = client
```

```
Getting CA Private Key
```

```
writing RSA key
```

 
```
[mysqld]
```

```
port = 3306
```

```
socket = /tmp/mysql.sock
```

```
skip-external-locking
```

```
key_buffer_size = 16K
```

```
max_allowed_packet = 1M
```

```
table_open_cache = 4
```

```
sort_buffer_size = 64K
```

```
read_buffer_size = 256K
```

```
read_rnd_buffer_size = 256K
```

```
net_buffer_length = 2K
```

```
thread_stack = 128K
```

```
table_open_cache=500
```

```
secure_file_priv=/tmp
```

```
max_connections = 400
```

```
max_user_connections=200
```

```
ssl-ca=/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/ca-cert.pem
```

```
ssl-cert=/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/server-cert.pem
```

```
ssl-key=/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/server-key.pem
```

```
ssl-cipher=DHE-RSA-AES256-SHA
```

```
​
```

```
[client]
```

```
ssl-cert=/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/client-cert.pem
```

```
ssl-key=/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/client-key.pem
```

 
```
2019-01-05T08:44:05.815408Z 0 [Warning] [MY-010068] [Server] CA certificate
```

```
/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/ca-cert.pem is self signed.
```

 
```
SHOW VARIABLES LIKE '%ssl%';
```

```
+--------------------+----------------------------------------------------------------------------------+
```

```
| Variable_name | Value |
```

```
+--------------------+----------------------------------------------------------------------------------+
```

```
| have_openssl | YES |
```

```
| have_ssl | YES |
```

```
| mysqlx_ssl_ca | |
```

```
| mysqlx_ssl_capath | |
```

```
| mysqlx_ssl_cert | |
```

```
| mysqlx_ssl_cipher | |
```

```
| mysqlx_ssl_crl | |
```

```
| mysqlx_ssl_crlpath | |
```

```
| mysqlx_ssl_key | |
```

```
| ssl_ca | /home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/ca-cert.pem |
```

```
| ssl_capath | |
```

```
| ssl_cert | /home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/server-cert.pem |
```

```
| ssl_cipher | |
```

```
| ssl_crl | |
```

```
| ssl_crlpath | |
```

```
| ssl_fips_mode | OFF |
```

```
| ssl_key | /home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/ssl_keys/server-key.pem |
```

```
+--------------------+----------------------------------------------------------------------------------+
```

```
17 rows in set (0,01 sec)
```

pilou@lubuntu:~/mysql80/mysql-8.0.13-linux-glibc2.12-x86\_64$ ./bin/mysql --defaults-file=/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86\_64/my.cnf -u root -h localhost -p --protocol tcp Enter password: Welcome to the MySQL monitor. Commands end with ; or \\g. Your MySQL connection id is 8 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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> status -------------- ./bin/mysql Ver 8.0.13 for linux-glibc2.12 on x86\_64 (MySQL Community Server - GPL) Connection id: 8 Current database: Current user: root@localhost SSL: Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.13 MySQL Community Server - GPL Protocol version: 10 Connection: localhost via TCP/IP Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 TCP port: 3306 Uptime: 20 sec

 
```
pilou@lubuntu:~/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64$ ./bin/mysql --defaults-file=/home/pilou/mysql80/mysql-8.0.13-linux-glibc2.12-x86_64/my.cnf -u root -h localhost -p --protocol tcp
```

```
Enter password:
```

```
Welcome to the MySQL monitor. Commands end with ; or \g.
```

```
Your MySQL connection id is 8
```

```
Server version: 8.0.13 MySQL Community Server - GPL
```

```
​
```

```
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
```

```
​
```

```
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> status
```

```
--------------
```

```
./bin/mysql Ver 8.0.13 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)
```

```
​
```

```
Connection id: 8
```

```
Current database:
```

```
Current user: root@localhost
```

```
SSL: Cipher in use is DHE-RSA-AES256-SHA
```

```
Current pager: stdout
```

```
Using outfile: ''
```

```
Using delimiter: ;
```

```
Server version: 8.0.13 MySQL Community Server - GPL
```

```
Protocol version: 10
```

```
Connection: localhost via TCP/IP
```

```
Server characterset: utf8mb4
```

```
Db characterset: utf8mb4
```

```
Client characterset: utf8mb4
```

```
Conn. characterset: utf8mb4
```

```
TCP port: 3306
```

```
Uptime: 20 sec
```

 
```
$ cd /etc/mysql
```

```
$ sudo mkdir ssl
```

```
$ cd ssl
```

 
```
La valeur du nom commun utilisée pour les certificats/clés du serveur et du client doit être différente de la valeur du nom commun utilisée pour le certificat CA. Pour éviter tout problème, je les règle comme suit. Sinon, vous obtiendrez une erreur d'échec de la vérification de la certification. Par conséquent, définissez-le comme suit :
```

```
Nom commun de l'AC : administrateur MariaDB
```

```
Nom commun du serveur : serveur MariaDB
```

```
Nom commun du client : client MariaDB
```

``` mysql> analyze table randomint;

+----------------------+---------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+----------------------+---------+----------+----------+

| myisamtest.randomint | analyze | status | OK |

+----------------------+---------+----------+----------+

1 row in set (0,09 sec)

mysql> show table status \G

************************** 1. row ***************************

Name: randomint

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 0

Avg_row_length: 0

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: 2019-01-14 12:00:11

Update_time: NULL

Check_time: NULL

Collation: utf8mb4_0900_ai_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0,00 sec) ```

Row Format	`REDUNDANT`	`COMPACT`	`DYNAMIC`	`COMPRESSED`
Compact Storage Characteristics	No	Yes	Yes	Yes
Enhanced Variable-Length Column Storage	No	No	Yes	Yes
Large Index Key Prefix Support	No	No	Yes	Yes
Compression Support	No	No	No	Yes
Supported Tablespace Types	system, file-per table, general\*	system, file-per-table, general\*	file-per-table, general\*	file-per-table, general\*
Required File Format	Antelope or Barracuda	Antelope or Barracuda	Barracuda	Barracuda

Row Format

`REDUNDANT`

`COMPACT`

`DYNAMIC`

`COMPRESSED`

Compact Storage Characteristics

Yes

Enhanced Variable-Length Column Storage

Yes

Large Index Key Prefix Support

Yes

Compression Support

Yes

Supported Tablespace Types

system, file-per table, general\*

system, file-per-table, general\*

file-per-table, general\*

Required File Format

Antelope or Barracuda

Barracuda

Connection 1	Connection 2
``` mysql> begin; Query OK, 0 rows affected (0,00 sec) mysql> insert into testtransaction values (2); Query OK, 1 row affected (0,00 sec) mysql> select * from testtransaction; ```	``` mysql> select * from testtransaction; Empty set (0,01 sec) ```

Connection 1	Connection 2
``` mysql> commit ; Query OK, 0 rows affected (0,10 sec) ```	``` select * from testtransaction; +------+ \| i \| +------+ \| 2 \| +------+ 1 row in set (0,00 sec) ```

Connection 1	Connection 2
``` mysql> begin -> ; Query OK, 0 rows affected (0,00 sec) mysql> alter table testtransaction add column testcolumn integer; Query OK, 0 rows affected (0,12 sec) Records: 0 Duplicates: 0 Warnings: 0 ```	``` mysql> select * from testtransaction; +------+------------+ \| i \| testcolumn \| +------+------------+ \| 2 \| NULL \| +------+------------+ 1 row in set (0,00 sec) ```

Connection 1	Connection 2
``` mysql> rollback ; Query OK, 0 rows affected (0,00 sec) ```	``` mysql> select * from testtransaction; +------+------------+ \| i \| testcolumn \| +------+------------+ \| 2 \| NULL \| +------+------------+ 1 row in set (0,00 sec) ```

Connection 1	Connection 2
``` SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; Query OK, 0 rows affected (0,00 sec) mysql> create table transactionlevel (i integer); Query OK, 0 rows affected (0,12 sec) mysql> begin -> ; Query OK, 0 rows affected (0,00 sec) mysql> insert into transactionlevel values (2); Query OK, 1 row affected (0,00 sec) ```	``` mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; Query OK, 0 rows affected (0,00 sec) mysql> begin; Query OK, 0 rows affected (0,00 sec) mysql> select * from transactionlevel; Empty set (0,00 sec) ```

Connection 1	Connection 2
``` commit; Query OK, 0 rows affected (0,06 sec) mysql> select * from transactionlevel; +------+ \| i \| +------+ \| 2 \| +------+ 1 row in set (0,00 sec) ```	``` mysql> select * from transactionlevel; Empty set (0,00 sec) ```

Connection 1	Connection 2
```SQL mysql> create table t (i integer); Query OK, 0 rows affected (0.08 sec) mysql> lock table t read; Query OK, 0 rows affected (0.00 sec) mysql> insert into t values(3); ERROR 1099 (HY000): Table 't' was locked with a READ lock and can't be updated ```	``` mysql> insert into t values(3); -- la connexion est bloqué ```

Connection 1	Connection 2
``` mysql> lock table testtransaction write; Query OK, 0 rows affected (0,00 sec) mysql> insert into testtransaction values(1,2); Query OK, 1 row affected (0,00 sec) ```	``` select * from testtransaction; -- la connection 2 est bloqué ```

Connection 1	Connection 2
``` mysql> begin ; Query OK, 0 rows affected (0,00 sec) mysql> select * from testtransaction where i=2 FOR SHARE; +------+------------+ \| i \| testcolumn \| +------+------------+ \| 2 \| NULL \| +------+------------+ 1 row in set (0,00 sec) mysql> insert into testtransaction values(1,2); Query OK, 1 row affected (0,00 sec) ```	``` mysql> begin; Query OK, 0 rows affected (0,02 sec) mysql> update testtransaction set testcolumn='pilou'; -- la transaction est bloqué ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction ```