mysql配置m_m-m mysql 配置

1.grants

GRANT REPLICATION SLAVE,FILE ON *.* TO 'repl1'@'192.168.56.221' IDENTIFIED

BY '123456';

FLUSH PRIVILEGES;

GRANT REPLICATION SLAVE,FILE ON *.* TO 'repl2'@'192.168.56.77' IDENTIFIED

BY '123456';

FLUSH PRIVILEGES;

2.

(56.77)

log-bin=mysql-bin #slave会基于此log-bin来做replication

server-id=1 #master的标示

binlog-do-db=tongchao

binlog-ignore-db=mysql

replicate-do-db=tongchao

replicate-ignore-db=mysql

log-slave-updates

slave-skip-errors=all

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=1

#user = mysql #未知

#log-bin=mysql-bin #在从服务器上启动日志记录，不是必须，但是官方建议

#server-id= 1 #服务器编号,唯一

#binlog-do-db=asteriskcdrdb #要同步的数据库

#binlog-ignore-db=mysql #不同步的数据?

#replicate-do-db=asteriskcdrdb #复制的数据库

#replicate-ignore-db=mysql #未知

#log-slave-updates #如果一个MASTER 挂掉的话，另外一个马上接管。

#slave-skip-errors=all #

#sync_binlog=1 #同步时钟

#auto_increment_increment=2 #

#auto_increment_offset=1 #自增涨增量

(56.221)

log-bin=mysql-bin

server-id= 2

binlog-do-db=tongchao

binlog-ignore-db=mysql

replicate-do-db=tongchao

replicate-ignore-db=mysql

log-slave-updates #如果一个MASTER 挂掉的话，另外一个马上接管。

slave-skip-errors=all

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=2

#sync_binlog=1

#auto_increment_increment=2

#auto_increment_offset=2

3.重启mysql

4.

进入MYSQL的SHELL。

flush tables with read lock

show master status

56.77

*************************** 1. row ***************************

File: mysql-bin.000012

Position: 106

Binlog_Do_DB: tongchao

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

56.221

*************************** 1. row ***************************

File: mysql-bin.000001

Position: 98

Binlog_Do_DB: tongchao

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

5.在各自机器上执行CHANGE MASTER TO命令

56.77

change master to

master_host='192.168.56.221',

master_user='repl2',

master_password='123456',

master_log_file='mysql-bin.000001',

master_log_pos=98;

start slave;

56.221

change master to

master_host='192.168.56.77',

master_user='repl1',

master_password='123456',

master_log_file='mysql-bin.000012',

master_log_pos=106;

start slave;

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

show processlist\G

7、释放掉各自的锁，然后进行插数据测试。

unlock tables;

MYSQL 的 MASTER到MASTER的主主循环同步

环境:

A 192.168.104.2

B 192.168.104.3

A

grant replication slave,file on *.* to ‘repl1′@’192.168.104.3′ identified by ’123456′;

FLUSH PRIVILEGES ;

B

GRANT REPLICATION SLAVE , FILE ON * . * TO ‘repl2′@’192.168.104.2′ IDENTIFIED BY ’123456′;

FLUSH PRIVILEGES ;

关闭两台服务器的msyql服务

mysqladmin -u root -p shutdown

A：

user = mysql #未知

log-bin=mysql-bin #在从服务器上启动日志记录，不是必须，但是官方建议

server-id= 1 #服务器编号,唯一

binlog-do-db=asteriskcdrdb #要同步的数据库

binlog-ignore-db=mysql #不同步的数据?

replicate-do-db=asteriskcdrdb #复制的数据库

replicate-ignore-db=mysql #未知

log-slave-updates #如果一个MASTER 挂掉的话，另外一个马上接管。

slave-skip-errors=all #

sync_binlog=1 #同步时钟

auto_increment_increment=2 #

auto_increment_offset=1 #自增涨增量

B:

user = mysql

log-bin=mysql-bin

server-id= 2

binlog-do-db=asteriskcdrdb

binlog-ignore-db=mysql

replicate-do-db=asteriskcdrdb

replicate-ignore-db=mysql

log-slave-updates #如果一个MASTER 挂掉的话，另外一个马上接管。

slave-skip-errors=all

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=2

#sync_binlog=1

#auto_increment_increment=2

#auto_increment_offset=2

#服务器频繁的刷新日志。这个保证了在其中一台挂掉的话，日志刷新到另外一台。从而保证了数据的同步 。

重启这两台mysql server

然后执行/usr/bin/mysqld_safe & #把mysqld_safe放入后台执行 如果不能执行先stop mysqld

分别进入两台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.000002

Position: 98

Binlog_Do_DB: asteriskcdrdb,asteriskcdrdb

Binlog_Ignore_DB: mysql,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.000002

Position: 98

Binlog_Do_DB: asteriskcdrdb,asteriskcdrdb

Binlog_Ignore_DB: mysql,mysql

1 row in set (0.00 sec)

然后保证两台数据库的记录一致,然后继续往下:

在各自机器上执行CHANGE MASTER TO命令。

A:

mysql> change master to

-> master_host=’192.168.104.3′,

-> master_user=’repl2′,

-> master_password=’123456′,

-> master_log_file=’mysql-bin.000002′, #同B服务器当中File: mysql-bin.000002

-> master_log_pos=98; #同B服务器录中Position: 98

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.104.2′,

-> master_user=’repl1′,

-> master_password=’123456′,

-> master_log_file=’mysql-bin.000002′, #同A服务器当中File: mysql-bin.000002

-> master_log_pos=98; #同B服务器录中Position: 98

Query OK, 0 rows affected (0.02 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

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

A:

mysql>show processlist\G

mysql> show processlist\G

*************************** 1. row ***************************

Id: 74

User: root

Host: localhost

db: NULL

Command: Query

Time: 0

State: NULL

Info: show processlist

*************************** 2. row ***************************

Id: 159

User: system user

Host:

db: NULL

Command: Connect

Time: 100

State: Waiting for master to send event

Info: NULL

*************************** 3. row ***************************

Id: 160

User: system user

Host:

db: NULL

Command: Connect

Time: 100

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

*************************** 4. row ***************************

Id: 169

User: repl1

Host: 192.168.104.3:4240

db: NULL

Command: Binlog Dump

Time: 55

State: Has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

4 rows in set (0.00 sec)

B:

mysql>show processlist\G

mysql> show processlist\G

*************************** 1. row ***************************

Id: 4

User: root

Host: localhost

db: NULL

Command: Query

Time: 0

State: NULL

Info: show processlist

*************************** 2. row ***************************

Id: 7

User: repl2

Host: 152.104.141.19:58615

db: NULL

Command: Binlog Dump

Time: 51

State: Has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

*************************** 3. row ***************************

Id: 8

User: system user

Host:

db: NULL

Command: Connect

Time: 40

State: Waiting for master to send event

Info: NULL

*************************** 4. row ***************************

Id: 9

User: system user

Host:

db: NULL

Command: Connect

Time: 40

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

4 rows in set (0.01 sec)

如果出错误,查看/var/log/mysqld.log 当中的日志,注意权限、访问控制、master_log_file、master_log_pos 等值

释放掉各自的锁，然后进行插数据测试。

A:

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

B:

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

下面可以通过添加表，删除表，添加记录，删除记录等方法测试双master备份。

根据测试结果，失败再看日志，成功就大功告成