MySQL Cluster data synchronization

MySQL cluster configuration is essential in website load balancing;

First of all, I personally prepared the load balance mode;

1, through the Nginx direction agent to spread the server pressure on the various servers;

2, the same code logic in each server;

3, using the Redis cache to save in-memory data, using the Redis synchronization function to synchronize different server memory data;

4, in the MySQL cluster configuration to achieve database data synchronization;

Here I have collated several data synchronization methods;

One: Master-slave server synchronization;

As the name implies: the main server is responsible for data deletion and modification, from the server responsible for synchronizing data;

The primary server establishes a binary file, each resulting statement change or disk change written to the date;

Read the main service from the server to the binary day to the date of the read to the Relaylog from the service, read from the service Relaylog synchronous primary master server;

The master server establishes the authorized copy account;

Use the account from the server to listen to the master server;

Steps:

1, first need at least two servers, my Side 118.xxx.xxx.1 (master), 118.xxx.xxx.2 (from) Two servers, two build MySQL way different, one installs MySQL, and mysql-server; One by direct installation of the mariadb way; no impact;

2, the main server modified/ETC/MY.CNF;

#在 [Mysqld], set the binary date to
#server-id general use server after one
Server-id=1log-binary=mysql-bin# Monitor Change mode, statement statement change, row row change, mixed smart selection binlog-format=mixed

3. Modify/ETC/MY.CNF from the server

#[mysqld] Add relay-Log

#server-id general use server after one

server-id=2

Relay-log=mysql-relay

4. Restart two server MySQL services

// 1 systemctl restart MySQL // 2systemctl Restart MARIADB

5, the main service establishment authorized account replication Client,replication Slave account Permit service binary date to

' REPL1 ' @'118.xxx.xxx.2'password';

Note that the permissions of the account can only be filled with * *, otherwise the following error will be reported

1221 (HY000): Incorrect usage of DB GRANT and GLOBAL privileges

6, from the service through the Account Link master server

#进入mysql, clears the sync replication information from the library; reset slave;

#改变同步主机的信息
Change Master to
Master_host= ' 118.xxx.xxx.1 ',
Master_user= 'repl1 ',
master_password= ' Password ',
Master_log_file= ' mysql-bin.000003 ',
master_log_pos=543;

Primary server binary Day to/var/lib/mysql

Show master status on the master server, view the main service day to status, file currently used log files, position in the log file currently used, and start synchronizing from this position;

7. View the status of synchronization information from the service, and start;

Show slave status \g

// start the sync start slave;

7. View the status after startup waiting for Master to send event indicates normal;

Error:

The server is not configured as slave; Fix in config file or with change MASTER to

This indicates a server-id error, where the Server-id is correctly written under [mysqld];

Failed to open the relay log './mariadb-relay-bin.000001 ' (Relay_log_pos 4)

No relay-bin.000001 date found, because the original configuration was not cleared before the modification;

Solution: Reset slave; After the change master to ...;

MySQL Cluster data synchronization