• mysql 主库有数据通过锁库做主从


    master@localhost[(none)]> grant replication slave on *.* to 'repl'@'192.168.1.177' identified by 'xxx';  
    Query OK, 0 rows affected (0.01 sec)  
      
    --全局读锁  
    master@localhost[(none)]> flush tables with read lock;  
    Query OK, 0 rows affected (0.02 sec)  
      
    master@localhost[(none)]> system pwd  
    /data/inst3406  
      
    --获取master binlog位置  
    master@localhost[(none)]> show master status;  
    +--------------------+----------+--------------+------------------+-------------------+  
    | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |  
    +--------------------+----------+--------------+------------------+-------------------+  
    | inst3406bin.000001 |     2169 |              |                  |                   |  
    +--------------------+----------+--------------+------------------+-------------------+  
    1 row in set (0.00 sec)  
      
    --使用mysqldump导出实例  
    master@localhost[(none)]> system mysqldump -uroot -pxxx -S /tmp/mysql3406.sock --routines --all-databases --opt >alldb.sql  
      
    master@localhost[(none)]> system ls  
    alldb.sql  data3406    
      
    --解锁  
    master@localhost[(none)]> unlock tables;  
      
    master@localhost[(none)]> exit  
      
    --从库上面导入dump  
    [mysql@app inst3406]$ mysql -uroot -pxxx -S /tmp/mysql3506.sock <alldb.sql  
      
    --从库上设置主库的相关信息(host,port等等)  
    [mysql@app inst3506]$ mysqls  
    slave@localhost[(none)]> change master to  
        -> MASTER_HOST='192.168.1.177',    
        -> MASTER_USER='repl',    
        -> MASTER_PASSWORD='xxx',    
        -> MASTER_PORT=3406,    
        -> MASTER_LOG_FILE='inst3406bin.000001',    
        -> MASTER_LOG_POS=2169;    
    Query OK, 0 rows affected, 2 warnings (0.01 sec)  
      
    --启动slave  
    slave@localhost[(none)]> start slave;  

    部分从库

    master@localhost[(none)]> grant replication slave on *.* to 'repl'@'192.168.1.177' identified by 'xxx';  
    Query OK, 0 rows affected (0.01 sec)  
      
    --全局读锁  
    master@localhost[(none)]> flush tables with read lock;  
    Query OK, 0 rows affected (0.02 sec)  
      
    master@localhost[(none)]> system pwd  
    /data/inst3406  
      
    --获取master binlog位置  
    master@localhost[(none)]> show master status;  
    +--------------------+----------+--------------+------------------+-------------------+  
    | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |  
    +--------------------+----------+--------------+------------------+-------------------+  
    | inst3406bin.000001 |     2169 |              |                  |                   |  
    +--------------------+----------+--------------+------------------+-------------------+  
    1 row in set (0.00 sec)  
      
    --使用mysqldump导出实例  
    master@localhost[(none)]> system mysqldump -uroot -pxxx -S /tmp/mysql3406.sock --routines --all-databases --opt >alldb.sql  
      
    master@localhost[(none)]> system ls  
    alldb.sql  data3406    
      
    --解锁  
    master@localhost[(none)]> unlock tables;  
      
    master@localhost[(none)]> exit  
      
    --从库上面导入dump  
    [mysql@app inst3406]$ mysql -uroot -pxxx -S /tmp/mysql3506.sock <alldb.sql  
      
    --从库上设置主库的相关信息(host,port等等)  
    [mysql@app inst3506]$ mysqls  
    slave@localhost[(none)]> change master to  
        -> MASTER_HOST='192.168.1.177',    
        -> MASTER_USER='repl',    
        -> MASTER_PASSWORD='xxx',    
        -> MASTER_PORT=3406,    
        -> MASTER_LOG_FILE='inst3406bin.000001',    
        -> MASTER_LOG_POS=2169;    
    Query OK, 0 rows affected, 2 warnings (0.01 sec)  
      
    --启动slave  
    slave@localhost[(none)]> start slave;  
  • 相关阅读:
    git执行sudo git pull origin xxx 提示 AutoMatic merge failed;fix conflicts and then commit the result
    mysql 两表关联更新
    宝塔上的redis 性能调整的requirepass 密码与配置文件的 requirepass 不一致
    php 默认文档为index.htm 或者其他
    layerui 弹窗里出现下拉框select
    微信小程序文字超出显示省略号
    MySQL用存储过程创建日期字典表
    书单
    手动更新表记录时自动更新 UPDATE_DATE
    Nginx $proxy_add_x_forwarded_for 实现多租户判断
  • 原文地址:https://www.cnblogs.com/mikeluwen/p/7850122.html
Copyright © 2020-2023  润新知