MySQL Cluster tutorial (v) Multi-master multi-building from the environment

MySQL Cluster tutorial (v) Multi-master multi-building from the environment

A: Overview:

A master more from, can alleviate the pressure of reading, but once the main downtime, you cannot write;

So we can adopt the dual master dual slave architecture

Architecture Planning:

Master Master 3307---> from slave 3309

Master Master 3308---> from slave 3310

3307 <---> 3308 inter-Main from

2 write nodes, and 2 read nodes under each write node;

B: File configuration:

1. Configure the configuration file for each MySQL server my.cnf

[Client]

Port = 3307

Socket =/usr/local/mysql-5.7.18/data/3307/mysql.sock

Default-character-set=utf8

[Mysqld]

Port = 3307

Socket =/usr/local/mysql-5.7.18/data/3307/mysql.sock

DataDir =/usr/local/mysql-5.7.18/data/3307

Log-error =/usr/local/mysql-5.7.18/data/3307/error.log

Pid-file =/usr/local/mysql-5.7.18/data/3307/mysql.pid

Character-set-server=utf8

Lower_case_table_names=1

Autocommit = 1

Other 3308 3309 3,310 you only need to modify the port number

2, with a master more from the same, all the node configuration file plus Binlog configuration

3307 Configuration:

Log-bin=mysql-bin

server-id=3307

3308 Configuration:

Log-bin=mysql-bin

server-id=3308

3309 configuration:

server-id=3309

3310 Configuration:

server-id=3310

Note: Here we are the main (master and slave) of the 3307 3308 Mutual

Log-bin=mysql-bin

Port of the Server-id= instance (Server-id to be unique)

3, the first master server 3307 my.cnf file added the following configuration: (Master 3307)

auto_increment_increment=2

Auto_increment_offset=1

Log-slave-updates

Sync_binlog=1

4, the second master server 3308 my.cnf file added the following configuration: (Master 3308)

auto_increment_increment=2

auto_increment_offset=2

Log-slave-updates

Sync_binlog=1

C: Configuration Item Description:

Auto_increment_increment, which controls the self-increment step of the primary key, is used to prevent duplicate self-increment field values from being duplicated between master and master, typically auto_increment_increment=n, how many primary servers, n is set to how much;

Auto_increment_offset=1 sets the self-increment starting value, which is set to 1, so that the Auto_increment field of master produces the values: 1, 3, 5, 7, ... Equal Odd ID

Note Auto_increment_offset settings, different master settings should not be the same, otherwise it is easy to cause primary key conflicts, such as Master1 offset=1, then Master2 offset=2,master3 offset=3

In dual-master mode, the Log-slave-updates configuration item must be configured, otherwise the update data on Master1 (3307) will be updated on Master2 (3308) and slave1 (3309), but will not be updated on slave2 (3310)

Sync_binlog means that every few transactions commit, MySQL Binlog cache into the log file, the default is 0, the most secure is set to 1;

F: Master and slave settings:

1: Start the service:

Go to the/usr/local/mysql-5.7.18/bin directory, restart the four MySQL service, and start with the specified configuration file:

./mysqld_safe--DEFAULTS-FILE=/USR/LOCAL/MYSQL-5.7.18/DATA/3307/MY.CNF &

./mysqld_safe--DEFAULTS-FILE=/USR/LOCAL/MYSQL-5.7.18/DATA/3308/MY.CNF &

./mysqld_safe--DEFAULTS-FILE=/USR/LOCAL/MYSQL-5.7.18/DATA/3309/MY.CNF &

./mysqld_safe--DEFAULTS-FILE=/USR/LOCAL/MYSQL-5.7.18/DATA/3310/MY.CNF &

2. Create a copy account on both primary servers and authorize: (3307, 3308)

Use port, host login to enter two main MySQL:

./mysql-uroot-p-p3307-h127.0.0.1

./mysql-uroot-p-p3308-h127.0.0.1
Create an account of the replication data on the primary server and authorize: (copy is the first name: recommended to follow the tutorial: Feel free to change 123456 is the password)

grantreplication slave on * * to ' copy ' @ ' percent ' identified by ' 123456 ';

3. Stop copying and refresh the Binlog log on both primary servers: (3307, 3308)

4. Stop replication from the server: (3309, 3310)

Execute on the MySQL command line:

Stop slave;

Reset slave;

Reset Master;

5. View binary log files and position values on the primary server: (3307, 3308)

Execute on the MySQL command line:

That is, executed on 3307 and 3308 respectively: Showmaster status;

6, set the master on slave (equivalent to 4 units need to set)

Set from server 3308, 3309, their master is 3307, that is, on 3308 and 3309 do the following:

Change Masterto master_host= ' 192.168.91.135 ',

master_user= ' Copy ',

Master_password= ' 123456 ',

master_port=3307,

Master_log_file= ' mysql-bin.000001 ',

master_log_pos=154;

Set from server 3307, 3310, their master is 3308, that is, on 3307 and 3310 do the following:

Change Masterto master_host= ' 192.168.91.135 ',

master_user= ' Copy ',

Master_password= ' 123456 ',

master_port=3308,

Master_log_file= ' mysql-bin.000001 ',

master_log_pos=154;

7. Execute on four MySQL servers: startslave; (MySQL command line execution)

Enter master-slave replication status after execution

Double Master double Slave Verification:

Check the function status from the server replication, execute the command:

mysql> showslave Status \g

If the value of slave_io_running and slave_sql_running is yes, that means the replication function is configured properly;

After you set up the dual master double from copy:

1. One of the main problems, you can switch to another master to write data, the new master synchronization data to its from;

2. After the old primary recovery service is in question, the new master synchronizes the data to it, and it synchronizes the data to itself, so that the data does not appear out of sync and the service is not available.

MySQL Cluster tutorial (v) Multi-master multi-building from the environment