`
hcw1314520
  • 浏览: 7416 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
最近访客 更多访客>>
社区版块
存档分类
最新评论

How To Set Up Database Replication In MySQL

阅读更多
个人技术博客:http://www.cooli.cc/

Version 1.1
Author: Falko Timme <ft [at] falkotimme [dot] com>
Last edited: 01/14/2006

This tutorial describes how to set up database replication in MySQL. MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though.

In this tutorial I will show how to replicate the database exampledb from the master with the IP address 192.168.0.100 to a slave. Both systems (master and slave) are running Debian Sarge; however, the configuration should apply to almost all distributions with little or no modification.

Both systems have MySQL installed, and the database exampledb with tables and data is already existing on the master, but not on the slave.

I want to say first that this is not the only way of setting up such a system. There are many ways of achieving this goal but this is the way I take. I do not issue any guarantee that this will work for you!


1 Configure The Master
First we have to edit /etc/mysql/my.cnf. We have to enable networking for MySQL, and MySQL should listen on all IP addresses, therefore we comment out these lines (if existant):

#skip-networking
#bind-address            = 127.0.0.1 


Furthermore we have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master), which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate the database exampledb, so we put the following lines into /etc/mysql/my.cnf:

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=exampledb
server-id=1 


Then we restart MySQL:

/etc/init.d/mysql restart


Then we log into the MySQL database as root and create a user with replication privileges:

mysql -u root -p
Enter password:


Now we are on the MySQL shell.


GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>'; (Replace <some_password> with a real password!)
FLUSH PRIVILEGES;

Next (still on the MySQL shell) do this:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;


The last command will show something like this:

+---------------+----------+--------------+------------------+| File          | Position | Binlog_do_db | Binlog_ignore_db |+---------------+----------+--------------+------------------+| mysql-bin.006 | 183      | exampledb    |                  |+---------------+----------+--------------+------------------+1 row in set (0.00 sec)

Write down this information, we will need it later on the slave!

Then leave the MySQL shell:

quit;


--------------------------------------------------------------------------------

There are two possibilities to get the existing tables and data from exampledb from the master to the slave. The first one is to make a database dump, the second one is to use the LOAD DATA FROM MASTER; command on the slave. The latter has the disadvantage the the database on the master will be locked during this operation, so if you have a large database on a high-traffic production system, this is not what you want, and I recommend to follow the first method in this case. However, the latter method is very fast, so I will describe both here.

If you want to follow the first method, then do this:

mysqldump -u root -p<password> --opt exampledb > exampledb.sql (Replace <password> with the real password for the MySQL user root! Important: There is no space between -p and <password>!)

This will create an SQL dump of exampledb in the file exampledb.sql. Transfer this file to your slave server!

If you want to go the LOAD DATA FROM MASTER; way then there is nothing you must do right now.


--------------------------------------------------------------------------------

Finally we have to unlock the tables in exampledb:

mysql -u root -p
Enter password:
UNLOCK TABLES;
quit;


Now the configuration on the master is finished. On to the slave...

2 Configure The Slave

On the slave we first have to create the database exampledb:

mysql -u root -p
Enter password:
CREATE DATABASE exampledb;
quit;



--------------------------------------------------------------------------------

If you have made an SQL dump of exampledb on the master and have transferred it to the slave, then it is time now to import the SQL dump into our newly created exampledb on the slave:

mysql -u root -p<password> exampledb < /path/to/exampledb.sql (Replace <password> with the real password for the MySQL user root! Important: There is no space between -p and <password>!)

If you want to go the LOAD DATA FROM MASTER; way then there is nothing you must do right now.


--------------------------------------------------------------------------------

Now we have to tell MySQL on the slave that it is the slave, that the master is 192.168.0.100, and that the master database to watch is exampledb. Therefore we add the following lines to /etc/mysql/my.cnf:

server-id=2
master-host=192.168.0.100
master-user=slave_user
master-password=secret
master-connect-retry=60
replicate-do-db=exampledb


Then we restart MySQL:

/etc/init.d/mysql restart



--------------------------------------------------------------------------------

If you have not imported the master exampledb with the help of an SQL dump, but want to go the LOAD DATA FROM MASTER; way, then it is time for you now to get the data from the master exampledb:

mysql -u root -p
Enter password:
LOAD DATA FROM MASTER;
quit;



If you have phpMyAdmin installed on the slave you can now check if all tables/data from the master exampledb is also available on the slave exampledb.


--------------------------------------------------------------------------------

Finally, we must do this:

mysql -u root -p
Enter password:
SLAVE STOP;


In the next command (still on the MySQL shell) you have to replace the values appropriately:

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;

MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
MASTER_USER is the user we granted replication privileges on the master.
MASTER_PASSWORD is the password of MASTER_USER on the master.
MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.
Now all that is left to do is start the slave. Still on the MySQL shell we run

START SLAVE;
quit;


That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave. Test it!


原文地址:http://www.howtoforge.com/mysql_database_replication
分享到:
评论

相关推荐

    database replication

    接着,来看一下“Database Replication”这本书的具体内容。这本书是“Synthesis Lectures on Data Management”系列的一部分,由位于滑铁卢大学的M.Tamer Özsu编辑。这个系列预计会出版50至125页的短小精悍的出版...

    MySQL 8 Cookbook epub 格式

    Perform backup tasks, recover data and set up various replication topologies for your database Maximize performance by using new features of MySQL 8 like descending indexes, controlling query ...

    深入理解MySQL Group Replication.pdf

    MySQL Database Replication是MySQL数据库的一部分,旨在提供高可用性和高性能的解决方案。 用例: * 高可用性:MySQL Group Replication可以提供高可用性的解决方案,避免单点故障和数据丢失。 * 高性能:MySQL ...

    MySQL 8 Administrator's Guide

    A comprehensive guide to performing query optimization, security and a whole host of other administrative tasks in MySQL 8 Table of Contents: Introduction to MySQL 8 Installing & Upgrading MySQL 8 ...

    mysql-8-cookbook2018

    Chapter 9, Replication, explains how to set up various replication topologies. The recipes on switching a slave from master-slave to chain replication and switching a slave from chain replication to ...

    MySQL Group Replication 详细搭建部署过程

    MySQL Group Replication 详细搭建部署过程 MySQL Group Replication 是一种基于组的复制技术,用于容错系统中。它由多个服务器(节点)组成,每个节点都可以独立执行事务,而读写事务则会在于 group 内的其他节点...

    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

    PostgreSQL for Data Architects(PACKT,2015)

    After this, you'll learn how to set up replication, use load balancing to scale horizontally, and troubleshoot errors. Finally, you will get acquainted with useful tools available in the PostgreSQL ...

    OpenStack Trove(Apress,2015)

    The book shows you how to set up and configure the Trove DBaaS framework, use prepackaged or custom database implementations, and provision and operate a variety of databases - including MySQL, ...

    mysql replication搭建.docx

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

    MySQL 8 Administrator’s Guide

    You will also learn how to perform replication, scale your MySQL solution and implement effective security techniques. A special section on the common and not so common troubleshooting techniques for...

    PostgreSQL for Data Architects 原版PDF by Maymala

    this, you'll learn how to set up replication, use load balancing to scale horizontally, and troubleshoot errors. As you continue through this book, you will see the significant impact of configuration...

    Oracle TimesTen In-Memory Database Replication Guide 11g Release

    Oracle TimesTen In-Memory Database Replication Guide 11g Release 2 (11.2.2) 是一个详细的文档,旨在指导用户如何在Oracle TimesTen数据库系统中设置和管理复制功能。TimesTen是一款高性能的内存数据库系统,它...

    Ubuntu上MySQL的Replication配置

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

    如何设置MySQL同步(Replication)

    mysql&gt; GRANT REPLICATION SLAVE ON *.* TO 'rep'@'rep2' IDENTIFIED BY 'rep'; ``` 如果需要在从服务器上执行`LOAD TABLE FROM MASTER`或`LOAD DATA FROM MASTER`,还需赋予额外权限: ``` mysql&gt; GRANT FILE,...

    Introducing InnoDB Cluster: Learning the MySQL High Availability Stack

    Set up, manage, and configure the new InnoDB Cluster feature in MySQL from Oracle. If you are growing your MySQL installation and want to explore making your servers highly available, this book ...

    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数据库主从复制步骤

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

Global site tag (gtag.js) - Google Analytics