Preface

Anyone who can read this article must know the power and benefits of the separation of reading and writing from the database, so let's cut the crap and get right to the point.

For convenience, you can configure database remote connection privileges first

use mysql ;
update user set host = '%' where user = 'root';

As shown in the figure:

The following is the official content

Firstly, the mysql configuration file is modified to let the master of the main server record the database operation log to the Binary log, and the i/o thread is opened from the server to synchronize the operation of the binary log record to the relay log (which exists in the cache of the server). In addition, the sql thread executes the operation of the relay log record from the slave server.

1. Open mysql database configuration file

vim /etc/my.cnf

2. Configure to open Binary log on the master server, mainly by adding:

server-id=1
log-bin=master-bin
log-bin-index=master-bin.index

As shown in the figure:

3. Restart mysql service after saving

service mysql restart

4. Check the configuration effect, enter the main database and execute

mysql> SHOW MASTER STATUS;

As shown in the figure:

Keep in mind the data circled in the diagram. Configuration is useful from the server.

5. Configure my.cnf from the slave server

Under the [mysqld] node, add:

server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

The server-id here is optionally filled in, but can not be duplicated with the main database

As shown in the figure:

mysql service needs to be restarted after configuration is completed

service mysql restart

6. Implementing the association between two databases

(1) Execution on the primary server

mysql> create user REPL;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'REPL'@'from server ip' IDENTIFIED BY 'password';
mysql> flush privileges;

These three lines of code mean:
1. Create a user REPL
2. Give it REPLICATION SLAVE permission (replication). This permission is for all tables in the main library. Slave server ip is from the server's ip address, and password is the REPL user password.
3. Refresh MySQL privileges to memory; because all user privileges are loaded into memory after MySQL is started; the operation of updating privileges will not be refreshed to memory immediately, and will not take effect until the next start; flush privileges allow MySQL to reload privileges, so that it can take effect immediately.

(2) Executing from the slave server

change master to master_host='master server ip',master_port=3306,master_user='REPL',master_password='password',master_log_file='master-bin.000009',master_log_pos=467;

Start the service after completion

start slave;

Then execute the command (be careful not to add a sign at the end of the command, or report an error)

show slave status \G

As shown in the figure:

As shown in the figure, this has been successfully implemented and we can test it.

Execution on primary server

create database test_2;

As shown in the figure:

Then it goes from the server to see if it's synchronized and executed.

show databases;

As shown in the figure:

As you can see from Tuli, synchronization has come, and other additions, deletions, checks and modifications will also be synchronized.

7. Apply to THINKPHP5 Project

Modification of database configuration file to TP project

<?php
return [
    // Database type
    'type'            => 'mysql',
    // server address
    'hostname'        => '192.168.204.30,192.168.204.31',
    // Database name
    'database'        => 'ireadgo',
    // User name
    'username'        => 'root',
    // Password
    'password'        => 'root',
    // port
    'hostport'        => '3306',
    // Connect dsn
    'dsn'             => '',
    // Database Connection Parameters
    'params'          => [],
    // Database coding defaults to utf8
    'charset'         => 'utf8',
    // Database table prefix
    'prefix'          => 'iread_',
    // Database Debugging Mode
    'debug'           => true,
    // Database deployment: 0 centralized (single server), 1 distributed (master-slave server)
    'deploy'          => 1,
    // Is it effective to separate master from slave in database reading and writing
    'rw_separate'     => true,
    // Number of primary servers after read-write separation
    'master_num'      => 1,
    // Specify slave server serial number
    'slave_no'        => '',
    // Are fields strictly checked for existence?
    'fields_strict'   => true,
    // Data Set Return Type
    'resultset_type'  => 'array',
    // Autowrite timestamp fields
    'auto_timestamp'  => 'datetime',
    // Default time format after time field is taken out
    'datetime_format' => 'Y-m-d H:i:s',
    // Do you need to perform SQL performance analysis?
    'sql_explain'     => false,
];

This concludes the course.

Attached Common Errors

Go to the main server and view MASTER_LOG_FILE and MASTER_LOG_POS

SHOW MASTER STATUS;

Then it executes from the server.

stop slave;
CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000009',MASTER_LOG_POS=2246;
start slave;
show slave status \G

It turned out that everything was back to normal.
As shown in the figure: