Step 2 — Replication Configuration on Primary Server

• On Primary Server

  • install nano  

  sudo apt-get install nano

• Edit my.cnf file

sudo nano /etc/mysql/my.cnf

 

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

• Restart MySQL server

sudo systemctl restart mysql

• mysql -u root -p

• CREATE USER 'replica_user'@'%' IDENTIFIED BY 'strongpassword';
  GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
  FLUSH PRIVILEGES;

• FLUSH TABLES WITH READ LOCK;
  SHOW MASTER STATUS;

• +------------------+----------+--------------+------------------+-------------------+
  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  +------------------+----------+--------------+------------------+-------------------+
  | mysql-bin.000007 |   291136 |              |                  |                   |
  +------------------+----------+--------------+------------------+-------------------+

• UNLOCK TABLES;

• Go to the temp folder and export the database

mysqldump -u root -p --all-databases --master-data > db_dump.sql

• Export the specific table

mysqldump -u root -p your_database your_table > /tmp/your_table.sql

Unlock tables

UNLOCK TABLES;

• Transfer the database/table to the slave server

scp /tmp/your_table.sql user@slave_server:/tmp/