Skip to main content

MySQL 5.7 Master-Slave

Server IPHost Role
192.168.10.2MySQL Master
192.168.10.3MySQL Slave

MySQL Master​

  1. Download the MySQL installation package

    wget https://pdpublic.mingdao.com/private-deployment/offline/common/mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz

  2. Extract to the installation directory

    tar -zxvf mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz
    mv mysql-5.7.42-linux-glibc2.12-x86_64 /usr/local/mysql

  3. Create a mysql user

    useradd -U -M -s /sbin/nologin mysql

  4. Create data and log directories and grant permissions

    mkdir -p /data/mysql/ /data/logs/mysql
    chown -R mysql:mysql /usr/local/mysql/ /data/mysql/ /data/logs

  5. Configure the systemd service file

    cat > /etc/systemd/system/mysql.service <<EOF
    [Unit]
    Description=MySQL Server
    Documentation=man:mysqld(8)
    Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
    After=network.target
    After=syslog.target
    [Service]
    User=mysql
    Group=mysql
    Type=forking
    PIDFile=/usr/local/mysql/mysqld.pid
    TimeoutSec=0
    PermissionsStartOnly=true
    ExecStart=/usr/local/mysql/bin/mysqld --daemonize --log-error=/data/logs/mysql/mysqld.log --datadir=/data/mysql --socket=/usr/local/mysql/mysql.sock --character-set-server=utf8 --pid-file=/usr/local/mysql/mysqld.pid --server-id=1 --log-bin=mysql-bin --max_connections=2000 --slow_query_log=1 --slow_query_log_file=/data/logs/mysql/mysql-slow.log
    LimitNOFILE=102400
    Restart=on-failure
    RestartPreventExitStatus=1
    PrivateTmp=false
    [Install]
    WantedBy=multi-user.target
    EOF

  6. Initialize

    /usr/local/mysql/bin/mysqld --initialize --datadir=/data/mysql/ --user=mysql --log-error=/data/logs/mysql/mysqld.log

  7. Start MySQL and enable it to start on boot

    systemctl daemon-reload
    systemctl enable mysql
    systemctl start mysql

  8. Change MySQL password

    /usr/local/mysql/bin/mysql -h127.0.0.1 -uroot -p$(grep 'temporary password' /data/logs/mysql/mysqld.log  | awk '{print $NF}')
    set PASSWORD = PASSWORD('123456');
    GRANT ALL ON *.* to root@'%' IDENTIFIED BY '123456';
    FLUSH PRIVILEGES;
    quit;
    • The root user password changed in the command is 123456. Please replace it during actual deployment.

MySQL Slave​

  1. Download the MySQL installation package

    wget https://pdpublic.mingdao.com/private-deployment/offline/common/mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz

  2. Extract to the installation directory

    tar -zxvf mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz
    mv mysql-5.7.42-linux-glibc2.12-x86_64 /usr/local/mysql

  3. Create a mysql user

    useradd -U -M -s /sbin/nologin mysql

  4. Create data and log directories and grant permissions

    mkdir -p /data/mysql/ /data/logs/mysql
    chown -R mysql:mysql /usr/local/mysql/ /data/mysql/ /data/logs

  5. Configure the systemd service file

    cat > /etc/systemd/system/mysql.service <<EOF
    [Unit]
    Description=MySQL Server
    Documentation=man:mysqld(8)
    Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
    After=network.target
    After=syslog.target
    [Service]
    User=mysql
    Group=mysql
    Type=forking
    PIDFile=/usr/local/mysql/mysqld.pid
    TimeoutSec=0
    PermissionsStartOnly=true
    ExecStart=/usr/local/mysql/bin/mysqld --daemonize --log-error=/data/logs/mysql/mysqld.log --datadir=/data/mysql --socket=/usr/local/mysql/mysql.sock --character-set-server=utf8 --pid-file=/usr/local/mysql/mysqld.pid --server-id=2 --log-bin=mysql-bin --max_connections=2000 --slow_query_log=1 --slow_query_log_file=/data/logs/mysql/mysql-slow.log
    LimitNOFILE=102400
    Restart=on-failure
    RestartPreventExitStatus=1
    PrivateTmp=false
    [Install]
    WantedBy=multi-user.target
    EOF

  6. Initialize

    /usr/local/mysql/bin/mysqld --initialize --datadir=/data/mysql/ --user=mysql --log-error=/data/logs/mysql/mysqld.log

  7. Start MySQL and enable it to start on boot

    systemctl daemon-reload
    systemctl enable mysql
    systemctl start mysql

  8. Change MySQL password

    /usr/local/mysql/bin/mysql -h127.0.0.1 -uroot -p$(grep 'temporary password' /data/logs/mysql/mysqld.log  | awk '{print $NF}')
    set PASSWORD = PASSWORD('123456');
    GRANT ALL ON *.* to root@'%' IDENTIFIED BY '123456';
    FLUSH PRIVILEGES;
    quit;
    • The root user password changed in the command is 123456. Please replace it during actual deployment.

Configure Master-Slave​

  1. Log in to the master node and configure the replication user

    /usr/local/mysql/bin/mysql --socket=/usr/local/mysql/mysql.sock -uroot -p123456
    grant replication slave on *.* to 'repl'@"%" identified by "123456";
    flush privileges;
    show master status;
    • The replication user created in the command is repl, with the password 123456. Please replace it during actual deployment.

  2. Log in to the slave node and configure master-slave

    /usr/local/mysql/bin/mysql --socket=/usr/local/mysql/mysql.sock -uroot -p123456
    change master to master_host="192.168.10.2",master_port=3306,master_user="repl",master_password="123456",master_log_file="mysql-bin.000001",master_log_pos=1265;
    start slave;
    • In the change master statement, replace the master_host address with the actual master server IP during deployment.
    • The values of master_log_file and master_log_pos in the change master statement should be based on the output of show master status; executed on the master node. Adjust them according to the actual deployment.

  3. Check replication status

    show slave status\G

    # In the output, both Slave_IO_Running and Slave_SQL_Running being Yes indicates normal replication.