Harold Hallikainen wrote:

>I'm SLWLY bringing up a new server and moving stuff from the old to the
>new. My next issue is moving over mysql database files. If someone knows
>off the top of their head what they're called and where they are, it'd
>save me a bunch of research. They are currently buried deep in an RH8
>machine, and I'll be moving them to an FC4 machine.
>
>THANKS!
>
>Harold
>
>

The files are stored in /var/lib/mysql. You will find one directory of
fields for each database you have created.

You would probably be better off if you used mysqldump to create an
ascii dump of the database, moved that to the new server and then use
mysqlimport to import the data to the new database.

Carl.

Redhat-install-list mailing list
Redhat-install-list (AT) redhat (DOT) com

To Unsubscribe Go To ABVE URL or send a message to:
redhat-install-list-request (AT) redhat (DOT) com
Subject: unsubscribe

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 with a live database. If you can stop it while
>>you move the binary files, then you're K, 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 LKS 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 'Reilly book, "MySQL & mSQL" by Yarger, Reese
and King (a.k.a. the "woodpecker" book). It's pretty good.
- Rick Stevens, Senior Systems Engineer rstevens (AT) vitalstream (DOT) com -
- VitalStream, Inc. http://www.vitalstream.com -
- -
- Brain: The organ with which we think that we think. -

Redhat-install-list mailing list
Redhat-install-list (AT) redhat (DOT) com

To Unsubscribe Go To ABVE URL or send a message to:
redhat-install-list-request (AT) redhat (DOT) com
Subject: unsubscribe

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 with a live database. If you can stop it while
you move the binary files, then you're K, 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 LKS 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 '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.

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 INT 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 INT 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 (DOT) com -
- VitalStream, Inc. http://www.vitalstream.com -
- -
- Do not taunt the sysadmins, for they are subtle and quick to anger -

Redhat-install-list mailing list
Redhat-install-list (AT) redhat (DOT) com

To Unsubscribe Go To ABVE URL or send a message to:
redhat-install-list-request (AT) redhat (DOT) com
Subject: unsubscribe