mysql主主复制架构_Mariadb/Mysql 主主复制架构

Centos7+MySQL主主复制架构

主主架构：就是互为主从的架构

node1:10.211.55.4 centos7 mariadb-5.5.65

node2:10.211.55.6 centos7 mariadb-5.5.65

1、安装启动mysql

all:

1)安装mariadb软件

yum install mariadb-server -y

3)启动进程

systemctl start mariadb

2、mysql双主配置

1)创建双主配置文件

node1：

[mysqld]

server-id=1

log-bin=mysql-bin

relay-log=mysql-relay-bin

binlog-do-db=test1

log-slave-updates

expire_logs_days=7

auto_increment_offset=1

auto_increment_increment=2

replicate-do-db=test1

node2：

[mysqld]

server-id=2

#<==标识服务器id

log-bin=mysql-bin

#<==打开mysql二进制日志

relay-log=mysql-relay-bin

#<==打开mysql中继日志

binlog-do-db=test1

#<==指定数据库binlog日志记录哪个db

log-slave-updates

#<==更新的数据写入二进制日志，查询show variables like 'log_slave_updates';

expire_logs_days=7

#<==binlog日志自动过期清理设置为7天

auto_increment_offset=2

#<==控制列中值得增长值，也就是步长

auto_increment_increment=2

#<==确定increment_offset列值得起点值

replicate-do-db=test1

#<==指定slave需要复制哪个库

注意：systemctl restart mariadb

2)添加同步账户

mysql

grant replication slave ,replication client on *.* to slave@'10.211.%.%' identified by 'centos';

3)查看坐标值：

node1:

show master status;

[root@node1 my.cnf.d]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 2

Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 245 | test1 | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

MariaDB [(none)]>

node2:

show master status;

MariaDB [(none)]> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000004 | 245 | test1 | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

MariaDB [(none)]> exit

4)从服务器连接配置

node2:

MariaDB [(none)]> change master to

-> master_host='10.211.55.4',

-> master_user='slave',

-> master_password='centos',

-> master_log_file='mysql-bin.000001',

#<==指明初始复制时的mysql1中的binlog文件

-> master_log_pos=245;

#<==指明初始复制时binlog文件的位置

Query OK, 0 rows affected (0.01 sec)

node1:

MariaDB [(none)]> change master to

-> master_host='10.211.55.6',

-> master_user='slave',

-> master_password='centos',

-> master_log_file='mysql-bin.000004',

-> master_log_pos=245;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>

5)开启开启slave线程

all：

mysql

MariaDB [(none)]> start slave;

6)查询slave状态

主要关注：

Slave_IO_Running: Yes #<==slave-IO线程是否yes

Slave_SQL_Running: Yes #<==slave-SQL线程是否yes

Replicate_Do_DB: test1 #<==同步的数据库是否正确

node1：

MariaDB [(none)]> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.211.55.6

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 245

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 529

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test1

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 245

Relay_Log_Space: 823

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2

1 row in set (0.00 sec)

ERROR: No query specified

node2：

MariaDB [(none)]> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.211.55.4

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 245

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 529

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test1

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 245

Relay_Log_Space: 823

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]>

7)测试

mysql是双向同步

在主上创建test_table数据表，插入master的数据

node1：

mysql

MariaDB [(none)]> use test1;

MariaDB [test1]> create table test_table (kk int auto_increment not null primary key, vv varchar(10));

MariaDB [test1]> insert into test_table(vv) values('master');

MariaDB [test1]> select * from test_table;

+----+--------+

| kk | vv |

+----+--------+

| 1 | master |

+----+--------+

1 row in set (0.00 sec)

备节点查询

node2：

mysql

MariaDB [test1]> use test1;

MariaDB [test1]> select * from test_table;

+----+--------+

| kk | vv |

+----+--------+

| 1 | master |

+----+--------+

1 row in set (0.00 sec)

备节点插入数据backup

node2:

MariaDB [test1]> insert into test_table(vv) values('backup');

MariaDB [test1]> select * from test_table;

+----+--------+

| kk | vv |

+----+--------+

| 1 | master |

| 2 | backup |

+----+--------+

2 rows in set (0.00 sec)

主节点查询backup有没有同步过来

node1：

mysql

MariaDB [test1]> select * from test_table;

+----+--------+

| kk | vv |

+----+--------+

| 1 | master |

| 2 | backup |

+----+--------+

2 rows in set (0.00 sec)