mysql primary and primary data synchronization, hot standby

Keywords: MySQL shell Database SELinux

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

Environmental Science

Operating system version: CentOS7 64 bit
MySQL version: mysql5.6.33
Node 1IP: 192.168.1.205 host name: edu-mysql-01
Node 2IP: 192.168.1.206 host name: edu-mysql-02

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

Note:
1> The operating system version and number of bits of the master and slave servers should be consistent
2> The versions of master and Slave databases should be the same
3> Data in master and Slave databases should be consistent

To configure

Reference before configuration MySQL 5.7 installation and configuration (YUM) Install MySQL (note that this article demonstrates version 5.6, and you need to change the yum source in this article to 5.6)

1. Security configuration

1> Firewall
Add mysql communication port (3306 by default)

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

Or turn off the firewall

shell> service iptables stop

2> Turn off selinux

shell> vi /etc/selinux/config
SELINUX=disabled

Change the value of SELINUX to disabled

2. Node 1 configuration (192.168.1.205)

2.1 add data synchronization configuration

shell> vim /etc/my.cnf

Add the following configuration items to [mysqld]:

# The ID of the server, which must be unique, usually sets its own IP
server_id=205
# Replication filtering: databases that do not need to be backed up (MySQL databases are generally not synchronized)
binlog-ignore-db=mysql
# Enable the binary log function. The name can be selected at will. It is better to have meaning (such as project name)
log-bin=edu-mysql-bin
# The memory allocated for each session is used to store the cache of binary logs during the transaction
binlog_cache_size=1M
# The format of master-slave copy (mixed,statement,row, the 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 it will not be deleted automatically.
expire_logs_days=7
## Skip all errors encountered in master-slave replication or errors of the specified type to avoid the interruption of slave side replication. 
## For example, 1062 error means that some primary keys are duplicate, and 1032 error is caused by inconsistency between master and slave database data
slave_skip_errors=1062
# Relay log when acting as slave
relay_log=edu-mysql-relay-bin
# Log? Slave? Updates means that slave writes the replication events to its binary log
log_slave_updates=1
# Primary key auto increment rule to avoid duplicate primary and secondary synchronization ID
auto_increment_increment=2  # Self increasing factor (every time2)
auto_increment_offset=1     # Self increasing offset (from1Start), singular

2.2 Master configuration

# Restart the service first
shell> service mysqld restart  
# Log in to mysql
shell> mysql -uroot -p 
# Create database synchronization user and grant corresponding permission
mysql> grant replication slave, replication client on *.* to 'repl'@'192.168.1.206' identified by 'root123456';
# Refresh authorization table information
mysql> flush privileges;
# To view the position and File values of the binlog File, you need to use the
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)

2.3 Slave configuration

# Master? User and master? Password: the user and password created by executing grant replication slave... On 206
# Master? Log? File and master? Log? Pos: run show master status on 206; the value of the file and Position fields corresponding to the command execution result
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 a slave node
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
          # Omit other configurations...

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, indicating that they are not running.

2.4 start Slave

Note: the synchronization account should be created on node 2 and then started, otherwise the master error will be reported

# Start the slave node and start work to receive the events sent by the master node (all events of database data change)
mysql> start slave;
# At this time, check 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:
            # ... omit other configuration

3. Node 2 configuration (192.168.1.206)

3.1 add data synchronization configuration

shell> vim /etc/my.cnf

Add the following configuration items to [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, even
auto_increment_increment=2
auto_increment_offset=2

3.2 Master configuration

# Restart the service first
shell> service mysqld restart  
# Log in to mysql
shell> mysql -uroot -p 
# Create database synchronization user and grant corresponding permission (only allow repl user 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;
# To view the position and File values of the binlog File, you need to use the
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)

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

3.3 Slave configuration

# show master status is executed on the master log File and master log pos: 205 nodes, corresponding to the values of 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:
              #... omit other configuration

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:
              ...Omit other configurations

4, validation

# Login 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 is the operation on node 206
#1. Log in 206 to query all databases, including mydb database
#2. Switch to mydb database, whether to include user table and have a piece of data
#3. Insert a piece of data in mydb.user table of 206 to check whether 205 synchronizes the past
mysql> insert into user values (2,'yangxin2','123456')

Posted by swampone on Mon, 30 Mar 2020 11:30:37 -0700

Programmer Group