I am a newbie in the world of MySQL. In fact I enabled it in my Linux
box only because it is required to run WordPress (the blogging
software). I was trying to plan ahead and figure out a way to backup
(and restore) a database, should my Linux machine ever goes belly up. I
disovered that the databases are stored in subdirectories under
/var/lib/mysql.
My questions are basically three:
(1) Why /var/lib? Why not some /home/mysql or similar? - where one
expects to find data, not executables or libraries?
(2) Is it possible to configure my installation of MySQL to use some
other path for the databases? (e.g. /home/mysql or
/home/linuxlover/mysql)
(3) Is there an article or doc that outlines a good practice for
backing up - and restoring - a database without relying on a specific
MySQL configuration? Ideally, I would like to be able to copy a
database from one server to another... but if not possible, just
backup/restore to same server is good enough. A URL would be
appreciated.
BTW, I am running MySQL 4.1.14, phpMyAdmin - 2.8.0.4 on Fedora Core 4.
Thanks!
Lynn 5 15377
OK - regarding question #2, using phpMyAdmin I was able to click "Show
MySQL system variables" (left pane) and see that "/var/lib/mysql/" is
stored in a "server variable" named "bdb home".
Is there a way to change it?
If so, how?
Thanks,
Lynn li**************@yahoo.com wrote: I am a newbie in the world of MySQL. In fact I enabled it in my Linux box only because it is required to run WordPress (the blogging software). I was trying to plan ahead and figure out a way to backup (and restore) a database, should my Linux machine ever goes belly up. I disovered that the databases are stored in subdirectories under /var/lib/mysql.
My questions are basically three:
(1) Why /var/lib? Why not some /home/mysql or similar? - where one expects to find data, not executables or libraries?
(2) Is it possible to configure my installation of MySQL to use some other path for the databases? (e.g. /home/mysql or /home/linuxlover/mysql)
(3) Is there an article or doc that outlines a good practice for backing up - and restoring - a database without relying on a specific MySQL configuration? Ideally, I would like to be able to copy a database from one server to another... but if not possible, just backup/restore to same server is good enough. A URL would be appreciated.
BTW, I am running MySQL 4.1.14, phpMyAdmin - 2.8.0.4 on Fedora Core 4.
Thanks! Lynn
Oops... I was too quick on the trigger... Actually, there is another
variable that contains the value "/var/lib/mysql/" called "datadir".
I think that "datadir" is the variable I am looking to change.
Is it possible to modify it so that MySQL looks from now on at a new
path?
If so, is it possible to move existing databases to the newly pointed
path?
Thanks,
Lynn
linuxlover992...@yahoo.com wrote: OK - regarding question #2, using phpMyAdmin I was able to click "Show MySQL system variables" (left pane) and see that "/var/lib/mysql/" is stored in a "server variable" named "bdb home".
Is there a way to change it?
If so, how?
And the answer is... Yes it is possible.
All needed is:
1. Shudown mysqld daemon (/etc/init.d/mysqld stop)
2. Move /var/lib/mysql/ to /home/mysql/
3. Modify /etc/my.conf so that datadir=/home/mysql
4. Start mysqld daemon (/etc/init.d/mysqld start)
Simple.
However, I decided not to do so - for the sake of simplifying future
updates of Fedora or MySQL. I realized that it is easier to deal with
the arbitrariness of the databases location than with some uknown
future incompatibilities when upgrading.
Lynn li**************@yahoo.com wrote: Oops... I was too quick on the trigger... Actually, there is another variable that contains the value "/var/lib/mysql/" called "datadir".
I think that "datadir" is the variable I am looking to change.
Is it possible to modify it so that MySQL looks from now on at a new path? If so, is it possible to move existing databases to the newly pointed path? li**************@yahoo.com wrote: I realized that it is easier to deal with the arbitrariness of the databases location than with some uknown future incompatibilities when upgrading.
Good plan. Though it is arbitrary, it is a convention on Linux to use
/var for variable files, like logs, temp files, and databases.
For more information on this convention, see: http://en.wikipedia.org/wiki/Filesys...archy_Standard
Regards,
Bill K.
>I am a newbie in the world of MySQL. In fact I enabled it in my Linux box only because it is required to run WordPress (the blogging software). I was trying to plan ahead and figure out a way to backup (and restore) a database, should my Linux machine ever goes belly up. I disovered that the databases are stored in subdirectories under /var/lib/mysql.
My questions are basically three:
(1) Why /var/lib? Why not some /home/mysql or similar? - where one expects to find data, not executables or libraries?
FreeBSD puts it in /var/db/mysql. I think /var may be the most
appropriate place for data, depending on how tied to the system
MySQL is. For example, some people use MySQL for login authentication,
so you can't even log in without it, so it better be mounted soon.
/home may, in some installations, be a NFS filesystem and shared
between machines, something which might not be wanted for MySQL.
(2) Is it possible to configure my installation of MySQL to use some other path for the databases? (e.g. /home/mysql or /home/linuxlover/mysql)
I believe there are three approaches:
(1) set datadir in /etc/my.cnf, (preferred) or
(2) as a command line option to mysqld (sometimes useful for running
TWO instances of mysqld on the same system with different data
directories and ports), or
(3) Create a symlink /var/lib/mysql that points to whatever directory
you want used.
In all cases you want to move the previous contents of /var/lib/mysql
to the new location first, with the server shut down when you do it.
Be sure to preserve permissions and ownerships. Then restart the server.
(3) Is there an article or doc that outlines a good practice for backing up - and restoring - a database without relying on a specific MySQL configuration?
mysqldump is pretty good. It doesn't care about the binary format
of tables in different MySQL versions, or directory locations.
Ideally, I would like to be able to copy a database from one server to another... but if not possible, just backup/restore to same server is good enough. A URL would be appreciated.
mysqldump -h src.hostname.example.com ... | mysql -h dest.hostname.example.com ...
run on another host command.hostname.example.com (which might be the
same as one of the servers). Options to mysqldump specify what to dump
and whether it's to dump schema, data, or both, and there are some
options for making the output palatable to lower-version servers.
For more information, type "mysqldump --help | more".
The source and destination servers can be the same if you are
careful (e.g. copy database xyz to xyzbackup1 on the same server).
For this to work, you need:
- MySQL servers set up and running on src.hostname.example.com and
dest.hostname.example.com.
- MySQL client software set up on command.hostname.example.com.
- Login accounts for some user on command.hostname.example.com on the
servers src.hostname.example.com and dest.hostname.example.com,
with enough privileges to do what you are trying to do.
- Network connectivity between the hosts, including no blocking of
MySQL connections by firewalls.
BTW, I am running MySQL 4.1.14, phpMyAdmin - 2.8.0.4 on Fedora Core 4.
Gordon L. Burditt This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: George Augustino |
last post by:
REQUEST FOR DISCUSSION (RFD)
unmoderated group comp.databases.mysql
Newsgroup line:
comp.databases.mysql MySQL relational database system discussion group.
This is a formal Request For...
|
by: NAN Team |
last post by:
Dear Proponent,
Your proposal has been accepted and the C.F.V. has been waived. The newsgroup
was created for you. As an added bonus, we have also created additional
newsgroups in the alt and us...
|
by: Joshua Beall |
last post by:
Hi All,
I am working on a registration system, and one of the things I am going to
need to be able to do it setup the database tables for each event that I
have registration for. Normally I...
|
by: William Drew |
last post by:
REQUEST FOR DISCUSSION (RFD)
unmoderated group comp.databases.mysql
This is an invitation to discuss the following proposal to create newsgroup
comp.databases.mysql. Please note that YOU...
|
by: Tim Williams |
last post by:
I'm trying to write a simple python program to access a MySQL
database. I'm having a problem with using MySQLdb to get the results
of a SQL command in a cursor. Sometimes the cursor.execute works,...
|
by: Kai Li |
last post by:
Hi,
I know that Mysql handles large database very well, but there is a project
that requires more than 2000 small databases(about 20 talbes with a few
rows) to be created within a Mysql server....
|
by: James Alexander Starritt |
last post by:
I also posted similarly in mailing.database.mysql
I have created a rather large (60 table database) website dealio in PHP that
works with MS SQL Server, Oracle, MySQL and presumably any other...
|
by: com |
last post by:
Extreme Web Reports 2005 - Soft30.com
The wizard scans the specified MS Access database and records
information such
as report names, parameters and subqueries. ......
|
by: John Salerno |
last post by:
Hi everyone. I just installed MySQL on my home PC and I tested out the
show databases query, and it lists two:
information_schema
bbdatabank
The second one is one I created myself, but I...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |