mysql主从复制-白红宇

mysql主从复制

阅读量：6552 次

发布时间：2019-06-24

本文共 7843 字，大约阅读时间需要 26 分钟。

【原理：MySQL 同步机制基于 master 把所有对数据库的更新、删除等)都记录在二进制日志里。因此，想要启用同步机制，在 master 就必须启用二进制日志。每个 slave 接受来自master 上在二进制日志中记录的更新操作，因此在 slave 上执行了这个操作的一个拷贝。】

slave 同步失败主要问题：<?xml:namespace prefix="o">?xml:namespace>

二进制日志只是从启用二进制日志开始的时刻才记录更新操作的，所有的 slave 必须在

启用二进制日志时把 master 上已经存在的数据拷贝过来。如果运行同步时 slave 上的数据和 master 上启用二进制日志时的数据不一致的话，那么 slave 同步就会失败。

数据库初始同步方式：

把 master 上的数据拷贝过来的方法之一实在 slave 上执行 LOAD DATA FROM MASTER 语句。不过要注意，LOAD DATA FROM MASTER 是从 MySQL4.0.0 之后才开始可以用的，而且只支持 master 上的 MyISAM 类型表

暂停主服务器，导入数据文件，在从服务器上导入。

【MySQL 同步细节】

MySQL 同步功能由 3 个线程(master 上 1 个，slave 上 2 个)来实现。执行 START SLAVE语句后，slave 就创建一个 I/O 线程。I/O 线程连接到 master 上，并请求 master 发送二进制日志中的语句。master 创建一个线程来把日志的内容发送到 slave 上。这个线程在 master

上执行 SHOW PROCESSLIST 语句后的结果中的 Binlog Dump 线程便是。slave 上的 I/O 线程读取 master 的 Binlog Dump 线程发送的语句，并且把它们拷贝到其数据目录下的中继日志(relay logs)中。第三个是 SQL 线程，salve 用它来读取中继日志，然后执行它们来更新数

据。如上所述，每个 mster/slave 上都有 3 个线程。每个 master 上有多个线程，它为每个slave 连接都创建一个线程，每个 slave 只有 I/O 和 SQL 线程。在 MySQL4.0.2 以前，同步只需 2 个线程(master 和 slave 各一个)。slave 上的 I/O 和 SQL 线程合并成一个了，它不使用中继日志。slave 上使用 2 个线程的优点是，把读日志和执行分开成 2 个独立的任务。执行任务如果慢的话，读日志任务不会跟着慢下来。例如，如果 slave 停止了一段时间，那么

I/O 线程可以在 slave 启动后很快地从 master 上读取全部日志，尽管 SQL 线程可能落后 I/O线程好几的小时。如果 slave 在 SQL 线程没全部执行完就停止了，但 I/O 线程却已经把所有的更新日志都读取并且保存在本地的中继日志中了，因此在 slave 再次启动后就会继续执行它们了。这就允许在 master 上清除二进制日志，因为 slave 已经无需去 master 读取更新日

志了。执行SHOW PROCESSLIST语句就会告诉我们所关心的master和slave上发生的情况。

地址参数设定

名称

角色

IP 地址

Master-MySQL

（RHEL5.3）

Master-MySQL

A（nod1）

eth0:192.168.1.1/24

GW:192.168.1.1

Slave-MySQL

(RHEL5.3)

Slave-MySQL

B（nod2）

eth0:192.168.1.2/24

GW:192.168.1.1

在这里我以root授权为例

基础安装mysql

[root@localhost ~]# mount /dev/cdrom /media/

[root@localhost ~]# vim /etc/yum.repos.d/rhel-debuginfo.repo

baseurl=file:///media/Server

enabled=1

gpgcheck=0

[root@localhost ~]# yum -y install mysql-server

[root@localhost ~]# service mysqld start

[root@localhost ~]# mysqladmin -uroot password 123456

Node2上同样方式安装mysql，并做如上操作

在这里我就用test数据库来做测试，在test数据库里新建一个data表，并添加一些数据，具体操作如下：

[root@localhost ~]# mysql -u root -p123456

mysql> use test;

Database changed

mysql> create table data(name VARCHAR(20), address VARCHAR(50), phone VARCHAR(20));

Query OK, 0 rows affected (0.01 sec)

mysql> create table data(name VARCHAR(20), address VARCHAR(50), phone VARCHAR(20));

Query OK, 0 rows affected (0.01 sec)

mysql> select * from data;

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

| name | address | phone |

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

| wuxinglai | neimenggu | 135220449221 |

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

设置数据库同步帐户：

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select user,host from user;

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

| user | host |

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

| root | 127.0.0.1 |

| root | 192.168.1.2 |

| | localhost |

| root | localhost |

| | localhost.localdomain |

修改配置文件：

停止 mysql 服务

