1. The background

This section describes how to set the primary/secondary synchronization of MySQL.

2. Set the primary/secondary synchronization of MySQL

Briefly, there are three aspects:

  • 1. Enable binary logging for the primary database
  • 2, set the configuration parameter from the secondary database to the primary database (change master to directive)
  • 3, Start slave (use the start slave command)

Specific operation:

Step 1: Enable binary logging and specify server-id — (configure primary database)

Edit the /etc/mysql.my.cnf file and add the following Settings:

[mysqld] log-bin=mysql-bin server-id=1Copy the code
  • Note: under the [mysqld] section.

After the modification is complete, restart the system and check whether the function is successfully enabled (see the extension of this article).

Step 2: Create a mysql user for synchronization and grant REPLICATION SLAVE privileges — (configure the master database)

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> FLUSH PRIVILEGES;
Copy the code

Step 3: Enable binary logging and specify server-id — (configure secondary database)

Edit the /etc/mysql.my.cnf file and add the following Settings:

[mysqld]
server-id=2
Copy the code

Step 4: Change the address of the “master database” pointing to the slave database (configured in the slave database)

Set parameters using the change master command, as shown in the following example:

Mysql > change master to master_host='192.168.1.8', master_user='repl', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=747;Copy the code

Once set up, check:

mysql> show slave status \G; * * * * * * * * * * * * * * * * * * * * * * * * * * * 1 row * * * * * * * * * * * * * * * * * * * * * * * * * * * Slave_IO_State: Master_Host: 192.168.1.8 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 747 Relay_Log_File: 59b3f32743e5-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: NoCopy the code

Step 5: View the offset of the binary log for the primary database (on the primary database)

Use the command to view the value of Position as shown in the following example:

mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB |  Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 747 | | | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Step 6: Enable sync from the database

Use the start slave command to start it.

mysql> start slave
Copy the code

The configuration is complete.

3. Perform other configurations

4. The extension

After modifying the primary database configuration, you can check whether the configuration takes effect (refer to Step 1 above).

mysql> show variables like 'log%' -> ; +----------------------------------------+--------------------------------+ | Variable_name | Value | +----------------------------------------+--------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/mysql-bin | | log_bin_index | /var/lib/mysql/mysql-bin.index | mysql> show variables like 'server%'; +----------------+--------------------------------------+ | Variable_name | Value | +----------------+--------------------------------------+ | server_id | 1 | | server_id_bits | 32 | mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB |  Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 747 | | | | +------------------+----------+--------------+------------------+-------------------+Copy the code

Start mysql in Docker mode

Docker run -di --name mysql_copy -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7Copy the code

What if there is no VI editor in Docker? To install a

apt-get update
apt-get install vim
Copy the code

To view the process list:

mysql> show processlist; For example, in the database see:  +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------ ------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------- -----------+ | 5 | root | localhost | zoo | Query | 0 | starting | show processlist | | 6 | system user | | NULL | Connect | 740 | Waiting for master to send event | NULL | | 7 | system user | | NULL | Connect | 593 | Slave has read all relay log; waiting for more updates | NULL | +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------- -----------+ 3 rows in set (0.00 SEC)Copy the code

On the server, set a lock to ensure that no operation is performed on the database for consistency.

FLUSH TABLES WITH READ LOCK;
Copy the code

After the primary database is backed up, resume the write operation

UNLOCK TABLES;
Copy the code

5. Reference:

www.cnblogs.com/cjsblog/arc…

Dev.mysql.com/doc/refman/…