Where does each MySQL data file type belong?

Question

I'm in the process of transferring a database from one server to another. I'm moving the data files directly, instead of using a SQL dump. To do this, I:

• ensured that both servers use the same MySQL version
• set innodb_fast_shutdown = 0
• stopped the MySQL server
• rsynced the data to the new server

I'm almost ready to start the new server, but I have a doubt. The old server uses the same data dir /var/lib/mysql for data, binlogs, etc., whereas the new server uses 3 different dirs for data, binlogs and tmp:

datadir=/mysql/data
tmpdir=/mysql/tmp
log-bin=/mysql/binlog/mysql-bin

The question is: where should each of the files go?

My reasoning is:

• /mysql/tmp: there should not be any temporary files left after a slow (or even a normal?) shutdown, so nothing should go there right now
• /mysql/binlog: I should only move the mysql-bin.* files
• /mysql/data: everything else should go there

Is that correct?

---

Here is the file list, for information (nxxxxxxx is my main database):

# ls -al /var/lib/mysql
total 38518860
drwxr-x--x.  9 mysql mysql       4096 20 nov.  13:50 .
drwxr-xr-x. 37 root  root        4096 29 déc.   2019 ..
-rw-rw----.  1 mysql mysql         56 21 déc.   2014 auto.cnf
-rw-------   1 mysql mysql       1680 27 déc.   2018 ca-key.pem
-rw-r--r--   1 mysql mysql       1112 27 déc.   2018 ca.pem
-rw-r--r--   1 mysql mysql       1112 27 déc.   2018 client-cert.pem
-rw-------   1 mysql mysql       1676 27 déc.   2018 client-key.pem
-rw-r-----   1 mysql mysql     589824 20 nov.  13:50 #ib_16384_0.dblwr
-rw-r-----   1 mysql mysql    8978432 19 nov.  18:28 #ib_16384_1.dblwr
-rw-r-----   1 mysql mysql      91522 20 nov.  13:50 ib_buffer_pool
-rw-r-----.  1 mysql mysql 9143582720 20 nov.  13:50 ibdata1
-rw-r-----   1 mysql mysql 1073741824 20 nov.  13:50 ib_logfile0
-rw-r-----   1 mysql mysql 1073741824 20 nov.  13:50 ib_logfile1
drwxr-x---   2 mysql mysql       4096 20 nov.  13:50 #innodb_temp
drwxr-sr-x.  2 mysql mysql       4096 19 nov.  18:28 mysql
-rw-r-----   1 mysql mysql 1073742002  5 nov.  21:45 mysql-bin.002418
-rw-r-----   1 mysql mysql 1073742033  6 nov.  12:00 mysql-bin.002419
                                    (...)
-rw-r-----   1 mysql mysql     341943 20 nov.  13:50 mysql-bin.002446
-rw-r-----   1 mysql mysql        179 20 nov.  13:50 mysql-bin.002447
-rw-r-----   1 mysql mysql        570 20 nov.  13:50 mysql-bin.index
-rw-r-----   1 mysql mysql   62914560 20 nov.  13:50 mysql.ibd
-rw-r--r--.  1 mysql mysql          6 19 nov.  18:28 mysql_upgrade_info
drwxr-sr-x.  2 mysql mysql      69632 17 nov.  17:15 nxxxxxxx
drwxr-x---   2 mysql mysql       4096 19 nov.  18:28 performance_schema
-rw-------   1 mysql mysql       1676 27 déc.   2018 private_key.pem
-rw-r--r--   1 mysql mysql        452 27 déc.   2018 public_key.pem
-rw-r--r--   1 mysql mysql       1112 27 déc.   2018 server-cert.pem
-rw-------   1 mysql mysql       1680 27 déc.   2018 server-key.pem
drwxr-x---.  2 mysql mysql      12288 27 déc.   2018 sys
drwxr-x---   2 mysql mysql       4096  1 mars   2019 test
-rw-r-----   1 mysql mysql  109051904 20 nov.  13:50 undo_001
-rw-r-----   1 mysql mysql  109051904 20 nov.  13:50 undo_002

Answer 1

That's fine, just you'll likely need to reconfigure you bin and tmp locations in your .cnf file after you move the files.

Here's an article specifically about reconfiguring the tmp location (I would imagine the bin directory is similar): How to change your MYSQL tmpdir