A) Take the backup from a replicating slave. Zero performance loss.
B) Use LVM to take a consistent snapshot. Copy the database files at your leisure.
C) Use Rsync over a remote connection to copy the data files, then do a final Rsync with the database briefly locked for a couple seconds.
As an aside, how are they taking a consistent backup with MyISAM tables without LOCKING the application for 8 hours straight? It doesn't seem possible.
Unless you create a different database user for each application user -so it is available with the user() or current_user() functions:
mysql> SELECT user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
it is not possible to know this at MySQL level unless it is provided by the application.
You could use SQL comments on application code:
/* user=jynus */ INSERT INTO test values (1);
Which can later be processed using the slow log, if active, or some of the auditing plugins. Please note that the mysql command-line client application deletes comments before sending them to the server.
The only way you can log it using triggers with a single user that I can now think is setting a variable at the start of the connection:
mysql> SET @user := 'jynus';
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE test (i int PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE log (id serial, i int, user varchar(100));
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TRIGGER test_AI AFTER INSERT ON test FOR EACH ROW
INSERT INTO log (i, user) VALUES (NEW.i, @user);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO test VALUES (5);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM log;
+----+------+-------+
| id | i | user |
+----+------+-------+
| 1 | 5 | jynus |
+----+------+-------+
1 row in set (0.00 sec)
Best Answer
This is easy to do.
Write a script to create the backup using the "mysqldump" tool available at http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html - Google "mysql backup unix/windows script" to find some examples.
Use a task scheduler to run this script. On UNIX this could be "cron", Windows as a "task scheduler". Best to do so at times when the app is under low-usage.
It is worth while putting some backup file cycling into the script so that you keep, say, every backup from the last week and delete the rest.
Also remember to backup to a physically different disk from the database itself so you have a backup if the disk dies.