Changing file locations.

[expired user #7404]'s profile image [expired user #7404] posted 10 years ago in General Permalink
Afternoon, folks. First post and a question about file locations. I created a new database and it was slam-dunked in my Program Files\MariaDB 10.0\data folder. Is it possible to have my database stored elsewhere so it gets caught by an end-of-day backup process? I search through all the preferences but couldn't see anything that would let me specify where databases are created. Is it possible? Tks.
ansgar's profile image ansgar posted 10 years ago Permalink
Yes, you can, though this is a server question, unrelated to HeidiSQL. You can set it per "datadir" option in your my.cnf or my.ini . See http://dev.mysql.com/doc/refman/5.1/en/windows-create-option-file.html
[expired user #7404]'s profile image [expired user #7404] posted 10 years ago Permalink
Ansgar, do you know of a forum that could help me with a server-related question? The reason I ask is that I changed the datadir location and it seemed to work on my home machine but doing the same at work the service fails to start. So, I uninstalled MariaDB and reinstalled it my work machine direct to my C: drive and not in program files. Everything starts up, I created a session and all seemed fine. I then copied the folder containing my database I created at home directly into the data folder. I started Heidi, logged into the session and it lists my database. When I double-click on the database, it shows an error for each table and it doesn't list the routine I created. The table error is like "Table 'tracedata.files' doesn't exist in engine."

I was hoping I could literally copy the database folder from one machine to another but perhaps doing so isn't the way to do it? Anyway, if you have any advice you can give me or point me to a forum where it could be answered more specifically I'd appreciate it. If I'm way off topic here please accept my apologies. :-)
ansgar's profile image ansgar posted 10 years ago Permalink
Copying the contents of the data directory is surely not the recommended way to do that. Anyway, if you do so, verify the following things:
* Stop both (!) servers, e.g. by typing "net stop mysql" in the command line.
* Use the same server version for the new server as in the old installation (the *exact* version, e.g. "MariaDB 5.1.69")
* Compare both my.ini/my.cnf files from the servers, and ensure all options are the same (except for file paths which can of course differ)
* Start the new or both servers again
[expired user #7404]'s profile image [expired user #7404] posted 10 years ago Permalink
Thanks Ansgar. I will look at that. I should have qualified it by saying I copied only that database folder and I was careful not to overwite anything else that existed in the data folder. If I open all the database files I can see reference to my comments and other things in the binary files so I'm happy I don't have an "empty" database.winkLet me try your suggestions and I will report back later. Thank you.
ansgar's profile image ansgar posted 10 years ago Permalink
By the way: The *recommended* way is to create a .sql dump from the old server's database, using mysqldump or HeidiSQL. Then, load that .sql file on the new server and execute it. Both things can be done via HeidiSQL, and no server must be stopped for these steps. This way is rock-stable and you don't have to mess neither with the server versions nor the config files. This is even easier than your file-copy approach.
[expired user #7404]'s profile image [expired user #7404] posted 10 years ago Permalink
I see what you mean and that could well be the better way. I just need to mention that my tables could well contain a substantial amount of data which is why I thought a file copy would be "safer" and perhaps, quicker as well. I'm sorry to say that I'm a newbie to Heidi and Mariadb! Let me create a quick database and see what happens when I export and re-import it.
ansgar's profile image ansgar posted 10 years ago Permalink
Hey, it's not a shame to be a newbie. Honestly, HeidiSQL aims to be best usable especially for newbies. If there is something too complicated in HeidiSQL please shout.
[expired user #7404]'s profile image [expired user #7404] posted 10 years ago Permalink
I will!

Please login to leave a reply, or register at first.