一、MySQL主从复制原理
首先要部署主从复制,只有主从复制完成了,才能在此基础上进行数据的读写分离
1-1 MySQL 支持的复制类型
■ 基于语句的复制(默认)
●在主服务器上执行的语句,从服务器执行同样的语句
■ 基于行的复制
●把改变的内容复制到从服务器
■ 混合类型的复制
●一旦发现基于语句无法精确复制时,就会采用基于行的复制
1-2 复制的工作过程
■ 在每个事务更新数据完成之前,Master在二进制日志记录这些改变。写入二进制日志完成后,Master通知存储引擎提交事务
■ Slave将Master的Binary log复制到其中继日志。首先,Slave开始一个工作线程——I/O线程,I/O线程在Master上打开一个普通的连接,然后开始Binlog dump process。Binlog dump process从Master的二进制日志中读取事件,如果已经跟上Master,他会睡眠并等待Master产生新的事件。I/O线程将这些事件写入中继日志。
■ SQL slave thread(SQL从线程)处理该过程的最后一步。SQL线程从中继日志读取事件,并重放其中的事件而更新Slave的数据,使其与Master中的数据一致。只要该线程与I/O线程保持一致,中继日志通常会位于OS的缓存中,所以中继日志的开销很小。
复制过程有一个很重要的限制,即复制在Slave上是串行化的,也就是说Master上的并行更新操作不能再Slave上并行操作。
二、案例概述
2-1 存在问题
■在企业网站中,后端MySQL数据库只有一台时,会有以下问题
●单点故障,服务不可用
● 无法处理大量的并发数据请求
● 数据丢失——大灾难
2-2 简易改造办法
■改造办法
● 增加MySQL数据库服务器,对数据进行备份,形成主备
● 确保主备MySQL数据库服务器数据是一样的
● 主服务器宕机了,备份服务器继续工作,数据有保障
2-3 更高级解决办法
■更高级解决办法
● 通过主从复制的方式来同步数据,再通过读写分离来提升数据库的并发负载能力
三、实验过程
3-1 实验环境
- 服务器配置
IP地址 主机 操作系统
20.0.0.25 master centos-7.6-x86_64
20.0.0.26 Slave1 centos-7.6-x86_64
20.0.0.27 Slave2 centos-7.6-x86_64
- 三台MySQL服务器关闭防火墙,关闭核心防护
3-2 建立时间同步环境
- 在主服务器上安装配置NTP时间同步服务器(20.0.0.25)
[root@localhost~]# yum -y install ntp ###如果不是最小化安装,就不用yum安装ntp
[root@localhost~]# vi /etc/ntp.conf
###删除下面四行###
server 0.centos.pool.ntp.org iburst
server 1.centos.pool.ntp.org iburst
server 2.centos.pool.ntp.org iburst
server 3.centos.pool.ntp.org iburst
###添加这两行###
server 127.127.1.0
fudge 127.127.1.0 stratum 8
[root@localhost~]# systemctl restart ntpd
[root@localhost~]# systemctl enable ntpd
[root@localhost ~]# service ntpd restart
[root@localhost ~]# systemctl restart ntpd
[root@localhost ~]# systemctl enable ntpd
- 在从服务器1上配置NTP同步(20.0.0.26)
[root@localhost ~]# yum -y install ntpdate
[root@localhost ~]# ntpdate 20.0.0.25
[root@localhost ~]# crontab -e
*/2 * * * * /usr/sbin/ntpdate 20.0.0.25 >> /var/log/ntpdate.log
[root@localhost ~]# touch /var/log/ntpdate.log
[root@localhost ~]# systemctl restart crond
[root@localhost ~]# systemctl enable crond
[root@localhost ~]# tail -f /var/log/ntpdate.log ###动态查看更新日志文件
- 在从服务器2上配置NTP同步(20.0.0.27)
[root@localhost ~]# yum -y install ntpdate
[root@localhost ~]# ntpdate 20.0.0.25
[root@localhost ~]# crontab -e
*/2 * * * * /usr/sbin/ntpdate 20.0.0.25 >> /var/log/ntpdate.log
[root@localhost ~]# touch /var/log/ntpdate.log
[root@localhost ~]# systemctl restart crond
[root@localhost ~]# systemctl enable crond
[root@localhost ~]# tail -f /var/log/ntpdate.log ###动态查看更新日志文件
时间同步还有一个方法,同步阿里云服务器,通过命令ntdpate ntp1.aliyun.com就可以执行
date -R可以查看时间
3-3 主服务器、从服务器1、从服务器2均安装MySQL服务
[root@localhost~]# yum -y install \
ncurses \
ncurses-devel \
bison \
cmake \
gcc \
gcc-c++
[root@localhost~]# useradd -s /sbin/nologin mysql
[root@localhost~]# cd /opt
[root@localhost opt]# tar xf mysql-boost-5.7.20.tar.gz
[root@localhost opt]# cd /opt/mysql-5.7.20/
[root@localhost mysql-5.7.20]#
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1
[root@localhost mysql-5.7.20]# make -j3 make -j3 install
[root@localhost mysql-5.7.20]# chown -R mysql:mysql /usr/local/mysql/
'//数据库目录进行权限调整'
[root@localhost mysql-5.7.20]# vi /etc/my.cnf
'//建立调整配置文件'
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
[root@localhost mysql-5.7.20]# chown mysql:mysql /etc/my.cnf
[root@localhost mysql-5.7.20]# echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
[root@localhost mysql-5.7.20]# echo 'export PATH' >> /etc/profile
'//设置环境变量'
[root@localhost mysql-5.7.20]# source /etc/profile
'//生效'
[root@localhost mysql-5.7.20]# cd /usr/local/mysql/
[root@localhost mysql]# bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data
[root@localhost mysql]# cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
[root@localhost mysql]# systemctl enable mysqld
[root@localhost mysql]# systemctl start mysqld
[root@localhost mysql]# systemctl status mysqld
[root@localhost mysql]# netstat -anpt | grep 3306
[root@localhost mysql]# mysqladmin -u root -p password
'//设置密码'
[root@localhost ~]# mysql -u root -p ## 验证是否可以登录数据库
Enter password:
mysql>
3-4 主服务器配置
[root@localhost ~]# vi /etc/my.cnf ###在原来server-id=1的地方修改成11后面新增log_bin=master-bin log-slave-updates=true###
server-id = 1 ###集群里的id不能一样
log_bin = master-bin ###开启二进制
log-slave-updates = true ###同步日志
[root@localhost ~]# systemctl restart mysqld ###重启数据库
登录Master数据库给从服务器授权
[root@localhost ~]# mysql -uroot -p
mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by '12345'; ###允许对方所有数据库所有表通过20.0.0网段使用myslave数据库
mysql> flush privileges;
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 154 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3-5 从服务器 1 配置
[root@localhost ~]# vi /etc/my.cnf ###在原来server-id=1的地方修改成22 后面新增relay-log=relay-log-bin relay-log-index=slave-relay-bin.index###
server-id = 2
relay-log = relay-log-bin ###从服务器上同步日志文件记录到本地
relay-log-index = slave-relay-bin.index ###定义relay-log位置和名称
[root@localhost ~]# systemctl restart mysqld ###重启数据库
登录slave数据库配置同步 ###注意下这边的master_log_file='master-bin.000001',master_log_pos=601;要和Master数据库信息不一致,不一致的话要更改先stop slave;然后更改同步信息
[root@localhost ~]# mysql -uroot -p
mysql> change master to master_host='20.0.0.25',master_user='myslave',master_password='12345',master_log_file='master-bin.000001',master_log_pos=599;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 20.0.0.25
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 599
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes ## 这里显示 Yes 就可以了
Slave_SQL_Running: Yes ## 这里显示 Yes 就可以了
....................略........................
1 row in set (0.00 sec)
Slave_IO_Running: connecting出现这样提示可能是主服务器防火墙没有关闭
3-6 从服务器 2 配置
[root@localhost ~]# vi /etc/my.cnf
server-id = 3
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
[root@localhost ~]# systemctl restart mysqld
mysql> change master to master_host='20.0.0.25',master_user='myslave',master_password='12345',master_log_file='master-bin.000001',master_log_pos=599;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 20.0.0.25
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 599
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes ## 这里显示 Yes 就可以了
Slave_SQL_Running: Yes ## 这里显示 Yes 就可以了
....................略........................
1 row in set (0.00 sec)
3-7 验证:MySQL主从服务器
3-7-1 主服务器
mysql> create database djb; ## 创建 djb 库
Query OK, 1 row affected (0.00 sec)
mysql> show databases; ## 查看主服务器有 djb 库
+--------------------+
| Database |
+--------------------+
| information_schema |
| djb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
3-7-2 从服务器 1
mysql> show databases; ## 查看从服务器 1 有 djb 库
+--------------------+
| Database |
+--------------------+
| information_schema |
| djb | ## 同步成功!
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
3-7-3 从服务器 2
mysql> show databases; ## 查看从服务器 2 有 djb 库
+--------------------+
| Database |
+--------------------+
| information_schema |
| djb | ## 同步成功!
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)