Detailed introduction to the source code and replication principle of mysql master-slave configuration

This article mainly talks about the implementation of mysql's master-slave replication and read-write separation. The following is the source code of mysql's master-slave replication. How to install the mysql database, I won’t go into it here, only its master-slave replication, the steps are as follows:

1. The master and slave servers perform the following operations respectively:
1.1. The versions are consistent
1.2. Initialize the table and start mysql in the background
1.3. Modify the root password

2. Modify the main server master:
#vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[Must] Enable Binary log
Server-id=222 //[Required] The unique ID of the server, the default is 1, generally take the last segment of the IP

3. Modify the slave server slave:
#vi /etc/my.cnf
[MySQLD]
Log-bin = MySQL-bin // [No must] enable binary logs
Server-id = 226 // The default is 1, generally take the last segment of the IP

4. Restart the mysql of the two servers
/etc/init.d/mysql restart

5. Create an account on the main server and authorize slave:
#/usr/local/mysql/bin/mysql -uroot -pmttang
mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@ '%' identified by 'q123456'; // Generally, the root account is not used. "%" means that all clients can connect. As long as the account and password are correct, the specific client IP can be used instead, such as 192.168.145.226, to strengthen Safety.

6. Log in to the mysql of the main server and query the status of the master
mysql>show master status;
-------------- ---- ---------- -------------- ------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
------------------ ---------- ---------- --- ------------------
| mysql-bin.000004 | 308 | ------ ---------- -------------- ------------------
1 row in set (0.00 sec)
Note: Do not operate the main server MYSQL after performing this step to prevent the main server status value from changing


7. Configure the slave server Slave: mysql>change master to master_host='192.168.145.222',master_user='mysync',master_password='q123456',
master_log_file='mysql-bin.000004',master_log_pos=308; //Be careful not to Disconnected, there are no single quotes before and after the 308 number.

Mysql>start slave; //Start slave server replication function


8. Check slave server replication function status:
mysql> show slave status \G

*************************** 1. row *************** ****************

                                                                                                                                        ’ ’ s ’ s ’ ’ s ’ ’ ’ ’ ’ ’ ’ ’ ’ to ’ Slave_IO_State: Waiting for master to send event
            – ’ Master_Host’s: 192.168.2-222 out-- Account name, try to avoid using root
            Master_Port: 3306   //Database port, some versions do not have this line
            Connect_Retry: 60
                   --         Master_Log_File: mysql-bin.000004
              Read _Master_Log_Pos: 600 //#Synchronous reading Get the position of the binary log, which is greater than or equal to Exec_Master_Log_Pos
Relay_Log_File: ddte-relay-bin.000003
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.0000 04
            Slave_IO_Running: Yes   //This status must be YES
Slave_SQL_Running: Yes //This status must be YES
 A NO is an error).

In the above operation process, the configuration of the master and slave servers is completed.


9. Master-slave server test:


Master server Mysql, establish a database, and create a table in this database to insert a piece of data: mysql> ; create database hi_db;
  Query OK, 1 row affected (0.00 sec)

  mysql> use hi_db;
  Database changed

  mysql>  create table hi_tb(id int(3),name char(10));
  Query OK, 0 rows affected (0.00 sec)
 
  mysql> insert into hi_tb values(001,'bobu');
  Query OK, 1 row affected (0.00 sec)

  mysql> show databases;
   --------------------
   | Database           |
   --------------------
   | information_schema |
   | hi_db                |
   | mysql                |
   | test                 |
   --------------------
   4 rows in set (0.00 sec)

从服务器Mysql查询：

   mysql> show databases;

   --------------------
   | Database               |
   --------------------
   | information_schema |
   | hi_db                 |       //I'M here，大家看到了吧
   | mysql                 |
   | test          |

    --------------------
   4 rows in set (0.00 sec)

   mysql> use hi_db
   Database changed
   mysql> select * from hi_tb;           //查看主服务器上新增的具体数据
   ------ ------
   | id   | name |
   ------ ------
   |    1 | bobu |
   ------ ------
   1 row in set (0.00 sec)
 

10、完成：
    编写一shell脚本，用nagios监控slave的两个yes（Slave_IO及Slave_SQL进程），如发现只有一个或零个yes，就表明主从有问题了，发短信警报吧。

相关推荐：

MySQL数据管理之备份恢复案例解析视频教程

详解MySQL实现主从复制过程-mysql教程

mysql教程：MySQL如何实现主从复制过程的示例详解

The above is the detailed content of Detailed introduction to the source code and replication principle of mysql master-slave configuration. For more information, please follow other related articles on the PHP Chinese website!