Mysql主从复制详解

最新推荐文章于 2024-08-18 00:19:58 发布

富士康质检员张全蛋

最新推荐文章于 2024-08-18 00:19:58 发布

阅读量465

点赞数 1

分类专栏： Mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_34556414/article/details/104990915

版权

Mysql 专栏收录该内容

35 篇文章 5 订阅

订阅专栏

MYSQL主从

随着访问量的不断增加，单台MySQL数据库服务器压力不断增加，需要对MYSQL进行优化和架构改造，MYQSL优化如果不能明显改善压力情况，可以使用高可用、主从复制、读写分离来、拆分库、拆分表来进行优化。

MYSQL主从复制集群在中小企业、大型企业中被广泛使用，MYSQL主从复制的目的是实现数据库冗余备份，将Master数据库数据定时同步至Slave库中，一旦Master数据库宕机，可以将WEB应用数据库配置快速切换至Slave数据库，确保WEB应用较高的可用性。

MySQL主从主要作用

实现备份

实现故障转移

实现读写分离

MySQL主从架构

主从工作原理

主从前提是作为主服务器角色的数据库服务器必须开启二进制日志

主服务器上面的任何修改都会通过自己的 I/O tread(I/O 线程)保存在二进制日志 Binary log 里面。从服务器上面也启动一个 I/O thread，通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个Realy log（中继日志）里面。从服务器上面同时开启一个 SQL thread 定时检查 Realy log(这个文件也是二进制的)，如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。

每个从服务器都会收到主服务器二进制日志的全部内容的副本。从服务器设备负责决定应该执行二进制日志中的哪些语句。除非另行指定，否则主从二进制日志中的所有事件都在从站上执行。如果需要，您可以将从服务器配置为仅处理一些特定数据库或表的事件。

MySQL主从部署 centos7.4 mysql 5.5版本源码安装

server1	192.168.179.99	master
Server2	192.168.179.100	Slave

配置master端（开启二进制文件，server-id唯一，给从库授权）

（1）[root@localhost mysql-5.5.60]# vim /usr/local/mysql55/my.cnf --在配置文件里面开启二进制文件log-bin=jfedu-bin，源码安装默认开启了，如果是yum安装将该行添加进配置文件[mysqld]下面

# Replication Master Server (default)

# binary logging is required for replication

（2）[root@localhost mysql-5.5.60]# vim /usr/local/mysql55/my.cnf --对于单台服务器来说server id并不重要，如果是集群主从那么server id必须要不同，那么就需要修改另外几台MySQL的server id了，修改master的server id=1，源码安装默认有，yum需要添加进去

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id=1

上面两部总结就是配置下面两行，在[mysqld]下面

log-bin=jfedu-bin

server-id=1

[root@localhost mysql-5.5.60]# /etc/init.d/mysqld restart --修改完配置文件重启数据库

mysql> show variables like "%log_bin"; --进入mysql服务器，查看二进制功能状态log_bin，可以看到已经开启ON，同时sql_log_bin也必须为on

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

| Variable_name | Value |

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

| log_bin | ON |

| sql_log_bin | ON |

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

（3）给从库授权，创建用于数据同步的账户

mysql> grant replication slave on *.* to "jfedu"@"192.168.179.100" identified by "123456";

Query OK, 0 rows affected (0.00 sec)

*.*所有数据库所有表格

授权jfedu用户以123456密码登入到192.168.179.100从服务器，来复制主服务器数据库所有表（也可以指定复制指定的数据库和表格）

mysql> flush privileges; --刷新权限

Query OK, 0 rows affected (0.00 sec)

（4）查看master状态，记录二进制文件名 jfedu-bin.000001 ，在从库指定从position 339哪个点开始

mysql> show master status;

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

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

| jfedu-bin.000001 | 339 | | |

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

1 row in set (0.00 sec)

[root@localhost ~]# ll /data/mysql/ | grep jfedu* --可以看到主库的数据目录下的这两个文件是用来同步的

-rw-rw---- 1 mysql mysql 339 Mar 20 11:03 jfedu-bin.000001

-rw-rw---- 1 mysql mysql 19 Mar 20 10:47 jfedu-bin.index

[root@localhost ~]# cat /data/mysql/jfedu-bin.index

./jfedu-bin.000001

配置slave

（1）这里不做互为主从就不需要和主库一样开启二进制功能，只需要修改id就行

