Mysql – Changing Data Directory on MySQL 8

mysql-8.0

Need: To store / accesses dbTable data from a separate drive not the default

I was using MySQL 5.7 so far & as specified in the solution – Link1 I added the my.ini file with the info as stated & it worked fine.

I now uninstalled MySQL 5.7, installed MySQL 8, created a my.ini file & specified the info as stated in this solution Link2. But when I check it still points to the default C:\ drive & hence unable to access data from the existing database stored in E:\ drive

SHOW GLOBAL VARIABLES LIKE 'datadir';

shows

C:\ProgramData\MySQL\MySQL Server 8.0\Data\

The new my.ini file which I have created as below in

C:\Program Files\MySQL\MySQL Server 8.0 

[mysqld]
datadir = E:/MySqlData
secure-file-priv="E:/MySqlData/Uploads"

Hope the issue faced is clear, await inputs.

Edit:

I found this input – Link Could anyone let me know if this could be a reason ? Also any known hex editors for Windows 10 ?

Update: Even after removing the hex characters, the server still does not start if I point the parameter datadir in my.ini file to my database folder in another drive

Best Answer

Make sure the directory where you want to write the data files is accessable for MySQL. I put it the security settings on Everyone.

You might need to copy the data also from old data directory to the new one.

Related Solutions

MySQL restore lost data after update from 5.7 -> 8.0.11

I was finally successful restoring the data. My problem was, that I tried to restore the data with a docker-compose, which somehow didn't work.

So I took a different approach

1. Run a docker image of mysql:8 and mounted the "corrupt" folder to it

docker run -d -e MYSQL_ROOT_PASSWORD=root -v /path/to/corrupt/folder:/var/lib/mysql --name mrestore mysql:8

The mysql-server started without any problems and I was able to run mysql with

mysql -u root -p.

But when I run the command mysql>show databases; It gave me an error like:

The user specified as a definer ('mysql.infoschema'@'localhost') does not exist

2. Fix user does not exist error

A quick google search led me to the answer of someone having the same problem: https://stackoverflow.com/questions/49992868/mysql-errorthe-user-specified-as-a-definer-mysql-infoschemalocalhost-doe

Which is:

mysql -u root -p
mysql> SET GLOBAL innodb_fast_shutdown = 1;
mysql_upgrade -u root -p

3. Dump databse

Finally I was able to start mysql and dump the database that I thought I've lost

mysqldump -u root -p databasename > databasename.sql

MYSQL 8.0 server not found in Workbench directory in Windows 10

MySQL server and MySQL Workbench both are installed in the default location ("Program Files" for 64bit apps or "Program Files x86" for 32bit apps). MySQL Workbench however only exists as a 64bit arch build.

Did you check the server path actually exists? If it does not you may have forgotten to install the new server. Simply run the MySQL Windows installer again and let it install the server.

About the left behind ProgramData content: that's probably a safety measure to avoid deleting your data unwantedly. If you don't need it anymore then just remove the 5.7 subfolder.

Related Question