• mysql主从复制

    mysqlbinlog的学习

    [root@localhost 3306]# ll
总用量 128
drwxr-xr-x. 7 mysql mysql  4096 6月  17 15:45 data
-rw-r--r--. 1 mysql mysql  2005 6月  17 2018 my.cnf
-rwx------. 1 mysql mysql  1307 5月  28 02:06 mysql
-rw-rw----. 1 mysql mysql   536 6月  16 05:34 mysql-bin.000006
-rw-rw----. 1 mysql mysql   307 6月  16 06:11 mysql-bin.000007
-rw-rw----. 1 mysql mysql  2827 6月  16 08:14 mysql-bin.000008
-rw-rw----. 1 mysql mysql   126 6月  16 08:14 mysql-bin.000009
-rw-rw----. 1 mysql mysql   150 6月  17 2018 mysql-bin.000010
-rw-rw----. 1 mysql mysql   150 6月  17 2018 mysql-bin.000011
-rw-rw----. 1 mysql mysql   811 6月  17 2018 mysql-bin.000012
-rw-rw----. 1 mysql mysql  1087 6月  17 2018 mysql-bin.000013
-rw-rw----. 1 mysql mysql   150 6月  17 2018 mysql-bin.000014
-rw-rw----. 1 mysql mysql   150 6月  17 2018 mysql-bin.000015
-rw-rw----. 1 mysql mysql   150 6月  17 2018 mysql-bin.000016
-rw-rw----. 1 mysql mysql   150 6月  17 2018 mysql-bin.000017
-rw-rw----. 1 mysql mysql   150 6月  17 11:54 mysql-bin.000018
-rw-rw----. 1 mysql mysql   150 6月  17 12:03 mysql-bin.000019
-rw-rw----. 1 mysql mysql  1656 6月  17 13:04 mysql-bin.000020
-rw-rw----. 1 mysql mysql   126 6月  17 13:04 mysql-bin.000021
-rw-rw----. 1 mysql mysql   617 6月  17 15:33 mysql-bin.000022
-rw-rw----. 1 mysql mysql   150 6月  17 15:34 mysql-bin.000023
-rw-rw----. 1 mysql mysql  2597 6月  17 15:48 mysql-bin.000024
-rw-rw----. 1 mysql mysql   532 6月  17 15:34 mysql-bin.index
-rw-rw----. 1 mysql mysql     5 6月  17 13:40 mysqld.pid
-rw-r-----. 1 mysql mysql 27602 6月  17 13:40 mysql_oldboy3306.err
srwxrwxrwx. 1 mysql mysql     0 6月  17 13:40 mysql.sock

    　mysqlbinlog=》解决msyql的binlog日志　用来记录mysql内部的增删改查等，对mysql数据库有更新的内容记录

     

     -d参数： 截取指定库的binlog

mysqlbinlog /data/mysql/mysql-bin.000023  > a.sql;

mysql -uroot -p123456 -S /data/3306/mysql.sock oldboy < a.sql
    -r 参数和重定向 > 是一样的作用

    　　

    --master-data的作用讲解

    　　master-data参数在建立slave数据库的时候会经常用到，因为这是一个比较好用的参数，默认值为1，默认情况下，会包含change master to，这个语句包含file和position的记录始位置。master-data=2的时候，在mysqldump出来的文件包含CHANGE MASTER TO这个语句，处于被注释状态

    --master-data=1 是告诉从库从主库哪个位置更新,包含mysql-bin的文件名和起始位置

    开始主从复制配置

    主从复制原理

    主从复制步骤

    [root@localhost 3306]# grep -E 'server-id|log-bin' ./my.cnf
log-bin = /data/3306/mysql-bin
server-id = 1

    其次建立同步用户

    grant replication slave on *.* to 'rep'@'192.168.1.%' identified by  'oldboy123';

    select user,host from mysql.user;

    show grants for rep@'192.168.10.%';

    mysql> show grants for rep@'192.168.1.%';
    +--------------------------------------------------------------------------------------------------------------------------+
    | Grants for rep@192.168.1.% |
    +--------------------------------------------------------------------------------------------------------------------------+
    | GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.1.%' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |
    +--------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    开始备份主库数据库：

    flush tables with read lock;//只读  锁表

    show variables like '%timeout%';

    show master status; //记录mysql-bin信息000004 3613

    mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      517 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

    mysql -uroot -p'oldboy123' -S /data/3306/mysql.sock -e "show master status"；
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B --events|gzip > /opt/rep.sql.gz  //手动记录mysql-bin信息000004 3613
或者
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B --events --master-data=1 > /opt/rep.sql   //可以查看mysql-bin信息000004 3613
unlock tables;//解锁

    #-A备份所有的库  -B 增加 use DB 和 drop等

    还有一个办法：
    便是在mysql这个数据库相关的那句mysqldump 加上 --lock-tables=0 这个参数，不锁表备份也可以

    solve从服务器操作

    1， 导入已经备份好的数据库

    mysql -uroot -poldboy123 -S /data/3307/mysql.sock < /opt/rep.sql //全库导入

    执行命令

    change master to 