[root@localhost mysql-5.5.60]# vim /usr/local/mysql55/my.cnf

server-id=2

[root@localhost mysql-5.5.60]# /etc/init.d/mysqld restart --重启服务，指定主库是谁

Shutting down MySQL. SUCCESS!

Starting MySQL.. SUCCESS!

（2）打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)

change master to master_host="192.168.179.99",master_user="jfedu", master_password="123456", master_log_file="jfedubin.000001", master_log_pos=339;

来指定要同步的主库是谁，指定了主库之后，主库肯定会向从库要账号密码的，这个账号和密码就是主库授权的用户和密码。之后告诉从库去主库里面拿哪个log文件。同时告诉他从文件的哪个点开始同步，这个点可以选择。

mysql> change master to master_host="192.168.179.99",master_user="jfedu", master_password="123456", master_log_file="jfedu-bin.000001", master_log_pos=339;

Query OK, 0 rows affected (0.01 sec)

（3）启动slave同步进程，这样从库就会去连接主库

mysql> slave start;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G --标红的两行必须为yes才算配置成功

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.179.99

Master_User: jfedu --主库复制的用户

Master_Port: 3306 --主库 mysqld 端口

Connect_Retry: 60

Master_Log_File: jfedu-bin.000001 --io_thread 读取主库 master_log_file

Read_Master_Log_Pos: 339 -- io_thread 读取主库 master_log_pos

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 253

Relay_Master_Log_File: jfedu-bin.000001 --sql_thread 执行主库的 master_log_file

Slave_IO_Running: Yes --关键， io_thread 是否 running

Slave_SQL_Running: Yes --关键， sql_thread 是否 running

Exec_Master_Log_Pos: 339 --sql_thread 执行主库的 master_log_pos

Seconds_Behind_Master: 0 --从库的延迟

Slave_IO_Running: Connecting 或 NO

IO_thread 异常，状态往往是 Slave_IO_Running: Connecting 或 NO。

IO_thread 是向 Master 发送请求读取 master binlog，如果处于 Connecting 状态，说明无

法正确地与 Master 进行连接，可能的原因有：

（1）网络不通（是否打开防火墙）

（2）复制用户的密码不对

（3）指定的 master_port 端口不对

通过 show slave status\G 可以看到相关错误信息，例如：

show slave status\G

Last_IO_Errno: 2003

Last_IO_Error: error connecting to master 'repl@192.168.179.99:3306' - retry-time: 60

retries: 86400

Slave_IO_Running: Connecting（从库指定的用户名与密码错误（与主库授权的用户名和密码不一致）或者防火墙没有关闭或者主库宕机了）

下面演示密码写错

mysql> slave stop;

mysql> change master to master_host="192.168.179.99",master_user="jfedu", master_password="23456", master_log_file="jfedu-bin.000001", master_log_pos=339;

mysql> slave start;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

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

Slave_IO_State: Connecting to master

Master_Host: 192.168.179.99

Master_User: jfedu

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: jfedu-bin.000001

Read_Master_Log_Pos: 339

Relay_Log_File: localhost-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: jfedu-bin.000001

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Last_IO_Error: error connecting to master 'jfedu@192.168.179.99:3306' - retry-time: 60 ret

如果处于 NO 状态

（1） master 上的 mysql-bin.xxxxxx 被误删

（2）主库磁盘空间满了

通过 show slave status\G 可以看到相关错误信息，例如

140828 15:47:20 [ERROR] Slave I/O: error connecting to master 'repl@192.168.179.99:3306' -

retry-time: 60 retries: 86400, Error_code: 2003

140828 15:47:21 [Note] Event Scheduler: Loaded 0 events

140828 15:47:21 [Note] /home/mysql/mysql/bin/mysqld: ready for connections.

Slave_IO_Running: No（正确登入了之后，会向主库请求jfedu-bin.000001这个文件，但是这个文件在主库里面找不到，主库就返回信息Slave_IO_Running: No）

mysql> slave stop;

Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host="192.168.179.99",master_user="jfedu", master_password="123456", master_log_file="jfedu-bin.000002", master_log_pos=339;

Query OK, 0 rows affected (0.01 sec)

mysql> slave start;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

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

Slave_IO_State:

Master_Host: 192.168.179.99

Master_User: jfedu

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: jfedu-bin.000002

