21 Mar 2016 @ 2:35 PM 

If you need master-master replication for your MySQL server. This example is for Centos 6 x64 but on other Linux OS the process is similar.

 

For example we have two following servers with IPs:
1.1.1.1 SERVER_A
2.2.2.2 SERVER_B

    1. Install MySQL server on both servers.yum -y install mysql-server
    2. By default, MySQL works only with localhost (127.0.0.1), so we need to edit MySQL config file /etc/my.cnf and add followingon SERVER_A:
      server-id = 1
      bind-address = 1.1.1.1
      log_bin = /var/log/mysql/mysql-bin.log
      binlog_do_db = database_for_replication
      #replicate-do-table = database_for_replication.table_for_replication (You can replicate only some table(s))
      on SERVER_B:
      server-id = 2
      bind-address = 2.2.2.2
      log_bin = /var/log/mysql/mysql-bin.log
      binlog_do_db = database_for_replication
      After changes – restart mysql on both servers /etc/init.d/mysql restart
    3. Create MySQL user for replication on BOTH servers (use command mysql to enter MySQL console).
      grant all privileges on *.* to replication_username@'%' identified by "some_password";
      flush privileges;

      Note:
      *.* means you grant access to ALL databases, if you need to grant access only to some database, change it -> some_db.* , or some table -> some_db.some_table
      ‘%’ means you grant access to ALL IPs, if you need to grant access only to some IP, change it -> ‘111.222.333.444’
    4. Next step, log into SERVER_A into MySQL console, and execute:
      show master status;
      
      the result will be similar to:
      +-------------------+----------+---------------------------+------------------+
      | File              | Position | Binlog_Do_DB              | Binlog_Ignore_DB |
      +-------------------+----------+---------------------------+------------------+
      | mysqld-bin.000005 | 49572095 | database_for_replication  |                  |
      +-------------------+----------+---------------------------+------------------+
      1 row in set (0.00 sec)
      
      REMEMBER THIS RESULT, YOU'LL NEED IT IN STEP 5
    5. Log into SERVER_B into MySQL console, execute:slave stop;
      change master to master_host='1.1.1.1', master_user='replication_username', master_password='some_password', master_log_file='mysqld-bin.000005', master_log_pos=49572095;
      slave start;
    6. Next step, log into SERVER_B into MySQL console, and execute:
      show master status;
      
      the result will be similar to:
      +-------------------+----------+---------------------------+------------------+
      | File              | Position | Binlog_Do_DB              | Binlog_Ignore_DB |
      +-------------------+----------+---------------------------+------------------+
      | mysqld-bin.000001 | 98748451 | database_for_replication  |                  |
      +-------------------+----------+---------------------------+------------------+
      1 row in set (0.00 sec)
      
      REMEMBER THIS RESULT, YOU'LL NEED IT IN STEP 7
    7. Log into SERVER_A into MySQL console, execute:slave stop;
      change master to master_host='2.2.2.2', master_user='replication_username', master_password='some_password', master_log_file='mysqld-bin.000001', master_log_pos=98748451;
      slave start;
      THAT’S IT, YOUR MYSQL CLUSTER READY
Posted By: lvlind
Last Edit: 06 Oct 2016 @ 03:51 PM

EmailPermalink
Tags


 

Responses to this post » (None)

 
Post a Comment

You must be logged in to post a comment.

Tags
Comment Meta:
RSS Feed for comments

 Last 50 Posts
Change Theme...
  • Users » 1
  • Posts/Pages » 34
  • Comments » 0
Change Theme...
  • VoidVoid « Default
  • LifeLife
  • EarthEarth
  • WindWind
  • WaterWater
  • FireFire
  • LightLight

About



    No Child Pages.

Need Help?



    No Child Pages.

Check your IP



    No Child Pages.