Master-slave replication of mysql learning

Keywords: MySQL master-slave Database SQL

This article uses MySQL 5.5 CentOS 6.5 64 bits

I. The Role of Master-Subordinate Reproduction

1. If the master server has problems, it can switch to the slave server quickly.

2. Applications with low real-time requirements or infrequent updates can perform query operations from the slave server to reduce the access pressure of the primary server. The read and write of data are separated to achieve the effect of load.

3. Data backup operations can be performed from the slave server to avoid the impact on the primary server during backup.

2. Building master-slave replication environment



1. Setting Authorized Users for Slave Servers in the Main Server

Create a user named user2 in the master server for the slave server with a password of 123

mysql> grant all on *.* to user2@ identified by "123";

Parametric interpretation:

grant:mysql authorization keyword

* *: All tables in all libraries


Check whether user authorization is successful:

mysql> show grants for user2@;


Test whether using user2 on slave server can login mysql on master server

[root@localhost tmp]# mysql -uuser2 -p123 test -h192.168.6.224;


2. Open the bin-log log log of the main server and set the value of server-id.

Modify my.cnf configuration file of the primary server:

#Open mysql bin-log log log
#The primary server is set to 1
server-id    = 1

3. Reset the bin-log log log: MySQL > reset master;.

Check the latest bin-log log status to see if it is in the starting position: MySQL > show master status;

mysql> show master status;
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000001 |      107 |              |                  |

4. Backing up the main database data

Backup data

b. Update bin-log log log

Here we use mysqldump to backup the data and use the - l -F parameter to set the read lock and update the bin-log log log directly when backing up the data.

   mysqldump -uroot -p111111 test -l -F > '/tmp/mysql_back/test.sql';

5. Send the backup data of the primary server to the slave server

  [root@localhost tmp]# scp mysql_back/test.sql

6. Reset bin-log logs on slave servers and use backup data in slave servers

  mysql> rester master;

  [root@localhost tmp]# mysql -uroot -p111111 test -v -f</tmp/mysql_back/test.sql;

7. Configure my.cnf parameters in slave server

Configuration slave server-id =2 (if there are multiple slave servers, there is a unique server-id)
    server-id = 2

b, # Open bin-log log log

Configuration of hosts, usernames, passwords, port numbers that need syn c hronization

#Configure hosts that need synchronization
 master-host     =
# The username the slave will use for authentication when connecting
# to the master - required
 master-user     =   user2
# The password the slave will authenticate with when connecting to
# the master - required
 master-password =   123
# The port the master is listening on.
# optional - defaults to 3306
 master-port     =  3306

d. Restart mysql for configuration files to take effect

  [root@localhost tmp]# service mysqld restart

If you can't restart the mysql server by changing the mode, you can use the following method

mysql> change master to master_host="",
mysql> slave start;

8. View slave status

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_User: user2
                  Master_Port: 3306
                Connect_Retry: 60    //Synchronize bin-log logs with master server every 60 seconds
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 107
               Relay_Log_File: localhost-relay-bin.000002  //slave server log
253 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes //These two lines are yes to indicate that the master-slave configuration was successful

Master_Log_File: Represents the name of the log file used for primary and standby synchronization on the host.

Read_Master_Log_Pos: Represents the location in the log file that was successfully synchronized last time.

If these two items do not match the values of File and Position previously seen on the primary server, they cannot be synchronized correctly.

Three, test

1. Add data to master server and view bin-log log status

mysql> insert into t1 values(13);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values(14);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(15);
Query OK, 1 row affected (0.01 sec)

mysql> show master status;
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000002 |      656 |              |                  |

2. View slave synchronization status

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_User: user2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 656
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 802
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Here you can see that the Postion of the primary server is equal to the Read_Master_Log_Pos value of the slave server and that the Slave_IO_Running and Slave_SQL_Running values are Yes. In this way, the master-slave configuration of mysql is successful.

4. Common commands of master-slave replication

1. start slave # Start replication threads

2. stop slave # stop replication threads

3. show slave status # View the status from the database

4. show master logs; # Check what bin-log logs are in the main database

5. Changemaster to # Dynamic Change to the Configuration of the Main Server

6. show process list; # view the running process from the database

Posted by dhie on Fri, 22 Mar 2019 19:36:54 -0700