backing up... is this the right way?

[Anon]

mysql_query("MYSqlDump $dbname>backup.txt");

doesn't seem to do.. well anything 😃

[[deleted]]

That's because mysqldump is not an SQL command, it's a program.

[Anon]

is there any way of doing it through mysql i.e. an MySQL command that does it?

[[deleted]]

You can do 'select into outfile' but that is different.

Just use exec() or system() in PHP to call the program.

[Anon]

This is how I do it for Backup Exec on my server.

MySQL files cannot be backed up 'hot' Instead you need to take a snapshot of the databases. MySQL has a tool which will take these snapshots live, avoiding the need for a process restart.

The easiest way to do this? Create a 'backup' directory in the /var/lib/mysql directory. This will be where all backups go. I will only backup this directory and the logs directory which is all that is needed for a full restore. All other files in /var/lib/mysql are NOT backed up. Using a central backup directory will also ensure that new DBs get backed up (so long as you add them to your hotcopy script)

Create a script in your MySQL directory (I call it sql_backup) that contains this command:

/usr/bin/mysqlhotcopy -u root -p xxxxxx --allowold --flushlog
--noindices

• /var/lib/mysql/backup

  Where xxxxx is the root password for MySQL and [list db's here] is a space delimited list of the databases you need backed up (you can exclude test for example)

  Then schedule this job nightly at right before your nightly server backups run (say in /etc/crontab make sure the job runs as user mysql) - it is VERY important you use a time just moments before the server backup! Backup Exec doesn't do proper incremental backups on Unix - instead it does 'daily' backups which means it'll only backup files changed that day - so this gets you a snapshot of the most recent data - the backup only takes a few seconds anyway

[[deleted]]

"it is VERY important you use a time just moments before the server backup!"

Heck no, never do anything 'just moments before' something else starts. You definately don't want to risk starting the backup before your mysql-copy script is finished. Leave at least a few minutes between making the mysql backup files and starting the tape-backup.

[Anon]

thanks for clarifying that Vincent.. I meant minutes not seconds... my hot copy takes a few seconds so I time it a few minutes before the server backup..

[[deleted]]

Good boy :-)