一、MySQL-Replication(主从复制)
1.1、MySQL Replication
主从复制(也称 AB 复制)允许将来自一个MySQL数据库服务器(主服务器)的数据复制到一个或多个MySQL数据库服务器(从服务器)。
根据配置,您可以复制数据库中的所有数据库,所选数据库甚至选定的表。
MySQL主从复制的优点包括:
-
横向扩展解决方案 - 在多个从站之间分配负载以提高性能。在此环境中,所有写入和更新都必须在主服务器上进行。但是,读取可以在一个或多个从设备上进行。该模型可以提高写入性能(因为主设备专用于更新),同时显着提高了越来越多的从设备的读取速度。
-
数据安全性 - 因为数据被复制到从站,并且从站可以暂停复制过程,所以可以在从站上运行备份服务而不会破坏相应的主数据。
-
分析 - 可以在主服务器上创建实时数据,而信息分析可以在从服务器上进行,而不会影响主服务器的性能。
-
备份 - 可以使用从服务器数据进行备份,减轻主服务器的压力。
1.2、Replication的原理
前提是作为主服务器角色的数据库服务器 必须开启二进制(binlog)日志
原理 主服务器上面的任何修改都会保存在二进制日志( Bin-log日志) 里面。 从服务器上面启动一个I/O线程, 连接到主服务器上面请求读取二进制(Bin-log)日志,然后把读取到的二进制日志写到本地的Realy-log(中继日志)里面。 从服务器上面同时开启一个SQL线程,读取Realy-log(中继日志),如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。
1.3、配置Replication
配置步骤:
实验: 两台虚拟机(一主一从) 两台都配置hosts解析 # cat /etc/hosts 192.168.62.152 mysql-master 192.168.62.151 mysql-slave1 两端关闭防火墙,selinux
主服务器
-
在主服务器上,您必须启用二进制日志记录并配置唯一的服务器ID。需要重启服务器。
编辑主服务器的配置文件 my.cnf
,添加如下内容
添加配置
[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1
创建日志目录并赋予权限
[root@mysql-1 ~]# mkdir /var/log/mysql [root@mysql-1 ~]# chown mysql.mysql /var/log/mysql
重启服务
[root@mysql-1 ~]# systemctl restart mysqld
2.应该创建一个专门用于复制数据的用户
每个从服务器需要使用MySQL 主服务器上的用户名和密码连接到主服务器。
例如,计划使用用户 repl
可以从任何主机上连接到 master
上进行复制操作, 并且用户 repl
仅可以使用复制的权限。
在 主服务器
上执行如下操作
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by '123'; 将复制的权限给与此用户
3.在从服务器
上使用刚才创建的用户进行测试连接
[root@mysql-slalve ~]# mysql -urepl -p'123' -hmysql-master1 测试是否连通,连通退出即可
下面的操作根据如下情况继续
主服务器中有数据
-
在主服务器中导出先有的数据,将此数据复制到每个从站。
-
使用mysqldump工具创建要复制的所有数据库的转储。这是推荐的方法,尤其是在使用时
InnoDB
。
[root@mysql ~]# mysqldump -u用户名 -p密码 --all-databases > dbdump.db 这里的用户是主服务器的用户
-
从主服务器中使用
scp
工具,把备份出来的数据传输到从服务器中。
在主服务中执行如下命令
[root@mysql ~]# scp dbdump.db root@mysql-slave1:/root/ 这里的 mysql-slave1 需要能被主服务器解析出 IP 地址,或者说可以在主服务器中 ping 通。
-
配置从服务器,并重启 在
从服务器
上编辑其配置文件my.cnf
并添加如下内容
// my.cnf 文件 [mysqld] server-id=2 配置之后,重启生效
-
导入数据到从服务器,并配置连接到主服务器的相关信息
登录到从服务器上,执行如下操作
/*导入数据*/ [root@mysql-slave1 ~] mysql -uroot -p'123' < alldb.sql
5.查看主服务器的binlog日志的名称和位置
6.在从服务器配置连接到主服务器的相关信息
mysql> CHANGE MASTER TO MASTER_HOST='mysql-master', -- 主服务器的主机名(也可以是 IP) MASTER_USER='repl', -- 连接到主服务器的用户 MASTER_PASSWORD='Qf_123456'; -- 到主服务器的密码 MASTER_LOG_FILE='mysql-bin.000001', -- 从主服务器的哪个binlog日志读取 MASTER_LOG_POS=589; -- 从主服务器的binlog日志的哪个位置开始同步
7.启动从服务器的复制线程
mysql> start slave; Query OK, 0 rows affected (0.09 sec)
8.检查是否成功
在从服务上执行如下操作,加长从服务器端 IO线程和 SQL 线程是否是 OK
mysql> show slave status\G
输出结果中应该看到 I/O 线程和 SQL 线程都是 YES
, 就表示成功。
执行此过程后,在主服务上操作的修改数据的操作都会在从服务器中执行一遍,这样就保证了数据的一致性。
主服务器中无数据
主服务器中设置
my.cnf
配置文件
[mysqld] log-bin=/var/log/mysql/mysql-bin server-id=1
创建日志目录并赋予权限
[root@mysql ~]# mkdir /var/log/mysql [root@mysql ~]# chown mysql.mysql /var/log/mysql 重启服务
找出root的初始密码
[root@mysql-master ~]# grep password /var/log/mysqld.log
设置root的密码 mysql> alter user root@localhost identified by 'Syf_123456'; 创建主从同步的用户 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by 'Syf_123456';
从服务器设置
my.cnf
配置文件
[mysqld] server-id=3 重启服务
在主服务器上操作
查看主服务器的binlog日志的名称和位置
mysql> show master status \G
-
在从服务器的 mysql 中执行如下语句
找出root的密码
[root@mysql-slave1 ~]# grep password /var/log/mysqld.log
进入从数据库
[root@mysql-slave1 ~]# mysql -uroot -p'dY3K2((y/hhk' mysql> CHANGE MASTER TO MASTER_HOST='mysql-master', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=681; mysql> start slave;
在从站上暂停复制
您可以使用和stop slave和start slave语句停止并启动从站上的复制 。
要停止从主服务器处理二进制日志,请使用:
mysql> stop slave; //暂停 mysql> start slave; //启动
1.4、配置Replication(gtid方式)
基于事务的Replication,就是利用GTID来实现的复制
GTID(全局事务标示符)最初由google实现,在MySQL 5.6中引入.GTID在事务提交时生成,由UUID和事务ID组成.uuid会在第一次启动MySQL时生成,保存在数据目录下的auto .cnf文件里,事务ID则从1开始自增使用GTID的好处主要有两点:
-
不再需要指定传统复制中的master_log_files和master_log_pos,使主从复制更简单可靠
-
可以实现基于库的多线程复制,减小主从复制的延迟
实验环境要求: 5.7.6 以上版本
主从均清除刚才实验的环境 [root@mysql-master ~]# systemctl stop mysqld [root@mysql-slave1 ~]# systemctl stop mysqld 注意:以下两步均危险操作,在以后工作环境中,绝对不能删除数据库。 可以先mysqldump导出一份备份文件,在执行此操作 [root@mysql-master ~]# rm -rf /var/lib/mysql/* [root@mysql-master ~]# rm -rf /var/log/mysql/* [root@mysql-slave1 ~]# rm -rf /var/lib/mysql/*
主库配置 vim /etc/my.cnf
[mysqld] log-bin=/var/log/mysql/mysql-bin server-id=1 #打开gtid模式 gtid_mode=ON enforce_gtid_consistency=1 重启服务 systemctl start mysqld
分别找出主从服务器root用户的初始密码 [root@mysql-master ~]# grep password /var/log/mysqld.log
主服务器修改数据库root用户密码(可选操作) [root@mysql-master ~]# mysqladmin -uroot -p'QsgW(=D#F9&i' password 'Syf_123456' 从服务器修改数据库root用户密码(可选操作) [root@mysql-slave1 ~]# mysqladmin -uroot -p'tUthsaZ0sh(?' password 'Syf_123456'
其他和之前的一样
-
创建专属用户并授权
-
假如有数据导出数据
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by 'Qf_123456'; mysql> flush privileges;
从库配置
测试用户有效性
mysql -urepl -p'Qf_123456' -hmysql-master 连接成功,退出
# vim /etc/my.cnf #在配置文件中添加配置 [mysqld] server-id=2 #打开gtid模式 gtid_mode=ON enforce_gtid_consistency=1 重启服务 systemctl start mysqld
Mysql 从服务器终端执行连接信息
mysql> CHANGE MASTER TO MASTER_HOST='mysql-master', MASTER_USER='repl', MASTER_PASSWORD='Qf_123456', MASTER_AUTO_POSITION=1; > start slave;
查看是否同步成功
mysql> show slave status\G
二、Replication故障排除
2.1、开启 GTID 后的导出导入数据的注意点
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events
意思是: 当前数据库实例中开启了 GTID 功能, 在开启有 GTID 功能的数据库实例中, 导出其中任何一个库, 如果没有显示地指定--set-gtid-purged参数, 都会提示这一行信息. 意思是默认情况下, 导出的库中含有 GTID 信息, 如果不想导出包含有 GTID 信息的数据库, 需要显示地添加--set-gtid-purged=OFF参数.
mysqldump -uroot -p --set-gtid-purged=OFF --all-databases > alldb.db
导入数据是就可以相往常一样导入了。
2.2、UUID一致,导致主从复制I/O线程不是yes
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work
致命错误:由于master和slave具有相同的mysql服务器uuid,导致I/O线程不进行;这些uuid必须不同才能使复制工作。
问题提示主从使用了相同的server UUID,一个个的检查:
检查主从server_id
主库:
mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | +---------------+-------+
从库:
mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 2 | +---------------+-------+ 1 row in set (0.00 sec)
server_id不一样,排除。
检查主从状态:
主库:
mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000002 Position: 849 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 21c27a81-633b-11ea-8d7d-00163e064efa:1-3 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: 172.31.47.161 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 849
File一样,排除。
最后检查发现他们的auto.cnf中的server-uuid是一样的。
[root@localhost ~]# vim /var/lib/mysql/auto.cnf [auto] server-uuid=4f37a731-9b79-11e8-8013-000c29f0700f
修改uuid并重启服务
关于mysql主从同步问题解决_a partial dump from a server-CSDN博客
如果上面的io错误:
可能是主库中没有授权
补充:
在数据库中:(帮助文档)
mysql> ? create
错误在这:(从库)
如何解决主从延迟:(不能完全解决)
dump?这个就是将自己数据库备份到一个文件里面
mysqldump -p -u -h -A > file.sql
===========================================================================
可以做解析更快
vim /etc/resolv.conf
将自己的ip和主机名解析(速度更快)
=========================================================================
通俗易懂
课堂笔记:
无数据情况下做主从复制
主库操作
1、 准备两台数据库,并关闭防火墙
2、 主库开启binlog日志
3、 主库创建一个远程登录账号
grant replication slave on *.* to 'slave'@'%' identified by 'QianFeng@123456';
flush privileges;
4、 记录binlog日志的位置然后重启数据库,使它们生效(有数据和没有数据都一样的)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 590 | | | |
+------------------+----------+--------------+------------------+-------------------+从库操作
5、 配置server-id
6、 配置主从同步
CHANGE MASTER TO
MASTER_HOST='192.168.91.129',
MASTER_USER='slave',
MASTER_PASSWORD='QianFeng@123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
7、启动slave
start slave;
8、检查主从复制状态
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
9、测试主从复制
写入数据,观察同步情况
10、如何重做主从
先停止slave stop slave;
清除slave配置 reset slave;
重新进行change master to 配置
start slave;
=========================================================================
有数据情况下做主从复制
1、清理上一个实验的数据过程
systemctl stop mysqld && rm -rf /var/lib/mysql/* /var/log/mysql/mysql-bin* && systemctl start mysqld
2、当前环境为,一台主库,没有开启binlog日志,并且在数据库中存在数据,一台从库,新数据库
3、开始进行主从配置
主库操作
1、导出数据2、开启binlog日志和server-id
3、主库创建一个远程登录账号
grant replication slave on *.* to 'slave'@'%' identified by '密码';
flush privileges;4、记录binlog日志的位置
mysql> reset master;
初始化·bin-log日志
Query OK, 0 rows affected (0.00 sec)mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+从库操作
5、 导入数据并配置server-id
6、 配置主从同步
CHANGE MASTER TO
MASTER_HOST='192.168.91.129',
MASTER_USER='slave',
MASTER_PASSWORD='QianFeng@123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=590,
MASTER_CONNECT_RETRY=10;
7、启动slave
start slave;
8、检查主从复制状态
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
9、测试主从复制
写入数据,观察同步情况
10、如何重做主从
先停止slave stop slave;
清除slave配置 reset slave;
重新进行change master to 配置
start slave;
=========================================================================
大数据情况下做主从复制
假如主库有数据,从库数据没了,
首先主库和从库都进入/var/lib/mysql这个目录下,停止mysql,冷传输,
有a 和 b (从库)
关闭b的数据库(冷传输)
将a的mysql下所有目录传过去
将a的mysql下的 ib* 发过去scp -r ( ) b:/var/lib/mysql/
然后在b中的mysql目录下赋权
chown -R mysql.mysql .
启动mysql
systemctl start mysqld然后再开始进行后续的bin-log的操作(主从配置)
和导出导入的区别:
快