Upgrading mysql questions

[Andreas Widerøe Andersen]

Hi,
I'm currently running several Joomla websites and phpBB forums on an old
FreeBSD server running mysql-server 3.23. Yesterday I upgraded the mysql
installation to 4.0.27 successfully. No problems at all. First I made
backups, then deinstalled mysql 3.23 and finally installed 4.0.27. I then
ran the mysql_fix_privilege_tables script which gave me some warnings, but
seemed OK. The test forum and databases started up fine.

Soon, I'll upgrade to 4.1 and then later to version 5 of the mysql server.

My question is: When upgrading mysql-server and running suggested/included
update scripts etc, do they only affect the base mysql server (and
associated files)? What about all the databases (Ie. phpBB/Joomla) that was
created under 3.23, should I run some sort of upgrading script on these
also? Need some advice here. Sounds logic to me that they also need to be
updated/optimized for the new system - somehow.

I hope someone will be able to assist me a little here.

Thanks and best regards,
Andreas

Re: Recommended backup scripts for mysql databases

[Andreas Widerøe Andersen]

Thanks for all your help. I now have a nice backup script running. What I'm
planning to do a little later this summer is to set up a RAID backup server
on my DSL connection ftp downloading the mysql databases every night from my
co-location server. This way I will have a nice archive of dumps on two
locations to be even safer.

In my setup I now have 3 my.sh scripts executed at the same time through a
cronjob. Could it be problematic to do this at the same time or does it not
matter? The databases aren't large.

Cheers,
Andreas

Re: Recommended backup scripts for mysql databases

[Andreas Widerøe Andersen]

I finally got the script working. Seems to run smooth on my FreeBSD 4.11system:

MYSQLDUMP=`which mysqldump 2/dev/null` ||
MYSQLDUMP=/usr/local/bin/mysqldump
MYSQLDUMP_ARGS=--opt --extended-insert -h localhost -umyuser -pmypassword
mydb
ARCHDIR=/backup/mysql
NAME=db_dump

# Remove archives older than 64 days
find ${ARCHDIR} -type f -mtime +64 | xargs rm -f
# Create new archives
cd ${ARCHDIR}  ${MYSQLDUMP} ${MYSQLDUMP_ARGS}  ${NAME}.`date +%Y%m%d`

I just have two more questions:

Should I use --extended-insert or not? I guess I can use both, but what is
recommended?

What would the correct script/way to restore the database?

Thanks,
Andreas

Re: Recommended backup scripts for mysql databases

[Andreas Widerøe Andersen]

Thanks for the examples. I haven't been able to get them to work yet.

Here's what I tried:

MYSQL=`which mysql 2/dev/null` || MYSQL=/usr/local/bin/mysql
MYSQL_ARGS=--opt -uroot -pmypassword db
ARCHDIR=/backup/mysql
NAME=db_dump

# Remove archives older than 64 days
find ${ARCHDIR} -type f -mtime +64 | xargs rm -f

# Create new archives
cd ${ARCHDIR}  mysqldump ${MYSQL} ${MYSQL_ARGS}  ${NAME}.`date +%Y%m%d`

I only get:
mysqldump: Got error: 1045: Access denied for user: '[EMAIL PROTECTED]' (Using
password: NO) when trying to connect

While I can login to mysql easily with this user/pass from the command
prompt. I have also tried the specific user for this database. Same problem.

Any ideas?

Thanks,
Andreas

Recommended backup scripts for mysql databases

[Andreas Widerøe Andersen]

Hi,
I have a few FreeBSD servers running various web/database things and I'm
looking for a good a reliable backup script that I can run through a
cronjob. I'm currently running the latest version of mysql323, but will
upgrade to version 4.1 soon aswell as upgrade most of the servers to the
latest FreeBSD version.

Any good suggestions to a script that will back up my databases and make
things ready for an easy restore if I need to?

Thanks!
Andreas