mysql主从复制搭建

---

---

一、MYSQL工作原理

（1）、示意图

（2）文字解释

- 主服务器开启二进制日志，从服务器开启中继日志
- 主服务器日志发生更新后，从服务器通过I/O线程探测到更新并发送请求二进制事件
- 主服务器通过dump线程将更新的二进制日志事件发送给从服务器
- 从服务器将事件写入中继日志，通过SQL线程日志读取为sql语句，重放执行语句，保持数据的统一性

二、搭建

（1）需求

主服务器：IP: 192.168.13.10
从服务器：IP: 192.168.13.20
需要工具：mysql-boost-5.7.20.tar

（2）主服务器搭建

[root@localhost ~]# systemctl disable firewalld
[root@localhost ~]# setenforce 0
###关闭防火墙
[root@localhost opt]# vim /etc/sysconfig/selinux
--------------------------------------------------------------------
##第七行修改  SELINUX=disabled
--------------------------------------------------------------------
[root@localhost opt]# vim /etc/my.cnf
---------------------------------------------------------------------
###### 23行修改： server-id = 1
###### 24行修改： log_bin = master-bin
###### 25行修改： log-slave-updates = true
---------------------------------------------------------------------
hostnamectl set-hostname Mysql1


sed -i "23c server-id = 1" /etc/my.cnf
sed -i "24i log_bin = master-bin" /etc/my.cnf
sed -i "25i log-slave-updates = true" /etc/my.cnf

systemctl restart mysqld

ln -s /usr/local/mysql/bin/mysql /usr/sbin/
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/

mysql -uroot -pabc123 -e"grant replication slave on *.* to 'myslave'@'192.168.13.%' identified by '123';"
mysql -uroot -pabc123 -e"grant all privileges on *.* to 'mha'@'192.168.13.%' identified by 'manager';"
mysql -uroot -pabc123 -e"grant all privileges on *.* to 'mha'@'Mysql1' identified by 'manager';"
mysql -uroot -pabc123 -e"grant all privileges on *.* to 'mha'@'Mysql2' identified by 'manager';"
mysql -uroot -pabc123 -e"grant all privileges on *.* to 'mha'@'Mysql3' identified by 'manager';"
mysql -uroot -pabc123 -e"flush privileges;"

mysql -u root -pabc123 -e "show master status;"|grep master-bin|awk '{print $1}' >/mnt/master_log_file
mysql -u root -pabc123 -e "show master status;"|grep master-bin|awk '{print $2}' >/mnt/master_position


spawn scp /mnt/master_position /mnt/master_log_file 192.168.13.20:/mnt/

mysql -u root -pabc123 -e "show master status"

从服务器搭建

systemctl disable firewalld
setenforce 0
sed -i "7c SELINUX=disabled" /etc/sysconfig/selinux

hostnamectl set-hostname Mysql2

sed -i "23c server-id = 2" /etc/my.cnf
sed -i "24i log_bin = master-bin" /etc/my.cnf
sed -i "25i relay-log = relay-log-bin" /etc/my.cnf
sed -i "26i relay-log-index = slave-relay-bin.index" /etc/my.cnf

systemctl restart mysqld

ln -s /usr/local/mysql/bin/mysql /usr/sbin/
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/

mysql -uroot -pabc123 -e"grant replication slave on *.* to 'myslave'@'192.168.13.%' identified by '123';"
mysql -uroot -pabc123 -e"grant all privileges on *.* to 'mha'@'192.168.13.%' identified by 'manager';"
mysql -uroot -pabc123 -e"grant all privileges on *.* to 'mha'@'Mysql1' identified by 'manager';"
mysql -uroot -pabc123 -e"grant all privileges on *.* to 'mha'@'Mysql2' identified by 'manager';"
mysql -uroot -pabc123 -e"grant all privileges on *.* to 'mha'@'Mysql3' identified by 'manager';"
mysql -uroot -pabc123 -e"flush privileges;"

master_log_file=`cat /mnt/master_log_file`
master_position=`cat /mnt/master_position`

mysql -uroot -pabc123 -e"change master to master_host='192.168.13.10';"
mysql -uroot -pabc123 -e"change master to master_user='myslave';"
mysql -uroot -pabc123 -e"change master to master_password='123';"
mysql -uroot -pabc123 -e"change master to master_log_file='"$master_log_file"';"
mysql -uroot -pabc123 -e"change master to master_log_pos='"$master_position"';"

mysql -uroot -pabc123 -e"start slave;"
mysql -uroot -pabc123 -e"show slave status\G" |grep Slave_IO_Running
mysql -uroot -pabc123 -e"show slave status\G" |grep Slave_SQL_Running

mysql -uroot -pabc123 -e"set global read_only=1;"

#mysql -uroot -pabc123 -e"show databases;"		#开启后可检查主从复制是否开启