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

IP address of node1: 192.168.1.254

IP address of node2: 192.168.1.253

The operating system is RHEL7.3,MySQL version 5.7.18

1. Modify the MySQL configuration file of node1

# Note that the server_id field must not be the same. Since the previous configuration server_id has been configured and its value is unique, it will not be configured here.

Modify the mysql configuration file for node1

# the ID of the server must be unique. Generally, set your own IP

Server_id=254

# replication filtering: databases that do not need to be backed up (MySQL libraries are generally not synchronized)

Binlog-ignore-db=mysql

# enable binary log function. You can take any name you want, preferably with meaning (such as project name)

Log-bin=lamp-mysql-bin

# memory allocated for each session, cache used to store binary logs during transactions

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 that it is not deleted automatically.

Expire_logs_days=7

# # skip all errors or specified types of errors encountered in master-slave replication to avoid interruption of replication on slave.

# # for example, 1062 error refers to duplicate primary keys, and 1032 error is due to inconsistency between master and slave database data

Slave_skip_errors=1062

# as a relay log from the server

Relay_log=lamp-mysql-relay-bin

# log_slave_updates means that slave writes replication events to its own binary log

Log_slave_updates=1

# Primary key self-increasing rule to avoid repetition of master-slave synchronous ID

Auto_increment_increment=2 # self-increment factor (modified according to the number of MySQL servers, in this case 2 MySQL servers)

Auto_increment_offset=1 # self-increasing offset (starting at 1)

2. Modify the MySQL configuration file of node2

# replication filtering: databases that do not need to be backed up (MySQL libraries are generally not synchronized)

Binlog-ignore-db=mysql

# enable binary log function. You can take any name you want, preferably with meaning (such as project name)

Log-bin=lamp-mysql-bin

# memory allocated for each session, cache used to store binary logs during transactions

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 that it is not deleted automatically.

Expire_logs_days=7

# # skip all errors or specified types of errors encountered in master-slave replication to avoid interruption of replication on slave.

# # for example, 1062 error refers to duplicate primary keys, and 1032 error is due to inconsistency between master and slave database data

Slave_skip_errors=1062

# as a relay log from the server

Relay_log=lamp-mysql-relay-bin

# log_slave_updates means that slave writes replication events to its own binary log

Log_slave_updates=1

# Primary key self-increasing rule to avoid repetition of master-slave synchronous ID

Auto_increment_increment=2 # self-increment factor (modified according to the number of MySQL servers, in this case 2 MySQL servers)

Auto_increment_offset=2 # self-increasing offset (starting at 2)

3. Restart the MySQL service on node1 and node2 respectively

Restart the MySQL service on node1

[root@node1 ~] # systemctl restart mysqld

Restart the MySQL service on node2

[root@node2 ~] # systemctl restart mysqld

4. Enter the MySQL database on node1 and configure accounts that allow synchronization from node2.

[root@node1] # mysql-u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 3

Server version: 5.7.18-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > grant replication slave, replication client on *. * to 'repl'@'192.168.1.253' identified by' repl@123'

Mysql > flush privileges

Query OK, 0 rows affected (0.06 sec)

5. View and record the position and File values of the binlog file on node1, which will be used when configuring the slave.

Mysql > show master status

+-- +

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

+-- +

| | lamp-mysql-bin.000001 | 631 | | mysql |

+-- +

1 row in set (0.00 sec)

Mysql >

6. Use node1 as your master server on node2, and turn on slave status.

Mysql > change master to master_host='192.168.1.254',master_user='repl', master_password='repl@123', master_port=3306, master_log_file='lamp-mysql-bin.000001', master_log_pos=631, master_connect_retry=30

Mysql > start slave

7. Check your slave status on node2.

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

The above two parameters should all be Yes status.

8. Create an account on node2 that allows synchronization from node1.

[root@node2] # mysql-u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 3

Server version: 5.7.18-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > grant replication slave, replication client on *. * to 'repl'@'192.168.1.254' identified by' repl@123'

Mysql > flush privileges

9. View and record the position and File values of the binlog file on node2.

Mysql > show master status

+-- +

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

+-- +

| | lamp-mysql-bin.000001 | 474 | | mysql |

+-- +

10. Use node2 as your master server on node1, and turn on slave status.

Mysql > change master to master_host='192.168.1.253',master_user='repl', master_password='repl@123', master_port=3306, master_log_file='lamp-mysql-bin.000001', master_log_pos=474, master_connect_retry=30

Mysql > start slave

Check your slave status on node1.

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

The above two parameters should all be Yes status.

12. Create a database in any database of node1 or node2 and it will be automatically synchronized to another database

Mysql > create database netser

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | netser |

| | performance_schema |

| | sys |

+-+

5 rows in set (0.09 sec)

On another node, if you look at the database, you will see exactly the same data information.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | netser |

| | performance_schema |

| | sys |

+-+

5 rows in set (0.06 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.