- MySQL/MariaDB - Moving Databases
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.24/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 E:\wamp64
Adapt your file or folder paths to suit your own installation.
--- Use databases elsewhere than in wamp/bin/mysql/mysql5.7.24/data
- Exit Wampserver
- Create a folder where you want to put your databases, e. g. G:\wwwwbases
- Copy *ALL* the files and *ALL* the folders of E:\wamp64\bin\mysql\mysql5.7.24\data\ in G:\wwwwbases\
Attention: Copy and not move. Otherwise no turning back is possible.
- Edit file E:\wamp64\bin\mysql\mysql5.7.24\my. ini
Replace:
datadir="J:/wamp/bin/mysql/mysql5.7.24/data"
by
;datadir="J:/wamp/bin/mysql/mysql5.7.24/data"
datadir="G:/wwwbases"
In this way, it is easy to go back if there is a problem.
- 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.25 is already installed in E:\wamp64\bin{mysql\mysql5.7.25.
Edit the file J\wamp\bin{mysql\mysql5.7.25\my.ini
Replace:
datadir="J:/wamp/bin/mysql/mysql5.7.25/data"
by
;datadir="J:/wamp/bin/mysql/mysql5.7.25/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.25
There should be no problem and the icon should go green again with MySQL 5.7.25 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 E:\wamp64\bin{mysqlql5.7.25\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:
E:\wamp64\bin\mysql\mysql5.7.25\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.
E:\wamp64\bin\mysql\mysql5.7.25\bin>
It's over, now you are ready to use your databases on MySQL 5.7.25.
Edited 1 time(s). Last edit at 01/11/2021 10:31AM by Otomatic.