Apache Friends Support Forum

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!?

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.

Hey thanks for the help but I figured it out by symlinking the database.

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

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

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 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...

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 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.