MySQL——主从复制，看了之后可以快乐的刷脚本了

MySQL主从复制

1.MySQL主从复制原理首先要部署主从复制,只有主从复制完成了,才能在此基础上进行数据的读写分离

1.1MySQL支持的复制类型

基于语句的复制。在主服务器上执行的SQL语句,在从服务器上执行同样的语句。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的缓存中,所以中继日志的开销很小,

2, MySQL读写分离原理

简单来说,主服务器写,从服务器读,基本的原理是让主数据库处理事务性查询,二从数据库处理Select查询数据库复制被用来把事务性查询导致的变更同步到群集中的从数据库。

目前比较常见的MySQL读写分离分为二种:

2.1、基于程序代码内部实现

在代码中根据select, insert进行路由分类,这类方法也是目前生产环境应用广泛的,优点是性能较好,因为在程序代码中实现,不需要增加额外的设备作为硬件开支;缺点是需要开发人员来实现,运维人员无从下手。

2.2、基于中间代理层实现

代理一般位于客户端和服务器之间,代理服务器接受到客户端的请求后通过判断后转发到后端数据库,有二个代表性程序

MySQL-Proxy ------MySQL的开源项目,通过自带的lua脚本进行SQL判断, MySQL官方不建议用这个在生产环境中

Amoeba (变形虫) ------由陈思儒开发,曾就职于阿里巴巴,该程序由Java语言开发,阿里将其用于生产环境但是它不支持事物和存储过程。

master, slave1, slave2

关闭防火墙、关闭核心防护

建立时间同步环境

1、在主机Master搭建时间同步服务器NTP (192.168.100.21)

[root@localhost ~]# yum -y install ntp

[root@localhost ~]# vi /etc/ntp.conf （在编辑器最后面添加以下内容）

server 127.127.1.0

fudge 127.127.1.0 stratum 8

[root@localhost ~]# service ntpd restart

[root@localhost ~]# systemctl restart ntpd

[root@localhost ~]# systemctl enable ntpd

2、在从服务器上配置NTP同步

登录到192.168.100.22

[root@localhost ~]# yum-y install ntpdate

[root@localhost ~]# ntpdate 192.168.100.21

[root@localhost ~]# crontab-e

*/2 * * * * /usr/sbin/ntpdate 192.168.100.21 >>/var/log/ntpdate.log

[root@localhost ~]# systemctl restart crond

[root@localhost ~]# systemctl enable crond

[root@localhost ~]# tail-f /var/log/ntpdate.log (动态查看更新日志文件)

登录到192.168.100.23

[root@localhost ~]# yum-y install ntpdate

[root@localhost ~]# ntpdate 192.168.100.21

[root@localhost ~]# crontab-e

*/2 * * * * /usr/sbin/ntpdate 192.168.100.21 >>/var/log/ntpdate.log

[root@localhost ~]# systemctl restart crond

[root@localhost ~]# systemctl enable crond

[root@localhost ~]# tail-f /var/log/ntpdate.log (动态查看更新日志文件)

同步操作

使用CRT登录3台主机,登录以后检查,登录是否正常,确认下主机的IP地址是否正确,各个主机通信是否正常。登录192.168.100.21此终端,在终端页面最下面,右击打开–send conmands to all sessions ###此功能是敲一条命令,其他终端全部同步执行

编译安装MySQL数据库
[安装MySQL服务]

安装mysql

yum -y install \

ncurses \

ncurses-devel \

bison \

cmake

useradd -s /sbin/nologin mysql

上传mysql-boost-5.7.20.tar.gz到opt目录下

cd /opt

tar zvxf mysql-boost-5.7.20.tar.gz

cd /opt/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

配置选项含义

1、DCMAKE_INSTALL_PREFIX：指定将 mysql 数据库程序安装到某目录下，

如目录/usr/local/ mysql。

2、DSYSCONFDIR：指定初始化参数文件目录

3、DDEFAULT_CHARSET：指定默认使用的字符集编码，如 utf8。

4、DDEFAULT_COLLATION：指定默认使用的字符集校对规则，utf8_general_ci

是适用于 UTF-8 字符集的通用规则。

5、DWITH_EXTRA_CHARSETS：指定额外支持的其他字符集编码。

用make-j2安装开始时间: 10: 38安装11:58接下来是make install时间开始13:37

—注意:如果在CMAKE的过程中有报错–

当报错解决后,需要把源码目录中的CMakeCache.txt文件删除,然后再重新CMAKE,否则错误依旧

–注意: make: * No targets specified and no makefile found. Stop.解决方法

1. wget http://tp.gnu.org/pub/gnu/ncurses/ncurses-5.6.tar.gz

