One, foreword

As the application service data continues to increase, the application response speed decreases. During the detection process, it is not difficult to find that most of the requests are query operations.

At this point, we can expand the database into a master-slave replication mode, separate the read and write operations, multiple databases share requests, so as to reduce the single library access pressure, and then the application is optimized. A 328-page MySQLPDF document is organized

This test uses two VMS: IP: 192.168.2.21 (active) IP: 192.168.2.22 (secondary)

Principle of master-slave replication

The synchronization operation is implemented by three threads, and the basic steps are as follows:

The master server logs updates to binary logs (logging is called binary log events) -- the master library thread; Slave libraries copy binary logs from the master library to local relay logs -- slave LIBRARY I/O threads; Read events from the relay log from the library and replace them into data -- from the library SQL thread.Copy the code

Configure the master library

# 3.1 Create a user

For security, prepare to create a new user for the secondary library to connect to the master library.

Create user 'repl'@'%' identified by 'repl'; Grant replication slave,replication client on *.* to 'repl'@'%' identified by 'repl';Copy the code

# 3.2 Modifying the configuration file

1) add vim /etc/my.cnf to [mysqld]

log-bin         = mysql-bin
log-bin-index   = mysql-bin.index
binlog_format   = mixed
server-id       = 21
sync-binlog     = 1
character-set-server = utf8
Copy the code

2) Save the file and restart the master library:

service mysqld restart
Copy the code

Configuration description:

Log-bin: sets the basic name of the binary log file. Log-bin-index: sets the file name of the binary log index. -STATEMENT: STATEMENT replication -row: ROW replication -mixed: MIXED replication. The default option is server-id: unique ID of the server. The default value is 1, and the last part of the IP address is recommended. Sync-binlog: The default value is 0. To prevent data loss, set this parameter to 1. This parameter is used to forcibly synchronize binary logs to disks every time a transaction is committed.Copy the code

Backup primary database data

If the primary and secondary databases are installed and the data is consistent, run the show master status command to view the log coordinates.

If the main library can be stopped, copy all database files directly.

If the primary repository is an online production repository, you can use mysqldump to back up data because it is available to all storage engines.

1) Set read locks on all tables in order to obtain a consistent snapshot:

flush tables with read lock;
Copy the code

2) Get the coordinates of binary log:

 show master status;
Copy the code

Return result:

+------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB |  Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 120 | | | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

3) Backup data:

Mysqldump-uroot-ptiger -- all-database-e --single-transaction --flush-logs --max_allowed_packet=1048576 --net_buffer_length=16384 > Mysqldump-uroot -- all-database-e-l --flush-logs --max_allowed_packet=1048576 --net_buffer_length=16384 > mysqldump-uroot -- all-database-e-l --flush-logs --max_allowed_packet=1048576 --net_buffer_length=16384 > /data/all_db. SQL 1 row in set (0.00 SEC)Copy the code

4) restore primary database write operation:

unlock tables;
Copy the code

Configure the slave library

# 4.1 Modifying the configuration file

1) add vim /etc/my.cnf to [mysqld]

log-bin             = mysql-bin
binlog_format       = mixed
log-slave-updates   = 0
server-id           = 22
relay-log           = mysql-relay-bin
relay-log-index     = mysql-relay-bin.index
read-only           = 1
slave_net_timeout   = 10
Copy the code

2) Save file and restart secondary library:

service mysqld restart
Copy the code

Configuration description:

Log-slave-updates: controls whether the updates on the slave are written to binary logs. The default value is 0. If slave serves only as the slave server, you do not need to enable it. If slave serves as the master of another server, you need to enable this function together with log-bin and binlog-format. In this way, slave reads logs from the master library and rewrites logs to its own binary logs. Relay-log: indicates the basic name of a relay log file. Relay-log-index: indicates the file name of the relay log index. Read-only: Set the slave to read-only, but users with super permission can still write. Slave_net_timeout: Set the network timeout period, that is, how long it takes to test whether the master and slave are connected. The default value is 3600 seconds, that is, one hour. This value is too large in the production environment, we changed it to 10 seconds.Copy the code

# 4.2 Importing backup data

If the backup is not performed in Step 3.3, skip this step.

mysql -uroot -p < /data/all_db.sql
Copy the code

# 4.3 Unify the coordinates of binary logs

According to the coordinates obtained in step 3.3, unified into the slave library:

Change master to master_host='192.168.2.21', master_user='repl', master_password='repl', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=120;Copy the code

Note: The newly created account is used here.

Start primary/secondary replication

1) Start the slave thread:

start slave;
Copy the code

2) Check the status of the secondary server replication function:

show slave status\G;
Copy the code

Return result:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
Copy the code

Only partial results are posted here.

Results:

Slave_IO_Running: this process reads binlog logs from the master and writes trunk logs on the slave. Slave_SQL_Running: This process is responsible for reading and executing the binlog in the trunk log.Copy the code

The status of both processes must be YES. If either process is NO, the replication will stop.

If Relay_Master_Log_File = Master_Log_File and Read_Master_Log_Pos = Exec_Master_Log_Pos, the slave and master are fully synchronized.

Five, validation,

Use a simple example:

Create a database named mysql_test in the primary library. If the synchronization is successful, you can query the database named mysql_test in the secondary library.

Vi. Reference materials

  • MySQL MySQLPDF website to sort out a 328 – page document dev.mysql.com/doc/refman/…