Read_Master_Log_Pos: 339

Relay_Log_File: localhost-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: jfedu-bin.000002

Slave_IO_Running: No

Slave_SQL_Running: Yes

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

Slave_SQL_Running: No

sql_thread 发生异常，状态就会变为 Slave_SQL_Running: NO。

sql_thread 发生异常的情况非常多，发生异常后，需要通过以下方法排查和解决：

（1）对比主库和从库的二进制日志的情况：

主库：

show master status\G

File: mysql-bin.000303

Position: 18711563

从库：

show slave status\G

Master_Log_File: mysql-bin.000303 --IO_thread

Read_Master_Log_Pos: 18711563 --IO_thread

Relay_Master_Log_File: mysql-bin.000303 --sql_thread

Exec_Master_Log_Pos: 18711163 --sql_thread

（2）通过 show slave status\G 查看错误信息：

show slave status\G

Last_SQL_Errno: 1062

Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database:

'test'. Query: 'insert into test values(1,2,3,4,5,6)'

（3）通过错误日志查看错误信息：

140828 16:27:51 [ERROR] Slave SQL: Error 'Duplicate entry '1' for key 'PRIMARY'' on query.

Default database: 'test'. Query: 'insert into test values(1,2,3,4,5,6)',

Error_code: 1062

140828 16:27:51 [Warning] Slave: Duplicate entry '1' for key 'PRIMARY' Error_code: 1062

140828 16:27:51 [ERROR] Error running query, slave SQL thread aborted. Fix the problem,

and restart the slave SQL thread with "SLAVE START". We stopped at log

'mysql-bin.000303' position 18711163

根据这些报错信息，往往就能够定位到发生异常的原因。如果我们了解产生异常的具体

事件，而且能够掌控，可以通过设置 sql_slave_skip_counter 参数来跳过当前错误。

set global sql_slave_skip_counter=1;

或者使用 slave_skip_errors 参数（read only variable），指定跳过某种类型的错误：

参数文件中设置：

slave_skip_errors=1062 --跳过 1062 错误

遇到错误时，不要一通百度后，然后根据看起来很类似的操作直接来进行操作。因为网

上大部分解决 sql_thread 异常的方法是：

（1）直接 set global sql_slave_skip_counter=n; （n 设置很大的值，即：跳过所有错误），

（2）设置 slave_skip_errors=all; 跳过所有类型的错误

（3）直接查看主库的 binlog，然后在从库上直接执行 change master to。

这些方法都会导致主从数据不一致。

如果发现从库与主库差异太大，无法通过手动操作或数据修改重新建立同步。可以参考上述"MySQL 主从复制搭建" 重新搭建从库。

主从复制延迟

主从复制延迟，可能的原因有：

（1）主从同步延迟与系统时间的关系，查看主从两台机器间系统时间差

（2）主从同步延迟与压力、网络、机器性能的关系，查看从库的 io， cpu， mem 及网络压力

（3）主从同步延迟与 lock 锁的关系（myisam 表读时会堵塞写），尽量避免使用 myisam 表。一个实例里面尽量减少数据库的数量。

（4）主从复制发生异常而中断，过很久之后才发现复制异常。可通过查看 master 与 slave 的status 估算相差的日志。如果相差太大，则可以考虑重做从库。

建立主从同步另外一种方法（重建备库也是使用该方法）

建立主从同步可以从主库上导出数据，也可以从已有的从库上导出数据，然后再导入到新的从库中， change master to 建立同步。

导出数据，在主库上导出数据：

mysqldump -u*** -p*** -S /data/mysql6001/mysql.sock --default-character-set=utf8 -q --single-transaction --master-data -A > /tmp/all_database.sql

（或者）在从库上导出数据：

mysqldump -u*** -p*** -S /data/mysql6001/mysql.sock --default-character-set=utf8 -q --single-transaction --dump-slave -A > /tmp/all_database.sql

NOTES：

--master-data 和--dump-slave 导出的备份中，会包含 master_log_file 和 master_log_pos 信息。

从库导入数据

mysql -u*** -p*** --default-character-set=utf8 < all_database.sql

NOTES：此处导入脚本，就已经在从库中执行了以下操作：

change_master_to master_log_file=' mysql-bin.000xxx', master_log_pos=xxxxxx;

富士康质检员张全蛋

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录