主从介绍
Mysql主从又叫Replication、AB复制。简单讲就是A与B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，实现数据实时同步

mysql主从是基于binlog，主上需开启binlog才能进行主从

主从过程大概有3个步骤

主将更改操作记录到binlog里

从将主的binlog事件（sql语句） 同步本机上并记录在relaylog里

从根据relaylog里面的sql语句按顺序执行

主从作用

• 实时灾备，用于故障切换
• 读写分离，提供查询服务
• 备份，避免影响业务

主从形式

* 一主一从 * 主主复制 * 一主多从---扩展系统读取的性能，因为读是在从库读取的 * 多主一从---5.7版本开始支持 * 联级复制

主从复制原理

主从复制步骤

• 主库将所有的写操作记录在binlog日志中，并生成log dump线程，将binlog日志传给从库的I/O线程

• 从库生成两个线程，一个是I/O线程，另一个是SQL线程

• I/O线程去请求主库的binlog日志，并将binlog日志中的文件写入relay log（中继日志）中
• SQL线程会读取relay loy中的内容，并解析成具体的操作，来实现主从的操作一致，达到最终数据一致的目的

主从复制配置步骤：

1. 确保从数据库与主数据库里的数据一致
2. 在主数据库里创建一个同步账户授权给从数据库使用
3. 配合主数据库（修改配置文件）
4. 配置从数据库（修改配置文件）

需求
搭建两台MYSQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境说明

在两台服务器上都按装mysql

