vlambda博客
学习文章列表

mysql单机、集群部署

1. 单机安装

  • docker run 安装单机版

     docker run -p 3306:3306 --name mysql5.7ByRun --privileged=true -v /data/log:/var/log/mysql -v /data/data:/var/lib/mysql -v /data/conf:/etc/mysql -e MYSQL_ROOT_PASSWORD=mengqizhang -d mysql:5.7

  • docker-compose 安装单机版

     需要挂在mysql的data数据目录，否则存在数据丢失的情况

version: '3'
services:
 mysql:
 image: mysql:5.7
 container_name: mysql7ByCompose
 ports:
 - "3306:3306"
 volumes:
 - /data/log:/var/log/mysql
 - /data/data:/var/lib/mysql
 - /data/conf:/etc/mysql
 # 下面是windows环境下目录挂载示例，和mac、linux稍微有点不同
 # - /F/mqz/a-mqz-project/docker-mysql-volumn/log:/var/log/mysql
 # - /F/mqz/a-mqz-project/docker-mysql-volumn/mysql/data/data:/var/lib/mysql
 # - /F/mqz/a-mqz-project/docker-mysql-volumn/conf:/etc/mysql
 privileged: true #针对chown: changing ownership of '/var/lib/mysql/': Input/output error 没权限的情况下
 restart: on-failure
 environment:
 - MYSQL_ROOT_PASSWORD=mengqizhang
 - TZ=Asia/Shanghai #设置容器时区


2. mysql权限问题


  查看权限： 登录mysql终端，输入 ：show grants;

  

   解决步骤：       

grant all privileges on . to 'root'@'%' with grant option;
flush privileges;

GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'mengqizhang' WITH GRANT OPTION;


3. 数据库引擎

  • 查看mysql支持的数据库引擎：show engines; 

  • 默认使用的数据库引擎是：innoDB

  • InnoDB和MyISAM的区别：

    MyISAM
     mysql 5.5之前默认数据库引擎
     不支持事务，当数据库崩溃后，无法安全恢复。查询速度比InnoDB快
     只能支持表级锁（table-level locking），不支持事务和行级锁
    InnoDB
     InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁
     支持事务、并且具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表

4. 数据库 Navicat SSH连接方式


  • 本质就是通过ssh登录一个服务器（mysql所在服务器或其他服务器），然后在这个服务器上连接mysql 



mysql单机、集群部署


5. mysql集群部署


    热备（双主多从）

    冷备（一主多从）


  • 主从复制原理及流程：

mysql单机、集群部署

1. 主库在执行DDL和DML后，一个事务完整结束后，会将这些数据操作、数据库结构操作记录到二进制文件中（bin_log）
2. 主库推送二进制文件到从库的中继日志（relay_log）中，随后从库会根据中继日志的内容重新执行，这样保证了从和主的数据、数据结构一致！
3. Mysql通过三个线程来完成了主从复制的，其中 Binlog Dump跑在主库上，IO和SQL线程跑在从库上
4. 当从库启动主从复制后（start slave），首先创建IO线程连接主库，连接成功后，主库的Binlog Dump线程将主库的bin_log二进制文件发送给IO线程，
 IO线程收到bin_log后会将其更新到中继日志Relay_log上，之后从库的SQL线程将中继日志Relay_log的内容执行！
 
 问题：
 异步复制问题 ：由于是异步线程执行主库推送的bin_log，所以slave容易出现延迟，若master出现宕机的情况，将会造成延迟的数据丢失。 
 解决 ：
 为了解决异步复制的问题，mysql5.5引入了半同步复制，其介于全量同步复制和异步复制之间。master执行完事务之后，并不直接返回，而是要等待至少一个slave写入成功才返回。
 由于需要与至少一个slave进行交互，性能相比较异步复制肯定是有不少折损的。
 
 全复制模式当然是要等待所有的slave节点复制完成，这种安全性最高，但是效率也最低。从概念上来讲，只有一个slave节点的半复制就是全复制。
 
 5.7之后，mysql实现了组复制（group replication）协议。它支持单主模式和多主模式，但在同一个group内，不允许同时存在。听起还好像很神奇，其实它还是通过paxos协议去实现的。

  • 主从复制定义

