[gentoo-user] MySQL Upgrade

[gentoo-user] MySQL Upgrade

[C. Beamer]

Hi all,

I just upgraded MySQL using the instructions on the Gentoo website.  For
the most part, everything went fine and now everything works, but I had
to make a couple of adjustments.  My question now is if there is a
proper way to do what I did as a workaround.

In September, when I wiped Fedora Core off my main system and installed
Gentoo, I had a MySQL database that I used for one reason and one reason
only.  To get my database files from the FC4 system to Gentoo, I just
copied them to a ZIP drive.  When I installed MySQL, in /var/lib/mysql/
I created a directory with the name of my database and copied my
database related files into that directory.  Then I set up the users
allowed to access to the database.  This worked fine and is the
workaround that I had to do today after upgrading MySQL.

The instructions on the Gentoo website for upgrading gave a step by step
to create a backup of my database.  However, when I went to restore the
database after the upgrade, the restore didn't work.  I'm thinking that
it was because of the way I got my database files into MySQL when I
initially installed MySQL under Gentoo.

Is there a way to rectify this so that the backup will work for future
upgrades?

Regards,

Colleen
-- 
gentoo-user@gentoo.org mailing list

Re: [gentoo-user] MySQL Upgrade

[Qian Qiao]

On 10/30/05, C. Beamer <cbeamer@interlynx.net> wrote:
> Hi all,
>
> I just upgraded MySQL using the instructions on the Gentoo website.  For
> the most part, everything went fine and now everything works, but I had
> to make a couple of adjustments.  My question now is if there is a
> proper way to do what I did as a workaround.
>
> In September, when I wiped Fedora Core off my main system and installed
> Gentoo, I had a MySQL database that I used for one reason and one reason
> only.  To get my database files from the FC4 system to Gentoo, I just
> copied them to a ZIP drive.  When I installed MySQL, in /var/lib/mysql/
> I created a directory with the name of my database and copied my
> database related files into that directory.  Then I set up the users
> allowed to access to the database.  This worked fine and is the
> workaround that I had to do today after upgrading MySQL.
>
> The instructions on the Gentoo website for upgrading gave a step by step
> to create a backup of my database.  However, when I went to restore the
> database after the upgrade, the restore didn't work.  I'm thinking that
> it was because of the way I got my database files into MySQL when I
> initially installed MySQL under Gentoo.
>
> Is there a way to rectify this so that the backup will work for future
> upgrades?

What was the error message, if it was about the key length exceeding
1000, then you hit a known bug.

-- Joe

--
There are 3 kinds of people in the world:
Those who can count, and those who can't.

-- 
gentoo-user@gentoo.org mailing list

Re: [gentoo-user] MySQL Upgrade

[C. Beamer]

Qian Qiao wrote:

>>The instructions on the Gentoo website for upgrading gave a step by step
>>to create a backup of my database.  However, when I went to restore the
>>database after the upgrade, the restore didn't work.  I'm thinking that
>>it was because of the way I got my database files into MySQL when I
>>initially installed MySQL under Gentoo.
>>
>>Is there a way to rectify this so that the backup will work for future
>>upgrades?
>>    
>>
>
>What was the error message, if it was about the key length exceeding
>1000, then you hit a known bug.
>  
>
No, I didn't get any error messages on restoring the database that was
created by the backup process that was included in the instructions. 
Also, the process for fixing the grant tables was fine.  However, when I
started mysql and tried to use the database, I was told that the
database didn't exist and upon checking, I discovered that the only
database that was there was "test".

So, in essence, the restore *did* work, but the backup just didn't pick
up my database.

Regards,

Colleen
-- 
gentoo-user@gentoo.org mailing list

Re: [gentoo-user] MySQL Upgrade

[Qian Qiao]

On 10/30/05, C. Beamer <cbeamer@interlynx.net> wrote:
> Qian Qiao wrote:
> >What was the error message, if it was about the key length exceeding
> >1000, then you hit a known bug.
> >
> >
> No, I didn't get any error messages on restoring the database that was
> created by the backup process that was included in the instructions.
> Also, the process for fixing the grant tables was fine.  However, when I
> started mysql and tried to use the database, I was told that the
> database didn't exist and upon checking, I discovered that the only
> database that was there was "test".
>
> So, in essence, the restore *did* work, but the backup just didn't pick
> up my database.

So the mysqldump you did on the 4.0 didn't dump the databases other
than test, hmmm.

