MySQL主从复制配置

1. 将MySQL的安装包上传至mysql的主节点/root/目录下，使用命令解压

   tar -xvf mysql-5.7.21-1.el7.x86_64.rpm-bundle.tar
   

2. 使用命令将解压出来的MySQL RPM包传至从节点

3. 执行以下命令之前，需要先移除mariadb（分别在mysql主,从节点执行）

   yum -y remove mariadb*
   

4. 使用以下命令按顺序安装mysql组件服务及依赖（分别在mysql主,从节点执行）

   yum install -y net-tools perl 
   rpm -ivh mysql-community-common-5.7.21-1.el7.x86_64.rpm
   rpm -ivh mysql-community-libs-5.7.21-1.el7.x86_64.rpm
   rpm -ivh mysql-community-libs-compat-5.7.21-1.el7.x86_64.rpm
   rpm -ivh mysql-community-client-5.7.21-1.el7.x86_64.rpm
   rpm -ivh mysql-community-server-5.7.21-1.el7.x86_64.rpm
   

5. 创建mysql目录

   mkdir -p /data1/var/lib
   chmod -R 777 /data1/var/lib
   

6. 初始化MySQL

   修改/etc/my.cnf分别在mysql的主,从节点执行以下命令初始化MySQL

   mysqld --initialize --user=mysql
   

7. 主从节点启动MySQL并设置自启动

   systemctl start mysqld
   systemctl enable mysqld
   

8. 编辑MySQL配置信息

   MySQL配置参数详解:https://blog.csdn.net/wlzx120/article/details/52301383

   在MySQL主节点编辑/etc/my.cnf修改MySQL配置信息

   [mysqld]
   #
   # Remove leading # and set to the amount of RAM for the most important data
   # cache in MySQL. Start at 70% of total RAM for dedicated server, else 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
   #
   # Remove leading # to set options mainly useful for reporting servers.
   # The server defaults are faster for transactions and fast SELECTs.
   # Adjust sizes as needed, experiment to find the optimal values.
   # join_buffer_size = 128M
   # sort_buffer_size = 2M
   # read_rnd_buffer_size = 2M
   datadir=/data1/var/lib/mysql
   socket=/var/lib/mysql/mysql.sock
   user=mysql
   max_allowed_packet = 32M
   expire_logs_days = 7
   log-error=/var/log/mysqld.log
   pid-file=/var/run/mysqld/mysqld.pid
   log-bin=/var/lib/mysql/mysql-bin
   relay-log=/var/lib/mysql/relay-mysql
   server-id=1
   character-set-server=utf8
   collation-server=utf8_general_ci
   character_set_server=utf8
   collation_server=utf8_general_ci
   binlog_format = mixed
   # Disabling symbolic-links is recommended to prevent assorted security risks
   symbolic-links=0
   [mysqld_safe]
   log-error=/var/log/mysqld.log
   pid-file=/var/run/mysqld/mysqld.pid
   

   从Mysql的服务器同样修改以上配置，但配置中的server-id修改为2，这个值不能相同。

   [mysqld]
   #
   # Remove leading # and set to the amount of RAM for the most important data
   # cache in MySQL. Start at 70% of total RAM for dedicated server, else 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
   #
   # Remove leading # to set options mainly useful for reporting servers.
   # The server defaults are faster for transactions and fast SELECTs.
   # Adjust sizes as needed, experiment to find the optimal values.
   # join_buffer_size = 128M
   # sort_buffer_size = 2M
   # read_rnd_buffer_size = 2M
   datadir=/data1/var/lib/mysql
   socket=/var/lib/mysql/mysql.sock
   user=mysql
   max_allowed_packet = 32M
   expire_logs_days = 7
   log-error=/var/log/mysqld.log
   pid-file=/var/run/mysqld/mysqld.pid
   log-bin=/var/lib/mysql/mysql-bin
   relay-log=/var/lib/mysql/relay-mysql
   server-id=2
   character-set-server=utf8
   collation-server=utf8_general_ci
   character_set_server=utf8
   collation_server=utf8_general_ci
   binlog_format = mixed
   # Disabling symbolic-links is recommended to prevent assorted security risks
   symbolic-links=0
   [mysqld_safe]
   log-error=/var/log/mysqld.log
   pid-file=/var/run/mysqld/mysqld.pid
   

9. 重启主从节点MySQL服务

   systemctl restart mysqld
   

10. 进入MySQL之前获取MySQL初始化密码，需获取MySQL初始化密码进入MySQL之后需要修改MySQL密码

    cat /var/log/mysqld.log
    

    如果使用初始密码登录不了,可以采用MySQL免密登录来修改密码

    1、在/etc/my.cnf中修改,在[mysqld]最后添加：skip-grant-tables
    2、重启MySQL
    3、直接输入mysql进入
    4、use mysql
    5、修改账号密码：update user set authentication_string=password(“12345”) where user=“root”;
    6、退出，删掉/etc/my.cnf的skip-grant-tables
    7、重启
    8、用刚才修改的账号密码登录
    9、set global validate_password_policy=0;
    10、再次修改密码（不可省略）：alter user ‘root’@‘localhost’ identified by ‘admin12345’;
    11、重启MySQL

11. 分别进入主从节点MySQL使用命令修改密码

    alter user 'root'@'localhost' identified by '12345';
    

    如果出现密码不满足策略导致失败,可以使用以下命令设置

    set global validate_password_policy=0;

12. 进入MySQL主节点创建用于复制的账号

    GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'mysync'@'192.168.120.%' IDENTIFIED BY '12345';
    

13. 不断执行命令 SHOW MASTER STATUS;查看Position偏移量变化情况，直到偏移量不发生变化，记录偏移量。

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

14. 在从节点配置复制的主节点，需注意MASTER_LOG_FILE和MASTER_LOG_POS分别对应Master节点的File和Position值

    CHANGE MASTER TO MASTER_HOST='192.168.120.2', MASTER_USER='mysync', MASTER_PASSWORD='12345',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=715;
    

15. 在从节点开启slave并确认开启成功

    start slave;
    show slave status G
    
    mysql> show slave status G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.120.2
                      Master_User: mysync
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 715
                   Relay_Log_File: relay-mysql.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
    

16. 此时在主节点的MySQL创建数据库,从节点的MySQL中也能看到这些库