大数据集群之MySQL5.7主从复制

　　　　　　　　　　　　　　　　　　　　　　　大数据集群之MySQL5.7主从复制

一、安装MySQL数据库并启动

1、在MySQL官网下载相应的安装包（https://dev.mysql.com/downloads/mysql/)

2、解压MySQL压缩文件至安装目录

[root@Hexindai-C12-120 software]# ll /export/software/
total 820192
-rw-r--r--. 1 root root      8409 Jul  1 14:27 jce_policy-8.zip
-rw-r--r--. 1 root root 194990602 Jul  1 14:55 jdk-8u211-linux-x64.tar.gz
-rw-r--r--. 1 root root 644869837 Jul  1 17:56 mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@Hexindai-C12-120 software]# 
[root@Hexindai-C12-120 software]# tar zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /export/servers/
[root@Hexindai-C12-120 servers]# mv mysql-5.7.26-linux-glibc2.12-x86_64/ mysql
[root@Hexindai-C12-120 servers]# mkdir /export/data/mysql -p
[root@Hexindai-C12-120 servers]# ll /export/servers/mysql/
total 36
drwxr-xr-x.  2 root root   4096 Jul  1 18:03 bin
-rw-r--r--.  1 7161 31415 17987 Apr 13 21:32 COPYING
drwxr-xr-x.  2 root root     55 Jul  1 18:03 docs
drwxr-xr-x.  3 root root   4096 Jul  1 18:03 include
drwxr-xr-x.  5 root root    230 Jul  1 18:03 lib
drwxr-xr-x.  4 root root     30 Jul  1 18:03 man
-rw-r--r--.  1 7161 31415  2478 Apr 13 21:32 README
drwxr-xr-x. 28 root root   4096 Jul  1 18:03 share
drwxr-xr-x.  2 root root     90 Jul  1 18:03 support-files
[root@Hexindai-C12-120 servers]# 
[root@Hexindai-C12-120 servers]# vim /etc/profile  #添加mysql环境变量
[root@Hexindai-C12-120 servers]# tail -2 /etc/profile
PATH=$PATH:$HOME/bin:/export/servers/mysql/bin/
export PATH
[root@Hexindai-C12-120 servers]# 
[root@Hexindai-C12-120 servers]# source /etc/profile
[root@Hexindai-C12-120 servers]#

3、创建MySQL用户，并将解压的MySQL程序文件授权给mysql用户。

root@hexindai-c12-121 ~]# useradd -s /sbin/nologin mysql
[root@hexindai-c12-121 ~]# id mysql
uid=1000(mysql) gid=1000(mysql) groups=1000(mysql)
[root@hexindai-c12-121 ~]# chown -R mysql.mysql /export/servers/mysql/
[root@hexindai-c12-121 ~]# chown -R mysql.mysql /export/data/mysql/
[root@hexindai-c12-121 ~]# 

4、初始化MySQL数据库

　　在安装前我们先安装一下mysql的依赖库：libaio.so.1,若不安装，执行mysql命令时会提示错误如下：

[root@hexindai-c12-120 ~]# mysqld
mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

　　安装依赖库libaio:

[root@hexindai-c12-120 ~]# yum -y install libaio*
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
epel/x86_64/metalink                                                                                                                                                          | 7.5 kB  00:00:00     
 * base: mirrors.aliyun.com
 * epel: mirrors.aliyun.com
 * extras: mirrors.aliyun.com
 * updates: mirrors.aliyun.com
base                                                                                                                                                                          | 3.6 kB  00:00:00     
epel                                                                                                                                                                          | 5.3 kB  00:00:00     
extras                                                                                                                                                                        | 3.4 kB  00:00:00     
updates                                                                                                                                                                       | 3.4 kB  00:00:00     
(1/3): epel/x86_64/updateinfo                                                                                                                                                 | 976 kB  00:00:00     
(2/3): updates/7/x86_64/primary_db                                                                                                                                            | 6.5 MB  00:00:00     
(3/3): epel/x86_64/primary_db                                                                                                                                                 | 6.7 MB  00:00:01     
Resolving Dependencies
--> Running transaction check
---> Package libaio.x86_64 0:0.3.109-13.el7 will be installed
---> Package libaio-devel.x86_64 0:0.3.109-13.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=====================================================================================================================================================================================================
 Package                                           Arch                                        Version                                               Repository                                 Size
=====================================================================================================================================================================================================
Installing:
 libaio                                            x86_64                                      0.3.109-13.el7                                        base                                       24 k
 libaio-devel                                      x86_64                                      0.3.109-13.el7                                        base                                       13 k

Transaction Summary
=====================================================================================================================================================================================================
Install  2 Packages

Total download size: 37 k
Installed size: 46 k
Downloading packages:
(1/2): libaio-0.3.109-13.el7.x86_64.rpm                                                                                                                                       |  24 kB  00:00:00     
(2/2): libaio-devel-0.3.109-13.el7.x86_64.rpm                                                                                                                                 |  13 kB  00:00:00     
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                                395 kB/s |  37 kB  00:00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : libaio-0.3.109-13.el7.x86_64                                                                                                                                                      1/2 
  Installing : libaio-devel-0.3.109-13.el7.x86_64                                                                                                                                                2/2 
  Verifying  : libaio-0.3.109-13.el7.x86_64                                                                                                                                                      1/2 
  Verifying  : libaio-devel-0.3.109-13.el7.x86_64                                                                                                                                                2/2 

