Recently I was attempting to import a large (136mb) SQL backup for a new client.
I first created a database and db user in PHPMYADMIN (most current version).
Then using my Windows workstation, I launched MySQL Workbench 6.1 (my favorite tool for importing/exporting) and tried to do a Server–>Data Import.
Pointed it to the 136mb file and first had a problem:
17:45:03 Restoring Z:\whateverdatabase.sql Running: mysql.exe --defaults-file="c:\users\aaa\appdata\local\temp\tmp8pbfwe.cnf" --host=192.168.x.x --user=root --port=3306 --default-character-set=utf8 --comments --database=btb-old < "Z:\\whateverdatabase.sql" ERROR 2006 (HY000) at line 44: MySQL server has gone away
Operation failed with exitcode 1
17:45:04 Import of Z:\whateverdatabase.sql has finished with 1 errors
Want an easy solution for this? It turns out there is a limit in the MySQL database for the size of imports…
Run this SQL statement against the MySQL database:
SET GLOBAL max_allowed_packet=250x1024x1024;
In my case, I picked a size (250mb) (above) that is larger than my SQL import.
Execute that script.
Re-run the import. Works perfectly!
17:47:42 Restoring Z:\whateverdatabase.sql
Running: mysql.exe --defaults-file="c:\users\aaa\appdata\local\temp\tmpwpesfe.cnf" --host=192.168.x.x --user=root --port=3306 --default-character-set=utf8 --comments --database=btb-old < "Z:\\whateverdatabase.sql"
17:50:18 Import of Z:\whateverdatabase.sql has finished
I recently crossed my fingers and went through the upgrade process to upgrade MySQL from 5.6.14 to 5.6.17 on my Windows server. (also works for 5.5.x to current)
NOTE – UPDATED 5/27/2016: If you are using the MYSQL Installer – Community (if that is in your Start -> program list, please use that to upgrade you instance. That is much simpler than my process below. The process below should now be considered a LEGACY process.
Don’t forget to run
mysql_upgrade -u root -p
to allow MySQL to upgrade all the tables to the new standard.
Through trial and error, I have found the easiest process to do this.
- stop the existing service via services
- command prompt (as administrator)
- change to the MySQL directory, BIN, and then type in mysqld –remove (to remove the service)
- If the above command does not work, try using SC DELETE MYSQL (put the exact service name in place of MYSQL). That should do the trick.
- go into the service manage and refresh and ensure the service is removed.
- go to the control panel and uninstall everything to do with MySQL (presuming this is on a dedicated MySQL box)
- make sure your data is moved to a directory on the root like “MYSQLDATA”
- delete every folder under programdata and program files pertaining to MySQL
- reboot the machine
- log into machine
- make sure .NET Framework 4.0 is installed
- download and run the MySQL Community 5.6.17 (or later) x32 MSI full installer
- do a fresh install
- Server type: Server (for production deployments)
- point things to the correct directories (e.g. your existing data directory, etc.)
- use the same root password as before (on the old install)
- add any extra admin users
- complete the install
- make sure the service is complete
- That’s it!
hope that helps someone else out there. I took the couple of hours to sort this all out for you!
Here is the easiest process that I know of for migrating (and upgrading) from one version of MYSQL running on Windows server, to a new Windows server.
- Setup the new Windows server, give it a fixed IP address (we’ll change that later to be at the same IP as the old server).
- Run all Windows updates
- Install IIS 7.5 (in our example, we were on W2K8 R2 Standard) and take all defaults.
- Go to Google and search for MICROSOFT PLATFORM INSTALLER, go to that Microsoft site, and run the installer. The current version as of this post was 4.0RC
- in the MPI, search for PHP and install PHP. Allow it to take any extras it chooses, hit next and install those items.
- Now that PHP is installed, launch the IIS 7.5 manager, stop the default site and setup a new website that we’ll use to park PHPMYADMIN on.
- Go download the latest version of PHPMYADMIN.
- extract it to your website directory into the /phpmyadmin/ directory.
- you should now be able to access the PHPMYADMIN site via http://localhost/phpmyadmin/
- Download and install the latest MSI installer download (of the x64 version if you have an x64 server)
- Install MYSQL
- Run the configuration wizard and complete the standard setup. In our case we used a dedicated MYSQL server, set a new password for the admin account and pretty much took all defaults.
- At this pount we just need to migrate the data over from the old server to the new server.
- Make sure you have MYSQL Workbench installed to do this (under windows). Current version is 5.2.30 CE.
- Under “Server Administration” you need to setup one connection for the old (current server) and one for the new one.
- Connect to the old server.
- Click Data Export.
- Export out ALL databases to one SQL file. This may take some time depending on the speed of your internet connection.
- when this is complete, go check the file to make sure it is (relatively) large depending on the number of databases that you exported out. MAKE SURE you export out the MYSQL database as well as all databases.
- Connect to the new server.
- Do a DATA IMPORT/RESTORE and load the SQL file you just exported
- Start the import. Again, this may take some time to complete.
- When complete, the new machine is now a clone of the old machine.
- Shut down the old server.
- Change the IP on the new server to the IP of the old server. Reboot (just to be sure).
- Once the new server is rebooted and it is at the “old IP” any of your sites that connect to MYSQL should now be working once again just as if they were connecting to the old server.
I hope this helps anyone who is in need of migrating/upgrading their Windows-based MYSQL install.