MySQL replication

I have 2 servers, one of which is a Master (my UDOO box) and the other which is a Slave (Raspberry PI). We tell the Master that it should keep a log of every action performed on it. We tell the slave server that it should look at this log on the Master and whenever something new happens, it should do the same thing.

Step 1. Create sleve_replication_user_account:

 

GRANT REPLICATION SLAVE ON *.* TO 'sleve_replication_user_account'@'%' 
  IDENTIFIED BY 'your_password';
FLUSH PRIVILEGES;

 

Step 2. Setup your Master.

Login to your “master” mysql server phpmyadmin and go to the replication tab, under Master configuration select set the options as your require:mysql1

Then copy the generated by PhpMyAdmin config and paste it to your my.cnf config file and restart your mysql server.

Go back to your phpmyadmin installation to verify that everything is working OK:

mysql2

Step 3. Setup your Slave.

Open Phpmyadmin on your slave go to replication tab and fill in the bits:

mysql3

Don’t forget to add the server-id to the slave my.cnf config.

Then add list of databases you want to replicate:

replicate-do-db=database_name1
replicate-do-db=database_name2
.....

This should match the config on your Master. Save your config and restart the mysql service.

Step 4. Export your DBs on Master to sql file

Run:

FLUSH TABLES WITH READ LOCK;

Then use Phpmyadmin to export one by one all the databases that you want to replicate.

Step 5. Import your DBs on Slave from exported sql files

Transfer exported sql files from Master to Slave.

log into MySQL, and create the databases:

mysql -u root -p
CREATE DATABASE `database_name1`;

then import the data:

mysql -u root -p database_name1  </home/my_home_dir/database_name1.sql

 

Step 6. Get it all running.

Now we are ready to kick things off. We need to find the position the Master is at in the logs. So, log into MySQL on your Master and run the following:

 mysql -u root -p
SHOW MASTER STATUS;

result:

+---------------------+----------+----------------------+------------------+
| File                | Position | Binlog_Do_DB         | Binlog_Ignore_DB |
+---------------------+----------+----------------------+------------------+
| mysql-bin.000076    | 00027442 | database_name1;      |                  |
+---------------------+----------+----------------------+------------------+

make note of this and login to MySql on your Slave:

mysql -u root -p
slave stop;
 CHANGE MASTER TO MASTER_HOST='AA.BB.CC.DD', MASTER_USER='slave_user', 
    MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000076', 
    MASTER_LOG_POS=00027442;
slave start;

where AA.BB.CC.DD is your Master IP address

then go back to the Master and run:

unlock tables;

And that is pretty much it.

 

Troubleshooting.

If the servers get out of sync run this on the Slave:

STOP SLAVE;
 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000049';
 CHANGE MASTER TO MASTER_LOG_POS=98;
 START SLAVE;

Note: MASTER_LOG_FILE must be the last file where it stop from replicating