Installed:
  libaio.x86_64 0:0.3.109-13.el7                                                                 libaio-devel.x86_64 0:0.3.109-13.el7                                                                

Complete!
[root@hexindai-c12-120 ~]#

　　初始化MySQL数据库

[root@hexindai-c12-120 ~]# mysqld --initialize  --user=mysql --basedir=/export/servers/mysql/ --datadir=/export/data/mysql/ &
[1] 16118
[root@hexindai-c12-120 ~]# 2019-07-02T01:41:17.524750Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-02T01:41:17.636642Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-02T01:41:17.672810Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-02T01:41:17.725802Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 7c4adbc4-9c6a-11e9-b107-000c29b54df2.
2019-07-02T01:41:17.726171Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-02T01:41:17.726856Z 1 [Note] A temporary password is generated for root@localhost: oenu_Tid4+xz

[1]+  Done                    mysqld --initialize --user=mysql --basedir=/export/servers/mysql/ --datadir=/export/data/mysql/
[root@hexindai-c12-120 ~]# 
[root@hexindai-c12-120 ~]# ll /export/data/mysql/
total 110628
-rw-r-----. 1 mysql mysql       56 Jul  2 09:41 auto.cnf
-rw-r-----. 1 mysql mysql      419 Jul  2 09:41 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul  2 09:41 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul  2 09:41 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul  2 09:41 ib_logfile1
drwxr-x---. 2 mysql mysql     4096 Jul  2 09:41 mysql
drwxr-x---. 2 mysql mysql     8192 Jul  2 09:41 performance_schema
drwxr-x---. 2 mysql mysql     8192 Jul  2 09:41 sys
[root@hexindai-c12-120 ~]# 

5、修改MySQL的配置文件

[root@hexindai-c12-120 ~]# grep -v '^$' /etc/my.cnf                                        
[mysqld]
basedir=/export/servers/mysql
datadir=/export/data/mysql
#socket=/var/lib/mysql/mysql.sock
socket=/export/data/mysql/mysql.sock
log-bin=cdh-mysql-bin
server-id=120
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.log
#pid-file=/var/run/mariadb/mariadb.pid
log-error=/export/data/mysql/mysql.log
pid-file=/export/data/mysql/mysql.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[root@hexindai-c12-120 ~]# 

6、启动MySQL服务

[root@hexindai-c12-120 ~]# cp /export/servers/mysql/support-files/mysql.server /etc/init.d/
[root@hexindai-c12-120 ~]# /etc/init.d/mysql.server start
Starting MySQL.Logging to '/export/data/mysql/mysql.log'.
 SUCCESS! 
[root@hexindai-c12-120 ~]#
[root@hexindai-c12-120 ~]# /etc/init.d/mysql.server status
 SUCCESS! MySQL running (17544)
[root@hexindai-c12-120 ~]# 

7、登录MySQL数据库并初始化管理员密码

[root@hexindai-c12-120 ~]# mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[root@hexindai-c12-120 ~]# ls /export/data/mysql/
auto.cnf              cdh-mysql-bin.index   ib_buffer_pool        ib_logfile0           ibtmp1                mysql.log             mysql.sock.lock       sys/
cdh-mysql-bin.000001  hexindai-c12-120.pid  ibdata1               ib_logfile1           mysql/                mysql.sock            performance_schema/   
[root@hexindai-c12-120 ~]# ln -s  /export/data/mysql/mysql.sock /tmp/   #上面的登录提示指在/tmp目录中没有mysql.sock文件，做软链接即可
[root@hexindai-c12-120 ~]# mysql -uroot -p                           
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log

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> alter user user() identified by 'wtnyihg';　　　　#修改管理员密码;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@hexindai-c12-120 ~]# mysql -uroot -p　　#退出重新验证修改的密码是否生效
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26-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 variables like '%server_id%';   #查看当前服务器的servier_id
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 120   |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.00 sec)

mysql> ^DBye
[root@hexindai-c12-120 ~]# 

8、在另一台服务器上做同样的操作搭建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）：
　　上述两者的结合。默认情况下优先使用基于语句的复制，只有当部分语句如果基于语句复制不完全的情况下才会自动切换为基于行数据的复制。

[root@hexindai-c12-120 ~]#mysql -uroot -p
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 6
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> 
mysql> SHOW VARIABLES LIKE '%BINLOG_FORMAT%';　　　　　　　　#很显然，默认就是基于行复制的
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
row in set (0.00 sec)

mysql> 
mysql> SET binlog_format='STATEMENT';　　　　　　　　　　　　#我们可以修改基于语句复制
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> SHOW VARIABLES LIKE '%BINLOG_FORMAT%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
row in set (0.00 sec)

