Move mysql databases from one version of mysql to another on the same PC
Posted by: zaxatron (---.lnse6.win.bigpond.net.au)
Date: January 04, 2018 01:37PM

Hi Everyone,


I have just finished upgrading my wamp64 to the latest version 3.1.1.

Now I have existing databases under mysql located at C:\wamp64\bin\mysql\mysql5.7.14.

I want to use mysql v7.20 located at C:\wamp64\bin\mysql\mysql5.7.20.

Can someone illustrate to me the procedure on how to do this?

I want my wordpress websites databases managed under the latest version of mysql.


Thanks.


Regards


Zaxa

Re: Move mysql databases from one version of mysql to another on the same PC
Posted by: Otomatic (Moderator)
Date: January 04, 2018 03:44PM

Hi,

The so-called normal way of transferring databases from one version of MySQL to another version is the export/import via SQL files.
Nevertheless, a solution exists to be able to easily use your databases with MySQL upgrades. It is not to leave the said databases in wamp/bin/mysql/mysql/mysql5.7.14/data/ but to move them outside the server tree (Wampserver in this case), including the mysql database, i. e. with the defined users (including root) and any passwords.
Warning: this way of proceeding is only valid as long as you stay in the same branch of MySQL, i. e. always with versions 5.7.x and evolving version number. For example, in this case, from Mysql 5.7.14 to mysql 5.7.20 and with both versions of mysql in 32 bit or both versions in 64 bit. It is not possible to switch from a 32-bit version to a 64-bit version.

Note: Procedure explained with Wampserver installed in J:\wamp
Adapt your file or folder paths to suit your own installation.

