Step 2 — Replication Configuration 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/