Set up mysql master-slave replication in detail

Posted by skaforey on Thu, 20 Jan 2022 12:40:00 +0100

First, I will introduce some basic information about my environment:

System version: centOs

Mysql version: 5.7.22

Main library ip: 10.138.65.2
Slave library ip: 10.12.206.89

1. master configuration

1.1 profile settings

Configure the following information in the configuration file,

1. Configure the Master to use binary log file location based replication

2. Binary logging must be enabled and a unique server ID must be established, otherwise master-slave replication cannot be performed.

3. Set the format of binlog file

vim /etc/my.cnf

bind_address                    = 0.0.0.0			# Allow all machines to connect remotely

# binlog configuration

log_bin          = /home/local/mysql/mysql-bin.log
# A unique server ID must be set
server-id        = 1
binlog_format    = ROW

1.2 restart mysql to make the configuration effective

service mysql restart

1.3 create an account for copying on the master

First log in to the host mysql with the root account

mysql -h -uroot -p

Create the account and password of the slave database in the master database, and enable the slave database to connect. You can choose to create a different account for each slave database or use the same account to connect to the master database by granting replication permission to the account.

Here, I create a repl account for the slave ip and grant replication permission.

# Create a copy account

CREATE USER 'repl'@'10.12.206.89' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.12.206.89';

1.4 record the location of binlog on the master

It is executed in the host. This information is mainly used to configure where to start replication on the slave

mysql> show master status;

-- give the result as follows
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000012 |     1176 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

1.5 backup data on master

Divide all database files on the host into dbdump SQL files are mainly used to recover files.

mysqldump -u root -p --all-databases --master-data > dbdump.sql

1.6 synchronize the backup files on the master to the slave machine

scp dbdump.sql 10.12.206.89:/tmp

2. slave configuration

2.1 configuration file

Also in / etc / my The following configurations are configured under CNF, mainly to set the ip address monitored from the server and set the server id. note that this id must be different from the host id.

To enable binlog configuration, set the file format and file path of binlog

# bin log configuration
bind-address = 10.12.206.89
log_bin          = /home/work/local/mysql/mysql-bin.log
server-id        = 2
binlog_format    = ROW

2.2 restart the server

service mysql restart

2.3 files backed up before importing

Of course, this step is not necessary. It can be backed up or not.

mysql -u root -p < /tmp/dbdump.sql

2.4 establish communication between slave database and master database

1. First, log in to the mysql server as root

mysql -u root -p

2. Pause backup

stop salve;

3. Create communication between slave library and master library

The main configuration information is as follows: main database id, port, backup account, password, and start bin log file of main database

CHANGE MASTER TO
MASTER_HOST='10.138.65.2',
MASTER_PORT=8306,
MASTER_USER='repl',
MASTER_PASSWORD='slavepass',
MASTER_LOG_FILE='mysql-bin.000012',	
MASTER_LOG_POS=1176;

4. Start backup

start slave;

2.5 viewing backup status

mysql>  show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.138.65.2
                  Master_User: repl
                  Master_Port: 8306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 2698
               Relay_Log_File: public-relay-bin.000012
                Relay_Log_Pos: 2911
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
	..................
  Seconds_Behind_Master: 0
  ..................

How to judge whether the backup is successfully established?

In fact, it mainly depends on three places

1,Slave_IO_Running: whether the I/O thread reading the binary log of the main program is running [if yes, the establishment is successful]

2,Slave_SQL_Running: whether the SQL thread executing the reading of binary log events in the master server is running [if yes, the establishment is successful]

3,Seconds_Behind_Master: 0 [indicates no delay. If it is null, it is generally not established]

Detect whether master-slave replication can be synchronized

1. Establish a database in the master and slave databases

mysql> CREATE DATABASE `test`;
mysql> use test

2. Create a test table

Create a database for testing

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3. Log in to the slave server and observe whether the data table is synchronized

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

summary

Here, the master-slave server has been built successfully. If you encounter any problems in the test, don't worry. Just query the data. Generally, people before the problem have stepped on the pit.

Programmer Think