mysql 主从复制各种组合数据库配置及相关操作

最新推荐文章于 2023-02-21 14:53:23 发布

蒙大帅

最新推荐文章于 2023-02-21 14:53:23 发布

阅读量731

点赞数

文章标签：数据库 mysql 服务器

原文链接：https://blog.csdn.net/sinat_34454743/article/details/125312746?spm=1001.2014.3001.5501

版权

mysql主从复制数据库配置及相关操作

什么是mysql的主从复制

MySQL主从复制也可以称为MySQL主从同步，它是构建数据库高可用集群架构的基础。它通过将一台主机的数据复制到其他一台或多台主机上，并重新应用relay log中的SQL语句来实现复制功能。MySQL支持单向、双向、链式级联、异步复制，5.5版本之后加入的半同步复制，5.6版本之后的GTID复制，MySQL5.7的多源复制、并行复制、loss-less复制。

2.常见的几种架构

1）单向主从模式：Master ——> Slave

　　2）双向主从模式：Master <====> Master

　　3）级联主从模式：Master ——> Slave1 ——> Slave2

　　4）一主多从模式

多主一从模式

3.主从复制功能

1）实时灾备

　　2）读写分离

　　3）高可用

　　4）从库数据统计

　　5）从库数据备份

　　6）平滑升级

4.主从复制原理

4.1异步复制

Master服务器

Slave服务

主从复制是异步复制过程

master开启bin-log功能，日志文件用于记录数据库的读写增删

需要开启3个线程，master IO线程，slave开启 IO线程 SQL线程，

Slave 通过IO线程连接master，并且请求某个bin-log，position之后的内容。

MASTER服务器收到slave IO线程发来的日志请求信息，io线程去将bin-log内容，position返回给slave IO线程。

slave服务器收到bin-log日志内容，将bin-log日志内容写入relay-log中继日志，创建一个master.info的文件，该文件记录了master ip 用户名密码 master bin-log名称，bin-log position。

slave端开启SQL线程，实时监控relay-log日志内容是否有更新，解析文件中的SQL语句，在slave数据库中去执行。

4.2半同步复制

MySQL默认的复制方式是异步复制，但是当主库宕机，在高可用架构坐准备切换，就会造成新的主库丢失数据的现象。

MySQL5.5版本之后引入了半同步复制，但是主从服务器必须同时安装半同步复制插件。在该功能下，确保从库接收完成主库传递过来的binlog内容已经写入到自己的relay log后才会通知主库上面的等待线程。如果等待超时(超时参数：rpl_semi_sync_master_timeout)，则关闭半同步复制，并自动转换为异步复制模式，直到至少有一台从库通知主库已经接收到binlog信息为止。

半同步复制时，为了保证主库上的每一个Binlog事务都能够被可靠的复制到从库上，主库在每次事务成功提交时，并不及时反馈给前端应用用户，而是等待其中的一个从库也接收到Binlog事务并成功写入中继日志后，出库才返回commit操作成功给客户端。半同步复制保证了事务成功提交后，至少有两份日志记录，一份在主库的Binlog日志上，另一份在至少一个从库的中继日志Relay log上，从而更近一步保证了数据的完整性。

在这个半同步复制模式下：第1、2、3中任何一个步骤中主库宕机，则事务并没有提交成功。从库也没有得到日志，此时的主从复制数据是一致的。

半同步复制提升了主从之间数据的一致性，让复制更加安全可靠，在5.7 版本中又增加了rpl_semi_sync_master_wait_point参数，用来控制半同步模式下主库返回给session事务成功之前的事务提交方式。

4.3GTID复制（略，了解）

GTID又叫全局事务ID，是一个以提交事务的编号，并且是一个全局唯一的编号。GTID是由server_uuid和事务id组成的，即GTID=server_uuid:transaction_id。

　　server_uuid是数据库启动自动生成的，保存在auto.cnf文件下，transaction_id是事务提交时由系统顺序分配的一个不会重复的序列行。

　　GTID存在的价值：

　　1）GTID使用master_auto_position=1代替了基于binlog和position号的主从复制方式，更便于主从复制的搭建。