A - Use databases elsewhere than in wamp/bin/mysql/mysql5.7.14/data
A0 -- Exit Wampserver
A1 -- Create a folder where you want to put your databases, e. g. G:\wwwwbases
A2 -- Copy *ALL* the files and *ALL* the folders of J:\wamp\bin\mysql\mysql5.7.14\data\ in G:\wwwwbases\
Attention: Copy and not move. Otherwise no turning back is possible.
A3 -- Edit file J:\wamp\bin{mysql\mysql5.7.14\my. ini
Replace:
datadir="J:/wamp/bin/mysql/mysql5.7.14/data"
by
;datadir="J:/wamp/bin/mysql/mysql5.7.14/data"
datadir="G:/wwwbases"
In this way, it is easy to go back if there is a problem.
A4 -- Launch Wampserver
Verify that MySQL works well and that your databases are accessible and usable.
If all goes well, we can continue.
We will assume that mysql 5.7.20 is already installed in J:\wamp\bin\mysql\mysql5.7.20.
Edit the file J\wamp\bin{mysql\mysql5.7.20\my.ini
Replace:
datadir="J:/wamp/bin/mysql/mysql5.7.20/data"
by
;datadir="J:/wamp/bin/mysql/mysql5.7.20/data"
datadir="G:/wwwbases"
Save the modified file.
With Wampserver running, switch to the MySQL version:
Left-click Wampmanager tray icon -> MySQL -> Version -> 5.7.20
There should be no problem and the icon should go green again with MySQL 5.7.20 as the active version.
Once there, you should avoid using databases immediately with this new version of MySQL.
Indeed, internal databases such as mysql or performance_schema or sys and your own databases may need to be updated in order to work properly with the new version of mysql.

This is why, with each version of MySQL it is provided a mysql_upgrade.exe program that allows to modify tables that require modifications after an update or evolution of MySQL.

This program MUST be run after each MySQL update or upgrade.

--- Execute mysql_upgrade.exe ---
The MySql server must be started; in principle, Wampserser is started there, so MySQL is started.
The procedure must be done in a command window pointing to J:\wamp\bin\mysqlql5.7.20\bin, and you're lucky because with Wampserver 3.1.1, you can do it with two clicks:
- Right-click icon Wampmanager -> Tools -> Command Windows -> Command window MySQL/bin

Arrived here it is necessary to know if you have or not put a password to root@localhost

if password, type
mysql_upgrade.exe --user=root --host=localhost --password=mypassword

if no password, type
mysql_upgrade.exe --user=root --host=localhost --password=

and wait for the procedure to be completed.
You get a result like that:
J:\wamp\bin\mysql\mysql5.7.20\bin>mysql_upgrade.exe --user=root --host=localhost --password=mypass
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
....
Checking databases.
agb.alex_livre_ban                                 OK
agb.alex_livre_censure                             OK
agb.alex_livre_config                              OK
agb.alex_livre_img_verif_add                       OK
....
Upgrade process completed successfully.
Checking if update is needed.

J:\wamp\bin\mysql\mysql5.7.20\bin>

It's over, now you are ready to use your databases on MySQL 5.7.20.

------------------------------------------------------------------------------------------------------------
Wampserver 3.1.4 32 bit - Apache 2.4.35 - PHP 7.2.11/7.1.23/7.0.32/5.6.38 - MySQL 5.7.23 - MariaDB 10.3.10
Wampserver 3.1.4 64 bit - Apache 2.4.35 - PHP 7.2.11/7.1.23/7.0.32/5.6.38 - MySQL 5.7.23 - MariaDB 10.3.10
PhPMyadmin 4.8.3 - MysqlDumper 1.24.5
on W10 and W7 Pro 64 bit
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Re: Move mysql databases from one version of mysql to another on the same PC
Posted by: zaxatron (---.lnse6.win.bigpond.net.au)
Date: January 06, 2018 12:15AM

Thank you very much.

Excellent, detailed explanation!

Re: Move mysql databases from one version of mysql to another on the same PC
Posted by: Otomatic (Moderator)
Date: January 06, 2018 10:16AM

Hi,

Whenever I give a procedure, it has been tried and verified in real life.
Otherwise, I always specify: Attention, not checked.

------------------------------------------------------------------------------------------------------------
Wampserver 3.1.4 32 bit - Apache 2.4.35 - PHP 7.2.11/7.1.23/7.0.32/5.6.38 - MySQL 5.7.23 - MariaDB 10.3.10
Wampserver 3.1.4 64 bit - Apache 2.4.35 - PHP 7.2.11/7.1.23/7.0.32/5.6.38 - MySQL 5.7.23 - MariaDB 10.3.10
PhPMyadmin 4.8.3 - MysqlDumper 1.24.5
on W10 and W7 Pro 64 bit
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Re: Move mysql databases from one version of mysql to another on the same PC
Posted by: ciprianmp (86.124.168.---)
Date: January 07, 2018 10:11AM

Oto, just to understand, this procedure works for upgrading to a new version of MySQL, then you can keep the entire db outside the specific data folder, but there is no downgrading (like if you want to switch to an older version, for testing/dev purposes), right?

Ciprian M.

Re: Move mysql databases from one version of mysql to another on the same PC
Posted by: Otomatic (Moderator)
Date: January 07, 2018 10:25AM

Hi,

> but there is no downgrading...
mysql_upgrade.exe only works for version upgrades (To a higher version number) and should not be used for version downgrades.
In the case of downgrades, the only way is to export/import databases in SQL file.

------------------------------------------------------------------------------------------------------------
Wampserver 3.1.4 32 bit - Apache 2.4.35 - PHP 7.2.11/7.1.23/7.0.32/5.6.38 - MySQL 5.7.23 - MariaDB 10.3.10
Wampserver 3.1.4 64 bit - Apache 2.4.35 - PHP 7.2.11/7.1.23/7.0.32/5.6.38 - MySQL 5.7.23 - MariaDB 10.3.10
PhPMyadmin 4.8.3 - MysqlDumper 1.24.5
on W10 and W7 Pro 64 bit
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Sorry, only registered users may post in this forum.