CODEWITHSUNDEEP
mysql
user734560
user734560

Reputation:

Changing the data folder of MySQL database

I tried to change the data folder path of MySQL by changing the datadir parameter in the my.ini file. But when I restarted MySQL service again I get the following error :

Error 1067: the process terminated unexpectedly.

I checked the .err file, the following errors are present: (they are all about InnoDB) 

120319  3:59:59 [Note] Plugin 'FEDERATED' is disabled.  
120319  3:59:59 InnoDB: The InnoDB memory heap is disabled  
120319  3:59:59 InnoDB: Mutexes and rw_locks use Windows interlocked functions  
120319  3:59:59 InnoDB: Compressed tables use zlib 1.2.3  
120319  3:59:59 InnoDB: Initializing buffer pool, size = 75.0M  
120319  3:59:59 InnoDB: Completed initialization of buffer pool  
InnoDB: Error: log file .\ib_logfile0 is of different size 0 99614720 bytes  
InnoDB: than specified in the .cnf file 0 39845888 bytes!  
120319  3:59:59 [ERROR] Plugin 'InnoDB' init function returned error.  
120319  3:59:59 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.  
120319  3:59:59 [ERROR] Unknown/unsupported storage engine: INNODB  
120319  3:59:59 [ERROR] Aborting

Upvotes: 1

Views: 753

Answers (2)

theking2
theking2

Reputation: 2783

Is this on Windows? If so one could use a VHD as storage device for MariaDB/MySQL. I've done so to prevent loss of data whenever I install or update the DB software (in Wamp, Xampp, Mamp, Wampserver etc.)

In order to do so I created a VHD with diskpart and mount it in boot time to the data folder in the respective MariaDB or MySQL folder (moving the original to data.orig first and copying it back as soon the device is running)

I use this script to mount the vhd at boot:

diskpart /s "C:\ProgramData\DB\connectvdisk\attach_mariadb-data.diskpart"

where attach_mariadb-data.diskpart contains

select vdisk file="c:\ProgramData\DB\mariadb-daten.vhdx"
attach vdisk

and the vhdx was set to mount to the proper location in Disk Manager.

The script is executed at boot time (trigger: At Startup) with Task Scheduler running cmd.exe with the following as parameter

/c "C:\ProgramData\DB\connectvdisk\connect-vhd.cmd"

I hijacked the ProgramData folder for this kind of storage. On some systems I've moved the scripts and device to secondary storage (D:) to be sure it survives a OS reinstall.

This continues to work on Windows 11

Upvotes: 0

Ashwin A
Ashwin A

Reputation: 3857

You have to specify the right size for: innodb_log_file_size=95M in your my.cnf or my.ini. It is specified as 38M currently I guess.

Also check from your data folder as how many ib_logfiles you have. If you have more than 2, specify also: innodb_log_files_in_group=2.

Upvotes: 1

Related Questions