环境准备
关闭防火墙以SELINUX
[[email protected] ~]# systemctl stop firewalld [[email protected] ~]# systemctl disable firewalld [[email protected] ~]# sed -ri ‘s/(SELINUX=).*/1disabled/g‘ /etc/selinux/config [[email protected] ~]# setenforce 0 安装mysql 安装依赖包 [[email protected] ~]# yum -y install ncurses-devel openssl-devel openssl cmake mariadb-devel 创建用户和组 [[email protected] ~]# groupadd -r -g 306 mysql [[email protected] ~]# useradd -M -s /sbin/nologin -g 306 -u 306 mysql 下载二进制格式的mysql软件包 [[email protected] ~]# cd /usr/src/ [[email protected] src]#wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz 解压软件至/usr/local/ [[email protected] src]# ls debug kernels mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz [[email protected] src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ [[email protected] src]# ls /usr/local/ bin etc games include lib lib64 libexec mysql-5.7.22-linux-glibc2.12-x86_64 sbin share src [[email protected] src]# cd /usr/local/ [[email protected] local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql "mysql" -> "mysql-5.7.22-linux-glibc2.12-x86_64/" [[email protected] local]# ll 总用量 0 drwxr-xr-x. 2 root root 6 11月 5 2016 bin drwxr-xr-x. 2 root root 6 11月 5 2016 etc drwxr-xr-x. 2 root root 6 11月 5 2016 games drwxr-xr-x. 2 root root 6 11月 5 2016 include drwxr-xr-x. 2 root root 6 11月 5 2016 lib drwxr-xr-x. 2 root root 6 11月 5 2016 lib64 drwxr-xr-x. 2 root root 6 11月 5 2016 libexec lrwxrwxrwx. 1 root root 36 9月 7 22:20 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/ drwxr-xr-x. 9 root root 129 9月 7 22:19 mysql-5.7.22-linux-glibc2.12-x86_64 drwxr-xr-x. 2 root root 6 11月 5 2016 sbin drwxr-xr-x. 5 root root 49 9月 3 23:02 share drwxr-xr-x. 2 root root 6 11月 5 2016 src 修改目录/usr/locaal/mysql的属主属组 [[email protected] local]# chown -R mysql.mysql /usr/local/mysql [[email protected] local]# ll /usr/local/mysql -d lrwxrwxrwx. 1 mysql mysql 36 9月 7 22:20 /usr/local/mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/ 添加环境变量 [[email protected] local]# ls /usr/local/mysql bin COPYING docs include lib man README share support-files [[email protected] local]# cd [[email protected] ~]# echo ‘export PATH=/usr/local/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh [[email protected] ~]# . /etc/profile.d/mysql.sh [[email protected] ~]# echo $PATH /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin 建立数据存放目录 [[email protected] ~]# cd /usr/local/mysql [[email protected] mysql]# mkdir /opt/data [[email protected] mysql]# chown -R mysql.mysql /opt/data/ [[email protected] mysql]# ll /opt/ 总用量 0 drwxr-xr-x. 2 mysql mysql 6 9月 7 22:25 data 初始化数据库 [[email protected] mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/ //这个命令的最后会生成一个临时密码，此处密码是1EbNA-k*BtKo 配置mysql [[email protected] ~]# ln -sv /usr/local/mysql/include/ /usr/local/include/mysql "/usr/local/include/mysql" -> "/usr/local/mysql/include/" [[email protected] ~]# echo ‘/usr/local/mysql/lib‘ > /etc/ld.so.conf.d/mysql.conf [[email protected] ~]# ldconfig -v 生成配置文件 [[email protected] ~]# cat > /etc/my.cnf <<EOF > [mysqld] > basedir = /usr/local/mysql > datadir = /opt/data > socket = /tmp/mysql.sock > port = 3306 > pid-file = /opt/data/mysql.pid > user = mysql > skip-name-resolve > EOF 配置服务启动脚本 [[email protected] ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [[email protected] ~]# sed -ri ‘s#^(basedir=).*#1/usr/local/mysql#g‘ /etc/init.d/mysqld [[email protected] ~]# sed -ri ‘s#^(datadir=).*#1/opt/data#g‘ /etc/init.d/mysqld 启动mysql [[email protected] ~]# service mysqld start Starting MySQL.Logging to ‘/opt/data/yanyinglai.err‘. .. SUCCESS! [[email protected] ~]# ps -ef|grep mysql root 4897 1 0 22:38 pts/2 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid mysql 5075 4897 6 22:38 pts/2 00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=yanyinglai.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306 root 5109 4668 0 22:38 pts/2 00:00:00 grep --color=auto mysql [[email protected] ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 100 127.0.0.1:25 *:* LISTEN 0 128 :::22 :::* LISTEN 0 100 ::1:25 :::* LISTEN 0 80 :::3306 :::* 修改密码 使用临时密码修改 [[email protected] ~]# 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.22 Copyright (c) 2000, 2018, 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> set password = password(‘123456‘); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> quit Bye mysql主从配置 确保从数据库与主数据库的数据一样先在主数据库创建所需要同步的库和表 [[email protected] ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.7.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. Al Oracle is a registered trademark of Oracle Corporation and affiliates. Other names may be trademarks of their respect owners. Type ‘help;‘ or ‘h‘ for help. Type ‘c‘ to clear the curr mysql> create database yan; Query OK, 1 row affected (0.00 sec) mysql> create database lisi; Query OK, 1 row affected (0.00 sec) mysql> create database wangwu; Query OK, 1 row affected (0.00 sec) mysql> use yan; Database changed mysql> create table tom (id int not null,name varchar(100)not null ,age tinyint); Query OK, 0 rows affected (11.83 sec) mysql> insert tom (id,name,age) values(1,‘zhangshan‘,20),(2,‘wangwu‘,7),(3,‘lisi‘,23); Query OK, 3 rows affected (0.07 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from tom;  ---- ----------- ------  | id | name | age |  ---- ----------- ------  | 1 | zhangshan | 20 | | 2 | wangwu | 7 | | 3 | lisi | 23 |  ---- ----------- ------  3 rows in set (0.00 sec) 备份主库 备份主库时需要另开一个终端，给数据库上读锁，避免在备份期间有其他人在写入导致数据同步的不一致 [[email protected] ~]# 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.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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> flush tables with read lock; Query OK, 0 rows affected (0.76 sec) //此锁表的终端必须在备份完成以后才能退出（退出锁表失效） 备份主库并将备份文件传送到从库 [[email protected] ~]# mysqldump -uroot -p123456 --all-databases > /opt/all-20180907.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [[email protected] ~]# ls /opt/ all-20180907.sql data [[email protected] ~]# scp /opt/all-20180907.sql [email protected]:/opt/ The authenticity of host ‘192.168.55.129 (192.168.55.129)‘ can‘t be established. ECDSA key fingerprint is SHA256:7mLj77SFk7sPkhjpMPfdK3nZ98hOuyP4OKzjXeijSJ0. ECDSA key fingerprint is MD5:a0:1b:eb:7f:f0:b6:7b:73:97:91:4c:f3:b1:89:d8:ea. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added ‘192.168.55.129‘ (ECDSA) to the list of known hosts. [email protected]‘s password: all-20180907.sql 100% 784KB 783.3KB/s 00:01 解除主库的锁表状态，直接退出交互式界面即可 mysql> quit Bye 在从库上恢复主库的备份并查看是否与主库的数据保持一致 [[email protected] ~]# mysql -uroot -p123456 < /opt/all-20180907.sql mysql: [Warning] Using a password on the command line interface can be insecure. [[email protected] ~]# mysql -uroot -p123456 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.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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 | | lisi | | mysql | | performance_schema | | sys | | wangwu | | yan |  --------------------  7 rows in set (0.18 sec) mysql> use yan; 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> select * from tom;  ---- ----------- ------  | id | name | age |  ---- ----------- ------  | 1 | zhangshan | 20 | | 2 | wangwu | 7 | | 3 | lisi | 23 |  ---- ----------- ------  3 rows in set (0.06 sec) 在主数据库创建一个同步账户授权给从数据使用 [[email protected] ~]# 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.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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 ‘repl‘@‘192.168.55.129‘ identified by ‘123456‘; Query OK, 0 rows affected (5.50 sec) mysql> grant replication slave on *.* to ‘repl‘@‘192.168.55.129‘; Query OK, 0 rows affected (0.04 sec) mysql> flush privileges; Query OK, 0 rows affected (0.09 sec) 配置主数据库编辑配置文件 [[email protected] ~]# vim /etc/my.cnf [[email protected] ~]# cat /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 pid-file = /opt/data/mysql.pid user = mysql skip-name-resolve //添加以下内容 log-bin=mysql-bin //启用binlog日志 server-id=1 //主数据库服务器唯一标识符 主的必须必从大 log-error=/opt/data/mysql.log 重启mysql服务 [[email protected] ~]# service mysqld restart Shutting down MySQL..... SUCCESS! Starting MySQL.Logging to ‘/opt/data/mysql.log‘. ............................... SUCCESS! [[email protected] ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 100 127.0.0.1:25 *:* LISTEN 0 128 :::22 :::* LISTEN 0 100 ::1:25 :::* LISTEN 0 80 :::3306 :::* 查看主库的状态 mysql> show master status;  ------------------ ---------- -------------- ------------------ -------------------  | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |  ------------------ ---------- -------------- ------------------ -------------------  | mysql-bin.000001 | 154 | | | |  ------------------ ---------- -------------- ------------------ -------------------  1 row in set (0.00 sec) 配置从数据库 编辑配置文件 [[email protected] ~]# cat /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 pid-file = /opt/data/mysql.pid user = mysql skip-name-resolve //添加以下内容： server-id=2 //设置从库的唯一标识符 从的必须比主小 relay-log=mysql-relay-bin //启用中继日志relay log error-log=/opt/data/mysql.log 重启从库的mysql服务 [[email protected] ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! [[email protected] ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 100 127.0.0.1:25 *:* LISTEN 0 128 :::22 :::* LISTEN 0 100 ::1:25 :::* LISTEN 0 80 :::3306 :::* 配置并启动主从复制 mysql> change master to -> master_host=‘192.168.55.130‘, -> master_user=‘repl‘, -> master_password=‘123456‘, -> master_log_file=‘mysql-bin.000001‘, -> master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.28 sec) 查看从服务器状态 mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.55.130 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: mysql-relay-bin.000003 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes //此处必须是yes Slave_SQL_Running: Yes //此处必须是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: 527 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_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 5abf1791-b2af-11e8-b6ad-000c2980fbb4 Master_Info_File: /opt/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) ERROR: No query specified 测试验证在主服务器的yan库的tom表插入数据: mysql> use yan; 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> select * from tom;  ---- ----------- ------  | id | name | age |  ---- ----------- ------  | 1 | zhangshan | 20 | | 2 | wangwu | 7 | | 3 | lisi | 23 |  ---- ----------- ------  3 rows in set (0.09 sec) mysql> insert tom(id,name,age) value (4,"yyl",18); Query OK, 1 row affected (0.14 sec) mysql> select * from tom;  ---- ----------- ------  | id | name | age |  ---- ----------- ------  | 1 | zhangshan | 20 | | 2 | wangwu | 7 | | 3 | lisi | 23 | | 4 | yyl | 18 |  ---- ----------- ------  4 rows in set (0.00 sec) 在从数据库查看是否数据同步 mysql> use yan; 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> select * from tom;  ---- ----------- ------  | id | name | age |  ---- ----------- ------  | 1 | zhangshan | 20 | | 2 | wangwu | 7 | | 3 | lisi | 23 | | 4 | yyl | 18 |  ---- ----------- ------  4 rows in set (0.00 sec)