mysql - 主从(単主单从)复制配置

小咪咪 2023-01-03 04:51 110阅读 0赞

17.1.1 How to Set Up Replication：https://dev.mysql.com/doc/refman/5.6/en/replication-howto.html
    
    
    【主库 - master节点配置】
    
    vim /usr/local/mysql/my.cnf
    
    # 常规通用配置
    basedir=/usr/local/mysql
    datadir=/usr/local/mysql/data
    port=3306
    socket=/tmp/mysql.sock
    
    # 添加如下主节点配置
    server_id=1
    log_bin=master_log
    # 设置不要复制的数据库(可设置多个)
    binlog-ignore-db=mysql
    binlog-ignore-db=information_schema
    #设置需要复制的数据库
    binlog-do-db=需要复制的主数据库名字
    #设置logbin格式
    binlog_format=STATEMENT
    
    # 创建用户：https://dev.mysql.com/doc/refman/5.6/en/replication-howto-repuser.html
    
    # 命令格式：
    mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';
    
    # GRANT ALL PRIVILEGES ON *.* TO
    
    # mysql -u root -p
    mysql> set global validate_password_policy=0;
    ERROR 1193 (HY000): Unknown system variable 'validate_password_policy'
    mysql> SHOW VARIABLES LIKE 'validate_password%';
    Empty set (0.00 sec)
    
    mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like 'validate%';
    Empty set (0.82 sec)
    
    mysql> install plugin validate_password soname 'validate_password.so';
    mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like 'validate%';
    +-------------------+---------------+
    | plugin_name       | plugin_status |
    +-------------------+---------------+
    | validate_password | ACTIVE        |
    +-------------------+---------------+
    1 row in set (0.00 sec)
    mysql> set global validate_password_policy=0;
    mysql> set global validate_password_length=4; # 密码最小长度
    
    mysql> use mysql;
    mysql> select User,Host,Password from user;
    
    # 保证主节点可以通过指定用户(比如root用户)远程访问！！！
    mysql> grant all privileges on *.* to 'root'@'%' identified by 'root123' with grant option;
    # 为主从复制创建远程进行数据复制的专有用户
    mysql> grant all privileges on *.* to 'slave'@'192.168.199.212' identified by 'slave' with grant option;
    mysql> flush privileges;
    
    注：在mysql中，为不存在的用户授权，就是同步创建用户并授权。
    
    mysql> show master status;
    Empty set (0.00 sec)      -- 主从配置是需要重启mysql服务的，否则配置不生效
    
    mysql> exit
    
    # 重启mysql服务
    # service mysql restart
    Shutting down MySQL......... SUCCESS!
    Starting MySQL...................................................................................................... SUCCESS!
    # mysql -u root -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1
    Server version: 5.6.50-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show master status;
    +-------------------+----------+--------------+------------------+-------------------+
    | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +-------------------+----------+--------------+------------------+-------------------+
    | master_log.000001 |      120 |              |                  |                   |
    +-------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    
    mysql> exit
    
    二进制文件位置：/usr/local/mysql/data/master_log.000001
    
    ------------------------------------------------------------------------------------------------------------------------
    
    【从库 - slave节点配置】
    
    
    # vim /usr/local/mysql/my.cnf
    
    添加如下配置：
    [mysqld]
    
    # 重要的数据节点从70%的内存开始配置，其他节点的给到10%的内存即可。
    # innodb_buffer_pool_size = 128M
    
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
    
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M
    
    # 常规通用配置
    basedir=/usr/local/mysql
    datadir=/usr/local/mysql/data
    port=3306
    # 主从复制配置
    server_id=2
    socket=/tmp/mysql.sock
    #启用中继日志
    relay-log=mysql-relay
    # 保存退出
    
    # 重启服务
    # service mysql restart
    
    # mysql -u root -p
    Enter password:
    mysql> stop slave;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    配置主从命令格式：
    change master to master_host='192.168.199.211',
           master_user='访问master使用的用户名',
           master_password='访问master使用的密码',
           master_log_file='master_log.000001',
           master_log_pos='具体数值';
    
    mysql> change master to master_host='192.168.199.211',master_user='slave',master_password='slave',master_log_file='master_log.000001';
    Query OK, 0 rows affected, 2 warnings (0.13 sec)
    
    或者：
    mysql> change master to master_host='192.168.199.211',master_user='slave',master_password='slave',master_log_file='master_log.000001',master_log_pos='具体数值';
    
    mysql> start slave;
    Query OK, 0 rows affected (0.12 sec)
    
    mysql> show slave status \G;
    
    
    ------------------------------------------------------------------------------------------------------------------------ 【测试主从同步 - 主节点操作】
    
    1.创建库+创建表+插入一条新的记录
    
    # create database `test_repl`;
    # use test_repl;
    # 建表
    create table `test_copy` (
      `id` varchar(64) NOT NULL,
      `test_name` varchar(255) DEFAULT NULL COMMENT '文件在FastDFS中的组名',
      `user_id` int(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    # 新增数据
    insert into `test_copy`(id,test_name,user_id) values('123456','test-jack',1);
    
    # 到从库进行查询
    select * from test_copy;
    
    到此主从复制配置完成！！！
    
    
    
    https://blog.csdn.net/thjnemo/article/details/41696395