MySQL master-slave replication is well covered so I won’t go deep into explanations and details. This supposes to be a simple how-to tutorial. However, I’m going to pinpoint a few details that might lead you astray while doing this easy setup.
We need to add a few settings to the master’s configuration file.
[mysqld] log-bin = mysql-bin server-id = 1
Find [mysqld] section in your configuration file located at:
/etc/mysql/mysql.conf.d/mysqld.cnf
First setting will turn on binary logging which is basically key for replication. MySQL is logging all databases’ changes to the binary log and which is used for syncing slave. The second setting is the master server id which obviously needs to be different from the slave’s. We need to restart the master server to activate the binary logging. We may check in /var/lib/mysql if MySQL is producing a file like this – mysql-bin.000001. The server will usually rotate and remove old logs automatically so we don’t need to think about it.
To be sure that replication will work properly we need to check that skip-networking is commented out as well as bind-address if it’s bound to localhost (127.0.0.1) addresses. Pay attention that when you comment out these settings your server will be accessible from the outside world, so use a firewall to protect it from abuse.
Before we continue with the slave setup it’s important to get some data from the master server which we are going to use with the slave because we are syncing existing production master. Log into the console and type this command:
mysql> flush tables with read lock;
This one will pause writing operations but if you’re using InnoDB type of tables won’t affect your server because all writing operations will be saved in memory and later applied to the disk once you unlock tables. For MyISAM type of tables, you would lose all writing operations on your database during the lock. We will cover MyISAM live syncing in another tutorial.
Now we need to get master status:
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000375 | 9595354 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
Write down this information as it will be required for syncing slave. Now we need to dump the master. It’s important to do that in another server session because if you exit mysql console it will unlock the master which is something we want to avoid.
In another terminal session dump the master:
$ mysqldump --all-databases -u root -p --master-data > master_dump.sql
Master-data switch would supposedly write the binary log file name and position to the output file so you wouldn’t need to provide that info manually to the slave. However, it wasn’t the case when I imported the dump. I’m not sure why (for the record I’m using version 5.7).
Now you can unlock the master:
mysql> unlock tables; mysql> grant replication slave on *.* to slaveuser@YOUR-SLAVE-IP identified by 'password';
The second command is to grant slaveuser access to the master for replication purposes. You should, of course, choose your own username and password as well as provide ip address of your slave server.
Now to the slave. Find [mysqld] section of your slave’s configuration file. My slave is multiple instances MySQL server so configuration is in /etc/my.cnf under [mysqld2] section. Add unique server id (must be different from the master) and restart MySQL server:
[mysqld] server-id = 2
Copy master dump file to the slave server and run import:
$ mysql -uroot -p < master_dump.sql
Since you are importing all databases keep in mind that your root password will be overwritten with the master’s root password. It might be obvious only after the server is restarted again. The import process will certainly be longer than dump because writing operations are slower than reading. So don’t worry if it takes some time without response.
Now we need to give the slave access credentials for the master and position in the binary log:
mysql> change master to MASTER_HOST = 'your-master-ip', MASTER_USER = 'slaveuser', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000375', MASTER_LOG_POS = 9595354; mysql> start slave;
Since we added –master-data switch during the master dump we could configure slave without master_log_file and master_log_pos directives but in my case, it didn’t work so I had to do it.
The slave might need some time to sync with the master. We can check if everything is working properly with:
mysql> show slave status \G
There are few error rows in the output of the above command that should be zero or empty and after the slave is synced we would find this row as well:
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Good luck!