mysql-slave(主从)

MySQL主从

注意： 由于MySQL不同版本之间的(二进制日志)bin-log格式可能会不一样，建议Master(主)的MySQL版本和Slave(从)的版本尽可能的相同。
原理： MySQL主从是基于binlog，在主机上需开启binlog才能进行主从；
binlog有三种模式：
Statement: Binlog中存储SQL的语句，存储日志量是最小的。但是，对于user()等函数存在bug
Row： 是主从复制更加安全，但是存储日志量大，但是不能直接进行读取。（默认）
Mixed：介于两者之间，对于不确定的操作使用Row记录。根据SQL语句有系统决定是基于段还是行复制。

大致过程：

a: master主机中的MySQL将更改操作的记录到bin-log(二进制日志文件)里；
b: slave主机将master主机中的的bin-log事件(也就是操作的sql语句)同步到本机上并记录在本机的relay-log里；
c: slave机再根据relay-log里面的sql语句依次按顺序执行

作用：
a: 实时灾备，用于故障切换
b: 读写分离，提供查询服务
c: 实时备份，避免影响业务

实现步骤：

时间同步

###system:    ubuntu-server18.04####
### 主从都需要做#################
apt-get install nptdate -y
ntpdate  0.cn.pool.ntp.org 
时区设置(CST):
mv /etc/localtime /etc/localtime-bak
cp /usr/share/zoneinfo/Asia/Shanghai  /etc/localtime

Master:

sudo apt-get install mysql-server -y
#----------------------------------------------------
##出现以下错##
#E: Could not get lock /var/lib/dpkg/lock - open (11: Resource temporarily unavailable)#
#E: Unable to lock the administration directory (/var/lib/dpkg/), is another process using it?#
#可以通过kill -9 杀死所有的apt-get 和apt进程，通过ps -A | grep apt。
#或者，删除锁定文件，何为锁定文件：锁定的文件会阻止 Linux 系统中某些文件或者数据的访问，这个概念也存在于 Windows 或者其他的操作系统中。一旦你运行了 apt-get 或者 apt 命令，锁定文件将会创建于 /var/lib/apt/lists/、/var/lib/dpkg/、/var/cache/apt/archives/ 中。这有助于运行中的 apt-get 或者 apt 进程能够避免被其它需要使用相同文件的用户或者系统进程所打断。当该进程执行完毕后，锁定文件将会删除。
#如果你没有看到 apt-get 或者 apt 进程，但是在上面两个不同的文件夹中看到了锁定文件，这是因为进程由于某个原因被杀掉了，因此你需要删除锁定文件来避免该错误。
#运行sudo rm  -rf  /var/lib/dpkg/lock  命令来移除 /var/lib/dpkg/ 文件夹下的锁定文件
#强制重新配置软件包:	sudo dpkg --configure -a
#同时删除 /var/lib/apt/lists/ 以及缓存文件夹下的锁定文件；sudo rm /var/lib/apt/lists/lock， sudo rm /var/cache/apt/archives/lock
#更新软件包源列表： sudo apt-get update

master配置文件设置

#-----------------------------------------------------
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
			bind-address = 127.0.0.1  更改为： bind-address = IP_Address
			server_id         = 9                # 服务器唯一标识；文件内有，可复制。
			log_bin             = /var/log/mysql/mysql-bin.log     # 启动MySQL二进制日志
			log_bin_index = master-bin.index      #可忽略，不填写。
			binlog_do_db = replicadb						#指定记录二进制日志的数据库,可忽略。
			binlog_ignore_db = mysql						#指定不记录二进制日志的数据库，可忽略。
			# binlog-format = ROW								#默认Row，日志量大，可选MIXED
			#expire_logs_days = 15                            #自动删除15天前的日志。默认值为0，表示从不删除。防止日志过大
#sudo systemctl restart mysql

Master-MySQL 数据库设置

#--------------------------------------------------------
sudo mysql
#查看log_bin是否 开启
show variables like 'log_%'
输出： log_bin                        ON
在MySQL提示符内，运行以下SQL查询，这些查询将创建副本用户并向用户授予REPLICATION SLAVE权限：
CREATE USER 'replica'@'172.19.79.157' IDENTIFIED BY 'replica_password';
CREATE USER 'replica'@'172.19.79.156' IDENTIFIED BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.19.79.157;
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.19.79.156;
#use mysq;
#select user,host from user;
#+---------------------------+--------------------+
#| user                   				|	 host         			 |
#+---------------------------+--------------------+
#| replica              				| 172.19.79.156  |
#| replica              				| 172.19.79.157  |
#| debian-sys-maint 		| localhost     		 |
#| mysql.session    			| localhost     		 |
#| mysql.sys        				| localhost    		 |
#| root             						| localhost    		 |
#+---------------------------+--------------------+
#6 rows in set (0.00 sec)
#远程登录授权：
update user set host='%' where user='root'
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
#在MySQL提示符内时，执行以下命令将打印二进制文件名和位置。
SHOW MASTER STATUS\G
输出：
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 1734
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
或者使用：SHOW MASTER STATUS
#需要记下文件名'mysql-bin.000001'和位置'1734'。 配置从属服务器时，您将需要这些值。 每个人服务器上的这些值可能会有所不同。

slave-MySQL 配置文件设置

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
			bind-address = 127.0.0.1  更改为： bind-address = IP_Address
			server_id         = 2                # 服务器唯一标识；文件内有，可复制。
			replicate-do-db=replicadb        #要同步的mstest数据库,要同步多个数据库，就多加几个replicate-db-db=数据库名，可选
			relay-log          = slave-relay-bin    #中继日志
			relay-log-index = slave-relay-bin.index
			#read_only = 1			#设置为只读，0为读写
			#log_slave_updates = 1/on  #设置行级复制，作为从库生效
			#relay_log_recovery = 1   #中继日志relay_log可以自我修复
#sudo systemctl restart mysql

slave-MySQL 数据库设置

sudo mysql
#首先，停止slave线程:	
stop slave;
#查看relay_log是否开启：
show variables like '%_log'
#运行以下查询，该查询将设置从服务器来复制主服务器:
	CHANGE MASTER TO
	MASTER_HOST='172.19.79.155',
	MASTER_USER='replica',
	MASTER_PASSWORD='replica_password',
	MASTER_LOG_FILE='mysql-bin.000001',
	MASTER_LOG_POS=1734;
#启动slave线程:
start slave;
#----------------------------------
# master_host对应主服务器的IP地址，
# master_port对应主服务器的端口，
# master_log_file对应show master status显示的File列：master-bin.000001，
# master_log_pos对应Position列：1734
#----------------------------------

查看slave状态信息：

show slave status\G;

说明：slave_IO_Running和Slave_SQL_Running都为yes才表示同步成功

延伸：binlog日志手动删除和自动清理

#查看binlog日志
show binary logs;
#查看正在使用的日志文件，当前正在使用的日志文件是mysql-bin.0000079，那么删除日志文件的时候应该排除掉该文件。：
show master status;
#删除某个日志文件之前的所有日志文件
purge binary logs to 'mysql-bin.000079';
show binary logs;
#输出：mysql-bin.000079
#删除某个日期之前的所有日志文件
PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00';  
#清理3天前binlog日志BEFORE，变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式。这里的清理是指从此刻-3*24*3600s前的文件，是此文件最后更新的时间。
 PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);
#重启自动删除：
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
expire_logs_days = 10
#不重启自动删除：
show variables like '%log%';
set global expire_logs_days = 10;