Configuration for Master 1 – 10.1.1.1
In your my.cnf file (on Debian 5 it’s located at /etc/mysql/my.cnf, on CentOS/RHEL 5 it’s at /etc/my.cnf) add the following:
server-id=1
log-bin=mysql-bin
port=3306
max_allowed_packet=256M
binlog_do_db= DatabaseName
expire_logs_days = 20
max_binlog_size = 500M
auto_increment_increment = 2
auto_increment_offset = 1
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
And run the following SQL command as the root user:
grant all on *.* to ‘msandbox’@’22.22%’ identified by ‘msandbox’;
flush privileges;
Restart the MySQL server.
mysql -u root -p
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
The output should look like this. Note down the file and the position – you’ll need both later.
+——————+———-+———————+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+———————+——————+
| mysql-bin.000004 | 98 | exampledb,exampledb | |
+——————+———-+———————+——————+
1 row in set (0.00 sec)
Open a second terminal for system 1, create the dump and transfer it to system 2.
Don’t leave the MySQL-shell at this point – otherwise you’ll loose the read-lock (If you make any new changes in the same window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.)
cd /tmp/
mysqldump -u root -p%mysql_root_password% –opt exampledb > sqldump.sql
scp sqldump.sql root@192.168.0.200:/tmp/
Afterwards close the second terminal and switch back to the first. Remove the read-lock and leave the MySQL-shell.
UNLOCK TABLES;
quit;
Configuration Slave – 10.1.1.2
CREATE DATABASE newdatabase;
EXIT;
Time to import the database dump on system 2.
mysql -h22.22.22.23 -umsandbox -P5173 -p DBNAME < /var/backup/DB_Backup.sql
In your my.cnf file (on Debian 5 it’s located at /etc/mysql/my.cnf add the following:
server-id = 2
log-bin=mysql-bin
binlog-do-db= DatabaseName
lower_case_table_names =1
Restart the MySQL server.
Enter into MYSQL(Slave)
–STOP SLAVE
– CHANGE MASTER TO MASTER_HOST=’12.34.56.789′,MASTER_USER=’slave_user’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS= 107;
START SLAVE;
SHOW SLAVE STATUS\\G
… and take a look at the slave status. It’s very important that both, Slave_IO_Running and Slave_SQL_Running are set to Yes. If they’re not, something went wrong and you should take a look at the logs.