--
There are 3 kinds of people in the world:
Those who can count, and those who can't.

-- 
gentoo-user@gentoo.org mailing list

Re: [gentoo-user] MySQL Upgrade

[C. Beamer]

Qian Qiao wrote:

>On 10/30/05, C. Beamer <cbeamer@interlynx.net> wrote:
>  
>
>>Qian Qiao wrote:
>>    
>>
>>>What was the error message, if it was about the key length exceeding
>>>1000, then you hit a known bug.
>>>
>>>
>>>      
>>>
>>No, I didn't get any error messages on restoring the database that was
>>created by the backup process that was included in the instructions.
>>Also, the process for fixing the grant tables was fine.  However, when I
>>started mysql and tried to use the database, I was told that the
>>database didn't exist and upon checking, I discovered that the only
>>database that was there was "test".
>>
>>So, in essence, the restore *did* work, but the backup just didn't pick
>>up my database.
>>    
>>
>
>So the mysqldump you did on the 4.0 didn't dump the databases other
>than test, hmmm.
>  
>

That's correct.  And as stated in my original post, I think it might
have something to do with the way I got the database into Gentoo - a
mkdir named after the database and then copy the related database files
into the directory.

Regards,

Colleen
-- 
gentoo-user@gentoo.org mailing list

Re: [gentoo-user] MySQL Upgrade

[Qian Qiao]

On 10/31/05, C. Beamer <cbeamer@interlynx.net> wrote:
> Qian Qiao wrote:
> >
> >So the mysqldump you did on the 4.0 didn't dump the databases other
> >than test, hmmm.
> >
> >
>
> That's correct.  And as stated in my original post, I think it might
> have something to do with the way I got the database into Gentoo - a
> mkdir named after the database and then copy the related database files
> into the directory.

Try dump and restore them one by one, if you are able to use them, you
should be able to dump them.

Documentations on mysqldump can be found at:
http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html

HTH.

-- Joe


--
There are 3 kinds of people in the world:
Those who can count, and those who can't.

-- 
gentoo-user@gentoo.org mailing list

Re: [gentoo-user] MySQL Upgrade

[Jeff Smelser]

