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

Let's learn about the deployment and construction of MySQL two-way master-slave replication process. I believe you will benefit a lot after reading it. There are not many words in the text. You want to deploy and build MySQL two-way master-slave replication process.

As the configuration of Master CVM apenglinux-001.cn

/-- build a database, table, backup database, and transfer the backup to another machine-/

[root@apenglinux-001] # mysql-uroot-p123456-e "create database db1;use db1;create table T1 (id int unsigned not null primary key auto_increment,name varchar); insert into T1 (name) values ('zhangsan'), (' lisi'), ('wangwu'); select * from T1;"

Mysql: [Warning] Using a password on the command line interface can be insecure.

+-+ +

| | id | name |

+-+ +

| | 1 | zhangsan |

| | 2 | lisi |

| | 3 | wangwu |

+-+ +

[root@apenglinux-001] # mysqldump-uroot-p123456-B db1 > db1_all.sql

[root@apenglinux-001] # mysqldump-uroot-p123456-B db1 > db1_all.sql

Mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@apenglinux-001 ~] # scp db1_all.sql 192.168.1.20:/root

The authenticity of host '192.168.1.20 (192.168.1.20)' can't be established.

ECDSA key fingerprint is SHA256:ENfUT65MBnG5u82/aeA84Wl7klhZZMS/MI1+36eGu8k.

ECDSA key fingerprint is MD5:bb:7a:dc:8b:d2:5b:99:54:9a:8d:f2:17:81:0a:5e:72.

Are you sure you want to continue connecting (yes/no)? Yes

Warning: Permanently added '192.168.1.20' (ECDSA) to the list of known hosts.

Root@192.168.1.20's password:

Db1_all.sql 100% 2024 856.5KB/s 00:00

/-- configure my.cnf-- /

[root@apenglinux-001 ~] # vim / etc/my.cnf

Log-bin=apenglinux-001.cn

Server-id=100

Binlog-do-db=db1

Binlog-ignore-db=mysql

/-- enable MySQL service-- /

[root@apenglinux-001 ~] # systemctl restart mysqld

/-- as a master, authorize and lock the table-- /

[root@apenglinux-001 ~] # mysql-uroot-p123456-e "grant replication slave on. To slave@192.168.1.20 identified by '123 challenge challenge flush tables with read lock;"

As the configuration of slave CVM apenglinux-002

/-- restore the database uploaded from master-- /

[root@apenglinux-002] # mysql-uroot-p123456 < db1_all.sql

/-- configure my.cnf-- /

Validate_password=off

Log-bin=apenglinux-002.cn

Server-id=90

Binlog-do-db=db1

Binlog-ignore-db=mysql

/-- restart mysql--/

[root@apenglinux-002 ~] # systemctl restart mysqld

/-- stop slave, specify the ip,user,password,log_file,log_pos of the primary CVM, and enable slave-- /

[root@apenglinux-002] # mysql-uroot-p123456-e "stop slave;change master to master_host='192.168.1.10',master_port=3306,master_user='slave',master_password='123',master_log_file='apenglinux-001.000001',master_log_pos=449;start slave;"

/-- go to the main CVM to start the undo table operation-- /

[root@apenglinux-001] # mysql-uroot-p123456-e 'unlock tables;'

/-- View the status of slave-- /

[root@apenglinux-002] # mysql-uroot-p123456-e 'show slave status\ G'

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

Last_IO_Errno: 0

Last_SQL_Errno: 0

As the configuration of master CVM apenglinux-002.cn

[root@apenglinux-002] # mysql-uroot-p123456-e 'grant replication slave on. To slave@192.168.1.10 identified by "123"; flush tables with read lock;show master status;'

Mysql: [Warning] Using a password on the command line interface can be insecure.

+-- +

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-- +

| | apenglinux-002.000001 | 449 | db1 | mysql | |

+-- +

As the configuration of slave CVM apenglinux-001.cn

[root@apenglinux-001] # mysql-uroot-p123456-e'stop slave;change master to master_host= "192.168.1.20", master_port=3306,master_user= "slave", master_password= "123", master_log_file= "apenglinux-002.000001", master_log_pos=449;start slave;'

Unlock it on apenglinux-002.cn.

[root@apenglinux-002] # mysql-uroot-p123456-e 'unlock tables;'

/-- View the status of slave-- /

[root@apenglinux-001] # mysql-uroot-p123456-e 'show slave status\ G'

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

Last_IO_Errno: 0

Last_SQL_Errno: 0

At this point, the apenglinux-001 and apenglinux-002 machines are subordinate to each other.

Test:

/-- add a record to apenglinux-001.cn and view it on apenglinux-002-- /

[root@apenglinux-001] # mysql-uroot-p123456-e 'insert into db1.t1 (name) values ("aa"); select * from db1.t1;'

Mysql: [Warning] Using a password on the command line interface can be insecure.

+-+ +

| | id | name |

+-+ +

| | 1 | zhangsan |

| | 2 | lisi |

| | 3 | wangwu |

| | 4 | aa |

+-+ +

[root@apenglinux-002] # mysql-uroot-p123456-e 'select * from db1.t1;'

Mysql: [Warning] Using a password on the command line interface can be insecure.

+-+ +

| | id | name |

+-+ +

| | 1 | zhangsan |

| | 2 | lisi |

| | 3 | wangwu |

| | 4 | aa |

+-+ +

/-- Delete two records on apenglinux-002.cn and view them on apenglinux-001-- /

[root@apenglinux-002 ~] # mysql-uroot-p123456-e'delete from db1.t1 limit 2ten select * from db1.t1;'

Mysql: [Warning] Using a password on the command line interface can be insecure.

+-+ +

| | id | name |

+-+ +

| | 3 | wangwu |

| | 4 | aa |

+-+ +

[root@apenglinux-001] # mysql-uroot-p123456-e 'select * from db1.t1;'

Mysql: [Warning] Using a password on the command line interface can be insecure.

+-+ +

| | id | name |

+-+ +

| | 3 | wangwu |

| | 4 | aa |

+-+ +

After reading this article on deploying and building MySQL two-way master-slave replication process, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

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.