mysql> 
mysql> quit
Bye
[root@hexindai-c12-120 ~]#

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

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

1、编辑my.cnf配置文件

[root@hexindai-c12-120 ~]# cat /etc/my.cnf
[mysqld]
basedir=/export/servers/mysql
datadir=/export/data/mysql
#socket=/var/lib/mysql/mysql.sock
socket=/export/data/mysql/mysql.sock
log-bin=cdh-mysql-bin
server-id=120
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.log
#pid-file=/var/run/mariadb/mariadb.pid
log-error=/export/data/mysql/mysql.log
pid-file=/export/data/mysql/mysql.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[root@hexindai-c12-120 ~]# 

[root@hexindai-c12-121 ~]# cat /etc/my.cnf
[mysqld]
basedir=/export/servers/mysql
datadir=/export/data/mysql
#socket=/var/lib/mysql/mysql.sock
socket=/export/data/mysql/mysql.sock
log-bin=cdh-mysql-bin
server-id=121
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.log
#pid-file=/var/run/mariadb/mariadb.pid
log-error=/export/data/mysql/mysql.log
pid-file=/export/data/mysql/mysql.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

You have new mail in /var/spool/mail/root
[root@hexindai-c12-121 ~]# 

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

[root@hexindai-c12-120 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26-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> create user 'copy'@'172.20.102.12%' identified by 'wtnyihg';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'copy'@'172.20.102.12%';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@hexindai-c12-120 ~]# 

[root@hexindai-c12-121 ~]# mysql -ucopy -p -h 172.20.102.120 -P 3306                
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.26-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> quit
Bye
[root@hexindai-c12-121 ~]# 

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

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

mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| cdh-mysql-bin.000001 |      881 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 
mysql> 

[root@hexindai-c12-120 ~]# mysqldump --all-databases --master-data -u root -p -P 3306 > cdh-master.db                   
Enter password: 
[root@hexindai-c12-120 ~]# ls
anaconda-ks.cfg  cdh-master.db  ens192_eht0.sh  ip7.sh  UnlimitedJCEPolicyJDK8
[root@hexindai-c12-120 ~]# 

mysql> 
mysql> unlock tables;  #释放锁
Query OK, 0 rows affected (0.00 sec)

mysql> 

4、将主库的镜像拷贝到从库中，让从库应用主库镜像

[root@hexindai-c12-120 ~]# ls
anaconda-ks.cfg  cdh-master.db  ens192_eht0.sh  ip7.sh  UnlimitedJCEPolicyJDK8
[root@hexindai-c12-120 ~]# scp cdh-master.db hexindai-c12-121:/root
The authenticity of host 'hexindai-c12-121 (172.20.102.121)' can't be established.
ECDSA key fingerprint is SHA256:K7sV/XQWDeUHJWc51L46Z7aj96AytYSy8ZgpaxzVgNA.
ECDSA key fingerprint is MD5:9e:0f:52:a2:09:2c:c7:58:9d:39:96:9c:c4:c4:cb:a8.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'hexindai-c12-121' (ECDSA) to the list of known hosts.
cdh-master.db                                                                                                                                                      100%  774KB 104.9MB/s   00:00    
[root@hexindai-c12-120 ~]# 

[root@hexindai-c12-121 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.26-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> 
mysql> source cdh-master.db;
mysql> 

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

[root@hexindai-c12-121 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.26-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> change master to
    -> master_host='hexindai-c12-120',
    -> master_port=3306,
    -> master_user='copy',
    -> master_password='wtnyihg',
    -> master_log_file='cdh-mysql-bin.000001',
    -> master_log_pos=4095;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> 

6、从库启动复制进程

[root@hexindai-c12-121 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.26-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> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: hexindai-c12-120
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: cdh-mysql-bin.000001
          Read_Master_Log_Pos: 4095
               Relay_Log_File: hexindai-c12-121-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: cdh-mysql-bin.000001
             Slave_IO_Running: No
            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: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4095
              Relay_Log_Space: 154
              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: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 120
                  Master_UUID: 7c4adbc4-9c6a-11e9-b107-000c29b54df2
             Master_Info_File: /export/data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 190703 17:03:00
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> 

四、创建CDH数据库并验证从库是否有数据

1、在主库中创建CDH数据库

root@hexindai-c12-120 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.26-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> 
mysql> create database cdh default character set = utf8;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> grant all privileges on cdh.* to 'cdh'@'172.20.102.12%' identified by 'wtnyihg' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdh                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> quit
Bye
[root@hexindai-c12-120 ~]# 

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

[root@hexindai-c12-121 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.26-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>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdh                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> 

3、测试完成后，记得要删除CDH库，否则我们在安装CM时会提示CDH库已经存在的报错。

mysql> drop database cdh;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

 

若数据库中有的数据不同步，需要进行操作的时候用 set sql_log_bin=0; 命令来临时屏蔽掉binlog,然后在操作！

 例 如：
    主库有一个aaa库，从库中没有，需要删除掉aaa库，在mysql下执行如下命令：
    set sql_log_bin=0;
    drop database aaa;