- 浏览: 1042421 次
- 性别:
- 来自: 广州
文章分类
最新评论
-
wenson:
lzjzy520 写道 求 项目完整元代码已经够清楚了,看不懂 ...
使用Spring JavaMail发送邮件总结 -
lzjzy520:
求 项目完整元代码
使用Spring JavaMail发送邮件总结 -
xiejx618:
能提高多大的性能? 不能用数据来说话吧希望你能用jmeter做 ...
利用APR本地库提高Tomcat性能 -
mrwalter:
学习了,挺好用的,局域网内速度很快
ssh远程文件传输命令scp -
wcily123:
不错
ssh远程文件传输命令scp
Version 1.0 Since version 5, MySQL comes with built-in support for master-master replication, solving the problem that can happen with self-generated keys. In former MySQL versions, the problem with master-master replication was that conflicts arose immediately if node A and node B both inserted an auto-incrementing key on the same table. The advantages of master-master replication over the traditional master-slave replication are that you don't have to modify your applications to make write accesses only to the master, and that it is easier to provide high-availability because if the master fails, you still have the other master. I do not issue any guarantee that this will work for you! In this tutorial I will show how to replicate the database exampledb from the server server1.example.com with the IP address 192.168.0.100 to the serverserver2.example.com with the IP address 192.168.0.101 and vice versa. Each system is the slave of the other master and the master of the other slave at the same time. Both systems are running Debian Etch; however, the configuration should apply to almost all distributions with little or no modifications. If MySQL 5.0 isn't already installed on server1 and server2, install it now: server1/server2: apt-get install mysql-server-5.0 mysql-client-5.0 To make sure that the replication can work, we must make MySQL listen on all interfaces, therefore we comment out the line bind-address = 127.0.0.1 in/etc/mysql/my.cnf: server1/server2: vi /etc/mysql/my.cnf Restart MySQL afterwards: server1/server2: /etc/init.d/mysql restart Then check with server1/server2: netstat -tap | grep mysql that MySQL is really listening on all interfaces: server1:~# netstat -tap | grep mysql Afterwards, set a MySQL password for the user root@localhost: server1/server2: mysqladmin -u root password yourrootsqlpassword Next we create a MySQL password for root@server1.example.com: server1: mysqladmin -h server1.example.com -u root password yourrootsqlpassword Now we set up a replication user slave2_user that can be used by server2 to access the MySQL database on server1: server1: mysql -u root -p On the MySQL shell, run the following commands: server1: GRANT REPLICATION SLAVE ON *.* TO 'slave2_user'@'%' IDENTIFIED BY 'slave2_password'; Now we do the last two steps again on server2: server2: mysqladmin -h server2.example.com -u root password yourrootsqlpassword mysql -u root -p GRANT REPLICATION SLAVE ON *.* TO 'slave1_user'@'%' IDENTIFIED BY 'slave1_password'; In the following I will assume that the database exampledb is already existing on server1, and that there are tables with records in it. We will set up replication of exampledb to server2, and afterwards we set up replication of exampledb from server2 to server1. Before we start setting up the replication, we create an empty database exampledb on server2: server2: mysql -u root -p CREATE DATABASE exampledb; Now we set up master-master replication in /etc/mysql/my.cnf. The crucial configuration options for master-master replication are auto_increment_incrementand auto_increment_offset: Let's assume we have N MySQL nodes (N=2 in this example), then auto_increment_increment has the value N on all nodes, and each node must have a different value for auto_increment_offset (1, 2, ..., N). Now let's configure our two MySQL nodes: server1: vi /etc/mysql/my.cnf Search for the section that starts with [mysqld], and put the following options into it (commenting out all existing conflicting options): Then restart MySQL: server1: /etc/init.d/mysql restart Now do the same on server2: server2: vi /etc/mysql/my.cnf server2: /etc/init.d/mysql restart Next we lock the exampledb database on server1, find out about the master status of server1, create an SQL dump of exampledb (that we will import intoexampledb on server2 so that both databases contain the same data), and unlock the database so that it can be used again: server1: mysql -u root -p On the MySQL shell, run the following commands: server1: USE exampledb; The last command should show something like this (please write it down, we'll need it later on): mysql> SHOW MASTER STATUS; Now don't leave the MySQL shell, because if you leave it, the database lock will be removed, and this is not what we want right now because we must create a database dump now. While the MySQL shell is still open, we open a second command line window where we create the SQL dump snapshot.sql and transfer it to server2 (using scp): server1: cd /tmp Afterwards, you can close the second command line window. On the first command line window, we can now unlock the database and leave the MySQL shell: server1: UNLOCK TABLES; On server2, we can now import the SQL dump snapshot.sql like this: server2: /usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave Afterwards, we must find out about the master status of server2 as well and write it down: server2: mysql -u root -p USE exampledb; SHOW MASTER STATUS; mysql> SHOW MASTER STATUS; Then unlock the tables: server2: UNLOCK TABLES; and run the following command to make server2 a slave of server1 (it is important that you replace the values in the following command with the values you got from the SHOW MASTER STATUS; command that we ran on server1!): CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave2_user', MASTER_PASSWORD='slave2_password', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=98; Finally start the slave: server2: START SLAVE; Then check the slave status: server2: SHOW SLAVE STATUS; It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output (otherwise something went wrong, and you should check your setup again and take a look at /var/log/syslog to find out about any errors): Afterwards, you can leave the MySQL shell on server2: server2: quit Now the replication from server1 to server2 is set up. Next we must configure replication from server2 to server1. To do this, we stop the slave on server1 and make it a slave of server2: server1: mysql -u root -p STOP SLAVE; Make sure that you use the values of the SHOW MASTER STATUS; command that you ran on server2 in the following command: server1: CHANGE MASTER TO MASTER_HOST='192.168.0.101', MASTER_USER='slave1_user', MASTER_PASSWORD='slave1_password', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=783; Then start the slave on server1: server1: START SLAVE; Then check the slave status: server1: SHOW SLAVE STATUS; It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output (otherwise something went wrong, and you should check your setup again and take a look at /var/log/syslog to find out about any errors): Afterwards you can leave the MySQL shell: quit If nothing went wrong, MySQL master-master replication should now be working. If it isn't, please check /var/log/syslog for MySQL errors on server1 andserver2. Setting Up Master-Master Replication With MySQL 5 On Debian Etch
Setting Up Master-Master Replication With MySQL 5 On Debian Etch
Author: Falko Timme <ft [at] falkotimme [dot] com>
Last edited 10/15/20071 Preliminary Note
2 Installing MySQL 5.0
[...]
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
[...]
tcp 0 0 *:mysql *:* LISTEN 2671/mysqld
server1:~#
FLUSH PRIVILEGES;
quit;
FLUSH PRIVILEGES;
quit;3 Some Notes
quit;4 Setting Up Replication
[...]
[mysqld]
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
master-host = 192.168.0.101
master-user = slave1_user
master-password = slave1_password
master-connect-retry = 60
replicate-do-db = exampledb
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = exampledb
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
expire_logs_days = 10
max_binlog_size = 500M
[...]
[...]
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
master-host = 192.168.0.100
master-user = slave2_user
master-password = slave2_password
master-connect-retry = 60
replicate-do-db = exampledb
log-bin= /var/log/mysql/mysql-bin.log
binlog-do-db = exampledb
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
expire_logs_days = 10
max_binlog_size = 500M
[...]
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 | 98 | exampledb | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql
scp snapshot.sql root@192.168.0.101:/tmp
quit;
cd /tmp
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql
FLUSH TABLES WITH READ LOCK;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 | 783 | exampledb | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
mysql> SHOW SLAVE STATUS;
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 192.168.0.100 | slave2_user | 3306 | 60 | mysql-bin.000009 | 98 | slave-relay.000002 | 235 | mysql-bin.000009 | Yes | Yes | exampledb | | | | | | 0 | | 0 | 98 | 235 | None | | 0 | No | | | | | | 0 |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)
mysql>
mysql> SHOW SLAVE STATUS;
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 192.168.0.101 | slave1_user | 3306 | 60 | mysql-bin.000009 | 783 | slave-relay.000002 | 235 | mysql-bin.000009 | Yes | Yes | exampledb | | | | | | 0 | | 0 | 783 | 235 | None | | 0 | No | | | | | | 0 |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)
mysql>
5 Links
评论
这不是山寨,这是原版收藏,最后面有原文地址。
发表评论
-
mysql主从同步报错故障处理总结 [数据库技术]
2015-12-14 13:37 4529前言 在发生故障切换后,经常遇到的问题就是同步报错,数据 ... -
MySQL Replication 主从同步原理及配置
2014-11-07 11:35 3220MySQL的Replication是一种多个MySQL的数据 ... -
关于MySQL 查询表数据大小的总结
2014-09-18 18:14 5006一:关于mysql表数据大小 我们知道mysql存储数据文 ... -
MySQL使用Event定时执行任务
2013-11-07 10:56 7082一、基本概念 mysql5 ... -
MySQL grant 语法的详细解析(帐号权限管理)
2013-10-09 16:31 1390记录一下mysql grant的一些用法。MySQL数据库 ... -
MySQL函数不能创建的解决方法
2013-09-18 11:34 1185在使用MySQL数据库时,有时会遇到MySQL函数不能创建的 ... -
mysql里的SUBSTRING
2013-09-16 11:23 1252截取字符串 substring(str, pos) subst ... -
MYSQL EVENT 使用手册
2012-10-15 19:28 1008一、前言 自MySQL5.1.6起,增加了一个非常有特 ... -
备忘:mysql备份的帐号权限
2012-01-13 04:55 1922mysql备份的帐号权限,只需要:show databases ... -
怎样用命令查看Mysql数据库大小?
2011-12-14 01:51 52871、进去指定schema 数据库(存放了其他的数据库的信息) ... -
解决InnoDB出现“the table is full”的问题
2011-12-09 02:40 1699最近数据库数据量及访问频率比较大的两个表出现了the tabl ... -
mysql从服务器出现的错误解決方法:Slave_SQL_Running: No(主-从)
2011-10-19 00:16 24961、出现错误提示、 Slave I/O: ... -
spring+atomikos+JTA多數據源配置
2011-05-06 22:54 2571spring+atomikos+JTA多數據源配置中,需要注意 ... -
mysql innodb数据库的优化
2011-01-12 19:48 3115硬件概況: linux服务器 ... -
Mac OS X 中mysql的配置文件my.cnf的位置
2010-10-10 21:33 24074OS X 系统安装的mysql默认是不用my.cnf配置文件的 ... -
Mysql双机备份方案
2010-06-13 13:55 2820运行环境: 虚拟机安装的是CentOS5 Mysql版 ... -
监控sql执行性能的工具:jdbmonitor
2010-02-16 01:18 1898一个用来监控sql执行性能的工具:jdbmonitor ... -
使用mysqlsla分析mysql日志
2009-12-12 03:53 2659mysqlsla是hackmysql.com推出的一款My ... -
持久层的数据库锁控制
2009-12-05 09:51 4014网站最近并发访问量增多,log常一旦抛出以下异常: Caus ... -
在linux里定时备份mysql
2009-11-26 01:56 17661、导出远程mysql数据库到本地成为sql文件: file ...
相关推荐
windows下mysql双向同步 现在给出A 的双向同步的完整配置 [mysqld] server-id=1 log-bin=C:\log-bin.log binlog-do-db=dn2 master-host=192.168.1.181 master-user=backup master-password=1234 master-port=3306 ...
MySQL的模式同步mysql表结构自动同步工具将用于线上数据库结构变化同步到本地环境!支持功能:同步新表同步轴向变动:添加,修改同步索引变动:添加,修改支持预览(只对比不同步变动)邮件通知变动结果支持屏蔽更新...
一主一从: Master: OS:centos release 5.6 DB:mysql 5.5.8 IP:192.168.1.2 Slave: OS:centos release 5.6 DB:mysql 5.5.8 IP:192.168.1.3 修改主机Master配置文件 (/etc/my.cnf) 代码如下: [mysqld] //至少要有...
Mysql主从同步备份策略 五月 18th, 2009 at 17:30 - 3,803 views 环境: 主从服务器上的MySQL数据库版本同为5.1.34 主机IP:192.168.0.1 从机IP:192.168.0.2 一. MySQL主服务器配置 1.编辑配置文件/etc/my.cnf # ...
MySQL 的 MASTER 到MASTER的主主循环同步
mysql主从同步和mysql双主多存数据同步配置,Master-MASTER,MASTER-SLAVER
MYSQL的masterslave数据同步配置
mysql数据库windows下mysql主从同步备份步骤 haha_yong2人评论2507人阅读2010-04-29 11:13:54 windows下mysql主从同步备份步骤 A:主服务器 IP地址:192.168.1.124 B:从服务器 IP地址:192.168.1.125 前提:设置...
│ 7_MySQL半同步复制.mp4 │ 作业.docx │ ├─新版MySQL DBA综合实战班 第10天 │ │ 1_课后作业讲解.mp4 │ │ 2_MySQL传统复制手动切换和GTID复制原理及切换.mp4 │ │ 3_Mycat原理和schema配置讲解.mp4 │ │ ...
host=192.168.1.222 master-user=repl master-password=123456 master-port=3306 master-connect-retry=60 replicate-do-db=repl //同步的数据库,不写本行 表示 同步所有数据库 然后重启slave机的mysql 在slave机中...
5、启动从库,可以进行主从库数据同步 /opt/mysql/share/mysql/mysql start /opt/mysql/bin/mysql -u root -p mysql>load data from master; 说明:这一步不这么做也可以,用数据库倒入或者直接目录考过来也行。 6...
MySQL主从复制(Master-Slave)的方式来同步数据,再通过读写分离(MySQL-Proxy/Amoeba)来提升数据库的并发负载能力 这样的方案来进行部署与实施的
主要为大家详细介绍了MYSQL5.6.33数据库主从(Master/Slave)同步安装与配置,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
基于canal的mysql和elasticsearch实时同步方案,支持增量同步和全量同步。
简单实用的同步工具,实现mysql数据库中数据定期同步到elasticsearch,只需简单的配置,便能达到非凡的效果,支持elasticsearch 5.X版本
重新同步MysqlMasterSlaveReplication 如何重置(重新同步)MySQL 主从复制先决条件: 此工具必须在主服务器上运行ssh 访问两台服务器(主从) 使用 ssh 从主服务器到从属服务器的 ssh 访问(谷歌:ssh without ...
主要介绍了mysql(master/slave)主从复制原理及配置图文详解,以前脚本之家小编发过相关的内容,但这么好的非常少见特分享一下,需要的朋友可以参考下
环境:PC:ubuntu 10.10 192.168.1.112(master) 192.168.10.245(slave) MySQL : 5.1.49-1ubuntu8.1-... #master 同步设置 server-id = 1 log_bin = /var/log/mysql/mysql-test-bin.log expire_logs_days =
mysql半同步复制和异步复制的差别如上述架构图所示:在mysql异步复制的情况下,Mysql Master Server将自己的Binary Log通过复制线程传输出去以后,Mysql Master Sever就自动返回数据给客户端,而不管slave上是否接受...