Most convenient way to save data and make backups in MySQL

Question

Hi, one of my colleagues suggested that I should make different databases (MySQL) for each client, so that if one account is compromised, we only need to restore a backup of a single database. I'm concerned about a fast access to the data: which is faster? selecting a database among thousands of them, or finding an entry among thousands on a table from a single database? And also, what's more convenient, in terms of speed and reliability: to backup a large database, or a large number of little ones? Also, Is there a way to backup only one entry in a table, and if there is, is it better than backing up lots of little databases or a large one?

The system I'm using is based on PHP/MySQL and jQuery (Javascript and AJAX), if it helps.

Answer 1

You definitely want to use a different database per client. Using a different database is much faster than trying to query a record from a combined table schema as well as being more secure(if you use different accounts per database).

Depending on your database engine, MYISAM or INNODB, etc there are different backup strategies as well. Other performance issues for instance, MYISAM is table level locking when a record is being updated which will be very slow if everything is using the same table.

Depending on your backup strategy and database engine you can use mysqldump or mysqlhotcopy for on the fly backups if you use MYISAM tables.