Another MySQL DBA

Saturday, February 24, 2024

MariaDB Encryption ( data at rest )

Here is a simple example showing a couple options for MariaDB Encryption.

You have to consider what you want to encrypt . The data communication (data in transit) or the data on the instance (data at rest).

This post is going to focus on the data at rest option using a AWS free tier node running on Amazon Linux. I will be using the world database on 2 different instances to show updating current tables with encryption as well as new loading tables to be auto-encrypted.

1st we will start with installs.. quick and simple just for this demo.

# yum -y install mariadb105-server
# vi /etc/my.cnf.d/mariadb-server.cnf
# cat /etc/my.cnf.d/mariadb-server.cnf | grep server_id
server_id=100

# cat /etc/my.cnf.d/mariadb-server.cnf | grep server_id
server_id=200
# systemctl start mariadb.service
# mysql
MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 10.5.23-MariaDB, for Linux (x86_64) using EditLine wrapper
Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.5.23-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 27 sec
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 100 |
+-------------+
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 200 |
+-------------+

We will load the world db into server_id 100 instance.

# mysql < world.sql
# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.23-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Now we can see that currently, both instances are not using encryption.

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 100 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
Empty set (0.000 sec)

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
Empty set (0.000 sec)

Now across both systems, I am going to set up Random Keys and encrypt them.

# mkdir /etc/mysql/

# mkdir /etc/mysql/encryption/
# (echo -n "1;" ; openssl rand -hex 32 ) | sudo tee -a /etc/mysql/encryption/keyfile
# (echo -n "2;" ; openssl rand -hex 32 ) | sudo tee -a /etc/mysql/encryption/keyfile
# (echo -n "100;" ; openssl rand -hex 32 ) | sudo tee -a /etc/mysql/encryption/keyfile

# cat /etc/mysql/encryption/keyfile
1;466139b487d5735b536a10502b0607d2b96dfa58a7f95ce4847d98ef585af8b5
2;a0f533c2e459dc64e0aeb1f0f8c9dfb496571e71001ca60fac35c8bbc6361096
100;4b4bfd61af39d57e068385cf1a023bdfc5972ed414a24167067ca5256fc314e6

# cat /etc/mysql/encryption/keyfile
1;f8b1b250d3bf7159e2abd15be146367415a80d3781bbcf7d96b26640d7efdc8a
2;c7eaccedd0ef561f0c08d461abfd651947230b08c71aec67c6064bbedb6408ec
100;90475521b6eb6be3c1ec02770f3e8f80b34130f37d25c89b51cf10ec6a7c5bb6

openssl rand -hex 128 > /etc/mysql/encryption/keyfile.key
openssl enc -aes-256-cbc -md sha1 -pass file:/etc/mysql/encryption/keyfile.key -in /etc/mysql/encryption/keyfile -out /etc/mysql/encryption/keyfile.enc

ls -ltr /etc/mysql/encryption/
total 12
-rw-r--r--. 1 root root 203 Feb 24 23:39 keyfile
-rw-r--r--. 1 root root 257 Feb 24 23:40 keyfile.key
-rw-r--r--. 1 root root 224 Feb 24 23:41 keyfile.enc

Now we can set up the cnf file to enable the plugin as well as options for encryption.

# vi /etc/my.cnf.d/mariadb-server.cnf
[mariadb]
## File Key Management
plugin_load_add = file_key_management
loose_file_key_management_filename = /etc/mysql/encryption/keyfile
loose_file_key_management_filename = /etc/mysql/encryption/keyfile.enc
loose_file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
loose_file_key_management_encryption_algorithm = AES_CBC

## InnoDB/XtraDB Encryption Setup
innodb_encrypt_log = ON
innodb_encrypt_temporary_tables=ON
innodb_encryption_threads=4
innodb_encrypt_tables=ON
innodb_default_encryption_key_id=1

## Aria Encryption Setup
aria_encrypt_tables = ON

## Temp & Log Encryption
encrypt-tmp-disk-tables = 1
encrypt-tmp-files = 1
encrypt_binlog = ON

Restart MariaDB so we can check on the current status.

systemctl restart mariadb.service

mysql

MariaDB [(none)]> select @@server_id;

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

| @@server_id |

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

| 100 |

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

