Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous – slaves need not be connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.
How To Set Up Database Replication In MySQL:
Configure master 192.168.100.10 (/etc/mysql/my.cnf):
log-bin = /var/log/mysql/mysql-bin.log server-id=10 auto-increment-increment = 2 auto-increment-offset = 1 binlog-do-db=testdatabase replicate-ignore-db=test replicate-ignore-db=mysql max-binlog-size=1024M
This settings will be available after a mysql restart.
Log into the MySQL shell and create a user with replication privileges:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.100.11' IDENTIFIED BY 'replication'
This wil create a user with replication privileges, with access from slave database, 192.168.100.11
In this moment we need a database dump (a copy of databasetest). This can be achived by mysqldump, mysqlhotcopy, load data from master (on slave) or just tar.gz the database files. Each method will be later discussed.
Configure slave 192.168.100.11 (/etc/mysql/my.cnf):
server-id = 11 auto-increment-increment = 2 auto-increment-offset = 2 master-host=192.168.100.10 master-user=slave master-password=replication master-port=3306 replicate-ignore-db=mysql replicate-ignore-db=test replicate-do-db=databasetest master-info-file=/var/lib/mysql/master.info master-connect-retry=60
Stop mysql server:
/etc/init.d/mysqld stop
Copy database to slave machine.
Start mysql server:
/etc/init.d/mysqld start
Check slave status:
mysql> show slave status \G
Look for this fields:
Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0
this mean that the replication is working!
Note:
“auto-increment-increment” and “auto-increment-offset” will be later explained.