MySQL5.7基于binary log的主从复制
分类:
IT文章
•
2022-05-24 22:15:37
MySQL5.7基于binary log的主从复制
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
基于binary log 的复制是指主库将修改操作写入binary log 中，从库负责读取主库的binary log ，并且在本地复制一份，然后里面的操作在从库执行一遍。
每个从库会保存目前读取主库日志的文件名和日志位置。
主库和每个从库都必须有一个唯一ID，叫server-id配置在配置文件中。
一.部署mysql数据库
1>.操作系统环境（2台配置想用的虚拟机即可，配置如下）
[root@node101 ~]# hostname
node101.yinzhengjie.org.cn
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# cat /etc/hosts | grep yinzhengjie
172.30.1.101 node101.yinzhengjie.org.cn
172.30.1.102 node102.yinzhengjie.org.cn
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# free -h
total used free shared buff/cache available
Mem: 3.7G 127M 3.4G 8.5M 166M 3.4G
Swap: 2.0G 0B 2.0G
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# uname -r
3.10.0-327.el7.x86_64
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# uname -m
x86_64
[root@node101 ~]#
[root@node101 ~]#
2>.安装mysql数据库
如下图所示，下载MySQL的tar包，下载地址：https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz。
![]()
[root@node101 ~]# mkdir -pv /yinzhengjie/softwares
mkdir: created directory ‘/yinzhengjie’
mkdir: created directory ‘/yinzhengjie/softwares’
[root@node101 ~]#
[root@node101 ~]# ll
total 629752
-rw-r--r--. 1 root root 644862820 Mar 1 08:24 mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz
[root@node101 ~]#
[root@node101 ~]# tar -zxf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz -C /yinzhengjie/softwares/
[root@node101 ~]#
[root@node101 ~]# ln -s /yinzhengjie/softwares/mysql-5.7.25-linux-glibc2.12-x86_64 /yinzhengjie/softwares/mysql
[root@node101 ~]#
[root@node101 ~]# mkdir /yinzhengjie/softwares/mysql/data
[root@node101 ~]#
[root@node101 ~]# ll /yinzhengjie/softwares/mysql/
total 40
drwxr-xr-x. 2 root root 4096 Mar 2 06:27 bin
-rw-r--r--. 1 7161 31415 17987 Dec 21 02:39 COPYING
drwxr-xr-x. 2 root root 6 Mar 2 06:27 data
drwxr-xr-x. 2 root root 52 Mar 2 06:27 docs
drwxr-xr-x. 3 root root 4096 Mar 2 06:27 include
drwxr-xr-x. 5 root root 4096 Mar 2 06:27 lib
drwxr-xr-x. 4 root root 28 Mar 2 06:27 man
-rw-r--r--. 1 7161 31415 2478 Dec 21 02:39 README
drwxr-xr-x. 28 root root 4096 Mar 2 06:27 share
drwxr-xr-x. 2 root root 86 Mar 2 06:27 support-files
[root@node101 ~]#
[root@node101 ~]# useradd -s /sbin/nologin mysql
[root@node101 ~]#
[root@node101 ~]# id mysql
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# chown mysql:mysql -R /yinzhengjie/softwares/mysql
[root@node101 ~]#
[root@node101 ~]# ll -d /yinzhengjie/softwares/mysql-5.7.25-linux-glibc2.12-x86_64/
drwxr-xr-x. 10 mysql mysql 4096 Mar 2 06:27 /yinzhengjie/softwares/mysql-5.7.25-linux-glibc2.12-x86_64/
[root@node101 ~]#
[root@node101 ~]# cat ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin:/yinzhengjie/softwares/mysql/bin/
export PATH
[root@node101 ~]#
[root@node101 ~]# source ~/.bash_profile
[root@node101 ~]#
[root@node101 ~]# mysqld --initialize --user=mysql --basedir=/yinzhengjie/softwares/mysql --datadir=/yinzhengjie/softwares/mysql/data &
[1] 2626
[root@node101 ~]# 2019-03-02T14:37:23.217229Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-03-02T14:37:24.603930Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-03-02T14:37:24.794673Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-03-02T14:37:24.862231Z 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: b2127a6e-3cf8-11e9-ae0d-000c29fe9bef.
2019-03-02T14:37:24.863307Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-03-02T14:37:24.864126Z 1 [Note] A temporary password is generated for root@localhost: prxUpf-#P7su
[1]+ Done mysqld --initialize --user=mysql --basedir=/yinzhengjie/softwares/mysql --datadir=/yinzhengjie/softwares/mysql/data
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# cat /etc/my.cnf
[mysqld]
basedir=/yinzhengjie/softwares/mysql/
datadir=/yinzhengjie/softwares/mysql/data/
[root@node101 ~]#
[root@node101 ~]# /etc/init.d/mysql.server start
Starting MySQL.Logging to '/yinzhengjie/softwares/mysql/data/node101.yinzhengjie.org.cn.err'.
SUCCESS!
[root@node101 ~]#
[root@node101 ~]# /etc/init.d/mysql.server status
SUCCESS! MySQL running (4494)
[root@node101 ~]#
[root@node101 ~]# /etc/init.d/mysql.server status
SUCCESS! MySQL running (4494)
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# 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.25
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 'yinzhengjie';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@node101 ~]#
[root@node101 ~]# 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 3
Server version: 5.7.25 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 |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> quit
Bye
[root@node101 ~]#
MySQL5.7安装过程笔记详解！戳这里！
安装MySQL5.7时如果遇到报错可参考我的笔记：https://www.cnblogs.com/yinzhengjie/p/10322426.html。
3>.关闭并禁用防火墙和selinux
![]()
[root@node101 ~]#
[root@node101 ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
Active: active (running) since Fri 2019-03-01 05:18:43 PST; 2h 24min ago
Main PID: 758 (firewalld)
CGroup: /system.slice/firewalld.service
└─758 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid
Mar 01 05:18:40 node101.yinzhengjie.org.cn systemd[1]: Starting firewalld - dynamic firewall daemon...
Mar 01 05:18:43 node101.yinzhengjie.org.cn systemd[1]: Started firewalld - dynamic firewall daemon.
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
Removed symlink /etc/systemd/system/basic.target.wants/firewalld.service.
[root@node101 ~]#
[root@node101 ~]# systemctl stop firewalld
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Mar 01 05:18:40 node101.yinzhengjie.org.cn systemd[1]: Starting firewalld - dynamic firewall daemon...
Mar 01 05:18:43 node101.yinzhengjie.org.cn systemd[1]: Started firewalld - dynamic firewall daemon.
Mar 01 07:43:47 node101.yinzhengjie.org.cn systemd[1]: Stopping firewalld - dynamic firewall daemon...
Mar 01 07:43:48 node101.yinzhengjie.org.cn systemd[1]: Stopped firewalld - dynamic firewall daemon.
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# systemctl stop firewalld
![]()
[root@node101 ~]# sed -i s'#SELINUX=enforcing#SELINUX=disabled#' /etc/selinux/config
[root@node101 ~]# getenforce
Enforcing
[root@node101 ~]# setenforce 0
[root@node101 ~]#
[root@node101 ~]# getenforce
Permissive
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# sed -i s'#SELINUX=enforcing#SELINUX=disabled#' /etc/selinux/config
4>.在主库（node101.yinzhengjie.org.cn）中添加测试数据
![]()
[root@node101 ~]# 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 2
Server version: 5.7.25 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>
mysql>
mysql> CREATE DATABASE course CHARACTER SET = utf8;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> USE course;
Database changed
mysql>
mysql>
mysql> CREATE TABLE dept(id INT PRIMARY KEY AUTO_INCREMENT,demt_name VARCHAR(64));
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE students(
-> sid INT PRIMARY KEY AUTO_INCREMENT,
-> sname VARCHAR(64),
-> gender VARCHAR(12),
-> dept_id INT NOT NULL,
-> CONSTRAINT student_dept FOREIGN KEY(dept_id) REFERENCES dept(id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> CREATE TABLE teacher(
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(64),
-> dept_id INT NOT NULL,
-> CONSTRAINT teacher_dept FOREIGN KEY(dept_id) REFERENCES dept(id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> CREATE TABLE course(
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> course_name VARCHAR(64),
-> teacher_id INT,
-> CONSTRAINT course_teacher FOREIGN KEY(teacher_id) REFERENCES teacher(id)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE score(sid INT,course_id INT,score INT,PRIMARY KEY(sid,course_id));
Query OK, 0 rows affected (0.04 sec)
mysql>
创建数据库与对应的表结构
![]()
插入预置数据
二.MySQL基于binlog的复制的配置方法
1>.修改my.cnf配置文件
主库需要开启bin-log，并且指定一个唯一的server-id，重启数据库。
在同一个复制组下的所有server_id都必须是唯一的，而且取值必须是正整数，取值范围是1~（2^32）-1。
确保主库的my.cnf中skip-networking参数为非开启状态，否则会导致主从库不能通信而复制失败
![]()
[root@node101 ~]# cat /etc/my.cnf
[mysqld]
basedir=/yinzhengjie/softwares/mysql/
datadir=/yinzhengjie/softwares/mysql/data/
log-bin=yinzhengjie-mysql-bin
server-id=1
[root@node101 ~]#
[root@node101 ~]# /etc/init.d/mysql.server restart #修改配置后，需要重启服务
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@node101 ~]#
[root@node101 ~]# ll /yinzhengjie/softwares/mysql/data/ #重启数据库后查看MySQL的数据目录
total 122952
-rw-r-----. 1 mysql mysql 56 Mar 2 06:37 auto.cnf
drwxr-x---. 2 mysql mysql 4096 Mar 3 06:25 course
-rw-r-----. 1 mysql mysql 362 Mar 3 06:28 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Mar 3 06:28 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Mar 3 06:28 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Mar 2 06:37 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Mar 3 06:28 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Mar 2 06:37 mysql
-rw-r-----. 1 mysql mysql 18440 Mar 3 06:28 node101.yinzhengjie.org.cn.err
-rw-r-----. 1 mysql mysql 5 Mar 3 06:28 node101.yinzhengjie.org.cn.pid
drwxr-x---. 2 mysql mysql 8192 Mar 2 06:37 performance_schema
drwxr-x---. 2 mysql mysql 8192 Mar 2 06:37 sys
-rw-r-----. 1 mysql mysql 154 Mar 3 06:28 yinzhengjie-mysql-bin.000001
-rw-r-----. 1 mysql mysql 31 Mar 3 06:28 yinzhengjie-mysql-bin.index
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# cat /etc/my.cnf
使用MySQL专用的脚本查看bin-log日志
![]()
[root@node101 ~]# ll /yinzhengjie/softwares/mysql/data/
total 122952
-rw-r-----. 1 mysql mysql 56 Mar 2 06:37 auto.cnf
drwxr-x---. 2 mysql mysql 4096 Mar 3 06:25 course
-rw-r-----. 1 mysql mysql 362 Mar 3 06:28 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Mar 3 06:28 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Mar 3 06:28 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Mar 2 06:37 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Mar 3 06:28 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Mar 2 06:37 mysql
-rw-r-----. 1 mysql mysql 18440 Mar 3 06:28 node101.yinzhengjie.org.cn.err
-rw-r-----. 1 mysql mysql 5 Mar 3 06:28 node101.yinzhengjie.org.cn.pid
drwxr-x---. 2 mysql mysql 8192 Mar 2 06:37 performance_schema
drwxr-x---. 2 mysql mysql 8192 Mar 2 06:37 sys
-rw-r-----. 1 mysql mysql 154 Mar 3 06:28 yinzhengjie-mysql-bin.000001
-rw-r-----. 1 mysql mysql 31 Mar 3 06:28 yinzhengjie-mysql-bin.index
[root@node101 ~]#
[root@node101 ~]# mysqlbinlog -v /yinzhengjie/softwares/mysql/data/yinzhengjie-mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190303 6:28:39 server id 1 end_log_pos 123 CRC32 0x8c40aa35 Start: binlog v 4, server v 5.7.25-log created 190303 6:28:39 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
l+R7XA8BAAAAdwAAAHsAAAABAAQANS43LjI1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACX5HtcEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ATWqQIw=
'/*!*/;
# at 123
#190303 6:28:39 server id 1 end_log_pos 154 CRC32 0xf9fa3f87 Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@node101 ~]#
[root@node101 ~]# mysqlbinlog -v /yinzhengjie/softwares/mysql/data/yinzhengjie-mysql-bin.000001 #在做修改之前的操作
![]()
[root@node101 ~]#
[root@node101 ~]# 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 2
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 DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| course |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql> USE course
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>
mysql> SHOW TABLES;
+------------------+
| Tables_in_course |
+------------------+
| course |
| dept |
| score |
| students |
| teacher |
+------------------+
5 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM students;
+-----+-----------+--------+---------+
| sid | sname | gender | dept_id |
+-----+-----------+--------+---------+
| 1 | Jason Yin | 0 | 1 |
| 2 | Andy | 0 | 1 |
| 3 | Bob | 0 | 1 |
| 4 | Ruth | 1 | 2 |
| 5 | Mike | 0 | 2 |
| 6 | John | 0 | 3 |
| 7 | Cindy | 1 | 3 |
| 8 | Susan | 1 | 3 |
+-----+-----------+--------+---------+
8 rows in set (0.00 sec)
mysql>
mysql> UPDATE students SET sname='尹正杰' WHERE sid=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> DELETE FROM students WHERE sid>5;
Query OK, 3 rows affected (0.00 sec)
mysql>
mysql>
mysql> CREATE INDEX idx_sname ON students(sname);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> SHOW CREATE TABLE students;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`sname` varchar(64) DEFAULT NULL,
`gender` varchar(12) DEFAULT NULL,
`dept_id` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `student_dept` (`dept_id`),
KEY `idx_sname` (`sname`),
CONSTRAINT `student_dept` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql>
一系列DML语句操作
![]()
[root@node101 ~]#
[root@node101 ~]# mysqlbinlog -v /yinzhengjie/softwares/mysql/data/yinzhengjie-mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190303 6:28:39 server id 1 end_log_pos 123 CRC32 0x8c40aa35 Start: binlog v 4, server v 5.7.25-log created 190303 6:28:39 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
l+R7XA8BAAAAdwAAAHsAAAABAAQANS43LjI1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACX5HtcEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ATWqQIw=
'/*!*/;
# at 123
#190303 6:28:39 server id 1 end_log_pos 154 CRC32 0xf9fa3f87 Previous-GTIDs
# [empty]
# at 154
#190303 6:34:22 server id 1 end_log_pos 219 CRC32 0xf24a19ab Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#190303 6:34:22 server id 1 end_log_pos 293 CRC32 0xb3462630 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1551623662/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 293
#190303 6:34:22 server id 1 end_log_pos 353 CRC32 0x73a81747 Table_map: `course`.`students` mapped to number 111
# at 353
#190303 6:34:22 server id 1 end_log_pos 431 CRC32 0x3fbc7a1b Update_rows: table id 111 flags: STMT_END_F
BINLOG '
7uV7XBMBAAAAPAAAAGEBAAAAAG8AAAAAAAEABmNvdXJzZQAIc3R1ZGVudHMABAMPDwMEwAAkAAZH
F6hz
7uV7XB8BAAAATgAAAK8BAAAAAG8AAAAAAAEAAgAE///wAQAAAAlKYXNvbiBZaW4BMAEAAADwAQAA
AAnlsLnmraPmnbABMAEAAAAberw/
'/*!*/;
### UPDATE `course`.`students`
### WHERE
### @1=1
### @2='Jason Yin'
### @3='0'
### @4=1
### SET
### @1=1
### @2='尹正杰'
### @3='0'
### @4=1
# at 431
#190303 6:34:22 server id 1 end_log_pos 462 CRC32 0x3280146f Xid = 17
COMMIT/*!*/;
# at 462
#190303 6:35:28 server id 1 end_log_pos 527 CRC32 0xb5a01913 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 527
#190303 6:35:28 server id 1 end_log_pos 601 CRC32 0x9b6f868c Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1551623728/*!*/;
BEGIN
/*!*/;
# at 601
#190303 6:35:28 server id 1 end_log_pos 661 CRC32 0xe0eec4e9 Table_map: `course`.`students` mapped to number 111
# at 661
#190303 6:35:28 server id 1 end_log_pos 746 CRC32 0xa9b72d9b Delete_rows: table id 111 flags: STMT_END_F
BINLOG '
MOZ7XBMBAAAAPAAAAJUCAAAAAG8AAAAAAAEABmNvdXJzZQAIc3R1ZGVudHMABAMPDwMEwAAkAAbp
xO7g
MOZ7XCABAAAAVQAAAOoCAAAAAG8AAAAAAAEAAgAE//AGAAAABEpvaG4BMAMAAADwBwAAAAVDaW5k
eQExAwAAAPAIAAAABVN1c2FuATEDAAAAmy23qQ==
'/*!*/;
### DELETE FROM `course`.`students`
### WHERE
### @1=6
### @2='John'
### @3='0'
### @4=3
### DELETE FROM `course`.`students`
### WHERE
### @1=7
### @2='Cindy'
### @3='1'
### @4=3
### DELETE FROM `course`.`students`
### WHERE
### @1=8
### @2='Susan'
### @3='1'
### @4=3
# at 746
#190303 6:35:28 server id 1 end_log_pos 777 CRC32 0x9539228a Xid = 18
COMMIT/*!*/;
# at 777
#190303 6:36:43 server id 1 end_log_pos 842 CRC32 0x4879ed39 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 842
#190303 6:36:43 server id 1 end_log_pos 961 CRC32 0x7cf1c0fd Query thread_id=2 exec_time=0 error_code=0
use `course`/*!*/;
SET TIMESTAMP=1551623803/*!*/;
CREATE INDEX idx_sname ON students(sname)
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# mysqlbinlog -v /yinzhengjie/softwares/mysql/data/yinzhengjie-mysql-bin.000001 #我们会发现操作日志都会被记录到bin log日志中！
2>.在主库创建一个专门用来复制的数据库用户，这样所有从库都可以用这个用户来连接主库，也可以确保这个用户只有复制的权限
虽然可以用任何拥有复制权限的MySQL用户来复制关系，但由于被使用的用户名和密码会明文保存在备库的master.info文件中，所以为安全起见，最好是使用仅有复制权限的独立用户。
[root@node101 ~]# 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 2
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> CREATE USER 'copy'@'172.30.1.10%' IDENTIFIED BY 'yinzhengjie';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> GRANT REPLICATION SLAVE ON *.* TO 'copy'@'172.30.1.10%';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> quit
Bye
[root@node101 ~]#
[root@node101 ~]#
![]()
[root@node102 ~]# mysql -u copy -pyinzhengjie -P 3306 -h node101.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 4
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 DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
mysql>
mysql> quit
Bye
[root@node102 ~]#
[root@node102 ~]#
[root@node102 ~]# mysql -u copy -pyinzhengjie -P 3306 -h node101.yinzhengjie.org.cn #从slave库验证远程连接主库是否正常
3>.获取主库的日志信息
为了确保建立的备库能从正确的bin log位置开启复制，要首先获取主库的bin log信息，包括当前的日志文件名和日志文件内的位置。
mysql> FLUSH TABLES WITH READ LOCK; #对主库上所有表加锁，停止修改，即在从库复制的过程中主库不能执行UPDATA，DELETE，INSERT语句！
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW MASTER STATUS; #获取主库的日志信息，file表示当前日志文件名称，position表示当前日志的位置。
+------------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------------+----------+--------------+------------------+-------------------+
| yinzhengjie-mysql-bin.000001 | 1424 | | | |
+------------------------------+----------+--------------+------------------+-------------------+
row in set (0.00 sec)
mysql>
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| course |
+------------+
row in set (0.00 sec)
mysql>
mysql> DROP TABLE dept; #我们对主库的所有表加锁后，我们是没法对表进行修改的，删除表也不行哟！
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql>
4>.主库数据生成镜像并上传到从库
有两种方式生成镜像，
一种是用mysqldump，是innodb存储引擎的方式；
![]()
[root@node101 ~]# 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@node101 ~]#
[root@node101 ~]# ll -h
total 784K
-rw-r--r--. 1 root root 781K Mar 3 06:58 yinzhengjie-master.db
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# cat yinzhengjie-master.db | tail -30
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `slow_log` (
`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`user_host` mediumtext NOT NULL,
`query_time` time(6) NOT NULL,
`lock_time` time(6) NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`sql_text` mediumblob NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2019-03-03 6:58:58
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# hostname
node101.yinzhengjie.org.cn
[root@node101 ~]#
[root@node101 ~]# hostname -i
172.30.1.101
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# sftp -oPort=22 root@node102.yinzhengjie.org.cn
Connected to node102.yinzhengjie.org.cn.
sftp> put yinzhengjie-master.db /root/yinzhengjie-master.db
Uploading yinzhengjie-master.db to /root/yinzhengjie-master.db
yinzhengjie-master.db 100% 781KB 780.9KB/s 00:00
sftp> quit
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# mysqldump --all-databases --master-data -u root -pyinzhengjie -P 3306 > yinzhengjie-master.db #将主库的所有库的表结构和表数据都导出来并将该文件上传到从库节点中。
![]()
[root@node102 ~]# hostname
node102.yinzhengjie.org.cn
[root@node102 ~]#
[root@node102 ~]# hostname -i
172.30.1.102
[root@node102 ~]#
[root@node102 ~]#
[root@node102 ~]#
[root@node102 ~]# ll
total 784
-rw-r--r--. 1 root root 799648 Mar 3 07:02 yinzhengjie-master.db
[root@node102 ~]#
[root@node102 ~]#
[root@node102 ~]# 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 3
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 DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
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, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
.......
mysql>
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| course |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql> use course;
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>
mysql>
mysql> SHOW TABLES;
+------------------+
| Tables_in_course |
+------------------+
| course |
| dept |
| score |
| students |
| teacher |
+------------------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql> SELECT * FROM students;
+-----+-----------+--------+---------+
| sid | sname | gender | dept_id |
+-----+-----------+--------+---------+
| 1 | 尹正杰 | 0 | 1 |
| 2 | Andy | 0 | 1 |
| 3 | Bob | 0 | 1 |
| 4 | Ruth | 1 | 2 |
| 5 | Mike | 0 | 2 |
+-----+-----------+--------+---------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql> source yinzhengjie-master.db; #从库将主库的文件导入到从库中，使得2个库的数据保持一致！
另一种是将数据文件从主库拷贝到从库，这种方式效率更高（省去了doum/import过程中insert语句执行导致的更新index的行为），但innodb不推荐使用。
![]()
[root@node101 ~]# hostname
node101.yinzhengjie.org.cn
[root@node101 ~]#
[root@node101 ~]# hostname -i
172.30.1.101
[root@node101 ~]#
[root@node101 ~]# /etc/init.d/mysql.server stop
Shutting down MySQL.. SUCCESS!
[root@node101 ~]#
[root@node101 ~]# ll /yinzhengjie/softwares/mysql/data/
total 110664
-rw-r-----. 1 mysql mysql 56 Mar 2 06:37 auto.cnf
drwxr-x---. 2 mysql mysql 4096 Mar 3 06:36 course
-rw-r-----. 1 mysql mysql 430 Mar 3 07:12 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Mar 3 07:12 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Mar 3 07:12 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Mar 2 06:37 ib_logfile1
drwxr-x---. 2 mysql mysql 4096 Mar 2 06:37 mysql
-rw-r-----. 1 mysql mysql 22776 Mar 3 07:12 node101.yinzhengjie.org.cn.err
drwxr-x---. 2 mysql mysql 8192 Mar 2 06:37 performance_schema
drwxr-x---. 2 mysql mysql 8192 Mar 2 06:37 sys
-rw-r-----. 1 mysql mysql 1447 Mar 3 07:12 yinzhengjie-mysql-bin.000001
-rw-r-----. 1 mysql mysql 31 Mar 3 06:28 yinzhengjie-mysql-bin.index
[root@node101 ~]#
[root@node101 ~]# tar -zcf yinzhengjie-mysql-data.tar.gz /yinzhengjie/softwares/mysql/data
tar: Removing leading `/' from member names
[root@node101 ~]#
[root@node101 ~]# ls
yinzhengjie-master.db yinzhengjie-mysql-data.tar.gz
[root@node101 ~]#
[root@node101 ~]# sftp -oPort=22 root@node102.yinzhengjie.org.cn
Connected to node102.yinzhengjie.org.cn.
sftp> put yinzhengjie-mysql-data.tar.gz /root/yinzhengjie-mysql-data.tar.gz
Uploading yinzhengjie-mysql-data.tar.gz to /root/yinzhengjie-mysql-data.tar.gz
yinzhengjie-mysql-data.tar.gz 100% 1336KB 1.3MB/s 00:00
sftp>
sftp> quit
[root@node101 ~]#
将主库的MySQL数据目录打包发送到从库中
![]()
[root@node102 ~]# 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 4
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 |
| course |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
mysql>
mysql> quit
Bye
[root@node102 ~]#
[root@node102 ~]# /etc/init.d/mysql.server stop
Shutting down MySQL.. SUCCESS!
[root@node102 ~]#
[root@node102 ~]#
[root@node102 ~]# hostname
node102.yinzhengjie.org.cn
[root@node102 ~]#
[root@node102 ~]# hostname -i
172.30.1.102
[root@node102 ~]#
[root@node102 ~]# ll
total 2120
-rw-r--r--. 1 root root 799648 Mar 3 07:02 yinzhengjie-master.db
-rw-r--r--. 1 root root 1367868 Mar 3 07:13 yinzhengjie-mysql-data.tar.gz
[root@node102 ~]#
[root@node102 ~]# cat /etc/my.cnf
[mysqld]
basedir=/yinzhengjie/softwares/mysql/
datadir=/yinzhengjie/softwares/mysql/data/
log-bin=yinzhengjie-mysql-bin
server-id=1
[root@node102 ~]#
[root@node102 ~]#
[root@node102 ~]# mv /yinzhengjie/softwares/mysql/data /yinzhengjie/softwares/mysql/data.`date +%F`
[root@node102 ~]#
[root@node102 ~]# ll
total 2120
-rw-r--r--. 1 root root 799648 Mar 3 07:02 yinzhengjie-master.db
-rw-r--r--. 1 root root 1367868 Mar 3 07:13 yinzhengjie-mysql-data.tar.gz
[root@node102 ~]#
[root@node102 ~]# tar -zxf yinzhengjie-mysql-data.tar.gz
[root@node102 ~]#
[root@node102 ~]# ll
total 2120
drwxr-xr-x. 3 root root 22 Mar 3 07:20 yinzhengjie
-rw-r--r--. 1 root root 799648 Mar 3 07:02 yinzhengjie-master.db
-rw-r--r--. 1 root root 1367868 Mar 3 07:13 yinzhengjie-mysql-data.tar.gz
[root@node102 ~]#
[root@node102 ~]# mv yinzhengjie/softwares/mysql/data /yinzhengjie/softwares/mysql/
[root@node102 ~]#
[root@node102 ~]# ll /yinzhengjie/softwares/mysql/
total 48
drwxr-xr-x. 2 mysql mysql 4096 Mar 2 06:57 bin
-rw-r--r--. 1 mysql mysql 17987 Mar 2 06:57 COPYING
drwxr-xr-x. 6 mysql mysql 4096 Mar 3 07:12 data
drwxr-xr-x. 5 mysql mysql 4096 Mar 3 07:12 data.2019-03-03
drwxr-xr-x. 2 mysql mysql 52 Mar 2 06:57 docs
drwxr-xr-x. 3 mysql mysql 4096 Mar 2 06:57 include
drwxr-xr-x. 5 mysql mysql 4096 Mar 2 06:57 lib
drwxr-xr-x. 4 mysql mysql 28 Mar 2 06:57 man
-rw-r--r--. 1 mysql mysql 2478 Mar 2 06:57 README
drwxr-xr-x. 28 mysql mysql 4096 Mar 2 06:57 share
drwxr-xr-x. 2 mysql mysql 86 Mar 2 06:57 support-files
[root@node102 ~]#
[root@node102 ~]#
[root@node102 ~]# /etc/init.d/mysql.server start
Starting MySQL. SUCCESS!
[root@node102 ~]#
[root@node102 ~]# 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 3
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>
mysql>
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| course |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql> USE course
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>
mysql> SHOW TABLES;
+------------------+
| Tables_in_course |
+------------------+
| course |
| dept |
| score |
| students |
| teacher |
+------------------+
5 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM students;
+-----+-----------+--------+---------+
| sid | sname | gender | dept_id |
+-----+-----------+--------+---------+
| 1 | 尹正杰 | 0 | 1 |
| 2 | Andy | 0 | 1 |
| 3 | Bob | 0 | 1 |
| 4 | Ruth | 1 | 2 |
| 5 | Mike | 0 | 2 |
+-----+-----------+--------+---------+
5 rows in set (0.00 sec)
mysql>
mysql> quit
Bye
[root@node102 ~]#
将主库的数据目录放在从库的数据目录中，启动从库的MySQL即可看到和主库相同的数据啦！前提是要保证主库和从库的配置文件要一致哟！
温馨提示：
当你是将数据目录直接拷贝到从库时，需要关闭主库和从库的数据库实例。启动从库实例之前，我们最好是手动将数据目录中的“auto.cnf”删除掉，因为它也是唯一标识一个服务器实例的一个标志，因此我们让从库和主库的auto.cnf的内容一致！此时我们手动将其删除即可，并且我们应该保证哥哥数据的server-id的值应该是不同的！然后重启数据库后，会在从库的数据目录中自动生成新的’auto.cnf’文件具体操作如下所示。
[root@node102 ~]#
[root@node102 ~]# ll /yinzhengjie/softwares/mysql/data/auto.cnf
-rw-r-----. 1 mysql mysql 56 Mar 2 06:37 /yinzhengjie/softwares/mysql/data/auto.cnf
[root@node102 ~]#
[root@node102 ~]#
[root@node102 ~]# cat /yinzhengjie/softwares/mysql/data/auto.cnf
[auto]
server-uuid=b2127a6e-3cf8-11e9-ae0d-000c29fe9bef
[root@node102 ~]#
[root@node102 ~]#
[root@node102 ~]# rm -f /yinzhengjie/softwares/mysql/data/auto.cnf
[root@node102 ~]#
[root@node102 ~]# cat /etc/my.cnf
[mysqld]
basedir=/yinzhengjie/softwares/mysql/
datadir=/yinzhengjie/softwares/mysql/data/
log-bin=yinzhengjie-mysql-bin
server-id=2
[root@node102 ~]#
[root@node102 ~]# /etc/init.d/mysql.server restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@node102 ~]#
[root@node102 ~]# cat /yinzhengjie/softwares/mysql/data/auto.cnf
[auto]
server-uuid=d4802ad0-3dc9-11e9-ae9a-000c29dc1634
[root@node102 ~]#
5>.在从库上建立复制关系
[root@node102 ~]# 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 2
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> CHANGE MASTER TO
-> MASTER_HOST='node101.yinzhengjie.org.cn',
-> MASTER_PORT=3306,
-> MASTER_USER='copy',
-> MASTER_PASSWORD='yinzhengjie',
-> MASTER_LOG_FILE='yinzhengjie-mysql-bin.000001',
-> MASTER_LOG_POS=1424;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql>
mysql> START SLAVE; #打开slave的同步进程
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> SHOW SLAVE STATUSG #查看slave复制进程信息
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: node101.yinzhengjie.org.cn
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: yinzhengjie-mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: node102-relay-bin.000003
Relay_Log_Pos: 391
Relay_Master_Log_File: yinzhengjie-mysql-bin.000002
Slave_IO_Running: Yes #如果你看到了这个IO和下面一行的SQL都为yes则说明你的配置是没有问题的！还及得IO和SQL线程各自的功能是干嘛的么？
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: 154
Relay_Log_Space: 778
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 #该参数表示同步的记录master和slave之间的差距！咱们理解数据的同步的延迟大小，若为0表示目前从库的数据和主库的数据是一样的！
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0 #如果上面的IO线程不是yes的话，我们就可以在这里查看抱错信息，根据相应的报错查找对应的解决方案即可，下面的SQL日志错误也是同样的道理。
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: b2127a6e-3cf8-11e9-ae0d-000c29fe9bef
Master_Info_File: /yinzhengjie/softwares/mysql/data/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:
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>
6>.主库上更新数据
![]()
[root@node101 ~]# hostname -i
172.30.1.101
[root@node101 ~]#
[root@node101 ~]# 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 3
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> SELECT * FROM course.students;
+-----+-----------+--------+---------+
| sid | sname | gender | dept_id |
+-----+-----------+--------+---------+
| 1 | 尹正杰 | 0 | 1 |
| 2 | Andy | 0 | 1 |
| 3 | Bob | 0 | 1 |
| 4 | Ruth | 1 | 2 |
| 5 | Mike | 0 | 2 |
+-----+-----------+--------+---------+
5 rows in set (0.02 sec)
mysql>
mysql>
mysql> UPDATE course.students SET sname='Jason Yin' WHERE sid=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> SELECT * FROM course.students;
+-----+-----------+--------+---------+
| sid | sname | gender | dept_id |
+-----+-----------+--------+---------+
| 1 | Jason Yin | 0 | 1 |
| 2 | Andy | 0 | 1 |
| 3 | Bob | 0 | 1 |
| 4 | Ruth | 1 | 2 |
| 5 | Mike | 0 | 2 |
+-----+-----------+--------+---------+
5 rows in set (0.00 sec)
mysql>
mysql> UPDATE course.students SET sname='Jason Yin' WHERE sid=1; #主库更新数据
![]()
[root@node102 ~]#
[root@node102 ~]# hostname
node102.yinzhengjie.org.cn
[root@node102 ~]#
[root@node102 ~]# hostname -i
172.30.1.102
[root@node102 ~]#
[root@node102 ~]# 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>
mysql>
mysql> SELECT * FROM course.students;
+-----+-----------+--------+---------+
| sid | sname | gender | dept_id |
+-----+-----------+--------+---------+
| 1 | Jason Yin | 0 | 1 |
| 2 | Andy | 0 | 1 |
| 3 | Bob | 0 | 1 |
| 4 | Ruth | 1 | 2 |
| 5 | Mike | 0 | 2 |
+-----+-----------+--------+---------+
5 rows in set (0.00 sec)
mysql>
mysql> quit
Bye
[root@node102 ~]#
[root@node102 ~]#
mysql> SELECT * FROM course.students; #从库查看数据已经被更改啦~
7>.查看bin log日志
注意，主库和从库都是查看最新的日志文件，我们可以根据文件编号来确定，而且主库查看的是yinzhengjie-mysql-bin.000002文件，而从库查看的是node102-relay-bin.000003。
![]()
[root@node101 ~]# ll /yinzhengjie/softwares/mysql/data/
total 122964
-rw-r-----. 1 mysql mysql 56 Mar 2 06:37 auto.cnf
drwxr-x---. 2 mysql mysql 4096 Mar 3 06:36 course
-rw-r-----. 1 mysql mysql 430 Mar 3 07:12 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Mar 3 07:51 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Mar 3 07:51 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Mar 2 06:37 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Mar 3 07:28 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Mar 2 06:37 mysql
-rw-r-----. 1 mysql mysql 26155 Mar 3 07:45 node101.yinzhengjie.org.cn.err
-rw-r-----. 1 mysql mysql 5 Mar 3 07:28 node101.yinzhengjie.org.cn.pid
drwxr-x---. 2 mysql mysql 8192 Mar 2 06:37 performance_schema
drwxr-x---. 2 mysql mysql 8192 Mar 2 06:37 sys
-rw-r-----. 1 mysql mysql 1447 Mar 3 07:12 yinzhengjie-mysql-bin.000001
-rw-r-----. 1 mysql mysql 456 Mar 3 07:51 yinzhengjie-mysql-bin.000002
-rw-r-----. 1 mysql mysql 62 Mar 3 07:28 yinzhengjie-mysql-bin.index
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# mysqlbinlog -v /yinzhengjie/softwares/mysql/data/yinzhengjie-mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190303 7:28:13 server id 1 end_log_pos 123 CRC32 0x35bede44 Start: binlog v 4, server v 5.7.25-log created 190303 7:28:13 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
jfJ7XA8BAAAAdwAAAHsAAAABAAQANS43LjI1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACN8ntcEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AUTevjU=
'/*!*/;
# at 123
#190303 7:28:13 server id 1 end_log_pos 154 CRC32 0x2d98f213 Previous-GTIDs
# [empty]
# at 154
#190303 7:51:20 server id 1 end_log_pos 219 CRC32 0xc171b0f8 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#190303 7:51:20 server id 1 end_log_pos 287 CRC32 0xa72fa9e7 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1551628280/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 287
#190303 7:51:20 server id 1 end_log_pos 347 CRC32 0x9149fef1 Table_map: `course`.`students` mapped to number 108
# at 347
#190303 7:51:20 server id 1 end_log_pos 425 CRC32 0x9ae384b1 Update_rows: table id 108 flags: STMT_END_F
BINLOG '
+Pd7XBMBAAAAPAAAAFsBAAAAAGwAAAAAAAEABmNvdXJzZQAIc3R1ZGVudHMABAMPDwMEwAAkAAbx
/kmR
+Pd7XB8BAAAATgAAAKkBAAAAAGwAAAAAAAEAAgAE///wAQAAAAnlsLnmraPmnbABMAEAAADwAQAA
AAlKYXNvbiBZaW4BMAEAAACxhOOa
'/*!*/;
### UPDATE `course`.`students`
### WHERE
### @1=1
### @2='尹正杰'
### @3='0'
### @4=1
### SET
### @1=1
### @2='Jason Yin'
### @3='0'
### @4=1
# at 425
#190303 7:51:20 server id 1 end_log_pos 456 CRC32 0x5bddb9c2 Xid = 16
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@node101 ~]#
[root@node101 ~]#
[root@node101 ~]# mysqlbinlog -v /yinzhengjie/softwares/mysql/data/yinzhengjie-mysql-bin.000002 #查看主库的最新日志
![]()
[root@node102 ~]# ll /yinzhengjie/softwares/mysql/data
total 123020
-rw-r-----. 1 mysql mysql 56 Mar 3 07:34 auto.cnf
drwxr-x---. 2 mysql mysql 4096 Mar 3 06:36 course
-rw-r-----. 1 mysql mysql 314 Mar 3 07:38 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Mar 3 07:51 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Mar 3 07:51 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Mar 2 06:37 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Mar 3 07:38 ibtmp1
-rw-r-----. 1 mysql mysql 157 Mar 3 07:55 master.info
drwxr-x---. 2 mysql mysql 4096 Mar 2 06:37 mysql
-rw-r-----. 1 mysql mysql 22776 Mar 3 07:12 node101.yinzhengjie.org.cn.err
-rw-r-----. 1 mysql mysql 387 Mar 3 07:45 node102-relay-bin.000002
-rw-r-----. 1 mysql mysql 693 Mar 3 07:51 node102-relay-bin.000003
-rw-r-----. 1 mysql mysql 54 Mar 3 07:45 node102-relay-bin.index
-rw-r-----. 1 mysql mysql 28282 Mar 3 07:45 node102.yinzhengjie.org.cn.err
-rw-r-----. 1 mysql mysql 5 Mar 3 07:38 node102.yinzhengjie.org.cn.pid
drwxr-x---. 2 mysql mysql 8192 Mar 2 06:37 performance_schema
-rw-r-----. 1 mysql mysql 74 Mar 3 07:51 relay-log.info
drwxr-x---. 2 mysql mysql 8192 Mar 2 06:37 sys
-rw-r-----. 1 mysql mysql 1447 Mar 3 07:12 yinzhengjie-mysql-bin.000001
-rw-r-----. 1 mysql mysql 177 Mar 3 07:23 yinzhengjie-mysql-bin.000002
-rw-r-----. 1 mysql mysql 177 Mar 3 07:34 yinzhengjie-mysql-bin.000003
-rw-r-----. 1 mysql mysql 177 Mar 3 07:38 yinzhengjie-mysql-bin.000004
-rw-r-----. 1 mysql mysql 154 Mar 3 07:38 yinzhengjie-mysql-bin.000005
-rw-r-----. 1 mysql mysql 155 Mar 3 07:38 yinzhengjie-mysql-bin.index
[root@node102 ~]#
[root@node102 ~]#
[root@node102 ~]# mysqlbinlog -v /yinzhengjie/softwares/mysql/data/node102-relay-bin.000003
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190303 7:45:22 server id 2 end_log_pos 123 CRC32 0x93c4b3af Start: binlog v 4, server v 5.7.25-log created 190303 7:45:22
# This Format_description_event appears in a relay log and was generated by the slave thread.
# at 123
#190303 7:45:22 server id 2 end_log_pos 154 CRC32 0xeeedf574 Previous-GTIDs
# [empty]
# at 154
#691231 16:00:00 server id 1 end_log_pos 0 CRC32 0x3c8e65f1 Rotate to yinzhengjie-mysql-bin.000002 pos: 4
# at 213
#190303 7:28:13 server id 1 end_log_pos 123 CRC32 0x35bede44 Start: binlog v 4, server v 5.7.25-log created 190303 7:28:13 at startup
ROLLBACK/*!*/;
BINLOG '
jfJ7XA8BAAAAdwAAAHsAAAAAAAQANS43LjI1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACN8ntcEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AUTevjU=
'/*!*/;
# at 332
#190303 7:45:22 server id 0 end_log_pos 391 CRC32 0xfd49277d Rotate to yinzhengjie-mysql-bin.000002 pos: 154
# at 391
#190303 7:51:20 server id 1 end_log_pos 219 CRC32 0xc171b0f8 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 456
#190303 7:51:20 server id 1 end_log_pos 287 CRC32 0xa72fa9e7 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1551628280/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 524
#190303 7:51:20 server id 1 end_log_pos 347 CRC32 0x9149fef1 Table_map: `course`.`students` mapped to number 108
# at 584
#190303 7:51:20 server id 1 end_log_pos 425 CRC32 0x9ae384b1 Update_rows: table id 108 flags: STMT_END_F
BINLOG '
+Pd7XBMBAAAAPAAAAFsBAAAAAGwAAAAAAAEABmNvdXJzZQAIc3R1ZGVudHMABAMPDwMEwAAkAAbx
/kmR
+Pd7XB8BAAAATgAAAKkBAAAAAGwAAAAAAAEAAgAE///wAQAAAAnlsLnmraPmnbABMAEAAADwAQAA
AAlKYXNvbiBZaW4BMAEAAACxhOOa
'/*!*/;
### UPDATE `course`.`students`
### WHERE
### @1=1
### @2='尹正杰'
### @3='0'
### @4=1
### SET
### @1=1
### @2='Jason Yin'
### @3='0'
### @4=1
# at 662
#190303 7:51:20 server id 1 end_log_pos 456 CRC32 0x5bddb9c2 Xid = 16
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@node102 ~]#
[root@node102 ~]#
[root@node102 ~]# mysqlbinlog -v /yinzhengjie/softwares/mysql/data/node102-relay-bin.000003 #查看从库的最新日志
三.MySQL基于bin log的复制常见报错汇总
1>.Last_IO_Errno:1593
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
报错原因：
服务器ID和复制集群其他的机器重复了。
解决办法：
删除备库的auto.cnf文件，重启mysql，生成新的UUID。
2>.Last_IO_Errno:2003
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'copy@172.30.1.101:3306' - retry-time: 60 retries: 86400
报错原因：
连接MySQL的master节点失败！
解决方案：
备库（slave）连接主库（master）失败，检查防火墙，用户密码端口是否设置正确。
四.MySQL基于binlog的多slave环境
1>.当第一个slave创建好之后，如果还想要创建其他的slave，则可以直接使用先前使用的备份文件，分别执行以下几个步骤：
在slave的my.cnf上分配新的server_id
从库应用主库的数据镜像
利用相同的change master命令将从库指定主库的日志信息和链接信息
slave start
这样第二个slave也就创建起来了。在主库上执行“show processlist”即可查看slave信息。
2>.如果想在事后再增加一个slave，但之前的备份文件已经不存在，或者主库的日志文件已经被清楚了的情况下，考虑使用如下方法：
在已经建立好的复制环境中新增一个从库，则不需要关闭主库复制数据，而是用已有的从库复制数据即可。
关闭现有的从库。（mysqladmin shutdown）
拷贝从库的文件到新的从库，包括log文件和relay log文件，其中如果relay log使用了从库的主机名，则需要调relay-log-index参数。
拷贝master info和reg log info文件到新的从库。
为新的从库分配一个唯一的seriver-id。
新的从库启动slave进程。
