You’ve made a mistake. You pulled off the MySQL folder from an old server instead of doing a mysqldump. You put the old files (*.frm, *.myd, *.myi) into a newer server running MySQL 8.0 or above, and none of the databases work. Is this a disaster? No, there is a way out of this mess. You will need a third computer to act as a host for a Virtual Machine that you can use to convert the MyISAM files (*.frm, *.myd, *.myi) to InnoDB (*.idb). Remember to download your MySQL folder to this host computer.
Step 1) Download Ubuntu 16.04.6 LTS https://old-releases.ubuntu.com/releases/ to the host.
Step 2) Download VirualBox https://www.virtualbox.org/wiki/Downloads to the host.
Step 3) Install VirtualBox on the host.
Step 4) Create a new Virtual Machine with the Ubuntu ISO you downloaded in Step 1. Create Shared folders on this VM to access your old MySQL files. For detailed instructions, go here https://ubuntu.com/tutorials/how-to-run-ubuntu-desktop-on-a-virtual-machine-using-virtualbox#1-overview
Step 5) Start the Ubuntu VM and log in.
Step 6) In the terminal, write the following commands:
sudo add-apt-repository 'deb http://archive.ubuntu.com/ubuntu trusty universe' sudo apt-get update apt-get install mysql-server-5.7
This will ensure you get an older version of MySQL.
Step 7) If you are not in the sudoers group of your VM, type the following in the terminal:
su -
The password is the same password you used to setup the Ubuntu VM. Now type the following:
adduser <my-username> sudo reboot
Redo Step 6
Step 8) This step is very important; only copy over your database folders to /var/lib/mysql. Exclude the original mysql folder from the old server. Including the mysql folder will overwrite the MySQL configuration of the VM. Here is an example of the old database folders:
Animals Moves_Like_Jagger mysql She_will_be_loved
Exclude the mysql folder from the list because it contains old configuration information for your old server.
Copying can be done using use the following command:
sudo cp -R /media/sf_Shared_Folder/Animals /var/lib/mysql sudo cp -R /media/sf_Shared_Folder/Moves_like_Jagger /var/lib/mysql ... sudo chown -R mysql:mysql /var/lib/mysql
Step 9) Run this command:
mysql -u root -p
Enter your MySQL password, which you set up when you installed MySQL server in Step 6. If you are not able to log into a MySQL shell, use these instructions to reset a MySQL root password: https://stackoverflow.com/questions/16556497/how-to-reset-or-change-the-mysql-root-password
Step 10) Run the following command in the MySQL shell:
SHOW DATABASES;
You should see all the databases you imported to the VM.
Step 11) Exit MySQL shell by typing quit or exit.
Step 12) In the terminal, use the following command for each database you want to move to the new server:
mysqldump -u root -p Animals > Animals.sql
Look here https://stackoverflow.com/questions/13484667/export-mysql-dump-from-command-line for detailed instructions.
Step 13) Copy the SQL files to a Shared folder.
Step 14) Upload the SQL files to the new server.
Step 15) Do the following to import the SQL files to the MySQL database on the new server:
mysql -u mysqluser -p CREATE DATABASE Animals; CREATE DATABASE Moves_like_Jagger ... exit mysql -u mysqluser -p Animals < Animals.sql mysql -u mysqluser -p Moves_like_jagger < Moves_like_Jagger.sql ...
Where mysqluser is the user id for an account that can edit the MySQL databases.
