`

How To Control Mysql Replication

阅读更多

How To Control Mysql Replication ZT

1. Environment

  
  In 192.168.1.202

  Database: RMC_BILLING
  DB Account: root
  DB Password:


  In 192.168.1.204
  Database: RMC_BILLING
  DB Account: root
  DB Password:

  We need to replicate the following tables from 192.168.1.204 to 192.168.1.202.
  users
  plan
  profile
  promotion_plan
  roles
  service_settings
  credit_cards
  currency_exchange
  country
  conference_plan

2. Steps


  Because we need replicate the data from 204 to 202, So, Mysql server in 204 is master, and the 202 is slave.
 
  a. Ssh to 204, Open /etc/mysql/my.cnf mysql configuration file. Make sure the following two lines is uncommented.

           server-id               = 1
           log_bin                 = /var/log/mysql/mysql-bin.log

  b. Ssh to 202, Open /etc/mysql/my.cnf mysql configuration file. Make sure it has following lines.

        server-id=2
        log-slave-updates
        read-only=1
        replicate-do-db=RMC_BILLING
        replicate-do-table=RMC_BILLING.users
        replicate-do-table=RMC_BILLING.plan
        replicate-do-table=RMC_BILLING.profile
        replicate-do-table=RMC_BILLING.promotion_plan
        replicate-do-table=RMC_BILLING.roles
        replicate-do-table=RMC_BILLING.service_settings
        replicate-do-table=RMC_BILLING.credit_cards
        replicate-do-table=RMC_BILLING.currency_exchange
        replicate-do-table=RMC_BILLING.country
        replicate-do-table=RMC_BILLING.conference_plan
        relay-log-purge=1

  c. Connect to master mysql server, Create a mysql user for replication.
 
        mysql> grant replication slave on *.* to 'rep'@'%' identified by 'rep';

  d. Lock the tables on master mysql server, So we can dump the master server's data and import to slave server. Make master and slave has the same data.
 
        mysql> flush tables with read lock;

  e. Show master status, and record the data.

        mysql> show master status;
        +------------------+----------+--------------+------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
        +------------------+----------+--------------+------------------+
        | mysql-bin.000002 |      228 |              |                  |
        +------------------+----------+--------------+------------------+
        1 row in set (0.00 sec)
 
  f. Connect to slave server and run the following commands. Notice, the data of MASTER_LOG_FILE and MASTER_LOG_POS come from step 6.

        mysql> CHANGE MASTER TO
            -> MASTER_HOST='192.168.1.204',
            -> MASTER_USER='rep',
            -> MASTER_PASSWORD='rep',
            -> MASTER_LOG_FILE='mysql-bin.000002',
            -> MASTER_LOG_POS=228;
        Query OK, 0 rows affected (0.01 sec)   

  g. Start the slave thread.

        mysql> start slave;

  h. OK, the replication is completed, Don't forget to unlock the master's tables.

        mysql> unlock tables;



3. Start/Stop slave thread by Java code.

We can start/stop the slave thread by java code(actually by JDBC). First create a user with all privileges.

mysql> GRANT ALL PRIVILEGES ON *.* TO slave@"%"  IDENTIFIED BY 'slave';

OK, Now, We have a user which use to start/stop slave thread. The following code is how to start/stop slave thread.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;


public class Slave {

    /**
     * @param args
     */
    public static void main(String[] args) {
        String connString = "jdbc:mysql://192.168.1.202:3306/RMC_BILLING";
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            Connection connection = DriverManager.getConnection(connString, "slave", "slave");
            PreparedStatement ps = connection.prepareStatement("start slave");
            //The next line is used to stop the slave.
            //PreparedStatement ps = connection.prepareStatement("stop slave");
            int executeUpdate = ps.executeUpdate();
            System.out.println(executeUpdate);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}


OK, That's all you need to do.
分享到:
评论

相关推荐

    mysql replication搭建.docx

    ### MySQL Replication 架构与实现 #### 一、MySQL Replication 概念与优势 MySQL Replication 是一种数据复制机制,它允许将一个MySQL服务器(主服务器或Master)的数据异步复制到一个或多个其他MySQL服务器(从...

    How to Perform System Replication for SAP HANA 2.0

    How to Perform System Replication for SAP HANA 2.0 How to Perform System Replication for SAP HANA 2.0

    MySql Replication Tutorial

    MySql Replication Tutorial,关于MySql Replication 的 PPT

    完整精品数据库课件 MySQL从入门到精通 第18章 MySQL Replication(共27页).ppt

    MySQL Replication是MySQL数据库系统中的一个重要特性,它允许数据从一个主服务器(master)自动同步到一个或多个从服务器(slaves)。这种技术主要用于数据备份、负载均衡和高可用性设置,确保即使在主服务器出现...

    利用JDBC和MySQL Replication实现数据库集群

    针对这一情况,提出在现有硬件的基础上利用JDBC规范与MySQL Replication实现数据库集群从而解决数据访问瓶颈。其主要方法是在进行JDBC连接之前实现负载均衡,所有SQL请求由负载均衡器进行统一调度。在数据库端利用...

    MySQL Replication一主多从环境搭建.docx

    MySQL Replication是一种数据库复制技术,允许数据从一个MySQL服务器(主服务器)实时同步到其他一个或多个MySQL服务器(从服务器)。这种技术对于实现高可用性、负载均衡和数据备份至关重要。以下是对一主多从环境...

    MySQL Group Replication 详细搭建部署过程

    在创建复制环境时,需要设置 hostname 和 ip 映射在 db1、db2、db3 上,并建立复制账号 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%' IDENTIFIED BY 'rlpbright_1927@ys';。然后,在 db1、db2、db3 上依次...

    深入理解MySQL Group Replication.pdf

    深入理解MySQL Group Replication MySQL Group Replication是一种高可用性和高性能的解决方案,旨在提供数据库的高可用性和高性能。它是MySQL数据库的一部分,旨在提供高可用性和高性能的解决方案。 背景: 数据库...

    第18章 MySQL Replication PPT

    MySQL复制(Replication)是MySQL数据库系统中一种强大的功能,它允许数据从一个服务器(主服务器)异步地复制到一个或多个其他服务器(从服务器)。这种架构为高可用性、负载均衡和数据备份提供了基础。 在MySQL ...

    Ubuntu上MySQL的Replication配置

    胖子摸索出来的,Ubuntu上MySQL的Replication配置,的简单记录步骤

    How to Perform System Replication for SAP HANA 2.0.pdf

    HANA SR同步配置文档 官方同步配置标准SAP推荐文档 Business Continuity requires that the ... This paper briefly describes SAP HANA System Replication in a step-by-step manner to support High Availab

    mysql replication修改库名及复制单个表

    在深入探讨如何通过MySQL Replication实现库名修改与单个表的复制之前,我们先来了解MySQL Replication的基本概念及其工作原理。MySQL Replication是一种数据复制机制,它允许从一台服务器(主服务器)向另一台或多...

    mysql-replication配置文档

    mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%'; ``` 然后,锁定主服务器的所有表以便进行安全的数据备份,同时获取当前的二进制日志文件名和位置: ```sql mysql> FLUSH TABLES WITH READ LOCK; mysql> ...

    mysql-replication mysql数据库主从复制步骤

    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; FLUSH PRIVILEGES; ``` #### 5. 获取主服务器的二进制日志位置和偏移量 - 使用以下命令获取二进制日志的位置和偏移量: ```sql SHOW MASTER STATUS; ``` ###...

    基于MySQL Replication的数据库集群解决方案.pdf

    【MySQL Replication数据库集群解决方案】 在构建电子商务系统数据库时,常常面临单一服务器处理能力和网络带宽不足的问题,以及对系统可靠性的高要求和快速故障恢复的需求。随着用户数量的增加,需要灵活扩展...

    深入理解MySQL Group Replication

    ### 深入理解MySQL Group Replication #### 背景与定义 MySQL Group Replication是一种高可用性和可扩展性的解决方案,它通过在多个MySQL服务器之间自动同步数据来确保数据的一致性和可用性。该技术自MySQL 5.7.17...

    MySQL Replication(复制)用MySQL5.5提高可扩展性和可用性_5.5

    MySQL Replication(复制)已经在一些著名的网站和企业广泛应用以将数据库的扩展性提升到极限水平。对用户而言可以简单快速地为数据库创建多个副本,超越单个数据库实例容量的限制,弹性扩展数据库系统以满足快速增长...

Global site tag (gtag.js) - Google Analytics