How To Move MariaDB Database Files After Upgrading Version

I just upgraded from MariaDB 10.1.x to 10.6.x and wanted to move some of my old databases over to the new version.

Typically you would use a database dump or the MariaDB upgrade wizard, but none of those would work for me.

I'm also running it on a Windows 10 machine, so a lot of the instructions don't apply.

I moved the files from C:/Program Files/MariaDB 10.1/data to ...MariaDB 10.6/data but that wasn't working.

When I fired up HeidiSQL, the tables looked like they were there, but the databases were showing as zero bytes.

Navigating to any of the tables brought up "This view probably contains an error in its code. SQL Error (1932): Table 'xyz.wp_posts' doesn't exist in engine."

Thankfully, the solution is pretty simple, even though it took me a few hours to figure it out.

How to move MariaDB database files after upgrading version on Windows

1. Open command prompt as administrator
2. Stop MariaDB with "net stop mariadb"
3. Copy the databases from the old /data to the new /data
4. Replace ibdata1 in the new /data with the one in the old /data
5. Restart MariaDB with "net start mariadb"

Yeah, it's that simple.

This assumes you have already installed MariaDB and the new version is working properly.

Let's go through the steps below.

Open Command Prompt as Administrator

To start and stop MariaDB, you need to run Command Prompt as an administrator. You'll get an access denied error message if you try to do it with the regular command prompt.

In the search bar, type "cmd".

You should see the Command Prompt app listed. Click "Run as administrator".

You should see an administrator command prompt. You'll know it's correct because the title bar will say "Administrator: Command Prompt" rather than "Command Prompt".

If you can't open Command Prompt as an administrator, you may not have sufficient permissions on the account you're using. In that case, you'll need to log in with a different account, or perhaps you have an IT manager who's locked you out.

Stop MariaDB

Type "net stop mariadb" (without the quotation marks) and hit Enter.

It may take a few seconds, but it should say "The MariaDB service was stopped successfully". That's when you know you're ready to proceed to the next step.

If you get the message "The MariaDB service is not started", that's ok, you can proceed to the next step. You just need it to not be running or it won't let you replace the ibdata1 file.

If you try to stop MariaDB with the regular Command Prompt, you'll get the error message "System error 5 has occurred. Access is denied." Just go to the previous step and open Command Prompt as an administrator.

Copy the databases

The database files will be in /MariaDB 10.1/data/, where "10.1" is the old version you had running.

My files were installed in C:/Program Files/MariaDB 10.1/data/, but you may have installed MariaDB somewhere else.

Each folder in /data/ is a unique database from your previous installation.

Just copy the folders you want to keep from the old folder to the new folder. If you're anything like me, there are a few in there that you don't need any more. This works fine with just copying over the ones you need. However if you want to copy them all over and delete the un-needed ones in HeidiSQL, that's probably even better.

Replace ibdata1

The step I was missing that caused the databases to show empty, broken tables was to copy ibdata1 from the old /data folder to the new /data folder.

Open the old folder, copy ibdata1.

Paste it in the new folder.

You'll get a message that "The destination already has a file named 'ibdata1'".

Select "Replace the file in the destination".

That should be all you need to do. However, if you haven't stopped MariaDB, you'll get a message "The action can't be completed because the folder or a file in it is open in another program".

Just open a command prompt as an administrator and stop MariaDB using the steps above.

Then just hit "Try Again" and it should copy correctly.

Start MariaDB

To actually use the databases, you'll need to restart MariaDB.

Go back to the Administrator Command Prompt, or open a new one.

Type "net start mariadb" without the quotation marks.

It will take a few seconds, but you should see "The MariaDB service was started successfully."

Now you're all set to use your old databases in the new MariaDB installation.

If you open HeidiSQL now, you should see your databases as they should be, with all the tables accessible, showing all the rows and data that you are expecting.

If you open HeidiSQL and get the error message "Can't connect to MySQL server on '127.0.0.1' (10061)", it likely means you haven't restarted MariaDB. Just follow the steps above.