mysql数据库主从复制

MySQL复制概述

 

1>.MySQL复制介绍

　　（1）MySQL复制允许将主实例（master）上的数据同步到一个或多个从实例（slave）上，默认情况下复制是异步进行的，从库也不需要一直连接主库来同步数据。

　　（2）MySQL复制的数据粒度可以是主实例上所有的数据库，也可以是指定的一个或多个数据库，也可以是一个数据库里的指定的表。

　　（3）MySQL复制带来的优势在于：
　　　　　　扩展能力：
    　　　　　　通过复制可以将MySQL的性分到一个或多个slave上。这要求所有的写操作和修改操作都必须在Master上完成，而读操作可以被分配到一个或多个salve上。将读写分离到不同服务执行之后，MySQL的读写性能得到提升。
　　　　　　数据库备份：
　　　　　　　　由于从实例时同步主实例的数据，所以可以将备份作业部署到从库。
　　　　　　数据分析和报表：
　　　　　　　　同样，一些数据分析和报表的实现可以在从实例执行，以减少对主库的性能影响。
　　　　　　容灾能力：
　　　　　　　　可以在物理距离较远的另一个数据建立slave,保证在主实例所在地区遭遇灾难时，在另一个数据中心能快速恢复。

2>.MySQL复制有两种方法

　　（1）传统方式
　　　　　　基于主库的bin-log将日志事件和事件位置复制到从库，从库再加以应用来达到主从同步的目的。

　　（2）Gtid方式
　　　　　　global transaction identitifiers 是基于事物来复制数据，因此也就不依赖日志文件，同时又能更好的保证主从库数据一致性。

3>.MySQL复制有多种类型

（1）异步复制
　　　　一个主库，一个或多个从库，数据异步同步到从库。

（2）同步复制
　　　　在MySQL cluster中特有的复制方式。

（3）半同步复制
　　　　在异步复制的基础上，确保任何一个主库上的事物在提交之前至少有一个从库已经收到该事物并日志记录下来。

（4）延迟复制
　　　　在异步复制的基础上，人为设定主库和从库的数据同步延迟时间，即保证数据延迟至少是这个参数。

4>.MySQL的复制原理

　如上图所示，MySQL复制的原理大致总结如下：
　　　　（1）在Slave 服务器上执行sart slave命令开启主从复制开关，开始进行主从复制。
　　　　（2）此时，Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接master服务器，并请求从执行binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行change master命令指定的）之后开始发送binlog日志内容
　　　　（3）Master服务器接收到来自Slave服务器的IO线程的请求后，二进制转储IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外，还有在master服务器端记录的新的binlog文件名称，以及在新的binlog中的下一个指定更新位置。
　　　　（4）当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件（MySQL-relay-bin.xxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容
　　　　（5）Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容，然后及时把Relay LOG 文件中的内容解析成sql语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句，并在relay-log.info中记录当前应用中继日志的文件名和位置点

5>.MySQL复制有三种核心格式

复制的工作原理是数据库修改记录到bin log日志并传递到slave，然后slave在本地还原的过程。而时间记录到bin log的格式会有所不同。

基于语句的复制（statement based replication）：
　　基于主库将SQL语句写入到bin log中完成复制。

基于行数据的复制（row based replication）：
　　基于主库将每一行数据变化的信息作为时间写入到bin log中完成日志。默认就是基于行级别的复制，因为它相对语句复制逻辑更为严谨。

混合复制（mixed based replication）：
　　上述两者的结合。默认情况下优先使用基于语句的复制，只有当部分语句如果基于语句复制不完全的情况下才会自动切换为基于行数据的复制。

 修改默认的复制模式格式案例（推荐大家使用默认的基于ROW的复制方式）

 

三.配置MySQL基于bin-log主从同步

1>.编辑my.cnf配置文件

[root@localhost ~]# cat /etc/my.cnf
[client]
port = 3306
socket = /application/mysql-5.6.34/tmp/mysql.sock
[mysqld]
port = 3306
socket = /application/mysql-5.6.34/tmp/mysql.sock
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
datadir=/application/mysql-5.6.34/data
server-id = 1
log-bin=mysql-bin
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
[mysqlhotcopy]
interactive-timeout

