Mysql的实时同步 - 双机互备.

最新推荐文章于 2024-08-05 14:21:54 发布

weixin_33690367

最新推荐文章于 2024-08-05 14:21:54 发布

阅读量89

点赞数

文章标签：数据库 shell

原文链接：https://my.oschina.net/lwaif/blog/626522

版权

为什么80%的码农都做不了架构师？>>>

设置方法：

步一设

A 服务服 (192.168.1.43) 上用户为 backup, 123456 , 同步的数据库为test;

B 服务服 (192.168.1.23) 上用户为 root, 123456, 同步的数据库为test;

步二配置 mysql.ini:

A服务器

#Replication master

server-id = 10

log-bin="E:\MySQL\logs\mysql_binary_log"//必须先创建这个文件

binlog-do-db=test

# Replication slave

master-host="192.168.1.23"

master-user=root

master-password="123456"

master-port=3306

master-connect-retry=60

replicate-do-db=test

B服务器

#Replication master

server-id = 2

log-bin="c:\mysql5\logs\mysql_binary_log"

binlog-do-db=test

# Replication slave

master-host="192.168.1.43"

master-user=backup

master-password=123456

master-port=3306

master-connect-retry=60

replicate-do-db=test

=============================================================

解释:

3)binlog-do-db=test 表示需要备份的数据库是test这个数据库,

如果需要备份多个数据库，那么应该写多行,如下所示:

binlog-do-db=backup1

binlog-do-db=backup2

binlog-do-db=backup3

解释:

1) server-id=2表示本机器的序号, A,B的server-id 不能相同;

2)log-bin表示打开binlog,打开该选项才可以通过I/O写到Slave的relay-log,也是可以进行replication的前提;

其中mysql_binary_log是日志文件的名称，mysql将建立不同扩展名，文件名为mysql_binary_log的几个日志文件.

3) master-host="192.168.1.23" 表示A做slave时的master为192.168.1.23;

4) master-user=root 这里表示master上开放的一个有权限的用户,使其可以从slave连接到master并进行复制;

5) master-password=123456 表示授权用户的密码;

6) master-port=3306 master上MySQL服务Listen3306端口;

7) master-connect-retry=60 同步间隔时间;

8) replicate-do-db=test 表示同步backup数据库;

最后重新启动两台机器的mysql.

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

查看状态及调试

1,查看master的状态

SHOW MASTER STATUS;

Position 不应为0

2,查看slave的状态

show slave status;

Slave_IO_Running | Slave_SQL_Running 这两个字段应为 YES|YES.

show processlist;

会有两条记录与同步有关 state为 Has read all relay log; waiting for the slave I/O thread to update it

和s Waiting for master to send event .

3,错误日志

MySQL安装目录\data\Hostname.err

4,CHANGE MASTER TO

如果A 的 Slave 未启动 ,Slave_IO_Running 为No.

可能会是B的master 的信息有变化,

查看B SHOW MASTER STATUS;

记录下 File,Position 字段. 假设为'mysql_binary_log.000004',98 ;

在A 下执行:

Stop Slave;

CHANGE MASTER TO

MASTER_LOG_FILE = 'mysql_binary_log.000004',

MASTER_LOG_POS = 98 ;

Start Slave;

5,SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n;

如果A的Slave_SQL_Running 为No.

Err文件中记录:

Slave: Error 'Duplicate entry '1' for key 1' on query....

可能是master 未向slave 同步成功,但slave中已经有了记录。造成的冲突.

可以在A上执行

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n;

跳过几步。再

restart salve;

就可以了。

解决多台写服务器主自增长ID重复问题

二台Mysql服务器，他们的IP地址分别为：
A：192.168.0.97
B：192.168.0.98
数据库都是 test_3306
首先我给两台服务器的my.ini 贴出来
A：my.ini

server-id=2
master-host=192.168.0.98
master-user=mydb
master-password=123
master-port=3306
master-connect-retry=1
replicate-do-db=tbqu
log-bin=
log-slave-updates
binlog-ignore-db=mysql
slave-skip-errors=all

B:my.ini

server-id=1
master-host=192.168.0.97
master-user=mydb
master-password=123
master-port=3306
master-connect-retry=1
replicate-do-db=tbqu
log-bin=
log-slave-updates
binlog-ignore-db=mysql
slave-skip-errors=all

在这里，配置文件和主从配置方法基本上一样
log-slave-updates 这个参数一定要加上，否则不会给更新的记录些到二进制文件里
slave-skip-errors 是跳过错误，继续执行复制操作
其他参数请参照 http://www.tbqu.com/post/15.html

多主互备和主从复制有一些区别，因为多主中都可以对服务器有写权限，所以设计到自增长重复问题

