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中也能看到这些库

posted @ 2020-01-14 16:59  陈小哥cw  阅读(80)  评论(0)  编辑  收藏  举报