Sign Up

Sign Up to our social questions and Answers Engine to ask questions, answer people's questions, and connect with other people.

Sign In

Login to our social questions & Answers Engine to ask questions answer people's questions & connect with other people.

Forgot Password

Lost your password? Please enter your email address. You will receive a link and will create a new password via email.

You must login to ask question.

You must login to add post.

MASTER TO SLAVE REPLICATION IN MYSQL

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 rootkevpzgkdhh.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.
admin

admin

Enlightened

I like to receive and deal with challenging tasks. I am a very enthusiastic person and I think this is a strong point of mine. My friends say that I am a very funny with a good sense of humor. As soon as I meet new people who are happy to meet me, I feel extremely comfortable with them.

Leave a comment

You must login to add a new comment.