How to Easily Migrate PHP Websites & MySQL Databases With SSL Setup to a Reliable Hosting Company
This article is written in the context of small business websites with traffic of less than 1,000 users per day.
Recently, I stumbled upon the challenge of migrating web hosting for one of my clients. The web application was built on PHP and had MySQL Database. Transferring PHP files to the new host was not as big of a challenge as it was to transfer the MySQL database. Here are the main challenges I had faced:
Hosting and MySQL Database Migration Challenges:
First of all, my web development team’s favorite database management tool “SQLYog” wouldn’t connect with the Database of the new hosting company (The very famous grid hosting provider). Secondly, it was giving permissions and access denied errors. Here is a sample of errors that were encountered:
Error Number: 1227
Error Message: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
The errors were popping up for SQL statements that were similar to following code (copied from MySQL Dump):
CREATE DEFINER=`user_name`@`%` FUNCTION `createMyFunction`(userId_1 BIGINT(20), field2 BIGINT(20), field3 TINYINT(2)) RETURNS int(11)
Also it has been a challenge for me to get hold of customer support. I would call them and would have to hold the line for up to 15 minutes, only to get a live agent asking for account identification and the call would drop before I could even start speaking. It happened thrice. I’m not sure whether it was just a bad day for me, or my phone connection or their customer support phone system has some issues. Finally, when I was able to speak with an agent just to learn that since I had a shared hosting account, they wouldn’t allow creating the stored procedures for my MySQL database. If we really needed this feature, our only option was to upgrade from shared hosting of $20 / month to VPS hosting of $30 to $50 / month. This was very upsetting. Also, causing us a delay of a few days for going live. My client didn’t want to spend that much on just web hosting, keeping in mind there were additional expenses involved like the domain, SSL certificate and static IP. The next section explains how I overcome these challenges.
Better Hosting Company allowed easy migration of MySQL Database and ease of setting up SSL:
Luckily an ex coworker suggested me to work with BlueHost.com, and to try their shared hosting package. It was much cheaper for just $4.95 / month so I have had my client buy this hosting for three years. They also gave us a dot com domain name for 1 year for free. Also setting up SSL was the easiest ever for a very competitive price of $49 / year.
It was all up and running in a matter of literally 10 minutes. My client just called the sales rep the next day after setting up initial hosting account, permitted him to charge the card on file to setup SSL and that was it. My site went down for just 5 minutes and in the next 5 minutes, I got the exclusive static IP and SSL up and running on my domain. Their process didn’t require me to generate any difficult to handle CSR Certificates or deal with putty software and command line scripting. It was all quick and automated, which was such a relief and quite effortless.
Coming back to MySQL Database, the BlueHost provided a simple yet popular hosting management system “cPanel”. There was an option of “Remote MySQL” under “Database Tools” which had shown me the IP and gave an option to add that IP on that page which I did. This has made my favorite SQLYog software possible to talk to my remote MySQL database on BlueHost. Through SQLYog, I could easily use the feature “Copy Database to Different Host/Database” by right-clicking on the database in the context menu as shown in the picture below:
Fig. 1.0: SQL Yog software showing option to copy database to different host/database
BlueHost allowed me to copy my entire database from previous hosting to BlueHost including the database functions and procedures by just making following minor modifications in my MySQL Dump file:
- Changed database name from “mydb_oldhost” to “mydb_bluehost”.
- Changed db user name from “user_oldhost” to “user_bluehost”.
- 3. Changed following clause at all occurrences in dump file:
- Replace: CREATE DEFINER= `user_oldhost`@`%`
- With: CREATE DEFINER=`user_bluehost`@`localhost`
- Actual database and user names are replaced with example names for confidentiality purpose.
- `user_oldhost`@`%` denotes that the user at previous host had root access. Even though
Bluehost didn’t allow database root access in shared hosting account. However, they did allow the stored procedures and I just had to define the access of new user at `user_bluehost`@`localhost` level instead of root and that is all I needed to get it working.
Finally, it was relatively easier to get hold of customer support at Bluehost via phone and there were also options available for live chat and email / ticket system via the control panel. I will highly recommend BlueHost to all my clients, colleagues and friends who are looking for a reliable web hosting company for relatively small scale websites (less than 1,000 users / day).
If you need help in building or migrating your website or mobile application on any popular platform such as iPhone, iPad, Android Phone and Tablets, Blackberry or Windows, just fill out the form below and I will be glad to help and make everything easier for you. Ciao!