　　2）GTID可以知道事务在最开始是哪个实例上提交的。

　　3）GTID方便实现主从之间的failover，无须找position和binlog。

　　GTID限制条件：

　　1）不能使用create table table_name select * from table_name。

　　2）不支持CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE语句操作。

　　3）不支持sql_slave_skip_counter。

5.主从搭建

mysql版本 5.5.56

5.1原理图

5.2准备实验环境

主(Master)机IP： 124.204.65.84:12485 （内网：172.16.120.10）

从(Slave) 机IP: 124.204.65.84:12493 （内网：172.16.120.11）

5.3配置主机master的my.conf文件

1.停止mysql /etc/init.d/mysqld stop

2.修改 vi /etc/my.conf

[mysqld]

server-id = 1 # 服务的唯一标识符

log-bin = mysql-bin # 开启二进制日志,默认路径在data/下

binlog_format = mixed # 二进制文件的格式

# 这里有３种格式

# binlog_format=statement 将主库输入的SQL语句,直接写入二进制文件中

# binlog_format=row 将主库修改的行写入二进制文件中

# binlog_format=mixed 以上二种的混合模式

# 在不同二进制格式下,对存储过程,函数,触发器,事件的数据复制可以参考

#下面参数,在本次实验中,不配置========================================

replicate-do-db = test # 需要复制的数据库 (在这个试验中,我不用这个参数)

replicate-ignore-db = mysql # 不需要复制的数据库 (在这个试验中,我不用这个参数)

replicate-ignore-db = information_schema # 不需要复制的数据库 (在这个试验中,我不用这个参数)

# 不使用replicate-do-db，replicate-ignore-db参数就代表全库都复制

# ===========================================================

如图：

3.启动master /etc/init.d/mysqld start

4.在master机器上创建远程登陆用户

用户名:slavec

密码:123456

允许来自：124.204.65.84 机器登陆

1.授权方式：此种授权是可远程登录Master，仅用于主从复制没有其它任何select insert update等权限

GRANT REPLICATION SLAVE on *.* to 'slavec'@'172.16.120.11' identified by ‘123456’;

FLUSH PRIVILEGES;

GRANT REPLICATION SLAVE ON *.* TO 'slavec'@'172.16.120.11' IDENTIFIED BY '123456' WITH GRANT OPTION;

查看授权结果

SHOW GRANTS FOR 'slavec'@'172.16.120.11';

测试从Slave端登录远程主(Master)机

mysql -uslavec -p123456 -h172.16.120.10

3. 从主(Master)机查看所有连接登录本机的客户端情况 SHOW PROCESSLIST ;

5.4修改slave从机my.conf文件

1.停止mysql服务 /etc/init.d/mysqld stop

2.修改 /etc/my.conf

[mysqld]

    log-bin=mysql-bin    # 确认也开启binlog日志

    binlog_format=mixed  # 确认binlog日志格式为mixd混合类型

    server-id = 2        # 在整个主从架构内指定一个唯一的server-id，范围：1^32

如图：

启动mysql : /etc/init.d/mysqld start

5.4.2slave服务选配

