Master-slave replication of two versions of Mysql 01/17 Update SLTechnology News&Howtos

Master-slave replication of two versions of Mysql

2025-01-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

MySQL AB replication

Mysql AB replication, also known as master-slave replication, implements data synchronization, which requires that all mysql versions are the same. If the version is inconsistent, the slave server version is higher than the master server, and the inconsistent version cannot be replicated in both directions.

There are two main points about AB replication:

1. Solve the data inconsistency caused by downtime, because mysql AB replication can back up data in real time.

2. Reduce the pressure of database service.

However, mysql AB replication is not suitable for big data environment. Cluster is recommended for big data environment.

The three main steps of Mysql replication:

① master server changes are recorded in binaries (binary log events)

② copies the binaries from the server post bar administrator server to its own relay log

③ relays the events in the log from the server to achieve the same environment as the master server.

Because there is a gap between Mysql5.1 and version 5.7.

Therefore, this blog post implements master-slave replication of their respective versions with 5.1 and 5.7.

Start building:

Environment: redhat6.5

Three virtual machines with the same version of MySQL 5.1:

Server1 (master): 172.25.141.4

Server2 (master&slave): 172.25.141.5

Server3 (slave): 172.25.141.6

Server1 (172.25.141.4):

Vim / etc/my.cnf (add the following)

Server-id=1

Log-bin=mysql-bin

Binlog-do-db=testdb

Binlog-ignore-db=mysql

/ etc/init.d/mysqld start

Mysql # # Login

Mysql > create database testdb

Mysql > grant replication slave on *. * to redhat@'172.25.141.5' identified by 'test123'

Mysql > show master status; # # View master status

Mysql > show master status

+-+

+-+

+-+

1 row in set (0.00 sec)

Mysql > quit

Mysqlbinlog mysql-bin.000003 # # View MySQL logs

Server2 (172.25.141.5):

Mysql-h 172.25.141.4-u redhat-ptest123 # # test

Vim / etc/my.cnf (add the following)

Server-id=2

Mysql

Mysql > create database testdb

Mysql > change master to master_host='172.25.141.4',master_user='redhat',master_password='test123',master_log_file='mysql-bin.000003',master_log_pos=1019

# master_log_file is the same as the File name in master status

# the master_log_pos=1019 number must be the same as the position of master

Mysql > slave start

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

# # #

Test:

Server1:

Mysql > use testdb

Mysql > create table users (username varchar (25) not null, password varchar (25) not null)

Mysql > insert into users values ('user1','123')

Mysql > insert into users values ('user2','456')

The items added above will also appear in the database testdb on Server2:

Mysql > select * from users

+-+ +

| | username | password |

+-+ +

| | user1 | 123 | |

| | user2 | 456 |

+-+ +

2 rows in set (0.00 sec)

# delete from users where username='user1'

# deleting an item on Server 1 will also delete it on Server 2

# # #

Server1 (master)-> server2 (master&slave)-> server3 (slave)

Server2 (172.25.141.5):

Vim / etc/my.cnf

Server-id=2

Log-bin=mysql-bin

Binlog-do-db=testdb

Binlog-ignore-db=mysql

Log-slave-updates

/ etc/init.d/mysqld start

Mysql

Mysql > grant replication slave on *. * to redhat@'172.25.141.6' identified by 'test123'

Mysql > show master status

+-+

+-+

+-+

1 row in set (0.00 sec)

Server3 (172.25.141.6):

Mysqlbinlog-- start-position=193 mysql-bin.000003 | mysql- uroot-paired pictures *

# MySQL operation log before serverA is imported, because there are no such operations before server3. To synchronize data, then the environment of server3 should be the same as that of server1.

Vim / etc/my.cnf

Server-id=3

/ etc/init.d/mysqld start

Mysql

Mysql > change master to master_host='172.25.141.5',master_user='redhat',master_password='test123',master_log_file='mysql-bin.000001',master_log_pos=106

Mysql > slave start

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

The occurrence of this proves that the slave of server3 is enabled successfully and data can be synchronized.

MySQL 5.7

Two virtual machines with the same version of MySQL:

Server1 (master)

Server2 (slave)

Installation package: mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar

Server1:

Tar xf mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar

Yum install *-y

Vim / etc/my.cnf

Server-id=1

Log-bin=mysql-bin

Binlog-do-db=testdb

Gtid-mode=on

Enforce-gtid-consistency=on

/ etc/init.d/mysqld start

Cat / var/log/mysqld.log | grep temporary # # View root password

Mysql_secure_installation

# # to change the password, it must contain uppercase and lowercase letters, numbers and special characters of not less than 8 characters

Mysql-paired login

Mysql > create database testdb

Mysql > grant replication slave on *. * to redhat@172.25.141.5 identified by'@ Ling110'

Mysql > show master status

+-+

+-+

+-+

1 row in set (0.00 sec)

Server2:

Tar xf mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar

Yum install *-y

Mysql-h 172.25.141.4-uredhat-p@Ling110 # # test

Vim / etc/my.cnf

Server-id=2

Gtid-mode=on

Enforce-gtid-consistency=on

/ etc/init.d/mysqld start

Mysql_secure_installation

Mysql-paired pictures *

Mysql > create database testdb

Mysql > change master to master_host='172.25.141.4',master_user='redhat',master_password='@Ling110',master_auto_position=1

Mysql > start slave

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

The test is the same as MySQL5.1

The difference between MySQL5.7 and MySQL5.1 is that MySQL5.7 can turn on multi-thread mode, which can effectively solve the delay problem of data synchronization.

Turn on multithreaded mode:

Server2:

Vim / etc/my.cnf

Slave-parallel-type=LOGICAL_CLOCK # # enable multithreading mode

Slave-parallel-workers=16

# # 16 is the official recommended number. 0 is the original single-threaded mode. Keep in mind that it cannot be set to 1. The performance will be worse than 0 because it is still single-threaded but with one more layer of forwarding that reduces efficiency.

Master-info-repository=TABLE # # Optimization

Relay_log_info_repository=TABLE # # Optimization

/ etc/init.d/mysqld restart

Mysql-paired pictures *

Mysql > show processlist; # # you can see multiple threads open

Mysql > show processlist

+-+-- +

+-+-- +

+-+-- +

19 rows in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.