...
Edit MySQL config file on the master server: /etc/my.cnf. At the end of the file, add the following lines. Then save changes and restart MySQL.
Code Block #replication expire_logs_days=30 server-id = 1 log-bin=/var/lib/mysql/mysql-bin.log log_bin_trust_function_creators=1 binlog_format=MIXED
Add the MySQL user destined for replication with the appropriate rights. Log in on MySQL CLI and run the following command:
Code Block GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO replica@'%' identified by 'PYvrVSSpFj1yl'; FLUSH PRIVILEGES;
Stop the MySQL service on the slave server.
Go back to the master node and run the following command. In order to synchronize the folders, you will have to provide the root password for connecting via SSH on the slave server.
Code Block rsync -varPe ssh /var/lib/mysql root@<slave_ip>:/var/lib/
Lock the database to prevent any new changes.
Code Block FLUSH TABLES WITH READ LOCK;
Check status on master server.
Code Block mysql> show master status; +------------------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------------------+----------+--------------+------------------+ | centos-6-b64-test-bin.000001 | 43311833 | | | +------------------------------+----------+--------------+------------------+
Again, synchronize the MySQL home directory on the slave server. Run the rsync command on the master server one more time.
Code Block rsync -varPe ssh /var/lib/mysql root@<slave_ip>:/var/lib/
Unlock tables.
Code Block UNLOCK TABLES; QUIT;
Edit MySQL config file on the slave server: /etc/my.cnf. At the end of the file, add the following lines:
Code Block expire_logs_days=30 log_bin = /var/lib/mysql/mysql-bin.log server-id = 2 max_allowed_packet=128M binlog_format=MIXED log_bin_trust_function_creators=1
Start MySQL service on the slave server, connect to MySQL CLI and run the following command:
Code Block slave stop;
Set the master parameters on the slave server.
Code Block change master to master_host='10.150.12.32', master_user='sclavetereplica', master_password='PYvrVSSpFj1yl', MASTER_LOG_FILE='centos-6-b64-test-bin.000001', MASTER_LOG_POS=43311833;
Code Block start slave;
Note The MASTER_LOG_POS should be Position from the previous step.
Check the slave status.
Code Block mysql> show slave status\G; *************************** 1. row *************************** .. Slave_IO_Running: Yes Slave_SQL_Running: Yes .. Exec_Master_Log_Pos: 43315418 ..
...
Except where otherwise noted, content in this space is licensed under a Creative Commons Attribution 4.0 International.