It is easy to make a backup of a MySql database and import the export again into another MySql database. However, when the backup is made on a Windows or Mac machine and is then used on a Linux machine, it is possible problems arise due to the fact that filenames/tablenames are case sensitive on the Linux OS but the export on Windows/Mac does not preserve case. Some propose other solutions – passing a parameter when starting the database or upgrading to a more recent version of MySql – to solve this problem. However, because I had no control over the database to take the backup from, I had to use the below method which just takes the backup file and a text file in which all tablenames are present with their case set right, and then uses the second file to simply replace all lowercase occurences in the first file with the version with the proper case.

Making a backup of a Mysql database and restoring it

Making a backup of a MySql database is straightforward. I am giving the statements below for a Mac, but on Windows the same mysqldump and mysql commands can be used from the bin folder of the MySql installation.

First navigate to your MySql installation folder. This will probably be something like:

cd /usr/local/mysql

And then run mysqldump:

sudo ./bin/mysqldump -u <username> -p <password> testdatabase > /development/testdatabase.sql

where <username> is your mysql username and <password> is your mysql password.

This will generate a sql script in the development folder that is able to recreate all tables and indexes and reinsert all data that are available in the testdatabase, by running the following command on the target server:

sudo ./bin/mysql testdatabase < /development/testdatabase.sql

assuming the testdatabase.sql was first sent to the development folder on the target server by using the scp command or some graphical client.

However, when running the above mysqldump command on Mac or Windows, all tablenames are stored in lowercase in the target sql script file by default. When it is then imported on a Linux server, all tablenames are in lowercase as well.

This is a problem, since the case of the tablenames matters on Linux machines. It is possible that applications – written in PHP, Java or something else – that will make use of the database expect some or all of these tablenames to be in upper case or have its first letter capitalized. These applications will then not run correctly.

Case sensitivity: the problem when migrating a MySql database from Windows/Mac to Linux

Hence, it is necessary the cases are preserved in the generated sql script. The answer to this Stackoverflow question suggests to restart the mysql database on the Mac/windows server with the lower_case_table_names parameter set to 0. For more information about this, please check the Mysql documentation about the lower_case_table_names parameter.

However, you might not have the permissions to take the database offline, restart it with this parameter and then run mysqldump again. You are then stuck with the script with only lower case table names. I think the only option is then to edit the script and replace all lower case occurences with the version with the proper case.

The method that I describe below is just a quick way to do this using Java.

Getting the list of tablenames with their case right

Most applications – such as Java web applications (using JPA/Hibernate for example), and Drupal and WordPress installations – are able to automatically create the database tables they need if they are missing. Hence, you can run the application once and make sure the tables are generated. You then open a MySql terminal.

On the Linux machine, you would run the following from the mysql installation bin folder:

mysql --user=<user> --password=<password>

After which you will get the mysql terminal.
Assuming the tables were already generated by the web application in the testdatabase database, you run:

mysql> show tables from testdatabase;

Which in my case renders:

+--------------------------------+
| Tables_in_testdatabase         |
+--------------------------------+
| Account_                       |
| Address                        |
| AnnouncementsDelivery          |
| AnnouncementsEntry             |
| AnnouncementsFlag              |
| AssetCategory                  |
| AssetCategoryProperty          |
| AssetEntries_AssetCategories   |
| AssetEntries_AssetTags         |
| AssetEntry                     |
| AssetLink                      |
| AssetTag                       |
| AssetTagProperty               |
| AssetTagStats                  |
| AssetVocabulary                |
| BlogsEntry                     |
| BlogsStatsUser                 |
| BookmarksEntry                 |
| BookmarksFolder                |
| BrowserTracker                 |
| CalEvent                       |
| Chat_Entry                     |
| Chat_Status                    |
| ClassName_                     |
| ClusterGroup                   |
| Company                        |
| Contact_                       |
| Counter                        |
| Country                        |
| CyrusUser                      |
| CyrusVirtual                   |
| DLFileEntry                    |
| DLFileRank                     |
| DLFileShortcut                 |
| DLFileVersion                  |
| DLFolder                       |
| EmailAddress                   |
| ExpandoColumn                  |
| ExpandoRow                     |
| ExpandoTable                   |
| ExpandoValue                   |
| Group_                         |
| Groups_Orgs                    |
| Groups_Permissions             |
| Groups_Roles                   |
| Groups_UserGroups              |
| IGFolder                       |
| IGImage                        |
| Image                          |
| JournalArticle                 |
| JournalArticleImage            |
| JournalArticleResource         |
| JournalContentSearch           |
| JournalFeed                    |
| JournalStructure               |
| JournalTemplate                |
| Layout                         |
| LayoutPrototype                |
| LayoutSet                      |
| LayoutSetPrototype             |
| ListType                       |
| Lock_                          |
| MBBan                          |
| MBCategory                     |
| MBDiscussion                   |
| MBMailingList                  |
| MBMessage                      |
| MBMessageFlag                  |
| MBStatsUser                    |
| MBThread                       |
| Mail_Account                   |
| Mail_Attachment                |
| Mail_Folder                    |
| Mail_Message                   |
| MembershipRequest              |
| OpenSocial_Gadget              |
| OrgGroupPermission             |
| OrgGroupRole                   |
| OrgLabor                       |
| Organization_                  |
| PasswordPolicy                 |
| PasswordPolicyRel              |
| PasswordTracker                |
| Permission_                    |
| Phone                          |
| PluginSetting                  |
| PollsChoice                    |
| PollsQuestion                  |
| PollsVote                      |
| Portlet                        |
| PortletItem                    |
| PortletPreferences             |
| QUARTZ_BLOB_TRIGGERS           |
| QUARTZ_CALENDARS               |
| QUARTZ_CRON_TRIGGERS           |
| QUARTZ_FIRED_TRIGGERS          |
| QUARTZ_JOB_DETAILS             |
| QUARTZ_JOB_LISTENERS           |
| QUARTZ_LOCKS                   |
| QUARTZ_PAUSED_TRIGGER_GRPS     |
| QUARTZ_SCHEDULER_STATE         |
| QUARTZ_SIMPLE_TRIGGERS         |
| QUARTZ_TRIGGERS                |
| QUARTZ_TRIGGER_LISTENERS       |
| RatingsEntry                   |
| RatingsStats                   |
| Region                         |
| Release_                       |
| ResourceAction                 |
| ResourceCode                   |
| ResourcePermission             |
| Resource_                      |
| Role_                          |
| Roles_Permissions              |
| SCFrameworkVersi_SCProductVers |
| SCFrameworkVersion             |
| SCLicense                      |
| SCLicenses_SCProductEntries    |
| SCProductEntry                 |
| SCProductScreenshot            |
| SCProductVersion               |
| SN_MeetupsEntry                |
| SN_MeetupsRegistration         |
| SN_WallEntry                   |
| ServiceComponent               |
| Shard                          |
| ShoppingCart                   |
| ShoppingCategory               |
| ShoppingCoupon                 |
| ShoppingItem                   |
| ShoppingItemField              |
| ShoppingItemPrice              |
| ShoppingOrder                  |
| ShoppingOrderItem              |
| SocialActivity                 |
| SocialEquityAssetEntry         |
| SocialEquityGroupSetting       |
| SocialEquityHistory            |
| SocialEquityLog                |
| SocialEquitySetting            |
| SocialEquityUser               |
| SocialRelation                 |
| SocialRequest                  |
| Subscription                   |
| TasksProposal                  |
| TasksReview                    |
| Team                           |
| Ticket                         |
| UserGroup                      |
| UserGroupGroupRole             |
| UserGroupRole                  |
| UserIdMapper                   |
| UserTracker                    |
| UserTrackerPath                |
| User_                          |
| Users_Groups                   |
| Users_Orgs                     |
| Users_Permissions              |
| Users_Roles                    |
| Users_Teams                    |
| Users_UserGroups               |
| Vocabulary                     |
| WSRP_WSRPConsumer              |
| WSRP_WSRPConsumerPortlet       |
| WSRP_WSRPProducer              |
| WebDAVProps                    |
| Website                        |
| WikiNode                       |
| WikiPage                       |
| WikiPageResource               |
| WorkflowDefinitionLink         |
| WorkflowInstanceLink           |
| blocked_user                   |
| deleted_message                |
| private_message                |
| read_message                   |
+--------------------------------+
176 rows in set (0.55 sec)