从(Slave)机配置-【可选】

    下面是生产阶段可能要用的功能，这些功能只能在从机的配置文件的[mysqld]区块里手工配置，无法通过 change master to ...命令实现。

    1.白名单 - 只想复制某(些)个库：

      replicate_do_db=zyyshop                 # 追加复制zyyshop库

      replicate_do_db=test                    # 追加只复制test库

      总结: 相当于只复制 zyyshop、test 两个库

            如果采用完全在配置文件里配置主(Master)机信息的方法，此项须添加，否则可能导致mysqld启动不了

    2.白名单 - 只想复制某(些)个库(的所有表)

      replicate_wild_do_table=zyyshop.%       # 追加复制zyyshop库的所有表

      replicate_wild_do_table=test.%          # 追加复制test库的所有表

      总结：也是相当于只复制 zyyshop、test 两个库。

    3.白名单 - 只想复制某(些)个库(的部分表)

      replicate_wild_do_table=zyyshop.stu     # 追加zyyshop.stu表

      replicate_wild_do_table=test.project    # 追加test.project表

      总结：相当于只复制zyyshop.stu、test.project两个表。

    4.黑名单 - 想忽略对mysql和test数据库的复制

      replicate_ignore_db=mysql               # 追加mysql库为忽略

      replicate_ignore_db=test                # 追加test库为忽略

      总结：相当于将mysql和test两数据库追加到忽略列表，将不会被复制。

    5.黑名单 - 想忽略对某些库的某些表的复制

      replicate_wild_ignore_table=mysql.users # 追加mysql.users表为忽略

      总结：本步骤是相当于追加了mysql.users具体的表被忽略，其它表照常会被复制。

    6.如果想实现“主-->从-->从”的链式架构，因为在默认情况下，from主(Master)机copy过来的数据不会写入从(Slave)机的binlog日志里，

      而是写入中继日志(localhost-relay-bin.00000x)里，所以本机如果还需要充当其它机器的主机，就将从主机复制来的数据写入从库时同

      时也显式地写入本地binlog日志，添加如下配置：

      log-slave-updates =1

5.5 slave连接master

5.5.1先查看当前主机二进制的位置

1．先查看当前主机二进制的位置show master status;

2.在slave从机上执行命令连接主机

CHANGE MASTER TO MASTER_HOST="172.16.120.10", MASTER_PORT=8764, MASTER_USER="slavec", MASTER_PASSWORD="123456", MASTER_LOG_FILE="mysql-bin.000004", MASTER_PORT=8764, MASTER_LOG_POS=336387, MASTER_CONNECT_RETRY=10;

备机上启动IO线程和SQL线程

START SLAVE;

4. 在备机上查看主从状态

SHOW SLAVE STATUS;

6.主主搭建

6.1原理图

说明：

Master-Master复制的两台服务器，既是master，又是另一台服务器的slave。这样，任何一方所做的变更，都会通过复制应用到另外一方的数据库中。

6.2准备环境

主(Master)机A IP： 124.204.65.84:12485 （内网：172.16.120.10）

主(Master)机B IP： 124.204.65.84: 12478 （内网：172.16.120.9）

6.3配置主机A

步骤1：

A服务器停掉mysql,修改my.conf service mysqld stop

vi /etc/my.conf

添加

#masterA自增长ID

auto_increment_offset = 1

auto_increment_increment = 2

log-slave-updates = true #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启

配置完后启动service mysqld start

6.4配置主机B

步骤1：

A服务器停掉mysql,修改my.conf service mysqld stop

vi /etc/my.conf

添加

#masterA自增长ID

auto_increment_offset = 2

auto_increment_increment = 2

log-slave-updates = true #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启

配置完后启动 service mysqld start

6.5查询A和B的status拿到bin-log和postion

分别查看A和B的 master的状态

SHOW MASTER STATUS;

A服务器:

B服务器:

给A添加B的同步用户信息

INSERT INTO mysql.user(HOST,USER,PASSWORD) VALUES('172.16.120.9','slaveg',PASSWORD('123456'));

-- 授权方式

GRANT REPLICATION SLAVE ON *.* TO 'slaveg'@'172.16.120.9' IDENTIFIED BY '123456' WITH GRANT OPTION;

FLUSH PRIVILEGES;

给B添加A的同步用户信息

INSERT INTO mysql.user(HOST,USER,PASSWORD) VALUES('172.16.120.10','slaveh',PASSWORD('123456'));

-- 授权方式

GRANT REPLICATION SLAVE ON *.* TO 'slaveh'@'172.16.120.10' IDENTIFIED BY '123456' WITH GRANT OPTION;

FLUSH PRIVILEGES;

在A上执行：

Stop slave;

CHANGE MASTER TO MASTER_HOST='172.16.120.9',MASTER_USER='slaveh',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000004',MASTER_PORT =8764,MASTER_LOG_POS=671;

Start slave;

在B上执行：

Stop slave;

