Mysql (win) Is it possible to get databases from multiple drives in the same time?

Question

My question would be, is there any way to read the mysql data folder from different drives in the same time (Windows) ?

For example: I have 1TB database from the drive C:, and another 1TB from the drive D:. So when I start mysql it will simply see both databases from both drives.

Answer 1

A given instance of MySQL can only use one datadir. Under the datadir, each MySQL schema lives in a subdirectory.

For example, the default datadir is C:\Program Files\MySQL\MySQL Server 8.0\data.

Schema1 would be under C:\Program Files\MySQL\MySQL Server 8.0\data\Schema1

Schema2 would be under C:\Program Files\MySQL\MySQL Server 8.0\data\Schema2

But if you could trick Windows into thinking that Schema2 subfolder actually uses storage from your physical drive D:, then you could achieve what you want.

One method is to use mklink as shown here: https://dev.mysql.com/doc/refman/8.0/en/windows-symbolic-links.html I would prefer this solution because it allows you to target a specific path on your D: drive.

A second option is to use virtual drives. Windows allows you to map drives to specific paths of another drive. Not many Windows users employ this option, they prefer to use drives as drive letters instead.

https://winaero.com/create-virtual-drive-from-a-folder-in-windows-10/

So you would use

SUBST D: C:\Program Files\MySQL\MySQL Server 8.0\data\Schema2

Read the article I linked to for more details.

Whichever solution you use, now files under Schema2 are automatically stored on your physical drive D:, and do not take up space on the physical drive C:.

What if you have already created the Schema2? So there is already a subdirectory for that schema's tables on C:. Well, you'll have to do some file juggling to make that work. I don't have a Windows machine so I can't test it or give advice on what the steps are.

Remember not to do any manual moving of MySQL tables around while the MySQL Server service is running. Stop the service first, or you will corrupt your data. Practice this sort of configuration on another server, not your important production data, and also make a backup of the whole database instance before you try anything.