1.ASP站长网MySQL主从复制主要用途: 读写分离
在开发工作中，有时候会遇见某个SQL语句需要锁表，导致暂时不能使用读的服务，这样会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情况，通过读从库也可以保障业务的正常运作。另外，随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问率过高，有了主从复制，增加多个数据存储节点，将负载分布到多个从节点，降低单机I/O磁盘访问率，从而提高I/O性能。
2. MySQL复制原理
(1).在Slave服务器上执行start slave命令开启主从复制开关，开始进行主从复制；
(2).此时，Slave服务器的I/O线程会使用通过在master上已经授权的可以进行复制操作的用户连接master服务器，并请求从执行binlog日志文件的指定位置(日志文件名和位置就是在配置主从复制服务执行change master命令指定的)之后开始发送binlog日志内容；
(3).Master服务器接收到来自Slave服务器的I/O线程的请求后，二进制转储I/O线程会根据Slave服务器的I/O线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的I/O线程，返回信息中除了binlog日志内容外，还有在Master服务器端记录的新的binlog文件名称，以及在新的binlog中的下一个指定更新位置。
(4).当Slave服务器的I/O线程获取到Master服务器上I/O线程发送的日志内容、日志文件及位置后，会将binlog日志内容依次写入到Slave端自身的Relay Log(中继日志)文件的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Slave服务器从新的binlog日志的指定文件及指定位置开始读取新的binlog日志内容；
(5).Slave服务器端的SQL线程会实时检测本地Relay Log中I/O线程新增的日志内容，然后把Relay Log文件中的内容解析成SQL语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样的SQL语句，并在Relay-Log.info中记录当前应用中继日志的文件名和位置点。
或者简单来总结就是:
主库db的更新事件(update、insert、delete)被写到binlog
主库创建一个binlog dump thread，把binlog的内容发送到从库
从库启动并发起连接，连接到主库
从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
从库启动之后，创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db
下面开始MySQL主从复制的实战部分
准备环境:两台CentOS7操作系统
[root@mysql-master ~]# uname -a
Linux mysql-master 3.10.0-957.el7.x86_64 #1 SMP Thu Nov 8 23:39:32 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
[root@MySQL-Slave ~]# uname -a
Linux MySQL-Slave 3.10.0-957.el7.x86_64 #1 SMP Thu Nov 8 23:39:32 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
2.YUM配置
采用阿里YUM源
[root@mysql-master ~]# cd /etc/yum.repos.d/
[root@mysql-master yum.repos.d]# pwd
/etc/yum.repos.d
[root@mysql-master yum.repos.d]# ls
CentOS-Base.repo CentOS-CR.repo CentOS-Debuginfo.repo CentOS-fasttrack.repo CentOS-Media.repo CentOS-Sources.repo CentOS-Vault.repo
将系统自带的repo文件进行备份
Master端:
[root@mysql-master yum.repos.d]# mkdir bak
[root@mysql-master yum.repos.d]# ls
bak CentOS-Base.repo CentOS-CR.repo CentOS-Debuginfo.repo CentOS-fasttrack.repo CentOS-Media.repo CentOS-Sources.repo CentOS-Vault.repo
[root@mysql-master yum.repos.d]# mv ./CentOS-* ./bak/
[root@mysql-master yum.repos.d]# ls
bak
[root@mysql-master yum.repos.d]# ls bak/
CentOS-Base.repo CentOS-CR.repo CentOS-Debuginfo.repo CentOS-fasttrack.repo CentOS-Media.repo CentOS-Sources.repo CentOS-Vault.repo
[root@mysql-master yum.repos.d]#
下载阿里云repo文件
[root@mysql-master yum.repos.d]# wget http://mirrors.aliyun.com/repo/Centos-7.repo
--2019-09-11 17:46:52-- http://mirrors.aliyun.com/repo/Centos-7.repo
Resolving mirrors.aliyun.com (mirrors.aliyun.com)... 219.144.98.104, 219.144.98.98, 219.144.98.99, ...
Connecting to mirrors.aliyun.com (mirrors.aliyun.com)|219.144.98.104|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 2523 (2.5K) [application/octet-stream]
Saving to: ‘Centos-7.repo’
100%[===========================================================================================================================================>] 2,523 --.-K/s in 0.001s
2019-09-11 17:46:52 (3.43 MB/s) - ‘Centos-7.repo’ saved [2523/2523]
[root@mysql-master yum.repos.d]# ls
bak Centos-7.repo
[root@mysql-master yum.repos.d]#
修改/etc/yum.conf文件
[root@mysql-master yum.repos.d]# sed -i 's/keepcache=0/keepcache=1/g' /etc/yum.conf
[root@mysql-master yum.repos.d]# cat /etc/yum.conf
[main]
cachedir=/var/cache/yum/$basearch/$releasever
keepcache=1
debuglevel=2
logfile=/var/log/yum.log
exactarch=1
obsoletes=1
gpgcheck=1
plugins=1
installonly_limit=5
bugtracker_url=http://bugs.centos.org/set_project.php?project_id=23&ref=http://bugs.centos.org/bug_report_page.php?category=yum
distroverpkg=centos-release
# This is the default, if you make this bigger yum won't see if the metadata
# is newer on the remote and so you'll "gain" the bandwidth of not having to
# download the new metadata and "pay" for it by yum not having correct
# information.
# It is esp. important, to have correct metadata, for distributions like
# Fedora which don't keep old packages around. If you don't like this checking
# interupting your command line usage, it's much better to have something
# manually check the metadata once an hour (yum-updatesd will do this).
# metadata_expire=90m
# PUT YOUR REPOS HERE OR IN separate files named file.repo
# in /etc/yum.repos.d
清除YUM缓存并生成新缓存
[root@mysql-master yum.repos.d]# yum clean all
Loaded plugins: fastestmirror
Cleaning repos: base extras updates
Cleaning up list of fastest mirrors
[root@mysql-master yum.repos.d]# yum makecache
Loaded plugins: fastestmirror
Determining fastest mirrors
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
base | 3.6 kB 00:00:00
extras | 3.4 kB 00:00:00
updates | 3.4 kB 00:00:00
(1/12): base/7/x86_64/group_gz | 166 kB 00:00:01
(2/12): base/7/x86_64/primary_db | 6.0 MB 00:00:17
(3/12): extras/7/x86_64/prestodelta | 73 kB 00:00:00
(4/12): extras/7/x86_64/filelists_db | 249 kB 00:00:01
(5/12): extras/7/x86_64/other_db | 131 kB 00:00:00
(6/12): extras/7/x86_64/primary_db | 215 kB 00:00:03
(7/12): updates/7/x86_64/prestodelta | 945 kB 00:00:04
(8/12): base/7/x86_64/filelists_db | 7.1 MB 00:00:34
(9/12): base/7/x86_64/other_db | 2.6 MB 00:00:19
(10/12): updates/7/x86_64/filelists_db | 5.2 MB 00:00:31
(11/12): updates/7/x86_64/other_db | 764 kB 00:00:01
(12/12): updates/7/x86_64/primary_db | 7.4 MB 00:00:30
Metadata Cache Created
查看目前可用的YUM
[root@mysql-master yum.repos.d]# yum repolist all
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
repo id repo name status
base/7/x86_64 CentOS-7 - Base - mirrors.aliyun.com enabled: 10,019
centosplus/7/x86_64 CentOS-7 - Plus - mirrors.aliyun.com disabled
contrib/7/x86_64 CentOS-7 - Contrib - mirrors.aliyun.com disabled
extras/7/x86_64 CentOS-7 - Extras - mirrors.aliyun.com enabled: 435
updates/7/x86_64 CentOS-7 - Updates - mirrors.aliyun.com enabled: 2,500
repolist: 12,954
如果操作系统是最小化安装，那么为了避免后期缺少相关依赖包而造成的麻烦，这里开始安装必要的开发工具
安装Epel源
[root@mysql-master yum.repos.d]# yum install -y epel-release
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
Resolving Dependencies
--> Running transaction check
---> Package epel-release.noarch 0:7-11 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=====================================================================================================================================================================================
Package Arch Version Repository Size
=====================================================================================================================================================================================
Installing:
epel-release noarch 7-11 extras 15 k
Transaction Summary
=====================================================================================================================================================================================
Install 1 Package
Total download size: 15 k
Installed size: 24 k
Downloading packages:
epel-release-7-11.noarch.rpm | 15 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : epel-release-7-11.noarch 1/1
Verifying : epel-release-7-11.noarch 1/1
Installed:
epel-release.noarch 0:7-11
Complete!
清除YUM缓存
[root@mysql-master yum.repos.d]# yum clean all
CentOS-7中安装开发工具的命令已经发生改变
[root@mysql-master yum.repos.d]# yum groupinstall "Development Tools" --setopt=group_package_types=mandatory,default,optional
Loaded plugins: fastestmirror
Determining fastest mirrors
epel/x86_64/metalink | 7.3 kB 00:00:00
* base: mirrors.aliyun.com
* epel: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
由 dawei
【声明】：九江站长网内容转载自互联网，其相关言论仅代表作者个人观点绝非权威，不代表本站立场。如您发现内容存在版权问题，请提交相关链接至邮箱：bqsm@foxmail.com，我们将及时予以处理。