1 row in set (0.001 sec)

MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;

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

| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |

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

| innodb_system | 1 | 1 |

| mysql/innodb_table_stats | 1 | 1 |

| mysql/innodb_index_stats | 1 | 1 |

| mysql/transaction_registry | 1 | 1 |

| mysql/gtid_slave_pos | 1 | 1 |

| world/city | 1 | 1 |

| world/country | 1 | 1 |

| world/countrylanguage | 1 | 1 |

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

8 rows in set (0.000 sec)

MariaDB [(none)]> select @@server_id;

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

| @@server_id |

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

| 200 |

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

1 row in set (0.000 sec)

MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;

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

| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |

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

| innodb_system | 1 | 1 |

| mysql/innodb_index_stats | 1 | 1 |

| mysql/gtid_slave_pos | 1 | 1 |

| mysql/innodb_table_stats | 1 | 1 |

| mysql/transaction_registry | 1 | 1 |

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

5 rows in set (0.000 sec)

Load up the world data into the server_id 200 instance as well.

# mysql < world.sql
# mysql
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
+----------------------------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------------------------+-------------------+----------------+
| innodb_system | 1 | 1 |
| mysql/innodb_index_stats | 1 | 1 |
| mysql/gtid_slave_pos | 1 | 1 |
| mysql/innodb_table_stats | 1 | 1 |
| mysql/transaction_registry | 1 | 1 |
| world/city | 1 | 1 |
| world/country | 1 | 1 |
| world/countrylanguage | 1 | 1 |
+----------------------------+-------------------+----------------+
8 rows in set (0.000 sec)

According to the information_schema.INNODB_TABLESPACES_ENCRYPTION we are encrypted now. However, they do not show that at the schema level. While they say it is encrypted if showing up in the INNODB_TABLESPACES_ENCRYPTION table, I would rather be sure and have it seen in the table and on the schema.

MariaDB [(none)]> show create table world.city\G
*************************** 1. row ***************************
Table: city
Create Table: CREATE TABLE `city` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.001 sec)

Up to this point, you can see that both instances have been accounted for in the INNODB_TABLESPACES_ENCRYPTION schema after the restart or loading of the schema and data.

So... a few table alters will help here...

MariaDB [world]> ALTER TABLE city ENCRYPTED=Yes ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.074 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [world]> ALTER TABLE country ENCRYPTED=Yes ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.031 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [world]> ALTER TABLE countrylanguage ENCRYPTED=Yes ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.033 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [world]> show create table city\G

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

Table: city

Create Table: CREATE TABLE `city` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`Name` char(35) NOT NULL DEFAULT '',

`CountryCode` char(3) NOT NULL DEFAULT '',

`District` char(20) NOT NULL DEFAULT '',

`Population` int(11) NOT NULL DEFAULT 0,

PRIMARY KEY (`ID`),

KEY `CountryCode` (`CountryCode`),

CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTED`=Yes `ENCRYPTION_KEY_ID`=1

1 row in set (0.000 sec)

This is simple and etc so far.. now we need to enable binlogs and double check more.

vi /etc/my.cnf.d/mariadb-server.cnf
log_bin=demo

cat /etc/my.cnf.d/mariadb-server.cnf | grep log_bin
log_bin=demo

# systemctl restart mariadb.service

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 100 |
+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
File: demo.000001
Position: 363
Binlog_Do_DB:
Binlog_Ignore_DB:

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 200 |
+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
File: demo.000001
Position: 363
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.000 sec)

Checking via a look at the binlogs....

mariadb-binlog--base64-output=DECODE-ROWS --verbose demo.000001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#240225 0:06:06 server id 100 end_log_pos 256 CRC32 0x04ce3741 Start: binlog v 4, server v 10.5.23-MariaDB-log created 240225 0:06:06 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

# at 256

# Encryption scheme: 1, key_version: 1, nonce: eb7991b210f3f4d2f7f21537

# The rest of the binlog is encrypted!

ERROR: Error in Log_event::read_log_event(): 'Event decryption failure', data_len: 2400465656, event_type: 240

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Good to see that it says it is being encrypted now.

MariaDB [world]> create table city2 like city;
Query OK, 0 rows affected (0.013 sec)

MariaDB [world]> insert into city2 select * from city;
Query OK, 4079 rows affected (0.078 sec)
Records: 4079 Duplicates: 0 Warnings: 0

MariaDB [world]> show create table city2\G
*************************** 1. row ***************************
Table: city2
Create Table: CREATE TABLE `city2` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTED`=Yes `ENCRYPTION_KEY_ID`=1
1 row in set (0.000 sec)