[root@node106.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
basedir=/yinzhengjie/softwares/mysql/
datadir=/yinzhengjie/softwares/mysql/data/
log-bin=yinzhengjie-mysql-bin
server-id=106
[root@node106.yinzhengjie.org.cn ~]# 

# egrep -Ev ‘^$|^#’ /etc/mysql/my.cnf
[mysqld]
server-id=2
skip-host-cache
skip-name-resolve
datadir = /var/lib/mysql
!includedir /etc/mysql/conf.d/

[root@node107.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
basedir=/yinzhengjie/softwares/mysql/
datadir=/yinzhengjie/softwares/mysql/data/
log-bin=yinzhengjie-mysql-bin
server-id=107
[root@node107.yinzhengjie.org.cn ~]# 

2>.在主库创建一个专门用来复制的数据库用户，这样所有从库都可以用这个用户来连接主库，也可以确保这个用户只有复制的权限 

mysql> CREATE USER 'copy'@'%' IDENTIFIED BY 'passwd';
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'copy'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> quit
Bye

[root@node107.yinzhengjie.org.cn ~]# mysql -u copy -pyinzhengjie -P 3306 -h node106.yinzhengjie.org.cn
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 20
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> 
mysql> QUIT
Bye
[root@node107.yinzhengjie.org.cn ~]# 
[root@node107.yinzhengjie.org.cn ~]# 

3>.获取主库的日志信息并生成主库数据镜像

mysql> FLUSH TABLES WITH READ LOCK;　　　　　　　　　　　　　　　　　　#对主库上所有表加锁，停止修改，即在从库复制的过程中主库不能执行UPDATA，DELETE，INSERT语句！
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> SHOW MASTER STATUS;        　　　　　　　　　　　　　　　　　　 #获取主库的日志信息，file表示当前日志文件名称，position表示当前日志的位置
+------------------------------+----------+--------------+------------------+-------------------+
| File                         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     4095 |              |                  |                   |
+------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

[root@node106.yinzhengjie.org.cn ~]#  mysqldump --all-databases --master-data -u root -pyinzhengjie -P 3306 > yinzhengjie-master.db 　　　　　　#另开一个终端生成镜像，在生成完成之前不要释放锁。
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@node106.yinzhengjie.org.cn ~]# 
[root@node106.yinzhengjie.org.cn ~]# ll
total 776
-rw-r--r-- 1 root root 791962 Jun 10 19:24 yinzhengjie-master.db
[root@node106.yinzhengjie.org.cn ~]# 

mysql>
mysql> UNLOCK TABLES;　　　　　　　　　　　　　　　　#主库数据生成镜像完毕后，我们需要把主库的锁释放掉，需要注意的是，在上锁这一段期间，我们无法对数据库进行写操作，比如UPDATA，DELETE，INSERT。
Query OK, 0 rows affected (0.00 sec)

mysql> 

4>.将主库的镜像拷贝当从库中，让从库应用主库镜像

mysqldump -A –events -uroot -ppasswd –master-data=2> /sqlbak/mscopy.db

[root@node106.yinzhengjie.org.cn ~]# ll
total 776
-rw-r--r-- 1 root root 791962 Jun 10 19:24 yinzhengjie-master.db
[root@node106.yinzhengjie.org.cn ~]# 
[root@node106.yinzhengjie.org.cn ~]# scp yinzhengjie-master.db node107.yinzhengjie.org.cn:~
The authenticity of host 'node107.yinzhengjie.org.cn (172.30.1.107)' can't be established.
ECDSA key fingerprint is SHA256:BkN6bKO2q5zMgvremE/3rOIsCaq9eTPudgfU0lhbqGo.
ECDSA key fingerprint is MD5:75:bd:cb:be:35:f8:45:a2:ea:74:bc:aa:29:74:4d:0d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'node107.yinzhengjie.org.cn,172.30.1.107' (ECDSA) to the list of known hosts.
root@node107.yinzhengjie.org.cn's password: 
yinzhengjie-master.db                                                                                                                                                                                  100%  773KB  60.7MB/s   00:00    
[root@node106.yinzhengjie.org.cn ~]# 

scp /sqlbak/mscopy.db 192.168.43.13:/var/lib/docker/volumes/c259d83b10420c15a8a6f890540ef6b6ea442e951bfd0b702213b75fa6abbb8a/_data

[root@node107.yinzhengjie.org.cn ~]# pwd
/root
[root@node107.yinzhengjie.org.cn ~]# 
[root@node107.yinzhengjie.org.cn ~]# ll
total 776
-rw-r--r-- 1 root root 791962 Jun 10 19:37 yinzhengjie-master.db
[root@node107.yinzhengjie.org.cn ~]# 
[root@node107.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 5
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.
mysql> source yinzhengjie-master.db;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 41 rows affected (0.00 sec)
Records: 41  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 699 rows affected (0.01 sec)
Records: 699  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1413 rows affected (0.01 sec)
Records: 1413  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 643 rows affected (0.08 sec)
Records: 643  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> 

5>.在从库上建立复制关系，即从库指定主库的日志信息和链接信息

mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.43..',
-> MASTER_PORT=3306,
-> MASTER_USER='copy',
-> MASTER_PASSWORD='passwd',
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=4095;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql>

6>.从库启动复制进程

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> 
mysql>

mysql> show slave status/G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.43.14
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 107
Relay_Log_File: 44da1998c108-relay-bin.000016
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1396
Last_Error: Error ‘Operation CREATE USER failed for ‘copy’@’%” on query. Default database: ”. Query: ‘CREATE USER ‘copy’@’%’IDENTIFIED BY ‘543546”
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 546912
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1396
Last_SQL_Error: Error ‘Operation CREATE USER failed for ‘copy’@’%” on query. Default database: ”. Query: ‘CREATE USER ‘copy’@’%’IDENTIFIED BY ‘543546”
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR:
No query specified

 

当slave-SQL-running：NO时
一般是事务回滚造成的：
解决办法：
mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;

 

　　

四.创建主库插入数据验证从库是否有数据

1>.在主库中创建cdh数据库

show databases;
insert into student values(7,'李青1',18,100);

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+—-+———+——+——-+
| id | name | age | grade |
+—-+———+——+——-+
| 1 | zha | 22 | 90 |
| 2 | ?? | 20 | 80 |
| 3 | ? | 20 | 80 |
| 4 | 青2 | 18 | 100 |
| 5 | 青3 | 18 | 100 |
| 6 | ??? | 18 | 100 |
| 7 | 李青1 | 18 | 100 |
+—-+———+——+——-+
7 rows in set (0.00 sec)

2>.在从库的服务器观察数据是否同步

[root@node107.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 12
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.

mysql> select * from student;
+—-+———+——+——-+
| id | name | age | grade |
+—-+———+——+——-+
| 1 | zha | 22 | 90 |
| 2 | ?? | 20 | 80 |
| 3 | ? | 20 | 80 |
| 4 | 青2 | 18 | 100 |
| 5 | 青3 | 18 | 100 |
| 6 | ??? | 18 | 100 |
| 7 | 李青1 | 18 | 100 |
+—-+———+——+——-+
7 rows in set (0.00 sec)