Live mySQL Database mirrors

srfdriver22

Hi guys,

I have a live database that is used by many people. But I would like to make a mirror of its contents as another database (on the same server) using a cronjob at set intervals. Does anyone know how to do this? I know how to setup the cronjob to call the PHP script, but I'm not quite sure on what the mySQL syntax or process is to do such a thing. I've looked at mysqldump but that only creates an output file, right? I would want to do this in one or two steps (if possible) without having to write something to disk.

Thanks. 🙂

bretticus

Have you explored replication ? I use it for my employers...works swell. 😉

bretticus

But I would like to make a mirror of its contents as another database (on the same server)

Well, that changes things. Replication is not the answer on further review 😉

Just out of curiosity, are you doing this for a snapshot backup or something?

bretticus

mysqldump manual says...

The most normal use of mysqldump is probably for making a backup of whole databases. See Mysql Manual section 21.2 Database Backups.
mysqldump --opt database > backup-file.sql
You can read this back into MySQL with:
mysql database < backup-file.sql

Just change the database name when you read it back in to the backup database.

Another suggestion, you can simpy copy the folder over. Never had a problem with this in the last 4 years (this may be the most simple solution.)

srfdriver22

Thanks for the quick replies. 🙂

I wonder how accessing these databases while this transfer is occuring will effect it? What will the end user see? Will they see a mySQL error or will it wait (like a locked table) until the process is done?

Also, do you know where I can find or know off hand the commands to copy the folder or and also to delete that .sql file mysqldump creates? I don't want to have GB of .sql files! 😉

I forgot to mention, that this database is quite large and will continue to grow on a regular basis.

bretticus

Quotes from manual...

I'm using the following script as daily cron, it works.

#!/bin/sh
date=date -I
mysqldump --opt --all-databases | bzip2 -c

/var/backup/databasebackup-$date.sql.bz2

If tables are stored in the InnoDB storage engine, mysqldump provides a way of making an online backup of these (see command below). This backup just needs to acquire a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and lock is released. So if and only if one long updating statement is running when the FLUSH... is issued, the MySQL server may get stalled until that long statement finishes, and then the dump becomes lock-free. So if the MySQL server receives only short (in the sense of "short execution time") updating statements, even if there are plenty of them, the initial lock period should not be noticeable.

shell> mysqldump --all-databases --single-transaction > all_databases.sql

For point-in-time recovery (also known as "roll-forward", when you need to restore an old backup and replay the changes which happened since that backup), it is often useful to rotate the binary log (see Section 5.10.4, “The Binary Log”) or at least know the binary log coordinates to which the dump corresponds:

shell> mysqldump --all-databases --master-data=2 > all_databases.sql
or
shell> mysqldump --all-databases --flush-logs --master-data=2 > all_databases.sql

The simultaneous use of --master-data and --single-transaction works as of MySQL 4.1.8. It provides a convenient way to make an online backup suitable for point-in-time recovery, if tables are stored in the InnoDB storage engine.

bretticus

I guess I didn't quite answer your question as you do not intend to create backups. You might want to look into the mysqlhotcopy command.

Still, your method seems a bit like madness. 😃 Why duplicate a large database to another on the very same server? Can you enlighten me here a bit? Possibly there's a better way to go about this.

If you wish to use mysqldump instead of mysqlhotcopy then just add the rm command to your cronjob

/pathto/mysqldump --opt database > backup-file.sql
You can read this back into MySQL with:
/pathto/mysql newdatabase < backup-file.sql
/pathto/rm backup-file.sql

srfdriver22

I have external programs updating the database. But I also have many web users selecting and updating as well. When the external programs update the database, the database becomes locked so the web users just sit there, which make it very slow. So, I figured, if I could have one copy of the database for the web users and one copy for the external programs (the external programs copy being the master), then the data might be a little bit delayed for the web users but that would be ok. And it wouldn't be slow for the web users.

ab4net

how then you have solved this issue ??? if you can help ...!!

srfdriver22

I didn't. But I switched to InnoDB tables which helped significantly since there are a lot of external connections to the DB.

ab4net

hmm ... great, can you advice where to read about InnoDB ...!!