I want to see these transactions though in the binlog.. how? You can use mariadb_binlog along with --read-from-remote-server to be able to see the data in the logs...

mariadb-binlog --base64-output=DECODE-ROWS --verbose --read-from-remote-server demo.000001 | more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240225 0:06:06 server id 100 end_log_pos 256 CRC32 0x04ce3741 Start: binlog v 4, server v 10.5.23-MariaDB-log created 240225 0:06:06 at startup
ROLLBACK/*!*/;
# at 256
#240225 0:06:06 server id 100 end_log_pos 296 CRC32 0x0c89f3bb Ignorable
# Ignorable event type 164 (Start_encryption)
# at 296
#240225 0:06:06 server id 100 end_log_pos 325 CRC32 0x535776a2 Gtid list []
# at 325
#240225 0:06:06 server id 100 end_log_pos 363 CRC32 0x2ac4a61b Binlog checkpoint demo.000001
# at 363
#240225 0:09:40 server id 100 end_log_pos 405 CRC32 0x93e10dc4 GTID 0-100-1 ddl
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=100*//*!*/;
/*!100001 SET @@session.gtid_seq_no=1*//*!*/;
# at 405
#240225 0:09:40 server id 100 end_log_pos 501 CRC32 0x39269040 Query thread_id=5 exec_time=0 error_code=0
use `world`/*!*/;
SET TIMESTAMP=1708819780/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_
for_timestamp=0/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=utf8,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table city2 like city
/*!*/;
# at 501
#240225 0:09:49 server id 100 end_log_pos 543 CRC32 0xde82b753 GTID 0-100-2 trans
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
START TRANSACTION
/*!*/;
# at 543
# at 602
#240225 0:09:49 server id 100 end_log_pos 602 CRC32 0x05bbb9e6 Annotate_rows:
#Q> insert into city2 select * from city
#240225 0:09:49 server id 100 end_log_pos 661 CRC32 0x9e0b4e0d Table_map: `world`.`city2` mapped to number 21
# at 661

Hopefully, this can at least help get you started ....

Resources:

https://mariadb.com/kb/en/securing-mariadb-encryption/

Thursday, November 12, 2020

Using your FRM file to get Schema and then import idb files..

This is a topic that overall you never should have to do... Why? Because you created backups right... You have tested and know the backups work so you can just restore those backups and get your lost schema and related data...

However that one instance in the corner office.. you never got around to setting up.. it not that important... just crashed and now you realize how you actually do use it...

All is not lost..

MySQL released their MySQL utilities awhile back and since been replaced more with the MySQL Shell.

mysqlfrm is still very handy though when needing to pull out the schema from an FRM file in a quick and simple command and it is a simple install.


           mysqlfrm --diagnostic city.frm

          # WARNING: Cannot generate character set or collation names without the --server option. 
          # CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.

         # Reading .frm file for city.frm:

         # The .frm file is a TABLE.

         # CREATE TABLE Statement:

         

         CREATE TABLE `city` (

           `ID` int(11) NOT NULL AUTO_INCREMENT,

           `Name` char(160) DEFAULT NULL,

           `CountryCode` char(12) NOT NULL,

           `District` char(80) NOT NULL,

           `Population` int(11) NOT NULL,

         PRIMARY KEY `PRIMARY` (`ID`),

         KEY `CountryCode` (`CountryCode`),

         KEY `popkey` (`Population`)

         ) ENGINE=InnoDB;

         

         #...done.

So now that you have the schema you lost... rebuild the DB or table. For the sake of the example, I will say we just lost the city data from the world DB.

$ cp city.ibd /tmp/


$ cp city.ibd /tmp/ 

mysql> LOCK TABLES city WRITE;

mysql> ALTER TABLE city DISCARD TABLESPACE;

 

cp  city.ibd /edb/local/mysql/data/rundeck/

chown tmdba:dba /edb/local/mysql/data/rundeck/city.ibd