主库的DDL和DML的操作通过二进制文件（bin_log）传输到从库上，从库对执行这个二进制文件，这样保证了主、库数据和数据库、表结构的一致！
一主多从：和上面从库配置一致，注意，如果stop slave;后，主库执行了的sql在从库start slave;后，从库的SQL-Running线程会继续跑stop slave;后主库执行的sql

  • 主库从库搭建 

      主库搭建：定义cnf配置文件，并且挂载到容器内部

    [mysqld]
    # 设置server_id，同一局域网中需要唯一
    server_id=101
    # 指定不需要同步的数据库名称
    binlog-ignore-db=mysql,admin,dynamic_datasource,kl,manage,nacos_config,wps
    # 指定数据库同步的名称
    # binlog-do-db=wps
    # 指定数据库-表同步的名称
    # binlog-do-table=skate_tab
    # 开启二进制日志功能
    log-bin=mysql-bin
    # 设置二进制日志使用内存大小（事务）
    binlog_cache_size=1M
    # 设置使用的二进制日志格式（mixed,statement,row） statement:基于sql语句的复制，row：基于行数据复制，只记录某行被修改，不记录sql，mixed：两种混合使用
    binlog_format=mixed
    # 二进制日志过期清理时间。默认值为0，表示不自动清理。
    expire_logs_days=0
    # 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
    # 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
    slave_skip_errors=1062

    # 双主 - 全局表自增开始值
    auto_increment_offset=1
    # 双主 - 全局表自增因子 （1，3，5，7........）
    auto_increment_increment=2

     docker-compose 安装主库 docker-compose -f docker-mysql-master up -d


    从库搭建：定义cnf配置文件，并且挂载到容器内部

    [mysqld]
    # 设置server_id，同一局域网中需要唯一
    server_id=102
    # 指定不需要同步的数据库名称
    binlog-ignore-db=mysql
    # 开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用
    log-bin=mysql-slave1-bin
    # 设置二进制日志使用内存大小（事务）
    binlog_cache_size=1M
    # 设置使用的二进制日志格式（mixed,statement,row）
    binlog_format=mixed
    # 二进制日志过期清理时间。默认值为0，表示不自动清理。
    expire_logs_days=0
    # 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
    # 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
    slave_skip_errors=1062
    # relay_log配置中继日志
    relay_log=mysql-relay-bin
    # log_slave_updates表示slave将复制事件写进自己的二进制日志
    # 此参数控制slave数据库是否把从master接受到的log并在本slave执行的内容记录到slave的二进制日志中，
    # 在级联复制环境中（包括双master环境），这个参数是必须的
    log_slave_updates=1
    # 执行复制数据库名
    # replicate-do-db=wps
    # 指定复制表名
    # replicate-do-table=user
    # slave设置为只读（具有super权限的用户除外）
    read_only=1

    # 双主 - 全局表自增开始值
    auto_increment_offset=1
    # 双主 - 全局表自增因子 （1，3，5，7........）
    auto_increment_increment=2

    docker-compose 安装主库 docker-compose -f docker-mysql-slave up -d

    version: "3"
    services:
     mysql-slave:
     image: mysql:5.7
     container_name: mysql-slave-MQZ
     ports:
     - "3305:3306"
     volumes:
     - /root/mqz/mysql-slave/log:/var/log/mysql
     - /root/mqz/mysql-slave/data:/var/lib/mysql
     - /root/mqz/mysql-slave/conf:/etc/mysql
     privileged: true
     environment:
     - MYSQL_ROOT_PASSWORD=mengqizhang
     - TZ=Asia/Shanghai
    6. 配置主从关系


  • 主库配置从库同步的用户并且授权 

    mysql单机、集群部署

  • 从库配置同步主库的配置 

  • mysql单机、集群部署

  • 查看从库配置同步主库配置是否成功 




  • 具体步骤：

  • 1. 按照mysql-master.yml生成主mysql，并且挂载my.cnf主配置文件
    2. 进入主库容器内，并且登陆myslq客户端：mysql -uroot -pmengqizhang
    3. 创建从库同步用户：CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
     并且配置权限：GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
    4. 查看主库的状态：show master status;

    5. 按照mysql-slave.yml生成从mysql，并且挂载my.cnf从配置文件
    6. 进入从库容器内，并且登陆mysql客户端：mysql -uroot -pmengqizhang
    7. 设置从库同步主库的配置：change master to master_host='192.168.2.9', master_user='slave-for-3306', master_password='123456', master_port=3305, master_log_file='mall-mysql-slave1-bin.000001', master_log_pos=3947, master_connect_retry=30;
     master_host：主库的IP地址；
     master_port：主库的运行端口；
     master_user：在主数据库创建的用于同步数据的用户账号 （即第3点在主库创建的用户）；
     master_password：在主数据库创建的用于同步数据的用户密码 （即第3点在主库创建的用户）；
     master_log_file：指定从数据库要复制数据的日志文件，通过查看主数据的状态，获取File参数；
     master_log_pos：指定从数据库从哪个位置开始复制数据，通过查看主数据的状态，获取Position参数；
     master_connect_retry：连接失败重试的时间间隔，单位为秒。
    8. 查看从库配置同步主库是否成功：show slave status \G;
     这个时候开始没有开启主从同步，所以Slave_IO_Running: No 和 Slave_SQL_Running: No
     其中IO 代表的是和主库连接是否成功
    9. 开启主从同步：start slave;
     这个时候查看主从已经开始同步：show slave status \G;
     Slave_IO_Running: Yes
     Slave_SQL_Running: Yes

    注意：这里是一主一从，可以一主多从，配置方式如上的从库配置无差别

  • 清除主从关系：

    主：

     步骤：
     1. reset master
     解析：
     a. 删除所有的bin_log文件，并且清空日志索引文件，重新开始记录bin_log日志文件内容
     c. show master status;命令在执行reset master后会发现 bin_log文件有所变化，之前的bin_log文件及position均被清空重置

    从：清除slave同步复制关系


    步骤：
     
     1. stop slave;
     2. reset slave all;
     
     解析：
     
     a. 执行reset slave all后，show slave status \G;将不再有任何记录输出
     b. reset slave 是mysql任何版本都有的功能，在stop slave;之后使用
     c. 主要做删除master.info和relay-log.info文件(中继日志)，删除所有的relay-log，并且创建一个新的relay-log文件
     d. 从Mysql 5.5开始，多了一个all参数。如果不加all参数，那么所有的连接信息仍然保留在内存中，包括主库地址、端口、用户、密码等。这样可以直接运行start 
     slave命令而不必重新输入change master to命令，而运行show slave status也仍和没有运行reset slave一样，有正常的输出。但如果加了all参数，那么这

