Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about mysql cluster configuration, which may not be well understood by many people. In order to let you know more, Xiaobian summarized the following contents for you. I hope you can gain something according to this article.

MySQL replication

Primary IP: 192.168.56.2

Slave IP: 192.168.56.3

1 Two-node Add sync account and close database:

node 1

Note that ip is specified as slave ip

[root@mysql1 home]# mysql -uroot -poracle

mysql> GRANT REPLICATION SLAVE ON *.* to 'syc'@'192.168.56.3' identified by 'syc';

Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.33 sec)

mysql> exit

Bye

[root@mysql1 home]# /etc/init.d/mysql stop

Shutting down MySQL.. [OK]

node 2

Note that ip specifies the master node ip

[root@mysql2 home]# mysql -uroot -poracle

mysql> GRANT REPLICATION SLAVE ON *.* to 'syc'@'192.168.56.2' identified by 'syc';

Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.33 sec)

mysql> exit

Bye

[root@mysql2 home]# /etc/init.d/mysql stop

Shutting down MySQL.. [OK]

2 Modify my.cnf file

Node 1 my.cnf file

[root@mysql1 home]# vi /etc/my.cnf

[client]

#Client Character Set

default-character-set=utf8

socket = /home/mysql/mysql.sock

[mysqld]

socket = /home/mysql/mysql.sock

server-id=1

#Open binlog

log-bin=mysql-bin

#This parameter is used to configure whether updates from server B are written to binary logs. This option is disabled by default. However, if this slave B is a slave of server A,

#At the same time as the master server of server C, then you need to develop this option, so that its slave server C can get its binary log for synchronization.

log-slave-updates

#In the replication process, due to various reasons, the slave server may encounter an error in executing SQL in BINLOG. By default, the server will stop the replication process and no longer synchronize until the user handles it himself.

slave-skip-errors=all

#This parameter is critical for MySQL, it not only affects the performance loss of Binlog to MySQL, but also affects the integrity of MySQL data.

sync_binlog=1

#should be set to the total number of servers in the entire fabric

auto-increment-increment = 2

#Set the starting point of automatic growth in the database to avoid primary key conflicts when synchronizing data between two servers

auto-increment-offset = 1

#Character Set

character-set-server=utf8

#case-insensitive

lower_case_table_names=1

[mysql]

#Character Set

default-character-set=utf8

auto-rehash

Node 2 my.cnf file

[root@mysql2 home]# vi /etc/my.cnf

[client]

socket = /home/mysql/mysql.sock

#Client Character Set

default-character-set=utf8

[mysqld]

server-id=2

socket = /home/mysql/mysql.sock

#Character Set

character-set-server=utf8

#case-insensitive

lower_case_table_names=1

#Open binlog

log-bin=mysql-bin

#This parameter is used to configure whether updates from server B are written to binary logs. This option is disabled by default. However, if this slave B is a slave of server A,

#At the same time as the master server of server C, then you need to develop this option, so that its slave server C can get its binary log for synchronization.

log-slave-updates

#In the replication process, due to various reasons, the slave server may encounter an error in executing SQL in BINLOG. By default, the server will stop the replication process and no longer synchronize until the user handles it himself.

slave-skip-errors=all

#This parameter is critical for MySQL, it not only affects the performance loss of Binlog to MySQL, but also affects the integrity of MySQL data.

sync_binlog=1

#should be set to the total number of servers in the entire fabric

auto-increment-increment = 2

#Set the starting point of automatic growth in the database to avoid primary key conflicts when synchronizing data between two servers

auto-increment-offset = 1

[mysql]

#Character Set

default-character-set=utf8

auto-rehash

3 Start MySQL database

node 1

[root@mysql1 home]# /etc/init.d/mysql start

Starting MySQL.. [OK]

node 2

[root@mysql2 home]# /etc/init.d/mysql start

Starting MySQL. [OK]

Record binlog position

node 1

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000003 | 120 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

node 2

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 | 120 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

4 Start replication

master node

mysql> stop slave;

mysql> change master to master_host='192.168.56.3',master_user='syc',master_password='syc',master_port=3306,master_log_file='mysql-bin.000002',master_log_pos=120;

mysql> start slave;

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.2

Master_User: syc

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 927

Relay_Log_File: mysql2-relay-bin.000002

Relay_Log_Pos: 793

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

from node

mysql> stop slave;

mysql> change master to master_host='192.168.56.2',master_user='syc',master_password='syc',master_port=3306,master_log_file='mysql-bin.000003',master_log_pos=120;

mysql> start slave;

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.3

Master_User: syc

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 927

Relay_Log_File: mysql1-relay-bin.000002

Relay_Log_Pos: 580

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Testing:

master node

mysql> create database bbbbb;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| aaaaa |

| bbbbb |

| mysql |

| performance_schema |

| test |

+--------------------+

6 rows in set (0.00 sec)

From node:

mysql> create database aaaaa;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| aaaaa |

| bbbbb |

| mysql |

| performance_schema |

| test |

+--------------------+

6 rows in set (0.00 sec)

Test complete:

Note:

show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

These two must be yes.

If the following problems occur:

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Need to check if there was a problem creating user rights or if the link information is wrong or the account is invalid.

After reading the above, do you have any further understanding of how mysql cluster configuration is? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

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.