mysql> ALTER TABLE city IMPORT TABLESPACE;

mysql> UNLOCK TABLES;

mysql> SELECT COUNT(*) FROM city;

Monday, September 21, 2020

MySQL mysql_config_editor & expect

This is just a note to help out anyone that might want to use the mysql_config_editor command in their automation tools.

the mysql_config_editor does not take a password argument so automation tools that might have before set your password in the .my.cnf file trying to use mysql_config_editor fails.

It is possible and quite simple though with the expect tool.

yum -y install expect

it works for apt-get also.

So in this example, I will show a simple bash script version.

1st.. my login path does not work...

mysql --login-path=local

ERROR 1045 (28000): Access denied for user

Set this with expect

You would execute this via your bash script.

expect <<EOD

spawn mysql_config_editor set --login-path=local --host=localhost --user=root --password

expect "password"

send -- "<PASSWORD>\r"

interact

EOD

Now it works...

mysql --login-path=local

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

Your MySQL connection id is 1002

Sunday, March 15, 2020

MySQL & Dockers...a simple set up

MySQL & Dockers... are not new concepts, people have been moving to Dockers for some time now. For someone who is just moving to this for development, it can have a few hurdles.

While MySQL works just fine running locally, if you are testing code across different versions of MySQL it is nice to have several versions easily available.

One option for years has been of course https://mysqlsandbox.net/ by Giuseppe Maxia. This is a very valid solution to be able to get several instances up and test replication and etc etc.

Dockers are now also another often used scenario when it comes to testing across different versions of MySQL. The following will just go over some of the steps to get several versions installed easily. I use OSX so these examples are for OSX.

You need Docker to start and of course and Docker Desktop is a handy tool for you to be able to get access easily.

Once I had Docker set up I can get my environment ready for MySQL.

Here I created a Docker folder that contains the MySQL data directories, Config files as well as the mysql-files directory if I needed it.

mkdir ~/Docker ;

mkdir ~/Docker/mysql_data;

mkdir ~/Docker/mysql-files;



mkdir ~/Docker/cnf;

Now inside mysql_data

cd ~/Docker/mysql_data;

mkdir 8.0;

mkdir 5.7;

mkdir 5.6;

mkdir 5.5;

Now I set up simple cnf files for this example. The primary thing to note is the bind-address. This is set to ensure it is opened up for us to reach MySQL outside of the docker. You can also notice that these files can be used to set up additional configuration information as you see fit per MySQL docker instance.

cd ~/Docker/cnf;

cat my.8.0.cnf

[mysqld]

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

datadir = /var/lib/mysql

secure-file-priv= /var/lib/mysql-files

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

bind-address = 0.0.0.0

port=3306

server-id=80

# Custom config should go here

!includedir /etc/mysql/conf.d/

cat my.5.7.cnf

[mysqld]

bind-address = 0.0.0.0

server-id=57

max_allowed_packet=32M

$ cat my.5.6.cnf

[mysqld]

bind-address = 0.0.0.0

server-id=56

$ cat my.5.5.cnf

[mysqld]

bind-address = 0.0.0.0

server-id=55

OK so now that we have configuration files set up, We need to build the dockers. A few things to note for the build commands.

--name We set a named reference for the docker.

Here we are mapping the configuration files, data directory and mysql-files directories to the docker . This allows us to adjust the my.cnf file and etc easily.

-v ~/Docker/cnf/my.8.0.cnf:/etc/mysql/my.cnf

-v ~/Docker/mysql_data/8.0:/var/lib/mysql

-v ~/Docker/mysql-files:/var/lib/mysql-files

We want to be able to reach these MySQL instances outside of the docker so we need to publish and map the port accordingly.

-p 3306:3306 This means 3306 local to 3306 inside docker

-p 3307:3306 This means 3307 local to 3306 inside docker

-p 3308:3306 This means 3308 local to 3306 inside docker

-p 3309:3306 This means 3309 local to 3306 inside docker

Then we also pass a couple of environment variables.

-e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here>

So putting it all together...

docker run --restart always --name mysql8.0   -v ~/Docker/cnf/my.8.0.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/8.0:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p  3306:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:8.0

docker run --restart always --name mysql5.7   -v ~/Docker/cnf/my.5.7.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/5.7:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p  3307:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:5.7

