Where does mysql put its databases - and what are they called?
I need to find a database! :-) On my Slackware installation I had (well, still have if I start it) a database called 'addresses'. I want to copy it across to the Fedora installation so that mysql on Fedora can see the database. However I can't find the database. I've tried 'locate addresses' but nothing there looks remotely like a database file (or files). I suspect that mysql buries all of its databases in one big file somewhere but what's it called? Thinking about it maybe what I need to do is boot up the Slackware installation and export the addresses database from mysql, then import it into the Fedora installation. Alternatively maybe I could just copy the whole of the mysql data across over the top of the Fedora files which only have the basics and a root user at the moment. -- Chris Green
On 2 Oct 2007, at 2:12 pm, Chris G wrote:
I need to find a database! :-)
On my Slackware installation I had (well, still have if I start it) a database called 'addresses'. I want to copy it across to the Fedora installation so that mysql on Fedora can see the database. However I can't find the database.
I've tried 'locate addresses' but nothing there looks remotely like a database file (or files).
I suspect that mysql buries all of its databases in one big file somewhere but what's it called?
On debian in /var/lib/mysql is a folder for each database, which then has a bunch of files in it that relate to the database. However, I wouldn't suggest copying the databases this way, better to dump them from the old install and then import them on the new: $ mysqldump -u dbuser -p dbname > dbname-$(date +"%Y%d%m").sql $ mysql -u dbuser -p dbname < dbname-$(date +"%Y%d%m").sql (Creating the database first, obviously) Thanks, David -- David Reynolds david@reynoldsfamily.org.uk
On Tue, Oct 02, 2007 at 02:20:53PM +0100, David Reynolds wrote:
On 2 Oct 2007, at 2:12 pm, Chris G wrote:
I need to find a database! :-)
On my Slackware installation I had (well, still have if I start it) a database called 'addresses'. I want to copy it across to the Fedora installation so that mysql on Fedora can see the database. However I can't find the database.
I've tried 'locate addresses' but nothing there looks remotely like a database file (or files).
I suspect that mysql buries all of its databases in one big file somewhere but what's it called?
On debian in /var/lib/mysql is a folder for each database, which then has a bunch of files in it that relate to the database.
However, I wouldn't suggest copying the databases this way, better to dump them from the old install and then import them on the new:
$ mysqldump -u dbuser -p dbname > dbname-$(date +"%Y%d%m").sql
$ mysql -u dbuser -p dbname < dbname-$(date +"%Y%d%m").sql
(Creating the database first, obviously)
Ah, thank you, *that's* where they are. I didn't find them using locate because I wasn't running locate as root. I've copied the whole old /var/lib/mysql directory over the top of the new one (having shut down mysqld first) and that seems to have worked pretty well, all the databases are now available on my new system. Thanks again! -- Chris Green
On Tuesday 02 October 2007 14:12:07 Chris G wrote:
I've tried 'locate addresses' but nothing there looks remotely like a database file (or files).
I suspect that mysql buries all of its databases in one big file somewhere but what's it called?
$ grep datadir /etc/mysql/my.cnf datadir = /var/lib/mysql
Thinking about it maybe what I need to do is boot up the Slackware installation and export the addresses database from mysql, then import it into the Fedora installation. Alternatively maybe I could just copy the whole of the mysql data across over the top of the Fedora files which only have the basics and a root user at the moment.
In my experience, you can just copy MySQL database directories and they work. It may not work for you though. Cheers, Richard -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Richard Lewis http://www.richard-lewis.me.uk/ JID: ironchicken@jabber.earth.li -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- +-------------------------------------------------------+ |Please avoid sending me Word or PowerPoint attachments.| |http://www.gnu.org/philosophy/no-word-attachments.html | +-------------------------------------------------------+
Richard Lewis wrote:
In my experience, you can just copy MySQL database directories and they work. It may not work for you though.
It's supposed to work (ie MySQL do state that this is "allowed", even across platforms. I've never had any problems. Of-course its wise to make sure that the database server is not writing to the files when you copy them! Shutting down the database is the easiest way for personal use. If moving files between MySQL versions a REPAIR TABLES is worthwhile after the copy. Mark Rogers
participants (4)
-
Chris G -
David Reynolds -
Mark Rogers -
Richard Lewis