The above is a list of tablenames for the Liferay 6.0 portal and migrating these tables is a case in which I actually used the method described here. I stripped the list of all pipes(|) and saved the list of tablenames into a txt file, one tablename per line.

If you cannot generate the list of tablenames with the right case automatically like above, you will need to manually create this file, severely slowing up the method used in this tutorial.

Setting the case right with some Java code

The below Java class takes the testdatabase.sql file and the tablenames.txt file as input and writes the sql script with the cases set right to a testdatabase-converted.sql file:

public class SetCaseRight {

	private static List<String> readFile(String fileName){
		try{
			List<String> lines = new ArrayList<String>();
			FileInputStream fstream = new FileInputStream(fileName);
			DataInputStream in = new DataInputStream(fstream);
			BufferedReader br = new BufferedReader(new InputStreamReader(in));
			String strLine;
			while ((strLine = br.readLine()) != null)   {
				lines.add(strLine);
			}
			in.close();
			return lines;
		}catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}

	private static String readFileAsString(String fileName){
        try {
			StringBuffer fileData = new StringBuffer(1000);
			BufferedReader reader = new BufferedReader(new FileReader(fileName));
			char[] buf = new char[1024];
			int numRead=0;
			while((numRead=reader.read(buf)) != -1){
			    String readData = String.valueOf(buf, 0, numRead);
			    fileData.append(readData);
			    buf = new char[1024];
			}
			reader.close();
			return fileData.toString();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
    }

	private static void writeStringToFile(String s, String fileName){
		try {
			BufferedWriter out = new BufferedWriter(new FileWriter(fileName));
			out.write(s);
			out.close();
		}
		catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args){
		String sqlSourceFile;
		String tableNamesTxtFile;
		String sqlTargetFile;
		if (args.length >= 1){ sqlSourceFile = args[0];} else { sqlSourceFile = "testdatabase.sql";}
		if (args.length >= 2){ tableNamesTxtFile = args[1];} else { tableNamesTxtFile = "tablenames.txt";}
		if (args.length >= 3){ sqlTargetFile = args[2];} else { sqlTargetFile = "testdatabase-converted.sql";}
		String sql = getSql(sqlSourceFile);
		for (String tableName: readTableNames(tableNamesTxtFile)){
			sql = sql.replaceAll("`(?i)" + tableName.trim()+"`", "`"+tableName.trim()+"`");
		}
		writeStringToFile(sql, sqlTargetFile);
	}

	public static String getSql(String sqlSourceFile){
		return readFileAsString(sqlSourceFile);
	}

	public static List<String> readTableNames(String tableNamesTxtFile){
		return readFile(tableNamesTxtFile);
	}
}

It is also possible to run the above class command line – without having to compile it yourself – by using this SetCaseRight class file:

java SetCaseRight testdatabase.sql tablenames.txt testdatabase-converted.sql

in a folder where java is on the PATH and where the first argument is the sqlTargetFile, the second argument is the tableNamesTxtFile and the third argument is the sqlTargetFile.

The resulting sql file is then ready to be imported on the Linux server.