docker run --restart always --name mysql5.6   -v ~/Docker/cnf/my.5.6.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/5.6:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p  3308:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:5.6

docker run --restart always --name mysql5.5   -v ~/Docker/cnf/my.5.5.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/5.5:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p  3309:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:5.5

After each execution of the above commands, you should get an id returned.

example: 3cb07d7c21476fbf298648986208f3429ec664167d8eef7fed17bf9ee3ce6316

You can start/restart and access each docker terminal easily via the Docker Desktop or just keep note of the related IDs and you execute via the terminal.

The Docker Desktop also shows you all the variables you passed so you can validate.

You can of course also access the CLI here, stop and start or destroy it easily.

$ docker exec -it 3cb07d7c21476fbf298648986208f3429ec664167d8eef7fed17bf9ee3ce6316 /bin/sh; exit

# mysql -p 

If the Docker container is already running you can now access MySQL via your localhost terminal.

$ mysql --host=localhost --protocol=tcp --port=3306 -p -u root

Now if you are having any access issues remember to ensure that MySQL accounts are correct and that your ports and mapping correctly.

Lost connection to MySQL server at 'reading initial communication packet'
ERROR 1045 (28000): Access denied for user 'root'@'192.168.0.5' (using password: YES)

Now you can see that all are up and available and the server Ids match what we set per cnf file eariler.

 
$ mysql --host=localhost  --protocol=tcp --port=3306 -e "Select @@hostname, @@version, @@server_id "

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

| @@hostname   | @@version | @@server_id |

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

| 58e9663afe8d | 8.0.19    |          80 |

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

$ mysql --host=localhost  --protocol=tcp --port=3307 -e "Select @@hostname, @@version, @@server_id "

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

| @@hostname   | @@version | @@server_id |

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

| b240917f051a | 5.7.29    |          57 |

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

$ mysql --host=localhost  --protocol=tcp --port=3308 -e "Select @@hostname, @@version, @@server_id "

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

| @@hostname   | @@version | @@server_id |

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

| b4653850cfe9 | 5.6.47    |          56 |

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

$ mysql --host=localhost  --protocol=tcp --port=3309 -e "Select @@hostname, @@version, @@server_id "

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

| @@hostname   | @@version | @@server_id |

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

| 22e169004583 | 5.5.62    |          55 |

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

Saturday, July 13, 2019

MySQL How do you restore tablespace

MySQL How do you restore tablespace?

This is not new information but I have not covered it much so addressing it now for those that need it.

If you lose your ibd files... you lose your data. So if you have a copy of one available.. or even if you are syncing from another database you can still import it. What/how do you lose tablespace?

Here is a simple example to recover tablespace.

 



mysql> Create database demo;



mysql> use demo;



