introduce

Why master slave replication

  • Do hot backup for data

  • If the primary database is down, you can quickly switch the service system to the secondary database to avoid data loss.

  • The I/O access frequency is too high for a single machine. In this case, multiple libraries are used to reduce the DISK I/O access frequency and improve the I/O performance of a single machine. If the database reads and writes are performed on the same database server, the performance of the business system deteriorates.

  • In the business of complex system, there is a scene, there is a SQL statement to lock table, temporarily cannot use the reading service, then it is affect the business operation, using the master-slave replication, let the main library is responsible for writing, read from the library is responsible for, in this way, even if the main library in the lock table, by reading from the repository can also ensure the normal operation of business. Reduce the load on the primary database through master slave replication (read/write separation).

The principle of

  • Step 1: Update events (UPDATE, INSERT, DELETE) of the master db are written to the binlog
  • Step 2: Initiate a connection from the library to connect to the master library
  • Step 3: The master library creates a binlog dump thread and sends the contents of the binlog to the slave library
  • Step 4: After starting from the slave library, create an I/O thread to read the binlog content from the master library and write it to the relay log
  • Step 5: An SQL thread is also created to read from the relay log, execute the read update event from Exec_Master_Log_Pos, and write the update to the SLAVE DB

Note: The above explanation is to explain what is done in each step, the entire mysql master-slave replication is asynchronous and not performed in accordance with the above steps.

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; The slave library copies the binary logs of the master library to a local relay log (relay)log) -- I/O threads from the library; Read events from the relay log from the library and replace them into data -- from the library SQL thread.Copy the code

A master-slave replication

The preparatory work

  • The versions of the primary and secondary databases should be the same
  • Data in the primary and secondary databases is consistent
  • Primary database: 10.168.1.248 Secondary database: 10.168.1.249

Configure the Master library

Modify MySQL configuration

Find the main database configuration file my.cnf, mine in /etc/my.cnf

vi /etc/my.cnf
Copy the code

Insert in the [mysqld] section

[mysqld]
log-bin=mysql-bin Enable binary logging
server-id=1 # set server-id to unique
Copy the code

** Configuration description **

  1. Log-bin: sets the basic name of the binary log file.
  2. Log-bin-index: sets the file name of the binary log index.
  3. Binlog_format: controls the binary log format and therefore the replication type. Three optional values
  4. -STATEMENT: STATEMENT replication
  5. -ROW: indicates ROW replication
  6. -MIXED: MIXED copy, default option
  7. Server-id: indicates the unique ID of the server. The default value is 1 and the last part of the IP address is recommended.
  8. 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.

Create a user

Restart the service and create a user for synchronization

Open the mysql session shell

mysql -uroot -p
Copy the code

Create user and authorize: user: repl Password: repl’

# create user
create user 'repl'@'10.168.1.249' identified by 'repl';

Grant replication and client access only
grant replication slave on *.* to 'repl'@'10.168.1.249';# assign permissions
Copy the code

Checking master Status

Record binary file name (mysql-bin.000001) and location (154):

mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB |  Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 1416 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 rowin set (0.00 sec)
Copy the code

Configure the Slave library

Modify MySQL configuration

Find the main database configuration file my.cnf, mine in /etc/my.cnf

vi /etc/my.cnf
Copy the code

Insert in the [mysqld] section

[mysqld]
server-id=2 # set server-id to unique
Copy the code

Execute synchronous SQL statements

Restart mysql, open mysql session, execute synchronous SQL statement (need primary server host name, login credentials, binary name and location) :

mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.168.1.248',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl',
    -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=1416; Query OK, 0 rows affected, 2 warnings (0.02sec)Copy the code

Example Start the slave synchronization process

mysql>start slave;
Copy the code

Viewing slave Status

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting forMaster to send event Master_Host: 10.168.1.248 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1917 Relay_Log_File: sl249-relay-bin.000003 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: ......Copy the code

If Slave_IO_Running and Slave_SQL_Running are both set to YES, the synchronization is successful.

validation

  • The primary server

    mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys  | +--------------------+ 4 rowsin set (0.00 sec)

mysql> create database mysql_test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_test         |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
Copy the code

  • From the server

    mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys  | +--------------------+ 4 rowsin set(0.00 SEC) mysql> database; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mysql_test | | performance_schema | | sys | +--------------------+ 5 rowsin set (0.00 sec)
Copy the code