出现的问题（多主自增长ID重复）
1:首先我们通过A,B的test表结构
2:掉A，在B上对数据表test(存在自增长ID)执行插入操作，返回插入ID为1
3:后停掉B，在A上对数据表test(存在自增长ID)执行插入操作，返回的插入ID也是1
4:然后我们同时启动A,B，就会出现主键ID重复

解决方法：
我们只要保证两台服务器上插入的自增长数据不同就可以了
如：A查奇数ID，B插偶数ID，当然如果服务器多的话，你可以定义算法，只要不同就可以了

在这里我们在A,B上加入参数，以实现奇偶插入

A：my.ini上加入参数

auto_increment_offset = 1
auto_increment_increment = 2
这样A的auto_increment字段产生的数值是：1, 3, 5, 7, …等奇数ID了

B：my.ini上加入参数

auto_increment_offset = 2
auto_increment_increment = 2
这样B的auto_increment字段产生的数值是：2, 4, 6, 8, …等偶数ID了

可以看出，你的auto_increment字段在不同的服务器之间绝对不会重复，所以Master-Master结构就没有任何问题了。当然，你还可以使用3台，4台，或者N台服务器，只要保证auto_increment_increment = N 再设置一下auto_increment_offset为适当的初始值就可以了，那样，我们的MySQL可以同时有几十台主服务器，而不会出现自增长ID重复。

在这里我们说的是2台MYSQL服务器，你也可以扩展到多台，实现方法类似
A -> B -> C-> D ->A
这样一个环形的备份结构就形成了，最后可要记住自增长ID（主键）要设计好哦，否则会出错的。

————————————————————————————————————————–

假定有三台Mysql服务器，他们的IP地址分别为：
192.168.1.8
192.168.1.88
192.168.1.188
在192.168.1.8的安装目录下找到my.ini文件，在该文件的最后加上：
server-id=1
log-bin
#asyntest1是我用来试验的数据库名称，到时候要换成你的数据库名字
binlog-do-db=asyntest1
#下面这句话很重要，只有加上它，从前一台机器上同步过来的数据才能同步到下一台机器
log-slave-updates
master-host=192.168.1.188
master-user=root
#在此处填入192.168.1.188的root密码
master-password=XXXXX
#asyntest1是我用来试验的数据库名称，到时候要换成你的数据库名字
replicate-do-db=asyntest1
master-connect-retry=10
#出现错误后忽略，如果不加这个，出现任何错误，同步进程会终止
slave-skip-errors=all

在192.168.1.88的安装目录下找到my.ini文件，在该文件的最后加上：
server-id=2
log-bin
#asyntest1是我用来试验的数据库名称，到时候要换成你的数据库名字
binlog-do-db=asyntest1
#下面这句话很重要，只有加上它，从前一台机器上同步过来的数据才能同步到下一台机器
log-slave-updates
master-host=192.168.1.8
master-user=root
#在此处填入192.168.1.8的root密码
master-password=XXXXX
#asyntest1是我用来试验的数据库名称，到时候要换成你的数据库名字
replicate-do-db=asyntest1
master-connect-retry=10
#出现错误后忽略，如果不加这个，出现任何错误，同步进程会终止
slave-skip-errors=all
在192.168.1.188的安装目录下找到my.ini文件，在该文件的最后加上：
server-id=3
log-bin
#asyntest1是我用来试验的数据库名称，到时候要换成你的数据库名字
binlog-do-db=asyntest1
#下面这句话很重要，只有加上它，从前一台机器上同步过来的数据才能同步到下一台机器
log-slave-updates
master-host=192.168.1.88
master-user=root
#在此处填入192.168.1.88的root密码
master-password=XXXXX
#asyntest1是我用来试验的数据库名称，到时候要换成你的数据库名字
replicate-do-db=asyntest1
master-connect-retry=10
#出现错误后忽略，如果不加这个，出现任何错误，同步进程会终止
slave-skip-errors=all

在192.168.1.8, 192.168.1.88, 192.168.1.188上建立完全一样的数据库asyntest1,重启这三台数据库，然后在任何一台机器上进行的更新操作，都会同步到另外的两台机器上，这是一种环形同步，在192.168.1.8有任何修改，会首先同步到192.168.1.88的机器上，88的机器再将同步的数据同步到 192.168.1.188的机器上。同样，如果在192.168.1.88上有任何更新，首先会同步到192.168.1.188的机器上，然后再同步到192.168.1.8的机器上；而在192.168.1.188的机器上有任何更新，首先会同步到192.168.1.8的机器上，然后在同步到 192.168.1.88的机器上。利用这种原理，可以解决任意多台机器的互相同步问题。

如果出现问题，首先请在每一台服务器上用命令行通过telnet命令检查其他机器3306端口的连通情况。另外，请检查各机器的防火墙设置和杀毒软件的配置。可将这些软件暂停后进行试验。

Mysql版本从5.1.7以后开始就不支持“master-host”类似的参数，所以以上方法只能做参考

