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 statusG  #查看主从同步是否正确

  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. 从库无法写入数据，即为正常

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/yuncong/p/10293572.html