0

I need to relocate mysql customer databases to another disk, for the purposes of:

 1. Separation of user data from system files
 2. Ease of backing up system and user data separately
 3. Ease of upgrading the system

The following articles were helpful:

  [how-to-move-mysql-datadir-to-another-drive][1]
  [move-a-mysql-database-to-another-location][2]

Running on a ubuntu-18 system, it appears the innodb_file_per_table variable is set by default:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_%';
...
| innodb_file_per_table                    | ON 
...

(Same results without the GLOBAL)

I can't find any reference to innodb_file_per_table in any configuration files; the only file with much in it is

  /etc/mysql/mysql.conf.d/mysqld.cnf

I'm concerned about what is still being stored in the files

  /var/lib/mysql/ibdata1
                /ib_logfileN

I've noticed that simply accessing a database causes these to be updated.

My questions are:

  1. If I move only the customer databases (to a separate disk), and the system disk is lost and needs to be recreated from scratch, will anything be lost?

  2. Am I better off moving all databases, including (mysql, performance_schema, and sys) to a separate disk?

  3. Am I better off moving the entire data directory to a separate disk, pointing to it via the "datadir" variable in /etc/mysql/mysql.conf.d/mysqld.cnf?

  4. What are the ramifications of not using option 3 if the system disk is lost?

Gary Aitken
  • 123
  • 6

1 Answers1

0

It would be best to move all of MySQL's tables to another disk. Any attempt to split up 'customers' would add complexity and make your goals difficult. (If that becomes a requirement, we can discuss further.)

Essentially the only file that is left behind by such a move is the configuration file(s). You mentioned /etc/mysql/mysql.conf.d/mysqld.cnf, but there might be other config files around there.

Somewhere else is a bigger directory that contains a bunch of files, plus subdirectories that correspond to the "databases" you have created. I would simply move that entire tree. If you are using a linux-based OS, then use a symlink from the old location to the new. No explicit configuration changes are needed.

Then, if either disk crashes, you can't get to the data. My point is that playing around with files and directories is not necessarily going to get you closer to "security".

Instead, focus on backups. And/or "Replication". That latter requires an extra server and gives a complete copy of the data. Then, if either server crashes, you have lost nothing.

LVM is another backup technique; the benefits from having a disk for the database. But that adds a bunch of complexity.

Suggest you read some more, and keep an open mind. You would find much that directly matches your ideas.

Rick James
  • 2,463
  • 1
  • 6
  • 13