刚刚抽空做了一下MYSQL 的主主同步。
把步骤写下来，至于会出现的什么问题，以后随时更新。这里我同步的数据库是TEST
1、环境描述。
主机：192.168.0.231（A）
主机：192.168.0.232（B）
MYSQL 版本为5.1.21
2、授权用户。
A：
mysql> grant replication slave,file on *.* to 'repl1'@'192.168.0.232' identified
by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
B：
mysql> grant replication slave,file on *.* to 'repl2'@'192.168.0.231' identified
by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
然后都停止MYSQL 服务器。

3、配置文件。
在两个机器上的my.cnf里面都开启二进制日志。
A：
user = mysql
log-bin=mysql-bin
server-id = 1
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all

skip-name-resolve

sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1

B：
user = mysql
log-bin=mysql-bin
server-id = 2
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all

skip-name-resolve

sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
至于这些参数的说明具体看手册。
红色的部分非常重要，如果一个MASTER 挂掉的话，另外一个马上接管。
紫红色的部分指的是服务器频繁的刷新日志。这个保证了在其中一台挂掉的话，日志刷新到另外一台。从而保证了数据的同步。
4、重新启动MYSQL服务器。
在A和B上执行相同的步骤
[root@localhost ~]# /usr/local/mysql/bin/mysqld_safe &
[1] 4264
[root@localhost ~]# 071213 14:53:20 mysqld_safe Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
/usr/local/mysql/bin/mysqld_safe: line 366: [: -eq: unary operator expected
071213 14:53:20 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

5、进入MYSQL的SHELL。
A：
mysql> flush tables with read lock\G
Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000007
Position: 528
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

B：
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 595
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
然后备份自己的数据，保持两个机器的数据一致。
方法很多。完了后看下一步。
6、在各自机器上执行CHANGE MASTER TO命令。
A：
mysql> change master to
    -> master_host='192.168.0.232',
    -> master_user='repl2',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000004',
    -> master_log_pos=595;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

B：
mysql> change master to
    -> master_host='192.168.0.231',
    -> master_user='repl1',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000007',
    -> master_log_pos=528;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

7、查看各自机器上的IO进程和 SLAVE进程是否都开启。
A：

mysql> show processlist\G
*************************** 1. row ***************************
Id: 2
User: repl
Host: 192.168.0.232:54475
db: NULL
Command: Binlog Dump
Time: 1590
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 2. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 1350
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 1149
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 4. row ***************************
Id: 5
User: root
Host: localhost
db: test
Command: Query
Time: 0
State: NULL
Info: show processlist
4 rows in set (0.00 sec)

B：

mysql> show processlist\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 2130
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 1223
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 4
User: root
Host: localhost
db: test
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 4. row ***************************
Id: 5
User: repl2
Host: 192.168.0.231:50718
db: NULL
Command: Binlog Dump
Time: 1398
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
4 rows in set (0.00 sec)

如果红色部分没有出现，检查DATA目录下的错误文件。

8、释放掉各自的锁，然后进行插数据测试。
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

插入之前两个机器表的对比：
A：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t11_innodb     |
| t22            |
+----------------+
B：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t11_innodb     |
| t22            |
+----------------+
从A机器上进行插入
A：
mysql> create table t11_replicas
    -> (id int not null auto_increment primary key,
    -> str varchar(255) not null) engine myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t11_replicas(str) values
    -> ('This is a master to master test table');
Query OK, 1 row affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t11_innodb     |
| t11_replicas   |
| t22            |
+----------------+
3 rows in set (0.00 sec)

mysql> select * from t11_replicas;
+----+---------------------------------------+
| id | str                                   |
+----+---------------------------------------+
| 1 | This is a master to master test table |
+----+---------------------------------------+
1 row in set (0.00 sec)

现在来看B机器：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t11_innodb     |
| t11_replicas   |
| t22            |
+----------------+
3 rows in set (0.00 sec)

mysql> select * from t11_replicas;
+----+---------------------------------------+
| id | str                                   |
+----+---------------------------------------+
| 1 | This is a master to master test table |
+----+---------------------------------------+
1 row in set (0.00 sec)

现在反过来从B机器上插入数据：
B：

mysql> insert into t11_replicas(str) values('This is a test 2');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t11_replicas;
+----+---------------------------------------+
| id | str                                   |
+----+---------------------------------------+
| 1 | This is a master to master test table |
| 2 | This is a test 2                      |
+----+---------------------------------------+
2 rows in set (0.00 sec)
我们来看A
A：
mysql> select * from t11_replicas;
+----+---------------------------------------+
| id | str                                   |
+----+---------------------------------------+
| 1 | This is a master to master test table |
| 2 | This is a test 2                      |
+----+---------------------------------------+
2 rows in set (0.00 sec)

好了。现在两个表互相为MASTER。

转载于:https://my.oschina.net/lwaif/blog/626522