Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Mysql master-master-slave experiment

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

Share

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

Implementation structure:

Prerequisite, this is a new installation of mysql server, already running mysql needs to make spare parts, and restore to another machine.

Master A:192.168.168.128

Master B:192.168.168.129

Slave 192.168.168.130

Install mysql on three machines

[root@yaho ~] # yum install-y mysql mysql-server mysql-dev

Modify the configuration files for master An and master B and slave.

[root@yaho ~] # vim / etc/my.cnf

Master A: add (modify) in [mysqld]

Server-id = 128,

Log-bin = mysql-bin

Sync-binlog = 1

Auto_increment_offset = 1 # starting value

Auto_increment_increment = 2 # step

Slave-skip-errors=1007,1008 # skips the unable to create database error and the unable to delete database error. If all mysql databases are the same, do not add

# slave-skip-errors=all # skip all errors. In fact, this parameter is quite dangerous and can easily cause data inconsistencies among several mysql.

Log-slave-updates # writes records of replication operations from master to the local bin-log log to achieve full synchronization of slave (backup)

Master B: add (modify) in [mysqld]

Server-id = 129

Log-bin = mysql-bin

Sync-binlog = 1

Auto_increment_offset = 2 # starting value

Auto_increment_increment = 2 # step

Slave-skip-errors=1007,1008 # skips the unable to create database error and the unable to delete database error. If all mysql databases are the same, do not add

# slave-skip-errors=all # skip all errors. In fact, this parameter is quite dangerous and can easily cause data inconsistencies among several mysql.

Log-slave-updates # writes records of replication operations from master to the local bin-log log to achieve full synchronization of slave (backup)

Slave: add (modify) in [mysqld]

Server-id = 130

Slave-skip-errors=1007,1008 # skips the unable to create database error and the unable to delete database error. If all mysql databases are the same, do not add

# slave-skip-errors=all # skip all errors. In fact, this parameter is quite dangerous and can easily cause data inconsistencies among several mysql.

Log-slave-updates # writes records of replication operations from master to the local bin-log log to achieve full synchronization of slave (backup)

3. Start the mysql of three master respectively, establish synchronous users, and authorize IP to allow remote access.

[root@yaho ~] # service mysqld start

Master A:

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl'@'192.168.2168.129' identified by' 123456authorization; # authorize master B

Master B:

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl'@'192.168.168.128' identified by' 123456license; # authorize master A

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl'@'192.168.168.130' identified by' 123456authorization; # authorize slave

4. View maste status information for master An and master:

Mysql > show master status

+-+ +

| | File | Position |

+-+ +

| | mysql-bin.000001 | 106 | |

+-+ +

5. Connect master to realize master-master-slave

Master A:

Mysql > change master to master_host='192.168.168.129',master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=106

Master B:

Mysql > change master to master_host='192.168.168.128',master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=106

Slave:

Mysql > change master to master_host='192.168.168.129',master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=106

6. Start the slave function on each of the three machines:

Mysql > start slave

Mysql > show slave status\ G

If both of the following are YES, it means success.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Test-slightly:

All you have to do is create a database in any master, and then show it, and if the other two mysql have this database, it means success.

Note: if mysqld does not start, you can comment on the line log-slave-updates first, and then remove the comment when the master slave is established.

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.

Views: 0

*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.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report