MySQL master-slave replication (operation and maintenance technology exchange group: 926402931, welcome to exchange together.)

Keywords: Linux MySQL Database SQL socket

Catalog

1. Environmental planning

host name role IP address
db01 MySQL main library 10.0.0.51
db02 MySQL slave Library 10.0.0.52

2. Enable binlog function for all MySQL to ensure different server IDs for all MySQL

Main storehouse:
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/application/mysql/tmp/mysql.sock
port=3306
server_id=10
log-error=/var/log/mysql.log
log-bin=/data/binlog/mysql-bin
sync_binlog=1
binlog_format=row

//From library:
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/application/mysql/tmp/mysql.sock
port=3306
server_id=11
log-error=/var/log/mysql.log
log-bin=/data/binlog/my-bin
sync_binlog=1
binlog_format=row

3. The master database authorizes the master-slave replication user

mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by '123456';

4. The main database makes the database data fully available, and then pushes the backup file to the slave database

[root@db01 ~]# mysqldump -uroot -p123  -A -B -R --master-data=2 --single-transaction |gzip >/backup/full_$(date +%F).sql.gz
[root@db01 ~]# scp /backup/full_2020-04-09.sql.gz  root@10.0.0.52:/backup

5. Restore backup files from the database to the database

[root@db02 ~]# gunzip /backup/full_2020-04-09.sql.gz
mysql> source /backup/full_2020-04-09.sql
mysql> show databases;

6. Find the binlog location from the database

[root@db02 ~]# sed -n '22p' /backup/full_2020-04-09.sql

7. Configure master-slave replication parameters from the slave database

mysql> change master to
    -> master_host='10.0.0.51',    
    -> master_port=3306,
    -> master_user='rep',  
    -> master_password='123456',
    -> master_log_file='mysql-bin.000008',  
    -> master_log_pos=1100;
mysql> start slave;
mysql> show slave status\G

8. The master database creates a database, and then verifies whether the data is synchronized with the slave database

db01: 
mysql> create database test1;

db02: 
mysql> show databases;

Posted by jameslloyd on Fri, 10 Apr 2020 08:13:01 -0700