MySQL Master Master Data Synchronization

Keywords: MySQL shell Database SELinux

Links to the original text: http://blog.csdn.net/xyang81/article/details/52562571

MySQL The principle of master-slave synchronization is the same as that of master-slave synchronization, but both sides are master-slave roles.

Environmental Science

operating system Version: CentOS 7 64 bits
MySQL version: MySQL 5.6.33
Node 1IP: 192.168.1.205 Host Name: edu-mysql-01
Node 2IP: 192.168.1.206 Host Name: edu-mysql-02

MySQL master-slave copy official documents: http://dev.mysql.com/doc/refman/5.6/en/replication.html

Be careful:
1 > Maintain consistency between master and slave operating system versions and digits
2 > Master and Slave data base Consistent versions
3 > Data consistency in Master and Slave databases

To configure

Reference before configuring MySQL 5.7 Installation and Configuration (YUM) Install MySQL (Note that this article demonstrates version 5.6, and you need to modify the yum source in this article to 5.6)

1. Security Configuration

1> firewall
Add mysql communication port (default is 3306)

shell> vim /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
shell> service  iptables restart
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3

Or close the firewall

shell> service iptables stop
  • 1
  • 1

2 > Close selinux

shell> vi /etc/selinux/config
SELINUX=disabled
  • 1
  • 2
  • 1
  • 2

Modify the value of SELINUX to disabled

2. Node 1 configuration (192.168.1.205)

2.1 Adding Data Synchronization Configuration

shell> vim /etc/my.cnf
  • 1
  • 1

Add the following configuration items in [mysqld]:

# Server ID, must be unique, generally set their own IP
server_id=205
# Replication filtering: databases that do not require backups (MySQL libraries are generally not synchronized)
binlog-ignore-db=mysql
# Open the binary log function, name can be taken at will, preferably meaningful (such as project name)
log-bin=edu-mysql-bin
# Memory allocated for each session, the cache used to store binary logs during a transaction
binlog_cache_size=1M
# Master-slave copy format (mixed,statement,row, default format is statement)
binlog_format=mixed
# The number of days that binary logs are automatically deleted/expired. The default value is 0, which means no automatic deletion.
expire_logs_days=7
## Skip all errors encountered in master-slave replication or specified types of errors to avoid slave end replication interruption. 
## For example: 1062 error refers to some primary key duplication, 1032 error is due to inconsistency between master and slave database data.
slave_skip_errors=1062
# Relay logs as slave servers
relay_log=edu-mysql-relay-bin
# log_slave_updates means slave writes replication events into its own binary log
log_slave_updates=1
# Primary key self-increasing rule to avoid duplication of master-slave synchronous ID
auto_increment_increment=2  # Self-increasing factor (add each time)2)
auto_increment_offset=1     # Self-increasing migration1Beginning), singular
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

2.2 Master configuration

# Restart the service first
shell> service mysqld restart  
# Log in to mysql
shell> mysql -uroot -p 
# Create database synchronization users and grant appropriate privileges
mysql> grant replication slave, replication client on *.* to 'repl'@'192.168.1.206' identified by 'root123456';
# Refresh Authorization Table Information
mysql> flush privileges;
# View the position (offset) and File (log file) values of the binlog file, which you need from the machine
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| edu-mysql-bin.000001 |      120 |              | mysql            |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

2.3 Slave configuration

# master_user and master_password: Execute the user and password created by grant replication slave on 206
# master_log_file and master_log_pos: Run show master status on 206; command execution results correspond to the values of File and Postion fields
mysql> change master to master_host='192.168.1.206',master_user='repl', master_password='root123456', master_port=3306, master_log_file='edu-mysql-bin.000001', master_log_pos=439, master_connect_retry=30;
# View status information as slave nodes
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.1.206
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: edu-mysql-bin.000001
          Read_Master_Log_Pos: 439
               Relay_Log_File: edu-mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: edu-mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
          # Eliminate other configurations...
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

Since the slave node has not been started at this time, the value of Slave_IO_State is empty, and the Slave_IO_Running and Slave_SQL_Running threads are No running.

2.4 Start Slave

Note: To restart after creating a synchronization account on Node 2, otherwise the master error will not be reported.

# Start the slave node and start working to receive events sent by the master node (all events of database data changes)
mysql> start slave;
# At this point, look at the status of the slave node
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.206
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: edu-mysql-bin.000001
          Read_Master_Log_Pos: 439
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 287
        Relay_Master_Log_File: edu-mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
            # ... Eliminate other configurations
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

3. Node 2 configuration (192.168.1.206)

3.1 Adding Data Synchronization Configuration

shell> vim /etc/my.cnf
  • 1
  • 1

Add the following configuration items in [mysqld]:

server_id=206
binlog-ignore-db=mysql
log-bin=edu-mysql-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
relay_log=edu-mysql-relay-bin
log_slave_updates=1
#IDSelf increasing2Start, double
auto_increment_increment=2
auto_increment_offset=2
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

3.2 Master configuration

# Restart the service first
shell> service mysqld restart  
# Log in to mysql
shell> mysql -uroot -p 
# Create database synchronization users and grant the corresponding permissions (only repl users are allowed to log in from 192.168.1.205)
mysql> grant replication slave, replication client on *.* to 'repl'@'192.168.1.205' identified by 'root123456';
# Refresh Authorization Table Information
mysql> flush privileges;
# View the position (offset) and File (log file) values of the binlog file, which you need from the machine
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| edu-mysql-bin.000001 |      439 |              | mysql            |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

At this point, you can start the slave service of node 1 (205)

3.3 Slave configuration

# Execute show master status on master_log_file and master_log_pos:205 nodes; values corresponding to File and position
mysql> change master to master_host='192.168.1.205',master_user='repl', master_password='root123456', master_port=3306, master_log_file='edu-mysql-bin.000001', master_log_pos=120, master_connect_retry=30;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.1.205
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: edu-mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: edu-mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: edu-mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
              #... Eliminate other configurations
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

3.4. Start Slave

shell> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.205
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: edu-mysql-bin.000001
          Read_Master_Log_Pos: 439
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 287
        Relay_Master_Log_File: edu-mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
              ...Eliminate other configurations
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

4, validation

# Log in 205 to create a database
shell> mysql -u root -p
mysql> create database if not exists mydb default character set utf8 collate utf8_general_ci;
mysql> create table user (id int, username varchar(30), password varchar(30));
mysql> insert into user values (1, 'yangxin', '123456');
# Here are the operations on 206 nodes
#1. Log on 206 to query all the libraries and whether they contain mydb database.
#2. Switch to the mydb library. Does it contain a user table with one piece of data?
#3. Insert a data into the mydb.user table in 2006 to see if 205 is in sync with the past
mysql> insert into user values (2,'yangxin2','123456')
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

The detailed process is shown in the following figure:

Relevant references:
Detailed description of master-slave replication, semi-synchronous replication and master-master replication of MySQL data required by operation and maintenance engineers

Posted by bweekly on Sat, 13 Apr 2019 17:21:32 -0700

Hot Keywords

©2024 Programmer Group