What is mysql cluster? Introducing mysql cluster

In the past, I felt that clustering, read-write separation, and xxxx were all awesome and awesome things. After finishing it, I found that it is actually very simple, as long as you are interested.

Two machines:

mysql01: 192.168.1.222

mysql02: 192.168.1.223

Note that if you are replicating and simulating a virtual machine, you must Change auto.cnf under mysql/data. The uuid of the two machines cannot be the same. The server's selinux must be disabled (modify /etc/selinux/config and change SELINUX to disabled).

MySQL5.6 has two ways to start master-slave replication: based on log (binlog) and based on GTID (global transaction identifier).
The principle of master-slave replication (also called A/B replication)
(1) Master records data changes in the binary log (binary log), which is the file specified by the configuration file log-bin. These records are called Binary log events (binary log events);
(2) Slave reads binary log events in the Master through the I/O thread and writes them to its relay log (relay log);
(3) Slave Redo the events in the relay log and execute the event information in the relay log locally one by one to complete the local storage of data, thereby reflecting the changes to its own data (data replay ).

mysql01：

vi /etc/my.cnf

## Add the following configuration items in [mysqld]
## Set server_id, usually set to IP
server_id=222
## Copy filtering: For databases that need to be backed up, output binlog
#binlog-do-db=roncoo
## Copy filtering: For databases that do not need to be backed up, do not output (mysql libraries generally do not Synchronization)
binlog-ignore-db=mysql
## Turn on the binary log function, you can take it at will, it is best to have meaning
log-bin=master-mysql
## Allocate for each session The memory used to store the binary log cache during the transaction
binlog_cache_size=1M
## The format of master-slave replication (mixed, statement, row, the default format is statement)

binlog_format=mixed

## The number of days after which binary logs will be automatically deleted/expired. The default value is 0, which means no automatic deletion.
expire_logs_days=7
## Skip all errors or errors of specified types encountered in master-slave replication to avoid replication interruptions on the slave side.
## For example: 1062 error refers to duplication of some primary keys, 1032 error is due to inconsistent master-slave database data
slave_skip_errors=1062

## relay_log Configure relay log

relay_log =relay01
## log_slave_updates means slave writes replication events into its own binary log
log_slave_updates=1

Start/restart the Master database service, log in to the database, create a data synchronization user, and grant the corresponding Permissions

service mysql restart
mysql -uroot -p

##Create data synchronization users and grant corresponding permissions

mysql> grant replication slave, replication client on *.* to 'repl01'@'192.168.1.223' identified by
'123456';
Query OK, 0 rows affected (0.00 sec)

## Refresh authorization table information

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# # Check the position number and write down the position number (you need to use this position number and the current log file on the slave)

mysql> show master status;
mysql02：

vi /etc/my.cnf

## In [mysqld] Add the following configuration items
## Set server_id, usually set to IP
server_id=223
## Copy filter: database that needs to be backed up, output binlog
#binlog-do-db=roncoo
##Copy filtering: databases that do not need to be backed up will not be output (mysql libraries are generally not synchronized)
binlog-ignore-db=mysql
## Enable binary logs in case the Slave acts as the Master of other Slave Use
log-bin=slave-mysql
## The memory allocated for each session is used to store the binary log cache during the transaction process
binlog_cache_size = 1M
## Master-slave replication Format (mixed, statement, row, the default format is statement)
binlog_format=mixed
## Number of days for binary logs to be automatically deleted/expired. The default value is 0, which means no automatic deletion.
expire_logs_days=7
## Skip all errors or errors of specified types encountered in master-slave replication to avoid replication interruptions on the slave side.
## For example: 1062 error refers to some duplicate primary keys, 1032 error is due to inconsistent master-slave database data
slave_skip_errors=1062
## relay_log configure relay log
relay_log=relay02
# #log_slave_updates means slave writes replication events into its own binary log
log_slave_updates=1

Restart the database service mysql restart

Add relevant parameters

mysql> 
change master to master_host='192.168.1.222', master_user='repl01',master_password='123456', master_port=3306, master_log_file='mysql01.000001',master_log_pos=429, master_connect_retry=30;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

上面执行的命令的解释：
master_host='192.168.1.222' ## Master 的 IP 地址
master_user='repl' ## 用于同步数据的用户（在 Master 中授权的用户）
master_password='123456' ## 同步数据用户的密码
master_port=3306 ## Master 数据库服务的端口
master_log_file='master-mysql.000001' ##指定 Slave 从哪个日志文件开始读复制数据（可
在 Master 上使用 show master status 查看到日志文件名）
master_log_pos=429 ## 从哪个 POSITION 号开始读
master_connect_retry=30 ##当重新建立主从连接时，如果连接建立失败，间隔多久后重试。
单位为秒，默认设置为 60 秒，同步延迟调优参数。
## 查看主从同步状态
mysql> show slave status\G;
可看到 Slave_IO_State 为空， Slave_IO_Running 和 Slave_SQL_Running 是 No，表明 Slave 还
没有开始复制过程。
## 开启主从同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
## 再查看主从同步状态
mysql> show slave status\G;

主要看以下两个参数，这两个参数如果是 Yes 就表示主从同步正常

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

可查看 master 和 slave 上线程的状态。在 master 上，可以看到 slave 的 I/O 线程创建的连接：

mysql01: mysql> show processlist\G;
mysql02: mysql> show processlist\G;

完成，测试在mysql01上新建数据库db1，查看mysql02。

从mysql02到mysql01是一样的步骤：

切换到mysql02上

##创建数据同步用户，并授予相应的权限

mysql> grant replication slave, replication client on *.* to 'repl02'@'192.168.1.222' identified by
'123456';
Query OK, 0 rows affected (0.00 sec)

## 刷新授权表信息

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

## 查看 position 号，记下 position 号（从机上需要用到这个 position 号和现在的日志文件)

mysql> show master status;
change master to master_host='192.168.1.223', master_user='repl02',master_password='123456', master_port=3306,master_log_file='mysql02.000002',master_log_pos=567,
master_connect_retry=30;
mysql> start slave;Query OK, 0 rows affected (0.00 sec)

## 再查看主从同步状态

mysql> show slave status\G;

在mysql02上创建数据库db2，查看mysql01即可

The above is the detailed content of What is mysql cluster? Introducing mysql cluster. For more information, please follow other related articles on the PHP Chinese website!