In this tutorial i will try to describes how to set up mysql database replication. It’s simple process; MySQL replication allows you to have an exact copy of a database from a master server on slave server. It means all updates happening on the master server are immediately replicated to the slave server database so that both databases are in sync. In this case the both the databases are identical. e.g If you delete any record on master server it will be removed from slave server to; Replication can help protect data against hardware failures though.
In following tutorial i will show how to replicate mysql database “testdb” from the master to slave server (IP address 192.168.0.200). Both systems (master and slave) are running ubuntu; However, the configuration should apply to almost all distributions with little or no modification. Presuming that both systems have MySQL installed, and the database “testdb” with tables and data is already existing on the master, but not on the slave.
There are many ways of achieving this goal but this is the way it worked for me. I do not issue any guarantee that this will work for you!
Step 1: Master server Configuration
Now edit the mysql server config file located at “/etc/mysql/my.cnf” so that its enabled for networking for MySQL, and MySQL should listen on all IP addresses, therefore we comment out these lines if exists.
because it listens this ip.
#skip-networking #bind-address = 127.0.0.1
Step 2: Enable log file for specific database.
Now we have to tell MySQL for which database it should write logs as its been used by the slave to see what are changed happening on the master server, which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate “testdb” database, so we put the following lines into mysql config file ( i. e. “/etc/mysql/my.cnf”).
log-bin = /var/log/mysql/mysql-bin.log binlog-do-db=testdb server-id=1
now restart mysql server and see it got restart without any error message if so it means changes are done correctly.
Step 3: Create new mysql user with replication privileges.
Then we log into the MySQL database as root and create a user with replication privileges.
GRANT REPLICATION SLAVE ON *.* TO 'testdb_slave_user'@'%' IDENTIFIED BY '
'; (Replace with a real password!) FLUSH PRIVILEGES; USE testdb; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; Output will be as follows: +---------------+----------+--------------+------------------+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---------------+----------+--------------+------------------+ | mysql-bin.006 | 183 | testdb | | +---------------+----------+--------------+------------------+ 1 row in set (0.02 sec)
Now quit the master server.
Step 4: create master server database backup and copy it to slave server. command to create testdb backup is as follows. This is the best way to create back if you are using production server. It may slowdown your server if database size is more.
mysqldump -u root -p
--opt testdb > testdb.sql
Step 5: now configure mysql slave.
Login to mysql on slave server and create database “testdb”.
Import data using existing dump file in to testdb.
mysql -u root -p
testdb < /path/to/testdb.sql
Step 6: Now we have to tell MySQL on the slave that it is the slave, that the master is 192.168.0.200, and that the master database to watch is testdb. Therefore we add the following lines to "/etc/mysql/my.cnf"
server-id=2 master-host=192.168.0.200 master-user=slave_user master-password=secret master-connect-retry=60 replicate-do-db=exampledb Now we restart MySQL using "/etc/init.d/mysql restart". If there is no error it means everything is fine. Now stop the slave server. CHANGE MASTER TO MASTER_HOST='192.168.0.200', MASTER_USER='testdb_slave_user', MASTER_PASSWORD='
', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183; MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.200). MASTER_USER is the user we granted replication privileges on the master. MASTER_PASSWORD is the password of MASTER_USER on the master. MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master. MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master. Now all that is left to do is start the slave. Still on the MySQL shell we run START SLAVE; quit; That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave. That's all now you can Test it!