MySQL Dual master Mutual preparation

First, install MySQL

There are so many different ways.

Second, excerpt from: https://linux.cn/thread-14135-1-1.html

1) First add the following in the [Mysqld] field on DB1 my.cnf:
Server-id = 1//database ID number, 1 is represented as master, where master_id must be a positive integer value between 1 and 232–1;
Log-bin=mysql-bin//enable binary logging;
Relay-log=mysql-relay-bin//used to define Relay-log log file naming format
Replicate-wild-ignore-table=mysql.%//replicate-wild-ignore-table is a filtering replication option that filters databases that do not need to be replicated
replicate-wild-ignore-table=test.%//mysql.% means that all objects under the MySQL library are not copied
replicate-wild-ignore-table=infomation_schema.%//corresponds to the/replicate-wild-do-table, which specifies the database or table to be replicated

It is important to note that you should not use the BINLOG-DO-DB or BINLOG-IGNORE-DB option on the main library, or use the replicate-do-db or REPLICATE-IGNORE-DB option from the library. Because this may cause problems with cross-Library update failures.
It is recommended to use the replicate-wild-ignore-table and replicate-wild-do-table two options from the library to resolve replication filtering issues.

2) then modify the DB2 on the MySQL configuration file my.cnf
Server-id = 2
Log-bin=mysql-bin
Relay-log=mysql-relay-bin
Replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=infomation_schema.%

3) Manually synchronize the database
If there is data already on the DB1, before performing the primary master interop, you need to keep the data of the two MySQL on the DB1 and DB2 in sync, first back up the MySQL data on DB1 and execute the following SQL statement:
Mysql>flush TABLES with READ LOCK; Note that after executing this command, do not exit the terminal, or the lock will fail.
#cd/var/lib
#tar ZCVF mysql_bak.tar.gz MySQL
#scp mysql_bak.tar.gz db2:/var/lib//upload the data to DB2, restart the MySQL on DB1 and DB2 in turn

4) Create a replication user and authorize
Mysql>grant replication Slave on * * to [e-mail protected] identified by ' Repl_user ';
Mysql>show Master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|   mysql-bin.000001 |       106 |         | |
+------------------+----------+--------------+------------------+

A) then set DB1 as its primary server on DB2, as follows:
Mysql>change MASTER to master_host= ' 192.168.1.11 ', master_user= ' repl_user ', master_password= ' Repl_user ', master_ Log_file= ' mysql-bin.000001 ', master_log_pos=106;
Note that the Master_log_file and master_log_pos two options, the values of these two options are exactly the results that are queried on the DB1 through show MASTER status;

b) The slave service can then be started on DB2, and the SQL command is as follows:
Mysql>start slave;
can show slave status\g by SQL statement; Query the running status details of slave on DB2;

Here, MySQL master-slave replication from DB1 to DB2 has been completed. Next, start configuring MySQL master-slave from DB2 to DB1.

5) First create a copy user on DB2;
Mysql>grant replication Slave on * * to [e-mail protected] identified by ' Repl_user ';
Mysql>show Master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|   mysql-bin.000001 |       106 |         | |
+------------------+----------+--------------+------------------+

A) then set DB2 as its primary server on DB1, as follows:
Mysql>change MASTER to master_host= ' 192.168.1.22 ', master_user= ' repl_user ', master_password= ' Repl_user ', master_ Log_file= ' mysql-bin.000001 ', master_log_pos=106;
Note that the Master_log_file and master_log_pos two options, the values of these two options are exactly the results that are queried on the DB1 through show MASTER status;

b) The slave service can then be started on DB1, and the SQL command is as follows:
Mysql>start slave;
can show slave status\g by SQL statement; Query the running status details of slave on DB1;

At this point, the master-slave copy of the MySQL dual Master interoperability mode is configured.

6) Configure keepalived to achieve MySQL dual master high Availability
Before you can perform a highly available configuration, you first need to install keepalived software on DB1 and DB2. About Keepalived installation, here is not detailed instructions, directly into the keepalived configuration process.
A) The following is the contents of the/etc/keepalived/keepalived.conf file on the DB1 server:
Global_defs {
Notification_email {
[Email protected]
[Email protected]
[Email protected]
}
Notification_email_from [email protected]
Smtp_server 192.168.200.1
Smtp_connect_timeout 30
router_id Mysqlha_devel
}

Vrrp_script Check_mysqld {
Script "/etc/keepalived/mysqlcheck/check_slave.pl 127.0.0.1" #检测MySQL复制状态的脚本
Interval 2
Weight 21
}

Vrrp_instance ha_1 {
State BACKUP #在DB1上和DB2上都设置为BACKUP
Interface eth0
VIRTUAL_ROUTER_ID 80
Priority 100
Advert_int 2
Nopreempt #不抢占模式, can only be set on high priority machines, low priority is not set.

Authentication {
Auth_type PASS
Auth_pass 1111
}

Track_script {
Check_mysqld
}

virtual_ipaddress {
192.168.1.33/24 Dev eth0 #MySQL的对外服务IP, VIP.
}
}

Where the contents of the/etc/keepalived/mysqlcheck/check_slave.pl file are as follows:
#!/usr/bin/perl-w
Use DBI;
Use Dbd::mysql;

#config variables
$SBM = 120;
$db = "Hamysql"
$host = $ARGV [0];
$port = 3306;
$user = "root";
$PW = "root"

#SQL Query
$query = "show slave status";

$DBH = Dbi->connect ("Dbi:mysql: $db: $host: $port", $user, $PW, {raiseerror = 0,printerror = 0});

if (!defined ($DBH)) {
Exit 1;
}

$sqlQuery = $dbh->prepare ($query);
$sqlQuery->execute;

$Slave _io_running = "";
$Slave _sql_running = "";
$Seconds _behind_master = "";

while (my $ref = $sqlQuery->fetchrow_hashref ()) {
$Slave _io_running = $ref->{' slave_io_running '};
$Slave _sql_running = $ref->{' slave_sql_running '};
$Seconds _behind_master = $ref->{' Seconds_behind_master '};
}

$sqlQuery->finish;
$DBH->disconnect ();

if ($Slave _io_running eq "no" | | $Slave _sql_running eq "no") {
Exit 1;
}else {
if ($Seconds _behind_master > $SBM) {
Exit 1;
}else {
Exit 0;
}
}

This is a Perl-written detection mysqlmysql copy state script, just modify the file in the MySQL database, the data port number, user name and password can be used directly, and to ensure that the file has executable permissions.

b) then copy the keepalived.conf file and the check_slave.pl file on the DB1 to the corresponding location on the DB2.
and modify the DB2 on the keepalived.conf file in the priority value of 80, because the configuration is not preemption mode, so you also need to remove the nopreempt option.
After the configuration is complete, the keepalived service is started on DB1 and DB2 respectively, and the VIP address should normally run on DB1.

Third, the attention point

If MySQL is a copy then the UUID will have a problem to modify, find the directory DataDir in my.cnf, modify the value of Service-uuid in AUTO.CNF.

MySQL Dual master Mutual preparation