7. 双主配置 （热备）


     注意两mysql服务版本最好一致！

  • 定义：两个数据库互为主从，即 A的从是B，B的从也是A

  • 需要更改 A和B的my.cnf配置，因为A和B库均有互为读写的权限，若表为自增id存在id冲突的情况，这里需要修改一下id自增规则，参考master/conf/my.cnf 、 slave/conf/my.cnf

  • 常见的业务场景下，只有一个主库，另外一个主库可以当成读库！

  • 可以借助mycat实现mysql集群的管理！！


    前置：
     3306是主库，3305是从库，配置了主从复制
     
     实现：让3306变成3305的从库，即3305修改的记录同步到3306上，3306修改的记录也会同步到3305上 
     
     1. 登陆3305从库，根据show master status \G; 查处3305的机binlog日志名即position
     2. 配置3305从库给3306开放出的权限：
     CREATE USER 'slave-for-3306'@'%' IDENTIFIED BY '123456';
     GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave-for-3306'@'%';
     
     
     3. 登陆3306，配置3306库为3305的从库：
     change master to master_host='192.168.2.9', master_user='slave-for-3306', master_password='123456', master_port=3305, master_log_file='mall-mysql-slave1-bin.000001', master_log_pos=3947, master_connect_retry=30;
     4. 查看3306从库配置信息：show slave status \G;
     5. 开启3306从库复制3305主库：start slave;


推荐阅读

[5.05] 暗喻幻想港中35周年限定版1.03+DLC+金手指+王中王存档 [5.05] 暗喻幻想港中35周年限定版1.03+DLC+金手指+王中王存档
大师代表作！！Linux云计算行业最全书籍首次发布 大师代表作！！Linux云计算行业最全书籍首次发布

相关文章