Detailed explanation of mysql master-slave configuration example

Why is it necessary to perform mysql master-slave configuration? What are its advantages? This article mainly shares with you the detailed explanation of mysql master-slave configuration examples, hoping to help everyone.

1: Implement server load balancing

Only the data update operation is implemented on the main server, and data query is not concerned. Query requests can be forwarded to multiple slave servers. Placing data updates and queries on different servers can improve data security, shorten the response time of applications, and improve system performance.

2: Realize off-site backup of data through replication

At any time, data backup is a very important link, and the mysql master-slave configuration can realize off-site backup very well , perfectly solved this problem.

3: Improve the availability of the database system

The database replication function realizes data synchronization between the master server and the slave server, increasing the availability of the database system. When a problem occurs on the master server, the database administrator can immediately let the slave server serve as the master server for data update and query services.

How does the slave copy the data on the master server?

1: When the data in the master changes, it will record the changes to the binary log (binary log).

2: The slave copies the master's log to its own relay log.

3: slave updates data based on log records.

Configure master-slave mysql

1: Configure master server

(1): Create a user 'link' on Master MySQL and authorize it Other slave servers can remotely access the master through the link user, read the binary log, and achieve data synchronization.

1. mysql> create user link;

2. ##mysql

   > GRANT REPLICATION SLAVE ON *.* TO 'link'@'%' IDENTIFIED BY 'mysql';

(2): Modify the mysql configuration file my.ini(windows) / my.cnf(linux)

If it is a Linux environment, modify the /etc/my.cnf file. If it is a Windows environment, find the my.ini file in the mysql installation directory. Under

[mysqld] add the following lines:

server-id=1   //给数据库服务的唯一标识,必须唯一  
log-bin=master-bin //开启二进制日志  
log-bin-index=master-bin.index

(3) Restart mysql, check the status, and output as follows

mysql> SHOW MASTER STATUS;  
+-------------------+----------+--------------+------------------+-------------------+  
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |  
+-------------------+----------+--------------+------------------+-------------------+  
| master-bin.000001 |      350 |              |                  |                   |  
+-------------------+----------+--------------+------------------+-------------------+  
1 row in set (0.00 sec)

2: Configure the slave server

(1) Modify the mysql configuration file and add the following lines:

server-id=2
relay-log-index=slave-relay-bin.index   
relay-log=slave-relay-bin //配置中继日志  
log_slave_updates = 1 //表示slave将复制事件写进自己的二进制日志  
#replicate-do-table=bison.user //库名.表名  用来指定只对数据库中的某张表做同步

(2) Restart mysql and connect to master

After restarting mysql, log in to mysql and use the CHANGE MASTER TO statement to connect to master

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.123',//主服务器ip  
    -> MASTER_USER='link',  
    -> MASTER_PASSWORD='mysql',  
    -> MASTER_LOG_FILE='mysql-bin.000001',//master服务器日志文件，主服务器mysql使用SHOW MASTER STATUS语句  
    -> MASTER_LOG_POS=0;//日志的开始位置

(3) Check whether the configuration is correct

mysql> SHOW SLAVE STATUS\G

2. 
   

   ##

   部分显示如下：  
   *************************** 1. row ***************************  
                Slave_IO_State:  
                   Master_Host: server1  
                   Master_User: repl  
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 4
                Relay_Log_File: mysql-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: mysql-bin.000001
             <span style="color:#ff0000;"><strong> Slave_IO_Running: No  
             Slave_SQL_Running: No</strong></span>

(4) Open slave
mysql
2. >

   start slave;

View configuration:

mysql> SHOW SLAVE STATUS\G  
部分显示如下：主要看Slave_IO_Running和Slave_SQL_Running  
*************************** 1. row ***************************  
               Slave_IO_State: Waiting for master to send event  
                  Master_Host: 192.168.1.234
                  Master_User: link  
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 350
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 548
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes  
            Slave_SQL_Running: Yes

ok, try creating a database on the main server.

Related recommendations:

Introduction to master-slave configuration in mysql server

Mysql series (15) mysql master-slave configuration

Mysql database master-slave configuration detailed explanation

The above is the detailed content of Detailed explanation of mysql master-slave configuration example. For more information, please follow other related articles on the PHP Chinese website!