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;