master_host='192.168.1.125',
master_port=3306,
master_user='rep',
master_password='oldboy123',
master_log_file='mysql-bin.000004',
master_log_pos=3613;

    　start slave;    //启动从服务器复制功能

    备注：如果主库备份数据库采用的是 --master-data=1参数，那么在从库这里  change master to 后面不需要制定 

    master_log_file='mysql-bin.000004',
master_log_pos=3613;
    因为这部分已经在还原数据的时候提前应用到数据库里面了

    检查从服务器复制功能状态：

      mysql> show slave statusG

   *************************** 1. row ***************************

              Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.2.222  //主服务器地址
              Master_User: mysync   //授权帐户名，尽量避免使用root
              Master_Port: 3306    //数据库端口，部分版本没有此行
              Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 600     //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos
              Relay_Log_File: ddte-relay-bin.000003
              Relay_Log_Pos: 251
              Relay_Master_Log_File: mysql-bin.000004
              Slave_IO_Running: Yes    //此状态必须YES
              Slave_SQL_Running: Yes     //此状态必须YES
                    ......

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

    　　

    主从服务器测试：

    主服务器Mysql，建立数据库，并在这个库中建表插入一条数据：

      mysql> create database hi_db;

      Query OK, 1 row affected (0.00 sec)

      mysql> use hi_db;

      Database changed

      mysql>  create table hi_tb(id int(3),name char(10));

      Query OK, 0 rows affected (0.00 sec)

      mysql> insert into hi_tb values(001,'bobu');

      Query OK, 1 row affected (0.00 sec)

      mysql> show databases;

       +--------------------+

       | Database           |

       +--------------------+

       | information_schema |

       | hi_db                |

       | mysql                |

       | test                 |

       +--------------------+

       4 rows in set (0.00 sec)

    从服务器Mysql查询：

       mysql> show databases;

       +--------------------+

       | Database               |

       +--------------------+

       | information_schema |

       | hi_db                 |       //I'M here，大家看到了吧

       | mysql                 |

       | test          |

       +--------------------+

       4 rows in set (0.00 sec)

       mysql> use hi_db

       Database changed

       mysql> select * from hi_tb;           //查看主服务器上新增的具体数据

       +------+------+

       | id   | name |

       +------+------+

       |    1 | bobu |

       +------+------+

       1 row in set (0.00 sec)

    知识要点回顾

     

    　

    数据库停止后起不来的问题解决

    rm   -f /data/3306/mysql.sock   /data/3306/*.pid

    查看数据库状态,mysql主从复制线程状态说明及用途

    Show  processlist;

     1 mysql> Show  processlist;
 2 ERROR 2006 (HY000): MySQL server has gone away
 3 No connection. Trying to reconnect...
 4 Connection id:    4
 5 Current database: *** NONE ***
 6 
 7 +----+------+---------------------+-------+-------------+------+-----------------------------------------------------------------------+-------------------+
 8 | Id | User | Host                | db    | Command     | Time | State                                                                 | Info              |
 9 +----+------+---------------------+-------+-------------+------+-----------------------------------------------------------------------+-------------------+
10 |  1 | root | 192.168.1.103:53315 | NULL  | Sleep       | 1664 |                                                                       | NULL              |
11 |  2 | rep  | 192.168.1.125:48754 | NULL  | Binlog Dump |  415 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL              |
12 |  3 | root | 192.168.1.103:59987 | hello | Sleep       |  336 |                                                                       | NULL              |
13 |  4 | root | localhost           | NULL  | Query       |    0 | NULL                                                                  | Show  processlist |
14 +----+------+---------------------+-------+-------------+------+-----------------------------------------------------------------------+-------------------+
15 4 rows in set (0.00 sec)
    master
     1 mysql> Show  processlist;
 2 +----+-------------+---------------------+-------+---------+------+-----------------------------------------------------------------------------+-------------------+
 3 | Id | User        | Host                | db    | Command | Time | State                                                                       | Info              |
 4 +----+-------------+---------------------+-------+---------+------+-----------------------------------------------------------------------------+-------------------+
 5 |  2 | root        | 192.168.1.103:53516 | NULL  | Sleep   |  596 |                                                                             | NULL              |
 6 |  3 | root        | localhost           | NULL  | Query   |    0 | NULL                                                                        | Show  processlist |
 7 |  4 | system user |                     | NULL  | Connect |  412 | Waiting for master to send event                                            | NULL              |
 8 |  5 | system user |                     | NULL  | Connect |  337 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL              |
 9 |  6 | root        | 192.168.1.103:59984 | hello | Sleep   |  329 |                                                                             | NULL              |
10 +----+-------------+---------------------+-------+---------+------+-----------------------------------------------------------------------------+-------------------+
11 5 rows in set (0.01 sec)
    slave

    从服务器授权管理：

    主服务器mysql 添加binlog-ignore-db

    vim /data/3306/my.cnf 添加忽略的数据库

    。

    。

    。

    key_buffer_size = 16M
    read_buffer_size = 1M
    read_rnd_buffer_size = 1M
    bulk_insert_buffer_size = 1M
    #myisam_sort_buffer_size = 1M
    #myisam_max_sort_file_size = 10G
    #myisam_max_extra_sort_file_size = 10G
    #myisam_repair_threads = 1
    #myisam_recover

    binlog-ignore-db = mysql
    binlog-ignore-db = information_schema
    binlog-ignore-db = performance_schema

    。。。。。


    通过read-only让从库只读访问

    参数说明：

    1，具有SUPER权限的用户可以更新，不受read-only参数影响，例如：root

    2，来自从服务器线程可以更新，不受影响，例如rep用户

     

    主从复制账户权限分配方案：

    1，主库和从库使用相同的用户，但是授予不同的权限

    　　主库对web用户授权 

    　　主库： GRANT SELECT ,INSERT,UPDATE,DELETE ON 'web'.*   to 'web'@'192.168.1.%' identified by 'oldboy123';

    　　从库则收回web用户的权限 REVOKE INSERT,UPDATE,DELETE ON ‘web’.* from  'web'@'192.168.1.%'  

    　　然后忽略授权库mysql库的同步

    　　binlog-ignore-db = mysql
    　　binlog-ignore-db = information_schema
    　　binlog-ignore-db = performance_schema

    　　replicate-ignore-db = mysql

    　　注意：这些参数=两边必须有空格

    2，主库和从库使用相同的用户，授予相同的权限（非 ALL权限）

     主库：GRANT SELECT ,INSERT,UPDATE,DELETE ON 'web'.*   to 'web'@'192.168.1.%' identified by 'oldboy123';

    　　从库设置read-only参数

    　　主库忽略授权同步

    　　binlog-ignore-db = mysql
    　　binlog-ignore-db = information_schema
    　　binlog-ignore-db = performance_schema

    　　replicate-ignore-db = mysql

    主从插入重复的错误或者不同步故障解决：

    主库增加user表 插入数据；从库正常同步。假如我删除了从库的user表，然后在主库user表中插入新数据。

    mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.125
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 968
               Relay_Log_File: relay-bin.000007
                Relay_Log_Pos: 886
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1146
                   Last_Error: Error 'Table 'hello.user' doesn't exist' on query. Default database: 'hello'. Query: 'INSERT INTO `user` 
    (`name`) VALUES ('9')'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 740
              Relay_Log_Space: 1410
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1146
               Last_SQL_Error: Error 'Table 'hello.user' doesn't exist' on query. Default database: 'hello'. Query: 'INSERT INTO `user`
     (`name`) VALUES ('9')'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

    这个时候怎么解决了？

     

    stop slave;//临时停止同步开关

set global sql_slave_skip_counter = 1;//将同步指针向下移一个，如果多次不同步，可以重复操作

start slave;

把主库的user表复制到从库；然后在主库user新增数据，查看从库，是ok的；

    　　

    Mysql从库记录binlog [级联]

    一主多从宕机从库切换主继续和从同步过程：

    主库master   donw机

    从库slave   down机

    Msyql官方同步架构图

    主主复制

    3307

    [mysqld]

    auto_increment_increment = 2

    auto_increment_offset    = 2

    log-bin = /data/3307/msyql-bin

    log-slave-updates

    expire_logs_days = 7

    [mysqld]

    3306

    [mysqld]

    auto_increment_increment = 2

    auto_increment_offset    = 1

    log-bin = /data/3306/msyql-bin

    log-slave-updates

    expire_logs_days = 7

    [mysqld]

    重启数据库

    /data/3306/msyql stop

    /data/3306/msyql start

    /data/3307/msyql stop

    /data/3307/msyql start

    导入数据库

    3307

    msyqldump -uroot -p'oldboy123'-S /data/3307/mysql.sock -A -B --master-data=1 -x --events > /opt/3307bak.sql

    3306

    msyqldump -uroot -p'oldboy123'-S /data/3306/mysql.sock  < /opt/3307bak.sql

    3306 执行以下

    change master to

    master_host="10.0.0.7,

    master_port=3307,

    master_user='rep',

    master_password='oldboy123;

    此处没有加binlog位置 因为备份库的时候 用的—master-data=1

    查看状态

    start slave;

    show slave statusG;

    在进行测试…

     
  • 相关阅读:
    (转)UIMenuController的使用，对UILabel拷贝以及定制菜单
    (转)ios多线程开发——NSOperation详解
    IOS custom statusBar 思路
    objectiveC的@property(atomic, retain)对引用计数的影响
    A Generic Particle IO Library
    RenderMan与CG生产流程简述
    Maya Mesh Relaxation Deformer
    个人黄金市场交易记录/Personal Gold Market Operation Record
    Implementation of TLRW ReadWrite Lock
    给想雇佣我的人说的话/Some words to somebody who want to hire me.
  • 原文地址：https://www.cnblogs.com/sunlong88/p/9164547.html
Copyright © 2020-2023  润新知