[-- Attachment #1: Type: text/plain, Size: 1857 bytes --]

On Sunday 30 October 2005 05:15 pm, C. Beamer wrote:

> In September, when I wiped Fedora Core off my main system and installed
> Gentoo, I had a MySQL database that I used for one reason and one reason
> only.  To get my database files from the FC4 system to Gentoo, I just
> copied them to a ZIP drive.  When I installed MySQL, in /var/lib/mysql/
> I created a directory with the name of my database and copied my
> database related files into that directory.  Then I set up the users
> allowed to access to the database.  This worked fine and is the
> workaround that I had to do today after upgrading MySQL.
>
> The instructions on the Gentoo website for upgrading gave a step by step
> to create a backup of my database.  However, when I went to restore the
> database after the upgrade, the restore didn't work.  I'm thinking that
> it was because of the way I got my database files into MySQL when I
> initially installed MySQL under Gentoo.

The problem here, is your not giving us enough to say what really went wrong 
with the restore. If you have innodb tables, for example, you can't just 
restore them in all cases, without tweaking things a bit.. Your just assuming 
what the issue was, and telling us, then saying we should tweak things based 
off something you really dont know..

I have never had a major problem restoring a db. I have had to make tweaks in 
MANY cases, however.. So I would get the error message, then we can discuss 
how to fix your case. 

That step by step guide really isnt gonna work in all situations.. Nothing 
personal to anyone, but there are too many situations out there, and the 
backup/restore in mysql isnt really the best.. Besides, I have never had 
problems with just upgrading, doing the upgrade guide what mysql says.. I 
have never restored anything to upgrade.

Jeff

[-- Attachment #2: Type: application/pgp-signature, Size: 189 bytes --]

Re: [gentoo-user] MySQL Upgrade

[C. Beamer]

Hi,

Jeff Smelser wrote:

>On Sunday 30 October 2005 05:15 pm, C. Beamer wrote:
>
>  
>
>>The instructions on the Gentoo website for upgrading gave a step by step
>>to create a backup of my database.  However, when I went to restore the
>>database after the upgrade, the restore didn't work.  I'm thinking that
>>it was because of the way I got my database files into MySQL when I
>>initially installed MySQL under Gentoo.
>>    
>>
>
>The problem here, is your not giving us enough to say what really went wrong 
>with the restore. If you have innodb tables, for example, you can't just 
>restore them in all cases, without tweaking things a bit.. Your just assuming 
>what the issue was, and telling us, then saying we should tweak things based 
>off something you really dont know..
>  
>
Nothing went wrong with the restore per se.  And you're right, I am
assuming the problem.

A better description would have been that the restore worked.  However,
the only database that was restored was the test database.  The database
that I had created by creating a directory that was named after the
database that I had created when using FC4 and then copying my database
related files into it, did not back up.  Hence my assumption that
because the database was *not* created in MySQL under Gentoo, perhaps
this was the reason that the backup did not "pick up" (for lack of a
better description) this database.

This is not a real big deal.  I can perform the same workaround that I
did this time.  I was just curious if there was a way to fix this so
that my database would back up properly.

A suggestion was made that I try to dump this database using mysqldump. 
It was stated that if I can use the database, which I can, I should be
able to dump it.  I haven't had a chance to try this yet.

Regards,

Colleen

-- 
gentoo-user@gentoo.org mailing list

Re: [gentoo-user] MySQL Upgrade

[Jeff Smelser]

[-- Attachment #1: Type: text/plain, Size: 812 bytes --]

On Tuesday 01 November 2005 04:58 pm, C. Beamer wrote:

> A better description would have been that the restore worked.  However,
> the only database that was restored was the test database.  The database
> that I had created by creating a directory that was named after the
> database that I had created when using FC4 and then copying my database
> related files into it, did not back up.  Hence my assumption that
> because the database was *not* created in MySQL under Gentoo, perhaps
> this was the reason that the backup did not "pick up" (for lack of a
> better description) this database.

How were you backing/restoring it from, if you were not using mysqldump then? 
I have done the above several times, and its always backed it up. 

Trying to figure out what went wrong here..

Jeff

[-- Attachment #2: Type: application/pgp-signature, Size: 189 bytes --]

Re: [gentoo-user] MySQL Upgrade

[C. Beamer]

Jeff Smelser wrote:

>On Tuesday 01 November 2005 04:58 pm, C. Beamer wrote:
>
>  
>
>>The database
>>that I had created by creating a directory that was named after the
>>database that I had created when using FC4 and then copying my database
>>related files into it, did not back up.  Hence my assumption that
>>because the database was *not* created in MySQL under Gentoo, perhaps
>>this was the reason that the backup did not "pick up" (for lack of a
>>better description) this database.
>>    
>>
>
>How were you backing/restoring it from, if you were not using mysqldump then? 
>I have done the above several times, and its always backed it up.
>  
>

This is probably more of an explanation than you require, but I'm going
to give it anyway.  :-)  Back a couple of years ago, I was looking for a
database program that I could use in Linux.  The database that I'm
speaking about in this thread was originally created in Microsoft
Access.  At that time, it was the last hurdle that kept me regularly
going into Windows on my home computer.

I read a bit about MySQL and since I had taken a basic DB2 course a
number of years ago, I figured that I could get the database into MySQL
by exporting it as a text file and then using that to create the MySQL
database.  So, that tells you that I am not a database guru by any means.

Up until now, what I needed to do in MySQL, I could do just fine and
never even knew about mysqldump until recently.  As I previously stated,
I've only been using Gentoo since September.  When I used to use FC4,
all I ever did was make a copy of the database files that had the
extensions of .MYI, .MYD and .frm and then when I needed to because of
an upgrade where I wiped my system, I would just create a directory in
the appropriate place that was named after the database and then copy
these files back into the directory.  Then, all I would have to do was
set up the appropriate permissions for access and I could use the
database.  This is how I did it to get the database into Gentoo
originally as well.

This works and is not a big deal.  As I said, I'm just curious if there
is a way to fix things so that my database will be backed up properly
with the mysqldump process that is run when upgrading.  As I also
stated, I haven't had the chance to try dumping the existing database
alone using mysqldump.

Regards,

Colleen
-- 
gentoo-user@gentoo.org mailing list

Re: [gentoo-user] MySQL Upgrade

[kashani]

C. Beamer wrote:
> This works and is not a big deal.  As I said, I'm just curious if there
> is a way to fix things so that my database will be backed up properly
> with the mysqldump process that is run when upgrading.  As I also
> stated, I haven't had the chance to try dumping the existing database
> alone using mysqldump.

	If the data is available within Mysql, then it can be dumped. I'd start 
looking at which options you used on the dump and even at the dump 
itself as the source of your problems. How you got the data into Mysql 
in the first place should not matter. If you look at the dump itself I'd 
pay special attention to the create lines. The usual mistake is not to 
have specified the create statements which means everything gets created 
as a table in a single database assuming you don't have any name space 
collisions.

Normally I do the following for a new install.

on the old server or install
mysqldump -u root -p --opt -Q > mysql-20051102.txt
	Not a super fancy dump, but if you're all text this should work fine.

on the new server or install
emerge mysql
/usr/bin/mysql_install_db
/etc/init.d/mysql start
/usr/bin/mysqladmin -u root password 'temppass'
mysql -u root -p
drop database test;
drop database mysql;
exit
mysql -u root -p < mysql-20051102.txt
mysql -u root -p
flush privileges;
exit

kashani
-- 
gentoo-user@gentoo.org mailing list

Re: [gentoo-user] MySQL Upgrade

[Jeff Smelser]

[-- Attachment #1: Type: text/plain, Size: 1646 bytes --]

On Wednesday 02 November 2005 12:52 pm, C. Beamer wrote:

> Up until now, what I needed to do in MySQL, I could do just fine and
> never even knew about mysqldump until recently.  As I previously stated,
> I've only been using Gentoo since September.  When I used to use FC4,
> all I ever did was make a copy of the database files that had the
> extensions of .MYI, .MYD and .frm and then when I needed to because of
> an upgrade where I wiped my system, I would just create a directory in
> the appropriate place that was named after the database and then copy
> these files back into the directory.  Then, all I would have to do was
> set up the appropriate permissions for access and I could use the
> database.  This is how I did it to get the database into Gentoo
> originally as well.

Well, this works for myisam, and few other, but not innodb.. Obviously you 
dont have it then..

> This works and is not a big deal.  As I said, I'm just curious if there
> is a way to fix things so that my database will be backed up properly
> with the mysqldump process that is run when upgrading.  As I also
> stated, I haven't had the chance to try dumping the existing database
> alone using mysqldump.

Well, then you would be more interested in mysqlhotcopy.. That pretty much 
does what your doing..

Mysql INC has yet to make a real backup/restore procedure for mysql. So 
mysqldump or mysqlhotcopy is pretty much it. If you use innodb, I would go 
after ibbackup. (not free).

I have been on a few developers about it, just doesnt seem high priority to 
them. (or I am not talking to the right ones).   

Jeff

[-- Attachment #2: Type: application/pgp-signature, Size: 189 bytes --]

Re: [gentoo-user] MySQL Upgrade

[kashani]

Jeff Smelser wrote:

> Well, then you would be more interested in mysqlhotcopy.. That pretty much 
> does what your doing..
> 
> Mysql INC has yet to make a real backup/restore procedure for mysql. So 
> mysqldump or mysqlhotcopy is pretty much it. If you use innodb, I would go 
> after ibbackup. (not free).
> 
> I have been on a few developers about it, just doesnt seem high priority to 
> them. (or I am not talking to the right ones).   

	Because once you start caring about your db that much it's usually 
easier to setup a crap box that replicates off your master. Then you can 
lock the whole db on your slave and do a mysqldump without causing 
issues on your main db regardless of which table type you're using. You 
also get the added benefit of a hot backup and no I/O hits on the main db.

	I'm managing five fairly busy Mysql servers so my tactics might not be 
those of the average user making the above a bit of overkill. I was very 
excited about Mysql's instance manager. However if your database has 
known periods of time when it isn't being used, ie you're using it as an 
Access replacement. Lock the tables, do the dump, free the tables, and 
save your cash for flaming Tiki drinks.

kashani
-- 
gentoo-user@gentoo.org mailing list

Re: [gentoo-user] MySQL Upgrade

[Jeff Smelser]

[-- Attachment #1: Type: text/plain, Size: 1338 bytes --]

On Wednesday 02 November 2005 02:23 pm, kashani wrote:

> 	Because once you start caring about your db that much it's usually
> easier to setup a crap box that replicates off your master. Then you can
> lock the whole db on your slave and do a mysqldump without causing
> issues on your main db regardless of which table type you're using. You
> also get the added benefit of a hot backup and no I/O hits on the main db.

No, thats unfeasable.. Your talking about little DB's.. I have a 300+ gig 
dbs's here and replication isnt the answer. Specially considering it is 
statement level over row level. 

> 	I'm managing five fairly busy Mysql servers so my tactics might not be
> those of the average user making the above a bit of overkill. I was very
> excited about Mysql's instance manager. However if your database has
> known periods of time when it isn't being used, ie you're using it as an
> Access replacement. Lock the tables, do the dump, free the tables, and
> save your cash for flaming Tiki drinks.

Heh, my server is never NOT being used.. Its really idiotic to have to do 
full's all the time, instead of being able to just do an incremental..

Like I said, your talking about these small db's.. doing a mysqldump takes 
over 3-5 hours.. I am not locking all my tables for up to 5 hours..

Jeff

[-- Attachment #2: Type: application/pgp-signature, Size: 189 bytes --]

Re: [gentoo-user] MySQL Upgrade

[kashani]

Jeff Smelser wrote:
> On Wednesday 02 November 2005 02:23 pm, kashani wrote:
> 
> 
>>	Because once you start caring about your db that much it's usually
>>easier to setup a crap box that replicates off your master. Then you can
>>lock the whole db on your slave and do a mysqldump without causing
>>issues on your main db regardless of which table type you're using. You
>>also get the added benefit of a hot backup and no I/O hits on the main db.
>  
> No, thats unfeasable.. Your talking about little DB's.. I have a 300+ gig 
> dbs's here and replication isnt the answer. Specially considering it is 
> statement level over row level. 

	Don't think you could have brought up the fact that you're complaining 
about the shortcomings of Mysql in an entirely different class than I 
and likely the original poster were talking about? I'd have wanted to 
know a bit more about the setup before attempting to needlessly 
complicating everything.

	So yes, if your db is 20 GB or less, what I mentioned will probably 
work without too much trouble. If your db is 100 GB or larger, you're 
likely spending enough on hardware and software to solve your issues 
that some vendor will offer to buy you flaming Tiki drinks so it's not 
all bad.

kashani
-- 
gentoo-user@gentoo.org mailing list

Re: [gentoo-user] MySQL Upgrade

[Jeff Smelser]

On Wednesday 02 November 2005 05:47 pm, kashani wrote:

> 	Don't think you could have brought up the fact that you're complaining
> about the shortcomings of Mysql in an entirely different class than I
> and likely the original poster were talking about? I'd have wanted to
> know a bit more about the setup before attempting to needlessly
> complicating everything.

And I dont think you listened to me. I told her that mysqldump and 
mysqlhotcopy isnt the best, but WILL work for her.. Look at it again.

> 	So yes, if your db is 20 GB or less, what I mentioned will probably
> work without too much trouble. If your db is 100 GB or larger, you're
> likely spending enough on hardware and software to solve your issues
> that some vendor will offer to buy you flaming Tiki drinks so it's not
> all bad.

I hope you didnt take you all night to think of this, its really not that 
good.

Jeff
-- 
gentoo-user@gentoo.org mailing list

Re: [gentoo-user] MySQL Upgrade

[kashani]

Jeff Smelser wrote:
> On Wednesday 02 November 2005 05:47 pm, kashani wrote:
>>	So yes, if your db is 20 GB or less, what I mentioned will probably
>>work without too much trouble. If your db is 100 GB or larger, you're
>>likely spending enough on hardware and software to solve your issues
>>that some vendor will offer to buy you flaming Tiki drinks so it's not
>>all bad.
> 
> I hope you didnt take you all night to think of this, its really not that 
> good.

	I'm not sure how to take that and am a bit confused since you're the 
one responding six hours later. :) Or one of us is having mail issues. :(

	Attempts to flame each other aside though I will continue the thread 
hijack in progress, what are your options for incremental backups with 
any RDBM? My understanding is that you're pretty much dependent on some 
sort of snapshotting from either your expensive hardware (EMC), or 
expensive filesystem (Veritas) if you don't have a third party package 
or your db isn't fancy enough. Oracle seems to have made good progress 
with their RMAN stuff, but I couldn't say how robust that is or how it 
compares to the former. I'm sadly out of date with Oracle as I haven't 
been involved in any reasonably sized systems newer than 8i.
	I guess the question is what sort of functionality would you want out 
of Mysql? And as a followup, everyone and their mother with reasonably 
sized Mysql databases appears to use replication to do their backups, 
psuedo-incremental or otherwise. Is it just the extra hardware and disk 
that you don't like or something else?

kashani
-- 
gentoo-user@gentoo.org mailing list