Change MySQL database directory on Windows?

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Change MySQL database directory on Windows?

Postby Gutterboy » 13. August 2009 12:41

Hey guys.... I tried to change the MySQL database directory to another drive but then mysql wouldn't start. :(

What I did was this..

my.cnf file

Code: Select all
# The MySQL server
[mysqld]
port= 3306
socket= "C:/xampp/mysql/mysql.sock"
basedir="C:/xampp/mysql"
tmpdir="K:/mysql/tmp"
datadir="K:/mysql/data"
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
ft_min_word_len=2
ft_max_word_len=15


I just noticed an error file that was placed in the "K:/mysql/data" directory... it reads as follows:

Code: Select all
090812  0:17:45  InnoDB: Started; log sequence number 0 43685
090812  0:17:45 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
090812  0:17:49  InnoDB: Started; log sequence number 0 43685
090812  0:17:49 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
090812  0:18:00  InnoDB: Started; log sequence number 0 43685
090812  0:18:00 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist


Any help!? :?:
Gutterboy
 
Posts: 7
Joined: 14. April 2009 16:51

Re: Change MySQL database directory on Windows?

Postby Izzy » 13. August 2009 19:01

Code: Select all
090812  0:18:00 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
Seems that the mysql databases are missing in the new location K:\mysql\data\ directory - did you copy over the mysql system database folders from C:\xampp\mysql\data\ to the new database directory location?

Good idea when relocating the database directory from it's original location to copy over all the existing database folders to the \data\ directory at the new location.
Last edited by Izzy on 13. August 2009 19:05, edited 1 time in total.
Izzy
 
Posts: 3344
Joined: 25. April 2006 17:06

Re: Change MySQL database directory on Windows?

Postby Gutterboy » 13. August 2009 19:04

Hey thanks for the help but I figured it out by symlinking the database. :)
Gutterboy
 
Posts: 7
Joined: 14. April 2009 16:51

Re: Change MySQL database directory on Windows?

Postby ar_raudy » 15. September 2010 07:38

what I must change in my.cnf file to change Mysql directory to other directory?
I am using ubuntu..

this's my some configuration in my.cnf

Code: Select all
[mysqld]
port      = 3306
socket      = /opt/lampp/var/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M


sorry, I'm newbie
ar_raudy
 
Posts: 1
Joined: 15. September 2010 07:24

Re: Change MySQL database directory on Windows?

Postby Gutterboy » 15. September 2010 11:19

Would you be happy in symlinking as I did myself? This is basically placing some small files in the current location that tells mysql where to find the actual database files.

If so.. here is how I did it:

Stop MySQL first..

My MySQL data files were all located in: C:\xampp\mysql\data\

So an example database would normally be located in: C:\xampp\mysql\data\example_db\ - where "example_db" is the folder that houses all the database files for that specific database.

Now, to move this database to a different location, I created a new mysql directory located at: K:\mysql\data\

I then moved the "C:\xampp\mysql\data\example_db\" folder & put it in that location.... so now I would have the directory "K:\mysql\data\example_db\" & the "C:\xampp\mysql\data\example_db\" would no longer exist.

Lastly I created a file with the same name as the database in my former mysql data directory, this file is called "example_db.sym" & would now be located at "C:\xampp\mysql\data\example_db.sym".

Inside this example_db.sym file all I would do would put the location of the new database folder..... so in this example it would contain the text "K:\mysql\data\example_db\".

Restart MySQL & that's it :)
Gutterboy
 
Posts: 7
Joined: 14. April 2009 16:51

Re: Change MySQL database directory on Windows?

Postby doojinsi » 13. October 2016 02:26

The symlink solution posted by Gutterboy does not work.
My platform Windows 10 and xampp 3.2.2.
I moved the folder the represented my database from c:\xampp\mysql\data to the target folder and created the recommended .sym file.
When I restarted MySQL and entered phpMyAdmin, the new database location was not available, could not be seen.
However, when I changed into the old data folder and made a symbolic link with
mklink /d mydb D:\data\mydb
this worked fine.

FYI
doojinsi
 
Posts: 2
Joined: 13. October 2016 00:14
XAMPP version: 3.2.2
Operating System: Windows 10

Re: Change MySQL database directory on Windows?

Postby Nobbie » 13. October 2016 11:17

doojinsi wrote:The symlink solution posted by Gutterboy does not work


Of course not, keep in mind that it has been posted in 2009, that means 7(!) years ago. Probably by using WindowsXP, meanwhile Microsoft rewrote the whole symlink code and replaced it by a sophisticated version. Now we have Windows10 and the year 2017 arises...
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Re: Change MySQL database directory on Windows?

Postby doojinsi » 13. October 2016 13:14

the MySQL manual at
https://dev.mysql.com/doc/refman/5.5/en/windows-symbolic-links.html
also states that creating the .sym solution should work so distance in time has nothing to do with this issue
What I'm really pointing out here is that in my particular setup (again, Win10, xampp 3.2.2 with Maria MySQL 10.1.13), creating the symlink with the textfile.sym extension was not viable
whereas the mklink solution did work.
Since I am relatively new to xampp, does anyone think I should upgrade to the latest xampp?
Would an updated version of MySQL in XAMPP (which includes MySQL 10.1.16) process the .sym file correctly?
I have a reason for wanting to use the .sym solution which is why I'm pursuing this issue.
doojinsi
 
Posts: 2
Joined: 13. October 2016 00:14
XAMPP version: 3.2.2
Operating System: Windows 10

Re: Change MySQL database directory on Windows?

Postby Nobbie » 13. October 2016 14:12

doojinsi wrote:the MySQL manual at
https://dev.mysql.com/doc/refman/5.5/en ... links.html
also states that creating the .sym solution should work so distance in time has nothing to do with this issue


You still dont get it. When .sym has been created (maybe since WindowsNT), MySQL was on Release 3 or 4, and it worked. But at some point, Microsoft "invented" another kind of symlink (i.e. they overtook it from Linux) and later on, MySQL 5.5 had been released. But in 2009 obviously the old Windows and the old MySQL Version worked with .sym. I am pretty sure that Gutterboy tested his solution and did not ly: "Here is how i did it".

As MySQL 5.5 is far older than WIndows10 (Windows10 is even not mentioned in the documentation above), Windows10 might have dropped .sym support. I dont think that old MySQL manuals are upgraded on each Windows Upgrade. I dont think that .sym support is a task of the program (like MySQL), it is part of the File System (i.e. part of the Kernel) of the Operating System. Same for the "new" symbolic link.
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 126 guests