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


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.

MySQL Binlogs:: How to recover

So I realized I had not made a post about this after this situation that recently came up.

Here is the scenario: A backup was taken at midnight, they used MySQL dumps per database. Then at ten am the next day the database crashed. A series of events happened before I was called in, but they got it up to a version of the database with MyISAM tables and the IBD files missing from the tablespace.

So option 1, restoring from backup would get us to midnight and we would lose hours of data. Option 2, we reimport the 1000's of ibd files and keep everything. Then we had option 3, restore from backup, then apply the binlogs for recent changes.

To make it more interesting, they didn't have all of the ibd files I was told, and I did see some missing. So not sure how that was possible but option 2 became an invalid option. They, of course, wanted the least data loss possible, so we went with option 3.

To do this safely I started up another instance of MySQL under port 3307. This allowed me a safe place to work while traffic had read access to the MyISAM data on the port 3306 instance.

Once all the backup dump files uncompressed and imported into the 3307 instance I was able to focus on the binlog files.

At first this concept sounds much harder risky than it really is. It is actually pretty straight forward and simple.

So first you have to find the data your after. A review of the binlog files gives you a head start as to what files are relevant. In my case, somehow they managed to reset the binlog so the 117 file had 2 date ranges within it.

First for binlog review, the following command outputs the data in human-readable format.

mysqlbinlog --defaults-file=/root/.my.cnf  --base64-output=DECODE-ROWS  --verbose mysql-bin.000117 >   review_mysql-bin.000117.sql


*Note... Be careful running the above command. Notice I have it dumping the file directly in same location as binlog. So VALIDATE that your file name is valid.  This mysql-bin.000117.sql is different than this mysql-bin.000117 .sql  . You will loose your binlog with the 2nd option and a space before .sql.

Now to save the data so it can be applied. Since I had several binlogs I created a file and I wanted to double-check the time ranges anyway.


mysqlbinlog --defaults-file=/root/.my.cnf --start-datetime="2019-07-09 00:00:00" --stop-datetime="2019-07-10 00:00:00" mysql-bin.000117 > binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf mysql-bin.000118 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf mysql-bin.000119 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --start-datetime="2019-07-10 00:00:00" --stop-datetime="2019-07-10 10:00:00" mysql-bin.000117 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --stop-datetime="2019-07-10 10:00:00" mysql-bin.000120 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --stop-datetime="2019-07-10 10:00:00" mysql-bin.000121 >> binlog_restore.sql

mysql --socket=/var/lib/mysql_restore/mysql.sock -e "source /var/lib/mysql/binlog_restore.sql"



Now I applied all the data from those binlogs for the given time ranges. The client double-checked all data and was very happy to have it all back.

Several different options existed for this situation, this happened to workout best with the client.

Once the validated all was ok on the restored version it was a simple stop both databases, moved the data directories (wanted to keep the datadir defaults intact) , chown the directories just to be safe and start up MySQL. Now the restored instance was up on port 3306.