mysql主从

一、定义

主从同步使得数据可以从一个数据库服务器复制到其他服务器上,在复制数据时,一个服务器充当主服务器(master),其余的服务器充当从服务器(slave)。因为复制是异步进行的,所以从服务器不需要一直连接着主服务器,从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件,可以指定复制所有的数据库,某个数据库,甚至是某个数据库上的某个表。

二、原理

  1. master将更改操作(DDL和DML)记录到binlog里
  2. slave将主的binlog事件(sql语句)同步本机上并记录在relaylog里
  3. slave根据relaylog里面的sql语句按顺序执行
    mysql主从

三、步骤

  1. master配置

    1
    2
    3
    4
    5
    6
    7
    8
    server-id=1
    log-bin=log
    binlog-do-db=database1 //要同步的数据库1
    binlog-do-db=database2 //要同步的数据库2
    binlog-do-db=database3 //要同步的数据库3
    ...
    binlog-do-db=databasen //要同步的数据库n
    binlog-ignore-db=mysql //要忽略的数据库
  2. slave配置

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    server-id=2
    master-host=master服务器ip
    master-user=mstest
    master-password=123456
    master-port=3306
    master-connect-retry=60
    replicate-db-db=dababase1
    replicate-db-db=dababase2
    replicate-db-db=dababase3
    ...
    replicate-db-db=dababasen
    replicate-ignore-db=mysql
  1. 验证
    1
    show slave status\G;

如果slave_io_runningslave_sql_running都为yes,那么表明可以成功同步了

四、好处

  1. 通过增加从服务器来提高数据库的性能,在主服务器上执行写入和更新,在从服务器上向外提供读功能,可以动态地调整从服务器的数量,从而调整整个数据库的性能。
  2. 提高数据安全-因为数据已复制到从服务器,从服务器可以终止复制进程,所以,可以在从服务器上备份而不破坏主服务器相应数据
  3. 在主服务器上生成实时数据,而在从服务器上分析这些数据,从而提高主服务器的性能
    读写分离

五、扩展

  1. mysql日志

    • 错误日志:-log-err(记录启动,运行,停止mysql时出现的信息)
    • 二进制日志:-log-bin(记录所有更改数据的语句,还用于复制,恢复数据库用)
    • 查询日志:-log(记录建立的客户端连接和执行的语句)
    • 慢查询日志: -log-slow-queries(记录所有执行超过long_query_time秒的所有查询)
  2. ddl、dml、dcl

    • DML(data manipulation language)数据操纵语言:就是我们最经常用到的 SELECT、UPDATE、INSERT、DELETE,主要用来对数据库的数据进行一些操作。
    • DDL(data definition language)数据库定义语言:就是我们在创建表的时候用到的一些sql,比如说:CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构,数据类型,表之间的链接和约束等初始化工作上。
    • DCL(Data Control Language)数据库控制语言:是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句