MySQL - From .ibd file to table structure json file

Retrieving Table Structure from MySQL .ibd file on Windows.  

Note: Tested solution on MySQL version 8.1 and above.

----

You can achieve this using ibd2sdi utility tool comes with MySQL.

Step 1: Go to MySQL Bin directory and open a command line.

Example: C:\Program Files\MySQL\MySQL Server 8.1\bin 

Adjust above path based on MySQL version installed on your PC

Step 2: Copy your MySQL data directory to c:\Temp for safety reason.

That is copy C:\ProgramData\MySQL\MySQL Server 8.1\Data  directory to C:\Temp\data

Step 3: Run the below command 

C:\Program Files\MySQL\MySQL Server 8.1\bin>ibd2sdi C:\Temp\data\mydbname\mytablename.ibd

Note: Replace mydbname with your database name and mytablename with your table name.

Note: you can dump this json into a file. There are many other options you can try.

For more info: https://dev.mysql.com/doc/refman/8.4/en/ibd2sdi.html

You can also generate Json file from .ibd file.

Example: C:\Program Files\MySQL\MySQL Server 8.1\bin>ibd2sdi --dump-file=C:\Temp\mytablename.json C:\Temp\data\mydatabasename\mytablename.ibd

------

Some useful links for MySQL Crash troubleshooting:

MySQL Service stopped and unable to start

• https://docs.rackspace.com/docs/how-to-resolve-mysql-service-starting-and-stopping-unexpectedly
• After trying a lot, I concluded it's not worth spending more time on this option.

• https://dev.mysql.com/doc/refman/8.4/en/ibd2sdi.html
• https://www.percona.com/blog/mysql-8-frm-drop-how-to-recover-table-ddl/

------

Lesson learned take MySQL backup on a regular basis.

Select Top 10 rows in MySQL - MS SQL Top keyword equivalent in MySQL

If you are from MS SQL Server background and trying to write query with TOP keyword in MySQL it won't work.

MS SQL Server equivalent of Top keyword in MySQL is Limit


Example 1: Simple Select statement without where clause or order by clause
In MS SQL Server
SELECT Top 10 * 
FROM Customers;

MySQL Equivalent 
SELECT * 
FROM Customers
LIMIT 10;




Example 2: Select statement with where clause
In MS SQL Server
SELECT Top 10 * 
FROM Customers
Where City = 'New York';

MySQL Equivalent 
SELECT * 
FROM Customers
Where City = 'New York'
LIMIT 10;





Example 3: Select statement with where and order by clause
In MS SQL Server
SELECT Top 10 * 
FROM Customers
Where City = 'New York'
Order by CustomerID desc;

MySQL Equivalent 
SELECT * 
FROM Customers
Where City = 'New York'
Order by CustomerID desc
LIMIT 10;





Example 4: Paging Query
In MS SQL Server
SELECT *
FROM
(
    SELECT  
       CustomerID, 
       CustomerName, 
       City,
       ROW_NUMBER() OVER (ORDER BY Customers.CreationDate DESC) as RowNum
    FROM Customers
    Where City = 'New York'
)
WHERE RowNum BETWEEN (@iCurrentPageIndex * @iPageSize) + 1 AND (@iCurrentPageIndex * @iPageSize) + @iPageSize;

MySQL Equivalent 
SELECT * 
FROM Customers
Where City = 'New York'
LIMIT 10,20;