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 架构与实现 #### 一、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
MySql Replication Tutorial,关于MySql Replication 的 PPT
MySQL Replication是MySQL数据库系统中的一个重要特性,它允许数据从一个主服务器(master)自动同步到一个或多个从服务器(slaves)。这种技术主要用于数据备份、负载均衡和高可用性设置,确保即使在主服务器出现...
针对这一情况,提出在现有硬件的基础上利用JDBC规范与MySQL Replication实现数据库集群从而解决数据访问瓶颈。其主要方法是在进行JDBC连接之前实现负载均衡,所有SQL请求由负载均衡器进行统一调度。在数据库端利用...
MySQL Replication是一种数据库复制技术,允许数据从一个MySQL服务器(主服务器)实时同步到其他一个或多个MySQL服务器(从服务器)。这种技术对于实现高可用性、负载均衡和数据备份至关重要。以下是对一主多从环境...
在创建复制环境时,需要设置 hostname 和 ip 映射在 db1、db2、db3 上,并建立复制账号 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%' IDENTIFIED BY 'rlpbright_1927@ys';。然后,在 db1、db2、db3 上依次...
深入理解MySQL Group Replication MySQL Group Replication是一种高可用性和高性能的解决方案,旨在提供数据库的高可用性和高性能。它是MySQL数据库的一部分,旨在提供高可用性和高性能的解决方案。 背景: 数据库...
MySQL复制(Replication)是MySQL数据库系统中一种强大的功能,它允许数据从一个服务器(主服务器)异步地复制到一个或多个其他服务器(从服务器)。这种架构为高可用性、负载均衡和数据备份提供了基础。 在MySQL ...
胖子摸索出来的,Ubuntu上MySQL的Replication配置,的简单记录步骤
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> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%'; ``` 然后,锁定主服务器的所有表以便进行安全的数据备份,同时获取当前的二进制日志文件名和位置: ```sql mysql> FLUSH TABLES WITH READ LOCK; mysql> ...
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; FLUSH PRIVILEGES; ``` #### 5. 获取主服务器的二进制日志位置和偏移量 - 使用以下命令获取二进制日志的位置和偏移量: ```sql SHOW MASTER STATUS; ``` ###...
【MySQL Replication数据库集群解决方案】 在构建电子商务系统数据库时,常常面临单一服务器处理能力和网络带宽不足的问题,以及对系统可靠性的高要求和快速故障恢复的需求。随着用户数量的增加,需要灵活扩展...
### 深入理解MySQL Group Replication #### 背景与定义 MySQL Group Replication是一种高可用性和可扩展性的解决方案,它通过在多个MySQL服务器之间自动同步数据来确保数据的一致性和可用性。该技术自MySQL 5.7.17...
MySQL Replication(复制)已经在一些著名的网站和企业广泛应用以将数据库的扩展性提升到极限水平。对用户而言可以简单快速地为数据库创建多个副本,超越单个数据库实例容量的限制,弹性扩展数据库系统以满足快速增长...