Go to Master Server
1. Make all the tables engine = innodb
As only innodb engines have binary logging feature which is essentially used for replication. Binary logging must be enabled on the master because the binary log is the basis for sending data changes from the master to its slaves. If binary logging is not enabled, replication will not be possible. MyIsam does not support binary logging.
Use following command to convert all the tables to InnoDB
mysql > SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') as ExecuteTheseSQLCommands
FROM information_schema.tables WHERE table_schema = 'db_name'
ORDER BY table_name DESC;
2. Start binary logging on Master and Assign server Id (Server ID assigning is necessary, If you omit server-id (or set it explicitly to its default value of 0), a master refuses connections from all slaves).
edit the file /etc/mysql/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=101
*For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in the master my.cnf file.
3. Create a slave user on Master DB
mysql> grant REPLICATION SLAVE on *.* to 'slave'@'IP_ADDRSS_OF_SLAVE' identified by 'slavePassword';
mysql> flush privileges;
4. Restart Master DB
and check
mysql> show master status;
You can also check if mysql-bin log files are getting created on not, where you have given path of data to be stored, may be at /var/lib/mysql
5. Take a dump of database
mysqldump -uroot -proot --single-transaction --master-data --databases db1,db2 > all_db.sql
And transfer the file on slave Machine
Go to slave Machine
6. Assign Server Id on slave DB
[mysqld]
server-id=102
7. Restart Slave DB
8. Import the dump file in database
mysql -uroot -proot < all_db.sql
9. Make this slave listen to Master
mysql> CHANGE MASTER TO MASTER_HOST='MASTE_HOST_IP_ADDRESS',MASTER_USER='slave',MASTER_PASSWORD='slavePassword';
mysql> flush privileges;
10. slave start
mysql > slave start;
mysql > show slave status;
DONE :)
Some troubleshoots and points:
- You can configure on slave mysql configuration that which all database or even which all tables you want to replicate or do not want to replicate
http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html - If replication fails due to data consistency issue means, data already exist in slave and master is still trying to push (may be due to several kind of issue), you can change the slave configuration to move ahead
mysql > change MASTER TO Master_Log_File='mysql-bin._desired_bin_log_file'
Reference : http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
