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.  

• https://downloads.mysql.com/archives/utilities 
• https://dev.mysql.com/downloads/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;



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

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.

• https://docs.docker.com/docker-for-mac/install/
• https://docs.docker.com/docker-for-windows/

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 |
+--------------+-----------+-------------+