Mysql master-slave config in my.cnf

Question

I'm following this guide to setting up a master-slave config (https://support.rackspace.com/how-to/set-up-mysql-master-slave-replication/) and i'm confused about the end of the document where they are setting up the SLAVE

WHen setting up the slave, they suggest putting the following into the SLAVE my.cnf file

bind-address = 0.0.0.0
server-id = 2
master-host =  [private-IP-of-db01]
master-user = [replication-username]
master-password = [replication-password]
master-connect-retry = 60

Then later on they suggest logging into mysql and then telling the slave where to start replicating from

# mysql -u root -p
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST='[private-IP-of-db01]',
MASTER_USER='[replication-username]',
MASTER_PASSWORD='[replication-password]',
MASTER_LOG_FILE='[file-listed-on-master-status]',
MASTER_LOG_POS=[log-position-listed-on-master-status];
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

My question is specifically with the MASTER LOG FILE and MASTER LOG POS. Are these things not to be set in my.cnf on the slave?

I ask this because if I have to restart the slave mysql for whatever reason one day, how will the slave know where to pick up from? Do I have to go through the process of re-impoorting the DB to know the position again? Or do I have to save the position # before taking the slave DB down?

Thanks!

Answer 1

Are these things not to be set in my.cnf on the slave?

No, You only can set those values during execution time (using a mysql client tool)

if I have to restart the slave mysql for whatever reason one day, how will the slave know where to pick up from? Do I have to go through the process of re-importing the DB to know the position again?

MySQL stores replication variables in files located by default in the data directory (Usually theses file names contain "relay" and "master" words) and it updates those file with every transaction, so no worries, if you need to restart your slave, the server already knows how to catch up the master.

Answer 2

Slave saves the correct coordinates in the master.info file on shutdown so replication will not be broken after restart. You need to set the LOG_FILE/LOG_POS only once on the slave initialization. All the rest mysql can do on its own.

Answer 3

Setting those things in my.cnf is frowned upon. It is better (especially for the password) to provide them in CHANGE MASTER. This is the preferred way.

Meanwhile, the log position is kept in a file (old versions) or a table (newer).