2. tar zxvf ncurses-5.6.tar.gz

3. ./configure-prefix-/usr/local -with-shared-without-debug

4. make

5. make install

4、数据库目录进行权限调整

[root@localhost mysql-5.7.20]#chown-R mysql:mysql /usr/local/mysql/

5、调整配置文件

[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

chown mysql:mysql /etc/my.cnf

6、设置环境变量

echo ‘PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH’ >> /etc/profile

echo ‘export PATH’ >> /etc/profile

source /etc/profile

7、初始化数据库

cd /usr/local/mysql/

bin/mysqld \

–initialize-insecure \

–user=mysql \

–basedir=/usr/local/mysql \

–datadir=/usr/local/mysql/data

cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/

8、数据库开启自启、关闭、状态

systemctl enable mysqld

systemctl start mysqld

systemctl status mysqld

netstat -anpt | grep 3306

9、设置MySQL密码

mysqladmin -u root -p password “123456” //刚开始没密码是空的直接回车，然后输入密码123456,在此确认123456,这是在root账户下运行的

10、登入数据库

mysql -u root -p ##这个命令敲下，提示要输入密码,这个就是刚才设置的密码123456

登录Master主服务器配置192.168.100.21

[root@localhost mysql]# vi /etc/my.cnf （在原来server-id = 1的地方修改成11后面新增log bin = master-bin log-slave-updates = true）

server-id = 11

log_bin = master-bin

log-slave-updates = true

[root@localhost mysql# systemctl restart mysqld ###重启数据库

登录Master数据库给从服务器授权

[root@localhost mysq#mysql -uroot -p

mysąl> grant replication slave on *.* to ‘myslave’@'192.168.100.%" identified by '123456;

mysąl> 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)

登录salve1从服务器配置192.168.100.22

[root@localhost mysq]#vi /etc/my.cnf （在原来server-id - 1的地方修改成22后面新增relay-log = relay-log-bin relay-log-index = slave-relay-bin.index）

server-id = 22

relay-log = relay-log-bir

relay-log-index = slave-relay-bin.index

[root@localhost mysq]# systemctl restart mysqld ###重启数据库

登录Slave数据库配置同步（注意下这边的master log file='master-bin.000001,master log pos=604;要和Master数据库信息一致,不一致的话要更改先stop slave;然后更改同步信息）

[root@localhost mysq# mysql -uroot-p

mysgl> change master to master host=‘192.168.100.21’,master user=‘myslave’,master password=‘abc123’,master log file='master-bin.0000001, master log_pos=154;

mysql> show slave status

**1. row

Slave 10 State: Waiting for master to send event

Master Host: 192.168.32.1

Master User: myslave

Master Port: 3306

Connect Retry: 6

Master Log File: master-bin.000001

Read Master Log Pos: 604

Relay Log File: relay-log-bin.000002

Relay Log Pos: 321

Relay Master Log File: master-bin.000001

Slave 10 Running: Yes (注意：开启,确认开启后证明程序运行没错）

Slave SQL Running: Yes (注意：开启,确认开启后证明程序运行没错）

登录salve2从服务器配置192.168.100.23

[root@localhost mysql]j# vi /etc/my.cnf （在原来server-id = 1的地方修改成22后面新增relay-log = relay-log-bin relay-log-index = slave-relay-bin.index）

server-id 22

relay-log = relay-log-bir

relay-log-index = slave-relay-bin.index

[root@localhost mysq]# systemctl restart mysqld ###重启数据库登录

Slave数据库配置同步（注意下这边的master log file='master-bin.00000 ,master log pos=604;要和Master数据库信息一致,不一致的话要更改先stop slave;然后更改同步信息）

[root@localhost mysql# mysql-uroot -p

mysql> change master to master host=‘192.168.100.21’,master user=‘myslave’,master password=‘abc123’,master log file=‘master-bin.0000001’,master log pos=154;

mysql> show slave statusG

********** **1. row

Slave 10 State: Waiting for master to send event

Master Host: 192.168.32.11

Master User: myslave

Master Port: 3306

onnect Retry: 60

Master Log File: master-bin.000001

Read Master Log Pos: 604

Relay Log File: relay-log-bin.000002

Relay Log Pos: 321

Relay Master Log File: master-bin.000001

Slave 10 Running: Yes (注意：开启,确认开启后证明程序运行没错）

Slave SQL Running: Yes (注意：开启,确认开启后证明程序运行没错）

之后进入数据库进行验证，在master数据库上创建一个库，看slave数据库里是否有相对应所创建的库即可

本文地址：https://blog.csdn.net/Geroge_Ming/article/details/108767734