Database Administrators Asked by BenMorel on February 16, 2021
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:
innodb_fast_shutdown = 0
rsync
ed the data to the new serverI’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 thereIs 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
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
Correct answer by J.D. on February 16, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP