MySQL主从复制

##命令集

#主库
vim /data/3306/my.cnf
    server-id = 1
    log-bin=/data/3306/data/mysql-bin
/data/3306/mysql restart
mysql -uroot -p123123 -S /data/3306/mysql.sock
    show variables like 'server_id';
    show variables like 'log_bin';
    grant replication slave on *.* to 'yunjisuan'@'192.168.200.%' identified by '123123';

    flush table with read lock;
mysqldump -uroot -p123123 -S /data/3306/mysql.sock --events -A -B |gzip > /server/backup/mysql_bak.$(date +%F).sql.gz
    unlock tables;
 
#从库
vim /data/3307/my.cnf
    server-id = 3
    relay-log = /data/3307/data/relay-bin
/data/3307/mysql restart
mysql -uroot -p123123 -S /data/3307/mysql.sock
    show variables like 'server_id';
    show variables like 'log_bin';
    CHANGE MASTER TO MASTER_HOST='192.168.200.123',MASTER_PORT=3306,MASTER_USER='yunjisuan',MASTER_PASSWORD='123123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=533;
mysql -uroot -p123123 -S /data/3307/mysql.sock -e "start slave"
mysql -uroot -p123123 -S /data/3307/mysql.sock -e "show slave status\G" |egrep "IO_Running|SQL_Running|Seconds_Behind_Master"

命令详解

#master服务器
#修改配置文件
vim /data/3306/my.cnf
server-id = 1
log-bin=/data/3306/data/mysql-bin
#重启配置文件
/data/3306/mysql restart
#登陆账号
mysql -uroot -p123123 -S /data/3306/mysql.sock
#里面查看MySQL的系统变量
show variables like 'server_id';
  server_id  1  #则成功
show variables like 'log_bin';
log_bin   OK    #则成功
#mysql里面输入命令创建主从复制账号，IP是从库的Ip，而且不能是范围
grant replication slave on *.* to 'yunjisuan'@'192.168.200.135' identified by '123123';
#刷新权限
flush privileges;
#检查账号
select user,host form mysql.user;

##主库备份
#实现对主库数据表只读，锁表（此窗口不能关，可以新建窗口执行下面操作）
flush table with read lock;
#查看锁表时间参数
show variables like '%timeout%';
#查看主库现在二进制文件内容记录标识位置
show master status;
#温备份
mysqldump -uroot -p123123 -S /data/3306/mysql.sock --events -A -B |gzip > /server/backup/mysql_bak.$(date +%F).sql.gz
#解锁主库
unlock tables;


#slave服务器
vim /data/3307/my.cnf
server-id = 3
relay-log = /data/3307/data/relay-bin
/data/3307/mysql restart
#登陆
mysql -uroot -p123123 -S /data/3307/mysql.sock
#查看mysql的系统变量
show variables like 'log_bin';
show variables like 'server_id';

#把主库的备份同步到从库来，使他们数据一致
cd /server/backup
gzip -d mysql_bak.2017-07-21.sql.gz
mysql -uroot -p123123 -S /data/3307/mysql.sock < mysql_bak.2017-07-21.sql

#登陆从库
mysql -uroot -p123123 -S /data/3307/mysql.sock 
#然后输入命令
CHANGE MASTER TO
MASTER_HOST='192.168.200.123'      # 这是主库IP
MASTER_PORT=3306                   # 主库端口，从库端口可以和主库不同
MASTER_USER='yunjisuan'            # 这是主库上建立的用户复制的用户yunjisuan
MASTER_PASSWORD='123123'           # 这是yunjisuan的密码
MASTER-LOG_FILE='mysql-bin.000001' # show master status 二进制文件名称，不能多空格
MASTER_LOG_POS=533                 # show master status 二进制文件记录位置，不能多空格
#查看从库新的master.info文件
ll /data/3307/data/master.info
cat /data/3307/data/master.info
#启动主从复制开关
mysql -uroot -p123123 -S /data/3307/mysql.sock -e "start slave"
#查看状态（\G是竖形显示）
mysql -uroot -p12323 -S /data/3307/mysql.sock -e "show slave status\G"
#查看状态最重要的三项
mysql -uroot -p123123 -S /data/3307/mysql.sock -e "show slave status\G" |egrep "IO_Running|SQL_Running|Seconds_Behind_Master"
#在测试就行了

#查看主库从库线程状态
show processlist\G
#跳针
stop slave;
set global sql_slave_skip_counter=1;
start slave;

##从库想成为主库时，my.cnf中（双主，级联）
#增加参数
log-slave-updates
log-bin=/data/3307/mysql-bin
#自动清除7天binlog日志，增加参数
#相当于find /data/3307/ -type f -name "mysql-bin.000*" -mtime +7 |xargs rm -f
exipire_logs_days = 7

企业备份

中小企业全量备份

推送：数据库定时任务备份后，rsync推送到备份服务器上
raid：在MySQL服务器上，额外的进行组raid挂载，将备份在此挂载目录里（节约成本）

中大型和大型企业（全备和增备）

每周一全备，每天一增备

增备

1：定时任务+增备切割==》每天0点flush logs 切割二进制日志
2：定时同步，每周日全备份，并进行一次flush logs，然后删除之前备份，最后最实

##主从复制
常用架构：主从同步，主从复制。主从复制加读写分离，写(20%的压力)给主，主实时同步给从(读80%)
架构类似于NFS,但主只能是一个，从可以多个，主只能写，并且不能有脚本，定时任务，等给主增加 压力的任务
类型：一主一从，一主多从，互为主从，主从级联，环绕多主。
实现：必须打开master端的binlog记录功能
过程：slave从master中回去binlog日志，然后再自己的slave端以相同顺序执行此binlog日志
打开：打开binlog，在master端配置文件mysql模块中，增加“log-bin”参数来实现
log-bin=/data/3306/mysql-bin
###原理：
异步复制，以master复制到slave
由三个线程完成，两个线程(I/O,SQL)在slave端，一个线程(I/O)在master端

原理分析： 主有一个MySQL主进程，一个I/O线程，从库有一个主进程，一个I/O线程，一个SQL线程。
用户向主库data里输入数据，主库主进程由于已经打开了bin-log日志，数据会被I/O线程记录在
binlog二进制日志里(mysql-bin.index)
1: 打开binlog日志
2: 创建主从复制账号
3: server id = 1 (主必须是1)
当从想复制时，自己生成I/O线程，主动向主发起验证，从的I/O线程自发的监看主的
mysql-bin.indnx日志,当住的日志更新时，从库主动发起验证，验证通过后，主的I/O线程
会把文件复制反馈给从的I/O线程，复制过来的数据放在中继日志中(relay-log=relay-bin)
从的SQL线程监看中继日志，发现更新，会把二进制语言翻译成SQL语言，记录在从库中。
1：开启中继日志 relay-log=relay-bin
2：主从复制验证信息录入
3：激活主从复制
4：server id = 2 (只要不是1，就行)

验证： 验证信息是什么？
1：主库IP 端口port
2：主库的复制账号和密码
3：复制哪个二进制文件的名字
4：二进制文件中的position具体位置
分析： 从库如何知道自己应该从哪个位置开始复制，从哪个文件开始复制？
需要有个记录文件，来记这些，I/O线程，记录文件：master.info
SQL线程也需要有个记录，为了把数据写入数据库，记录文件：relay-log.info
过程： 1：从库I/O线程找主库I/O线程申请验证，并把想要的，从哪开始复制的信息找到
2：主I/O线程把自己日志文件需要的binlog日志复制出来
3：主I/O线程把新的binlog日志和新的position位置和名字，发给从I/O线程
4：从I/O线程把位置名字记录在master.info中
5：SQL把新的信息更新到relay-log.info中
6：SQL写入数据库

主从复制故障处理

交互： 1）stop slave;
       2）drop database 库名；（因为出现错错误的是创建了库，所以删除他）
       3) start slave;
非交互 1）stop slave;
       2）set gloval sql_slave_skip_counter=1;(跳过1次，多次不同步，可以多次执行)
       3）start slave;

主从复制延迟，及解决方法

     1）      从库太多
    解决：       减少从库，3-5个最好
     2）      从库硬件比主库差
    解决：       提高从库硬件配置
     3）      慢SQL语句太多
    解决：       通过慢查询日志，找到慢SQL语句，去找开发人员做优化 
     4）      主从复制设计问题
    解决：        升级MySQL版本到5.6以上 
     5）      主从库之间的网络延迟
    解决：        提高宽带网速，
     6）      读库读写压力大
    解决：        提高主库硬件，并且架构前端加buffer及缓存层

加库以及备份

假如MySQL的binlog日志把系统盘空间占满了，你如何在不影响数据安全的情况下解决这个问题？
1）先临时锁表， flush table with read lock; 此命令不能关闭，关闭后临时锁表失效。
2) 再复制另一个ssh通道
3）再做全备份， mysqldump -uroot -p123123 --databases 所有的库名 > /backup/库名_$(date +%F).sql;
4）删除所有日志 find /usr/local/mysql/data/ -type f -name “mysql-bin.[0-9]+” |xargs rm- rf
5）刷新日志 flush logs;
6) 解锁 unlock tables;

如何授权读写账号

一个账号，分别在主库写，从库读，设置不同的权限，但是当宕机时，需要从库的备份做恢复，这时在主库恢复时，执行的用户时从库的，权限不一样，很麻烦。我们备份时 mysqldump 命令有个参数，可以备份同时不备份用户所属

一个账号，并且主库从库权限一样，所以我们需要改从库的表权限，设置为read only 只读权限。
1） killall mysqld 或 mysqladmin -uroot -p12123 -S /data/3307/mysql.sock shutdown
(临时设置) mysql_safe --defaults-flie=/data/3307/my.cnf --read-only
2）vim /etc/my.cnf (添加只读参数)
[mysql]
read-only

备份与恢复

全备与恢复
  完全备份：备份与恢复操作简单
      冷备份 : tar /usr/local/mysql/data 或 /var/lib/mysql
      热备份 : mysqldump -uroot -p123213 库名 > /backup/库名_$(date +%F_%w).sql
            多库：--databases 库名 库名
            所有库：--all-databases
            对库里的表备份 
                mysqldump -uroot -p123213 库名 表名 > /backup/库名_$(date +%F_%w).sql
            对结构备份
                -d 库名 表名
      
  恢复：
      交互：  source /backup/文件名
      非交互：mysql -uroot -p123123 < /backup/文件名
      
      
增备与恢复: 通过mysqlbinlog备份，日志存在于data里的 mysql-bin.* (起始于mysql-bin.000001)
   开启 vim /etc/my.cnf
       51 log-bin=mysql-bin              #开启二进制文件
       52 max_binlog_size=10240000       #二进制默认最大1M，到最大后，自动切割
   
   读取二进制文件  mysqlbinlog mysql-bin.000001
   刷新(切割新日志) flush logs;
   

恢复：
   一般恢复：mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 |mysql -uroot -p123123
   以时间点恢复：
      以二进制文件开始写到这个时间点结束
         mysqlbinlog --stop-datetime='年月日时分秒' 二进制文件 |mysql -u -p
      以这个时间点开始写到二进制文件结束
         mysqlbinlog --start-datetime='年月日时分秒' 二进制文件 |mysql -u -p 
      以这个时间点开始写到这个时间点结束
         mysqlbinlog --start-datetime='年月日时分秒' --stop-datetime='年月日时分秒'  |mysql -u -p
         
    以位置点恢复： 
     查看二进制文件位置记录（id）
      show master status;
        以二进制文件开始写到这个位置结束
           mysqlbinlog --stop-position='id' 二进制文件 |mysql -u -p
        以这个位置开始写到二进制文件结束  
           mysqlbinlog --start-position='id' 二进制文件 |mysql -u -p 
        以这个位置点开始写到这个位置结束
           mysqlbinlog --start-position='id' --stop-position='id'  |mysql -u -p