Replication on different datadir

MySQL & MariaDB Percona Server for MySQL 5.7
#1

Hi,

I have a running replication, but I tried running replication on different datadir, but I’ve got

/opt/percona-5.5.62-38.14-ssl101/bin/mysqld: File ‘/var/lib/mysql/data/mysql-bin.000045’ not found (Errcode: 2)

201215 14:56:42 [ERROR] Failed to open log (file ‘/var/lib/mysql/data/mysql-bin.000045’, errno 2)

even though I have changed datadir, innodb_data_home_dir, relay_log, relay_log_index, log_bin, log_bin_index to its new values.

Any ideas why?

Is it even possible to run replica on different data dir?

Kind regards,

Rok

1 Like
#2

Hi Rokj,

Apart from changing directories from MySQL, did you check that files have correct permission and that file /var/lib/mysql/data/mysql-bin.000045 exists?

Above message might be related to file not existing, wrong permission and impossibility to open it, or trying to find for a file on wrong path.

Also, mysql-bin.index file contains the path to each binary log file. You should double check that each file name on .index file does exactly match real file name.

You should not manually change files while MySQL is running (and if possible, do not edit .index file manually) since it can cause inconsistencies

1 Like
#3

Hi CTutte, thank you for answering.

Files have correct permission and files exists.

I’ve changed mysql-bin.index to proper values and now I get different error

201216 14:10:31 [ERROR] Failed to open the relay log ‘/var/lib/mysql/data/relay-bin.002966’ (relay_log_pos 1039053410)

201216 14:10:31 [ERROR] Could not find target log during relay log initialization

201216 14:10:31 [ERROR] Failed to initialize the master info structure

… even though I have

relay_log = /var/lib/mysql/data/wordpress/relay-bin

relay_log_index = /var/lib/mysql/data/wordpress/relay-bin.index

and in relay-bin.index directories changed to correct one /var/lib/mysql/data/wordpress/relay-bin.002966 for example.

Any ideas for this one?

Rok

1 Like
#4

As I’ve read on internet forums, relay_log “cannot be changed” when replication is alive, so I’ll stop master and replica, move master to replica, do the change … and go from there.

Thanks.

1 Like
#5

Hi Rokj,

Another thing you can try doing for avoiding switchover, is to execute “RESET SLAVE” (on replica server) to forget replication settings and purge relay logs, then execute again “CHANGE MASTER TO” statement to setup replication from scratch and start fetching relay logs again. You will need to write down “Master_Log_File” and “Exec_Master_Log_Pos” to know last binary log and position last executed on replica. Then use those same file/pos on RELAY_LOG_FILE and RELAY_LOG_POS to restore replication from where it left off.

Let me know how it goes

1 Like
#6

I’ve went with the plan described in my previous post and it worked. Thanks again.

1 Like