location of mysql database files?

[Rick Stevens]

Harold Hallikainen wrote:
>>Harold Hallikainen wrote:
>>
>>>>The only _reliable_ method for moving mysql data from machine to machine
>>>>is via mysqldump/mysqlrestore or by using the replication tools in the
>>>>later versions of mysql.  Moving the data files around is not
>>>>recommended--especially with a live database.  If you can stop it while
>>>>you move the binary files, then you're OK, but don't do it while mysql
>>>>is running.
>>>>
>>>>BTW, the default location for the data files is
>>>>
>>>>	/var/lib/mysql/name-of-database
>>>>
>>>>Inside each directory, the files are named *.frm, *.MYD, and *.MYI,
>>>>where the asterisks are replaced by the table names.  10 tables yields
>>>>30 files (a .frm, a .MYD and a .MYI for each)
>>>>
>>>
>>>
>>>
>>>I tried copying the mysql files over from the RH8 machine to the FC4,
>>>and
>>>it LOOKS like it worked, but the owners have changed from harold to root
>>>(I have to use -uroot in mysql to see the databases). Is user info and
>>>database ownership info in the same location as the databases? I may
>>>still
>>>go the export/import route, but I'm trying to use rsync to keep a backup
>>>server in sync with another.
>>
>>If you don't specify "-p" on the cp command, _new_ files will be created
>>with the UID/GID of the process doing the cp command.  Since these
>>files didn't exist on the FC4 machine and you did the "cp" as root,
>>the files got "root:root" ownership.
>>
>>As root, you can "chown -R harold:haroldsgroup /var/lib/mysql/dbname/*"
>>to set the ownerships back to harold (assuming the user harold is set up
>>on the machine).  The permissions regarding who's allowed to do what to
>>each database is stored in the "mysql" database:
>>
>>	# mysql
>>	> connect mysql
>>	> show tables\g
>>
>>I'd recommend getting the O'Reilly book, "MySQL & mSQL" by Yarger, Reese
>>and King (a.k.a. the "woodpecker" book).  It's pretty good.
> 
> 
> 
> THANKS for the help! I'll add the book to my wish list. I used rsync and
> told it to maintain ownership and permissions, so the files are owned by
> mysql on the FC4 machine, same as on the RH8 machine.
> 
> The problem is that on the RH8 machine, I can do
> 
>>mysql -uharold
>>show databases;
> 
> 
> and see all the databases.
> 
> On the FC4 machine, thus far I have to do
> 
>>mysql -uroot
>>show databases;
> 
> 
> to see them. Using the root user, I CAN see the tables, etc. in the
> databases, so it looks like they moved over ok, but mysql doesn't seem to
> know what user is supposed to have access to the databases.

Ah!  You need to add harold to the MySQL database:

	# mysql
	> connect mysql
	> INSERT INTO user VALUES
	> ('%', 'harold', password('pwd'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')\g

Replace "pwd" with harold's password.  If user harold doesn't have a
MySQL password, then replace the "password('pwd')" bit with just "''"
(two single quotes):

	> INSERT INTO user VALUES
	> ('%', 'harold', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 
'Y', 'Y', 'Y', 'Y')\g

> Time to buy the book?

A lot of this would be explained in there.  For example, this bit is
on, let's see (pulling book off shelf), pages 51-54 in the first edition
("Security" in chapter 4).
----------------------------------------------------------------------
- Rick Stevens, Senior Systems Engineer     rstevens at vitalstream.com -
- VitalStream, Inc.                       http://www.vitalstream.com -
-                                                                    -
- Do not taunt the sysadmins, for they are subtle and quick to anger -
----------------------------------------------------------------------