一、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;" #开启后可检查主从复制是否开启