CHANGE MASTER TO MASTER_HOST='172.16.120.10',MASTER_USER='slaveg',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000007',MASTER_PORT =8764,MASTER_LOG_POS=49466;

Start slave;

分别查询 A,B的 show slave status\G;

查询

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

是否都为yes

如图

7.主主从

7.1原理图

7.2准备环境

主A: 124.204.65.84:12485(内网172.16.120.10)

主B: 124.204.65.84:12478(内网172.16.120.9)

从C: 124.204.65.84:12496(内网172.16.120.12)

7.3配置主机A

步骤1：

A服务器停掉mysql,修改my.conf service mysqld stop

vi /etc/my.conf

添加

#masterA自增长ID

auto_increment_offset = 1

auto_increment_increment = 2

log-slave-updates = true #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启

配置完后启动service mysqld start

7.4配置主机B

步骤1：

A服务器停掉mysql,修改my.conf service mysqld stop

vi /etc/my.conf

添加

#masterA自增长ID

auto_increment_offset = 2

auto_increment_increment = 2

log-slave-updates = true #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启

配置完后启动 service mysqld start

7.5配置从机C

步骤1：

A服务器停掉mysql,修改my.conf service mysqld stop

vi /etc/my.conf

-- 配置

SERVER-id=12

LOG-BIN=mysql-BIN

binlog_format=mixed

-- etc/my.conf 配置不需要同步的数据

replicate_ignore_db=amc

replicate_ignore_db=amc_20171018

replicate_ignore_db=amc_20181108

replicate_ignore_db=amc_hbrb

replicate_ignore_db=db_mblog

replicate_ignore_db=hbcms

replicate_ignore_db=hbrb

replicate_ignore_db=hb_oa

replicate_ignore_db=healthmedia

replicate_ignore_db=mscms

replicate_ignore_db=sxxw

replicate_ignore_db=taiji_blog

replicate_ignore_db=amc_hbrbtest

replicate_ignore_db=amc_sxwb

replicate_ignore_db=mysql

replicate_ignore_db=performance_schema

replicate_ignore_db=shinyv_mms_jj

replicate_ignore_db=xuyong

replicate_ignore_db=information_schema

replicate_ignore_db=sys

7.6访问账号配置

A为B配置，B为A配置，同6（主主搭建）

重点：A为c配置

-- 10（主服务器）

-- 授权方式：此种授权是可远程登录Master，仅用于主从复制没有其它任何select insert update等权限

GRANT REPLICATION SLAVE ON *.* TO 'slavei'@'172.16.120.12' IDENTIFIED BY '123456';

FLUSH PRIVILEGES;

GRANT REPLICATION SLAVE ON *.* TO 'slavei'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

-- 查看授权结果

SHOW GRANTS FOR 'slavei'@'172.16.120.12';

C执行

-- 查看10服务器的 show master status;

-- 同步master的bin-log和postion

STOP SLAVE;

CHANGE MASTER TO MASTER_HOST="172.16.120.10", MASTER_PORT=8764,MASTER_DELAY =20, MASTER_USER="slavei", MASTER_PASSWORD="123456", MASTER_LOG_FILE="mysql-bin.000007", MASTER_LOG_POS=115601, MASTER_CONNECT_RETRY=10;

START SLAVE;

SHOW SLAVE STATUS;

一定要注意：slave_io_running slave_sql_running 都为yes

7.7延迟加载说明

配置参数： MASTER_DELAY =20

8. Mysql的容灾，备份操作

8.1mysqldump备份

8.1.1原理

实际上就是把数据库从mysql库里以sql语句的形式直接输出并保存成文件

备份的文件/* 到 */都是注释,其余都是sql语句

备份恢复就相当于把所有sql语句执行一遍

8.1.2用法

mysqldump -u username -p 数据库名 [表名]... > 文件路径

8.1.3用例

备份所有库并压缩：mysqldump -u root -p -A > xx.sql

备份某个库 mysqldump -u root -p -B DBNAME > xx.sql

备份多个库 mysqldump -u root -p -B DBNAME1 DBNAME2 > xx.sql

格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > 文件名.sql

