Shulou(Shulou.com)06/01 Report--

I. Mutual master-slave configuration

1.1. Resource situation

192.168.11.177 server1

192.168.11.180 server2

192.168.11.210 VIP

1.2, server1 configuration

# vi / etc/my.cnf

[mysqld]

Server-id=1

Log-bin=mysql-bin

Relay_log=mysql-realy-bin

Relay_log_index=slave-mysql-realy-bin.index

Expire_logs_days=15

Binlog_format=mixed

Auto-increment-increment=2

Auto-increment-offset= 1

Innodb_flush_log_at_trx_commit=1

Replicate-ignore-db=sys

Replicate-ignore-db=mysql

Replicate-ignore-db=information_schema

Replicate-ignore-db=performance_schema

Datadir=/data/mysql_data

Socket=/var/lib/mysql/mysql.sock

Character_set_server=utf8

Sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

# Disabling symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

# innodb optimization

Innodb_buffer_pool_size=8G

Innodb_log_file_size=256M

Innodb_flush_method=O_DIRECT

Max_connections=500

Innodb_autoextend_increment=128

1.3The server2 configuration

# vi / etc/my.cnf

Server-id=2

Log-bin=mysql-bin

Relay_log=mysql-realy-bin

Relay_log_index=slave-realy-bin.index

Expire_logs_days=15

Binlog_format=mixed

Auto-increment-increment=2

Auto-increment-offset= 2

Innodb_flush_log_at_trx_commit=1

Replicate-ignore-db=sys

Replicate-ignore-db=mysql

Replicate-ignore-db=information_schema

Replicate-ignore-db=performance_schema

Datadir=/data/mysql_data

Socket=/var/lib/mysql/mysql.sock

Character_set_server=utf8

Sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

# Disabling symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

# innodb optimization

Innodb_buffer_pool_size=8G

Innodb_log_file_size=256M

Innodb_flush_method=O_DIRECT

Max_connections=500

Innodb_autoextend_increment=128

1.4. Change the account password on server1 and server2 to create a synchronous account

# mysql-p

Mysql > set password=password ('*')

Mysql > create user repluser@'%' identified by'*'

Mysql > grant replication slave, replication client on *. * to repluser@'%'

1.5. Establish synchronization with server2

Master status of server1

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000003 | 882 | |

+-+

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.11.177', MASTER_USER='repluser', MASTER_PASSWORD='*', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=882

Query OK, 0 rows affected, 2 warnings (0.10 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State:

Master_Host: 192.168.11.177

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 882

Relay_Log_File: mysql-realy-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB: sys,mysql,information_schema,performance_schema

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 882

Relay_Log_Space: 154

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

Master_UUID:

Master_Info_File: / data/mysql_data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

# vi / data/mysql_data/auto.cnf

[auto]

Server-uuid=cbcefb67-9f9a-11e8-91b3-06ba24001d86

>

Server-uuid=cbcefb77-9f9a-11e8-91b3-06ba24001d86

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.11.177

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 882

Relay_Log_File: mysql-realy-bin.000003

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: sys,mysql,information_schema,performance_schema

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 882

Relay_Log_Space: 527

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: cd146946-9f95-11e8-9a29-063696001d83

Master_Info_File: / data/mysql_data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

Mysql > start slave

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000003 | 154 | |

+-+

1.6. establish synchronization with server1

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.11.180', MASTER_USER='repluser', MASTER_PASSWORD='*', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154

Query OK, 0 rows affected, 2 warnings (0.22 sec)

Mysql > start slave

Query OK, 0 rows affected (0.01 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.11.180

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 154

Relay_Log_File: mysql-realy-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: sys,mysql,information_schema,performance_schema

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 154

Relay_Log_Space: 527

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2

Master_UUID: cbcefb67-9f9a-11e8-91b3-06ba24001d86

Master_Info_File: / data/mysql_data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

Mysql > create database ceshi_db

Query OK, 1 row affected (0.01sec)

Mysql > use ceshi_db1

Database changed

Mysql > create table home (id int (10) not null,name char (10))

Query OK, 0 rows affected (0.14 sec)

1.7. Server2 verification

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | ceshi_db |

| | mysql |

| | performance_schema |

| | sys |

+-+

5 rows in set (0.00 sec)

Mysql > use ceshi_db

Database changed

Mysql > show tables

+-+

| | Tables_in_ceshi_db |

+-+

| | home |

+-+

1 row in set (0.00 sec)

Mysql > create database ceshi_db1

Query OK, 1 row affected (0.01sec)

Mysql > create table home (id int (10) not null,name char (10)); ^ C

Mysql > use ceshi_db1

Database changed

Mysql > create table home (id int (10) not null,name char (10))

Query OK, 0 rows affected (0.09 sec)

1.8. server1 verification

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | ceshi_db |

| | ceshi_db1 |

| | mysql |

| | performance_schema |

| | sys |

+-+

6 rows in set (0.00 sec)

Mysql > use ceshi_db1

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > show tables

+-+

| | Tables_in_ceshi_db1 |

+-+

| | home |

+-+

1 row in set (0.00 sec)

II. Keepalived configuration

2.1By server1 configuration

# cat / etc/keepalived/keepalived.conf

Global_defs {

Smtp_server 127.0.0.1

Smtp_connect_timeout 30

Router_id mysql-1

}

Vrrp_script chk_mysql {

Script "/ etc/keepalived/scripts/mysql_check.sh"

Interval 2

Weight-5

Fall 2

Rise 1

}

Vrrp_instance VI_1 {

State MASTER

Interface eth0

Virtual_router_id 61

Priority 100

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Track_script {

Chk_mysql

}

Virtual_ipaddress {

182.168.11.210

}

}

2.2, server2 configuration

# cat / etc/keepalived/keepalived.conf

Global_defs {

Smtp_server 127.0.0.1

Smtp_connect_timeout 30

Router_id mysql-2

}

Vrrp_script chk_mysql {

Script "/ etc/keepalived/scripts/mysql_check.sh"

Interval 2

Weight-5

Fall 2

Rise 1

}

Vrrp_instance VI_1 {

State BACKUP

Interface eth0

Virtual_router_id 61

Priority 99

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Track_script {

Chk_mysql

}

Virtual_ipaddress {

182.168.11.210

}

}

2.3, script

# cat / etc/keepalived/scripts/mysql_check.sh

#! / bin/bash

Counter=$ (netstat-na | grep "LISTEN" | grep "3306" | wc-l)

If ["${counter}"-eq 0]; then

Service keepalived stop

Fi

2.4. Non-preemption mode

If server1 is set to VIP non-preemption mode, make the following changes

Server1

# vi / etc/keepalived/keepalived.conf

Add

Nopreempt

State MASTER

>

State BACKUP

# prevent switching to slave library, automatically switching back to master library after master keepalived is restored

Reference:

Https://blog.csdn.net/qq_36276335/article/details/69942101

Https://www.cnblogs.com/kevingrace/p/6710136.html

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.