root@localhost ~]# service mysqld stop

在[mysqld]中加入以下内容

[root@localhost ~]# vim /etc/my.cnf

8 server-id=1

9 log-bin

10 binlog-do-db=test

11 max_binlog_size=104857600

12 replicate-same-server-id

13 master-host=192.168.1.2

14 master-user=root

15 master-password=123456

16 master-port=3306

17 master-connect-retry=60

18 replicate-do-db=test

19 binlog-ignore-db=mysql

以下是参数说明

server-id=1 #设置服务器的 ID 号

log-bin #设置同步 log

binlog-do-db=test #设置同步数据库

max_binlog_size=104857600 #设置同步 log 最大 size:104857600 字节

replicate-same-server-id #在复制过程中同步相同的 master id 号

#下面指定自己作为客户端同步时，与主机之间同步的设置信息

master-host=192.168.1.2 #主机IP

master-user=backup #登陆服务端的账户名

master-password=backup #登陆服务端的账户密码

master-port=3306 #服务端打开的端口

master-connect-retry=60 #与服务端断点重试间隔为 60 秒

replicate-do-db=test #表示同步test数据库

binlog-ignore-db=mysql #设置不同步的数据库

[root@localhost ~]# service mysqld start

将test.sql复制到mysqlB的/tmp目录下

[root@localhost ~]# mysqldump -h localhost -u root -p123456 test> test1.sql

[root@localhost ~]# scp test.sql

至此nod1 服务器上有关mysql的设置已完成，下一步开始配置nod2

设置nod2

设置数据库同步帐户：

[root@localhost ~]# mysql -u root -p123456

授与从 192.168.1.1 主机上登录用户root数据复制权限

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* to 'root'@'192.168.1.1' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

刷新权限

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

更改Mysql配置文件/etc/my.cnf

[root@localhost ~]# service mysqld stop

[root@localhost ~]# vi /etc/my.cnf

8 server-id=2

9 log-bin

10 binlog-do-db=test

11 max_binlog_size=104857600

12 replicate-same-server-id

13 master-host=192.168.1.1

14 master-user=root

15 master-password=123456

16 master-port=3306

17 master-connect-retry=60

18 replicate-do-db=test

19 binlog-ignore-db=mysql

还原从mysqlA备份过的test1.sql

[root@localhost tmp]# mysql -u root -p test < /tmp/test1.sql

重启两边的 mysql 服务

在 primary (A)服务器上 MySQL 命令符下输入

mysql> show master status;

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

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

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

1 row in set (0.00 sec

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.2

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqld-bin.000003

Read_Master_Log_Pos: 98

Relay_Log_File: mysqld-relay-bin.000007

Relay_Log_Pos: 236

Relay_Master_Log_File: mysqld-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test

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: 98

Relay_Log_Space: 236

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

1 row in set (0.00 sec)

mysql>stop slave;

mysql>start slave;

Show Slave status:此处 Slave_IO_Running ,Slave_SQL_Running 都应该是 yes,表示从库的 I/O,Slave_SQL

线程都正确开启.

在 Mysql 中可通过以下命令来查看主从状态

show master status 查看 master 状态

show slave status 查看 slave 状态

show processlist G 查看当前进程

stop slave 暂时停止 slave 进程

start slave 开始 slave 进程

错误日志

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;

验证

在A上test插入一条数据

mysql> use test;

mysql> insert into data(name,address,phone) values('yangchunlin','zhaqi','17590305988');

Query OK, 1 row affected (0.00 sec)

mysql> select * from data;

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

| name | address | phone |

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

| wuxinglai | neimenggu | 135220449221 |

| yangchunlin | zhaqi | 17590305988 |

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

2 rows in set (0.00 sec)

在B上查看

mysql> select * from data;

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

| name | address | phone |

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

| wuxinglai | neimenggu | 135220449221 |

| yangchunlin | zhaqi | 17590305988 |

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

2 rows in set (0.00 sec)

转载于:https://blog.51cto.com/wxlxiao5/1167587

你可能感兴趣的文章

第 45 章 HyperDex

查看>>

会议室预定系统实践(轻松解放开发) - PostgreSQL tsrange(时间范围类型) + 排他约束...

查看>>

Centos6安装SaltStack

查看>>

世纪互联发布全新云战略，打造混合IT服务平台

查看>>

关于在angular2及以上版本引入bootstrap 并有提示功能

Spring Cloud微服务分布式云架构-集成项目

查看>>

namenode与secondary namenode简要学习

Linux监控平台介绍，zabbix监控介绍，安装zabbix，忘记Admin密码如何做

查看>>

公众号消息会话开发

查看>>

The method xx方法( ) is undefined for the type XX类

技术沙龙|区块链商用落地的策略与技术坑-区块链扩展和Fabric商用（杭州）

查看>>