例如： mysqldump -uroot -p tj_master_m > /home/chenjian/tj_master_m.sql

备份指定数据库指定表(多个表以空格间隔)

mysqldump -uroot -p mysql db event > /backup/mysqldump/2table.db

备份恢复 mysql -uroot -p  < xxx.sql

soure方法

mysql > use db_name

mysql > source /backup/mysqldump/db_name.db

8.2利用集群热备份

以上主从，主主，主主从搭建环境即是。

8.3定时自动备份

8.3.1创建shell脚本

vi bkDatabaseName.sh

内容：

#!/bin/bash
mysqldump -uusername -ppassword DatabaseName > /home/dbback/DatabaseName_$(date +%Y%m%d_%H%M%S).sql

对备份进行压缩：

#!/bin/bash
mysqldump -uusername -ppassword DatabaseName | gzip > /home/dbback/DatabaseName_$(date +%Y%m%d_%H%M%S).sql.gz

注意：
把 username 替换为实际的用户名；
把 password 替换为实际的密码；
把 DatabaseName 替换为实际的数据库名；

8.3.2添加可执行权限

chmod u+x bkDatabaseName.sh

执行：./bkDatabaseName.sh

8.4添加linux的计划任务

安装crontab （此次略）

8.5利用工具备份

参考文章：MySQL常用备份方式及备份工具并举例 - 简书

附：mysql的安装

附.1版本

mysql-5.5.56-linux-glibc2.5-x86_64.tar.gz

附.2解压和安装

tar -zxvf mysql-5.5.56-linux-glibc2.5-x86_64.tar.gz

mv mysql /usr/local/mysql

cd /usr/local/mysql/

创建mysql用户组和mysql用户

sudo groupadd mysql

sudo useradd -r -g mysql mysql

关联myql用户到mysql用户组中

sudo chown -R mysql:mysql /usr/local/mysql/

sudo chown -R mysql:mysql /usr/local/mysql/data/

sudo chown -R mysql /usr/local/mysql/

sudo chown -R mysql /usr/local/mysql/data/

sudo chown -R mysql:mysql /usr/local/mysql

更改mysql安装文件夹mysql/的权限

sudo chmod -R 755 /usr/local/mysql/

安装libaio依赖包

yum install libaio

初始化mysql

cd /usr/local/mysql/bin/

sudo /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data/ --basedir=/usr/local/mysql/

sudo ./mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --initialize

启动mysql服务

sh /usr/local/mysql/support-files/mysql.server start

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

chmod 755 /etc/init.d/mysqld

修改my.cnf文件

vi /etc/my.cnf

为以下配置

[client]

character-set-server = utf8

port = 12584

socket = /usr/local/mysql/mysql.sock

[mysqld]

character-set-server = utf8

user = mysql

port = 8080

socket = /usr/local/mysql/mysql.sock

basedir = /usr/local/webserver/mysql

datadir = /usr/local/mysql/data

log-error = /usr/local/mysql/mysql_error.log

pid-file = /usr/local/mysql/mysql.pid

:wq 保存退出

启动mysql

/etc/init.d/mysqld start

附.2.1错误解决

./mysqld: Table 'mysql.plugin' doesn't exist

解决：

sudo find / -name mysql_install_db得到/usr/local/mysql/scripts/mysql_install_db

cd /usr/local/mysql/

执行/usr/local/mysql/scripts/mysql_install_db --user=mysql

附.3服务启动和停止

启动：/etc/init.d/mysqld start

停止：/etc/init.d/mysqld stop

或 ps -ef|grep mysql

Kill -9 XXX

重启：/etc/init.d/mysqld restart

附.4添加用户

/etc/init.d/mysqld stop

vi /etc/my.cnf

在[mysqld]下添加skip-grant-tables

/etc/init.d/mysqld start

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

mysql/bin 目录

mysql -uroot -p

提示输入密码，直接回城

use mysql;

update user set password=password("TAIji123!") where user='root';

GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘root’;

flush privileges;

exit;

mysql开机启动

cp support-files/mysql.server /etc/rc.d/init.d/mysqld