mysql主从复制

mysql 主从复制

基本命令:

  • 启动mysql,这里使用的是yum下载,所以使用systemctl管理

    systemctl start mariadb
    
  • 远程连接mysql服务端

    mysql -uroot -p -h 192.168.11.11
    参数解释: -u  用户
    		-p  密码,可先不输入,回车后输入, 
    		-h  服务端地址
    
  • 修改mysql密码

    set password = PASSWORD('新密码');
    
  • 创建mysql用户

    create user yuncong@'%' identified by 'yuncong123';
    # 新建用户 yuncong@任意地址可连,密码yuncong123
    
  • 查询mysql库中的用户信息

    # 进入mysql库
    use mysql;
    
    # 查询语句
    select host,user, password from user;
    
  • 授权语句

    # 语法
    grant 权限 on 数据库.表名 to 账户@主机名       对特定数据库中的特定表授权
    grant 权限 on 数据库.* to 账户@主机名         对特定数据库中的所有表给与授权
    grant 权限1,权限2,权限3 on *.* to 账户@主机名      对所有库中的所有表给与多个授权
    
    # 权限分类:
    查询,插入,更新,新增
    select ,insert,update, create
    
    #yuncong用户对所有数据库中文件有任何操作
    grant all privileges  on *.*  to "yuncong"@'%';
    
  • 移除权限

    revoke all privileges on *.* from yuncong@"%";
    
  • 详情请创建用户和授权请看:https://www.cnblogs.com/yuncong/articles/10122719.html

数据库备份与恢复

  • 使用mysqldump进行数据备份

    mysqldump -u root -p --all-databases > /tmp/db.dump
    

    使用scp将数据发送到需要用的设备中

  • 数据导入

    # 登录需要导入的数据库之后
    source /tmp/db.dump
    

    或者

    mysql -uroot - < /tmp/db.dump
    

    基于mysqldump实现逻辑备份查看该博客https://www.cnblogs.com/yuncong/articles/10122695.html#autoid-1-0-0

mysql主从复制

  • 前言

    MySQL数据库的主从复制方案,是其自带的功能,并且主从复制并不是复制磁盘上的数据,而是通过binlog日志复制到需要同步的的从服务器上

  • mysql数据库特点

    MySQL数据库支持单向,双向,链式级联,等不同业务场景的复制,在复制的过程中,一台服务器充当主服务器(master),接收来自用户的内容跟新,而一个或者多个其他的服务器充当从服务器(slave),接收来自master上binlog文件的日志内容,解析出SQL,重新更新到salve,使得主从服务器数据达到一致.

  • 主从复制的逻辑有以下几种

    • 一主一从,单向主从同步模式,只能在master端写入数据
    • 一主多从

    img

  • mysql主从复制优点:

    在生产环境中,MySQL主从复制都是异步的复制方式,即不是严格的实时复制,但是给用户的体验都是实时的。
    MySQL主从复制集群功能使得MySQL数据库支持大规模高并发读写成为可能,且有效的保护了服务器宕机的数据备份
    
  • 应用场景

    利用复制功能当master服务器出现问题时,我们可以从人工的切换到从服务器继续提供服务,此时服务器的数据和宕机时数据几乎是完全一致的.

    复制功能也可以用做数据本分,但是如果人为IDE执行drop,delete等语句删除,那么从库的备份功能也就失效了

  • 主从机制实现原理

    img

    (1) master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events); 
    (2) slave将master的binary log events拷贝到它的中继日志(relay log); 
    (3) slave重做中继日志中的事件,将改变反映它自己的数据。
    

主从复制步骤

主库操作
  1. 在master主库上操作,开启主库功能

    # 先将mariadb服务端关闭
    systemctl stop mariadb 
    
    # 编辑/etc/my.cnf写入以下信息
    [mysqld]
    server-id=10
    log-bin=qishi-logbin
    
    # 保存退出
    启动mariadb服务端
    systemctl start mariadb 
    
    
  2. 在主库创建用户,用户同步数据

    create user yuanqing@'%' identified by 'yq666';
    

    授予普通用户, slave的身份

    grant replication slave on *.* to 'yuanqing'@'%';
    
  3. 登录mysql,锁定数据库的库表,禁止写入

    # 锁定数据库
    flush table with read lock;
    
    查看主库的状态,记录下,日志文件的名字和位置
    show master status; 
    
  4. 退出mysql,导出主库的数据

    mysqldump -u root -p --all-databases > /opt/db.dump   
    # 这里的路径要自己知道在哪
    
  5. 远程传输主库的数据,给从库,进行导入

    scp /opt/db.dump   root@192.168.11.167:/opt/
    
  6. 解锁主库的锁,写入数据,查看从库是否同步

    unlock tables;
    
从库操作
  1. 关闭该从库的数据库

    systemctl stop mariadb
    
    # 查看进程确认
    ps -ef |grep mysql
    
  2. 在从库的 /etc/my.cnf中添加参数,添加只读参数

    vim /etc/my.cnf
    # 添加文本
    
    [mysqld]
    server-id=3
    read-only=true
    
  3. 重启数据库

    systemctl restart mariadb
    
  4. 导入主库传过来的数据

    mysql -uroot -p  <  /opt/db.dump 
    
    # 或者登录mysql,使用source 
    source /opt/db.dump
    
  5. 登录mysql,,输入命令开启主从之间的复制关系

    change master to master_host='192.168.11.96',
    master_user='yuanqing',
    master_password='yq666',
    master_log_file='qishi-logbin.000001',
    master_log_pos=871;
    
    
    # host 是主的地址,   master是主的用户, password为密码,   log_file是主库查询的日志,和pos
    
  6. 开启slave同步功能

    start slave;
    
  7. 检查slave机器的主从是否正确

    show slave status\G  #查看主从同步是否正确
    
  8. 确认查询结果中的下列参数是否是yes,即主从复制正确

      Relay_Master_Log_File: qishi-logbin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
    
主库操作

注意:

​ 此时mariadb数据库,请退出root用户,使用普通用户配置,因为root身份权限太大,无法达到read-only效果

​ 此时mariadb数据库,请退出root用户,使用普通用户配置,因为root身份权限太大,无法达到read-only效果

  1. 登录普通用户

    mysql -uyuanqing -p 
    
  2. 此时在主库写入数据,查看从库是否正确同步

  3. 从库无法写入数据,即为正常

posted @ 2019-01-19 22:46  云丛  阅读(217)  评论(0编辑  收藏  举报