Restoring Linux backup on Windows, lower_case_table_names

MySQL & MariaDB Percona XtraBackup
1

Versions are MySQL 8 and Xtrabackup 8.

We use Xtrabackup to create backups on Linux systems where lower_case_table_names is at the default value 0.
I am trying to restore such a Linux backup on a Windows machine and have run into conflicts related to lower_case_table_names settings.

I tried all three possibilities for lower_case_table_names on the Windows computer, but no luck:

lower_case_table_names=0 :
2020-02-14T10:09:38.533600Z 0 [ERROR] [MY-010158] [Server] The server option ‘lower_case_table_names’ is configured to use case sensitive table names but the data directory is on a case-insensitive file system which is an unsupported combination. Please consider either using a case sensitive file system for your data directory or switching to a case-insensitive table name mode.

lower_case_table_names=1 :
2020-02-14T10:18:35.080002Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server (‘1’) and data dictionary (‘0’).

lower_case_table_names=2
2020-02-14T10:19:38.280331Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server (‘2’) and data dictionary (‘0’).

I am aware that this is not directly an Xtrabackup question. The same issue would occur if I would simply copy the “data” directory from Linux to Windows.
But I was hoping that one of the Percona tools allows a conversion from a case sensitive MySQL database into one that can be used on Windows.

I know that using lower_case_table_names=1 on both platforms would solve the issue. But the Linux hosts are running production systems with customer data and therefore this is not an option.

Does anybody have an idea on how to migrate MySQL “data” from Linux to Windows? Possibly an option available with Xtrabackup?

2

I have found a solution in the meantime:

Since April 2018 Windows 10 supports case-sensitivity for the NTFS file system, even on a per-directory basis. Who would have thought.
So after decrypting and decompressing the backup and before starting MySQL on Windows, I put the “data” folder in place and use this Windows command:

After that MySQL can be started successfully with lower_case_table_names=0 or lower_case_table_names=2 (2 is the default for Windows, MySQL automatically switches to 0 if you configure 2).

Thank you Mr. Nadella. You have made earth a better place! :slight_smile:

3

Thanks for updating with the solution, it could well help others in the future so it’s greatly appreciated!
Please do come back to us if you encounter any other challenges with using Percona products.