mysql> CREATE TABLE `demotable` (

    ->   `id` int(11) NOT NULL AUTO_INCREMENT,

    ->   `dts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    ->   PRIMARY KEY (`id`)

    -> ) ENGINE=InnoDB;

Now we store some data...

 

mysql> INSERT INTO demotable (id) VALUES (NULL);

Query OK, 1 row affected (0.10 sec)



mysql> INSERT INTO demotable (id) VALUES (NULL);

Query OK, 1 row affected (0.08 sec)



mysql> SELECT * FROM demotable;

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

| id | dts                 |

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

|  1 | 2019-07-12 23:31:34 |

|  2 | 2019-07-12 23:31:35 |

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

2 rows in set (0.00 sec)

OK now lets break it..

 

# systemctl stop mysqld

# cd /var/lib/mysql/demo/

# ls -ltr

total 80

-rw-r-----. 1 mysql mysql 114688 Jul 12 23:31 demotable.ibd

# mv demotable.ibd /tmp/



# systemctl start mysqld

# mysql demo



mysql> show tables;

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

| Tables_in_demo |

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

| demotable      |

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

1 row in set (0.00 sec)



mysql> desc demotable;

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

| Field | Type      | Null | Key | Default           | Extra                                         |

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

| id    | int(11)   | NO   | PRI | NULL              | auto_increment                                |

| dts   | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |

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

2 rows in set (0.01 sec)



mysql> INSERT INTO demotable (id) VALUES (NULL);

ERROR 1812 (HY000): Tablespace is missing for table `demo`.`demotable`.

Broken and lost tablespace... Now we can recover it..

 

demo]# cp /tmp/demotable.ibd .



mysql> ALTER TABLE demotable DISCARD TABLESPACE;



demo]# cp /tmp/demotable.ibd .

demo]# ls -ltr

total 112

-rw-r-----. 1 root root 114688 Jul 12 23:50 demotable.ibd

demo]# chown mysql:mysql demotable.ibd

demo]# mysql demo

mysql> ALTER TABLE demotable IMPORT TABLESPACE;

ERROR 1034 (HY000): Incorrect key file for table 'demotable'; try to repair it



mysql> REPAIR TABLE demotable;

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

| Table          | Op     | Msg_type | Msg_text                                                |

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

| demo.demotable | repair | note     | The storage engine for the table doesn't support repair |

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

Notice now we also got another error.. This is usually tied to space available to tmpdir, and repair doesn't work for .ibd anyway.

 

mysql> select @@tmpdir;

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

| @@tmpdir |

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

| /tmp     |

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



# vi /etc/my.cnf

tmpdir=/var/lib/mysql-files/



# systemctl restart mysqld

# mysql demo

OK used the mysql-files directory just for example.
Now we can try again.

 

mysql> ALTER TABLE demotable IMPORT TABLESPACE;

Query OK, 0 rows affected, 1 warning (0.61 sec)



mysql>  INSERT INTO demotable (id) VALUES (NULL);

Query OK, 1 row affected (0.11 sec)



mysql>  SELECT * FROM demotable;

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

| id | dts                 |

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

|  1 | 2019-07-12 23:31:34 |

|  2 | 2019-07-12 23:31:35 |

|  3 | 2019-07-12 23:56:08 |

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

OK worked.
Now, this is all nice and simple if you just have one table. But what about 100s...

Automate it, of course, and use your information_schema to help.

Make a few more copies for test.

 

mysql> create table demotable1 like demotable;

Query OK, 0 rows affected (0.51 sec)



mysql> create table demotable2 like demotable;

Query OK, 0 rows affected (1.04 sec)



mysql> create table demotable3 like demotable;

Query OK, 0 rows affected (0.74 sec)



mysql> create table demotable4 like demotable;

Query OK, 0 rows affected (2.21 sec)

break them all..

 

demo]# mv *.ibd /tmp/

Now using your information_schema.tables table, you can build out all the commands you will need.

 

# vi build_discard.sql

# cat build_discard.sql

SELECT CONCAT(" ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," DISCARD TABLESPACE;  ") as CMD FROM information_schema.TABLES WHERE TABLE_SCHEMA='demo';



# vi build_import.sql

# cat build_import.sql

SELECT CONCAT(" ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," IMPORT TABLESPACE;  ") as CMD FROM information_schema.TABLES WHERE TABLE_SCHEMA='demo';

 

# mysql -N < build_import.sql > import_tablespace.sql

# mysql -N < build_discard.sql  | mysql demo



demo]# cp /tmp/*.ibd .

demo]# chown mysql:mysql *.ibd

# systemctl restart mysqld

# mysql demo < import_tablespace.sql

# mysql demo



mysql> INSERT INTO demotable (id) VALUES (NULL);

Query OK, 1 row affected (0.08 sec)



mysql> INSERT INTO demotable1 (id) VALUES (NULL);

Query OK, 1 row affected (0.05 sec)



mysql> INSERT INTO demotable2 (id) VALUES (NULL);

Query OK, 1 row affected (0.09 sec)



mysql> INSERT INTO demotable3 (id) VALUES (NULL);

^[[AQuery OK, 1 row affected (0.37 sec)



mysql> INSERT INTO demotable4 (id) VALUES (NULL);

Query OK, 1 row affected (0.12 sec)

And it worked.

Pages

Saturday, February 24, 2024

MariaDB Encryption ( data at rest )

Resources:

Thursday, November 12, 2020

Using your FRM file to get Schema and then import idb files..

Monday, September 21, 2020

MySQL mysql_config_editor & expect

Sunday, March 15, 2020

MySQL & Dockers...a simple set up

Saturday, July 13, 2019

MySQL How do you restore tablespace