MySQL 5.7 Dual master replication +keepalived, general business is generally enough

Business requirements:

Build 2 databases for Zabbix, a library for server monitoring, a library for network monitoring.

Hardware:

Two servers, hard disk is 1.2 T SSD card, memory 128G

Architecture:

Want to do two-master replication +keepalived, the architecture is probably as

Host a ip:192.168.1.2

Host B ip:192.168.1.3

vip:192.168.1.4

First, install MySQL 5.7

Download the Yum package for your operating system at the URL below

http://dev.mysql.com/downloads/repo/yum/

Run the following two commands to install

RPM-IVH mysql57-community-release-el6-8.noarch.rpm

Yum-y Install mysql.x86_64 mysql-server.x86_64 mysql-devel.x86_64

Second, do some simple optimization work

echo "NoOp" >/sys/block/nvme0n1/queue/scheduler

Wait a minute

Third, configuration/etc/my.cnf

The core work is this, because 5.7 of the changes are relatively large, I would simply give a list of my configuration instructions.

Character_set_server=utf8

Max_connections = 5000

Max_connect_errors = 200000

Transaction_isolation = read-committed

Explicit_defaults_for_timestamp = 1

Tmp_table_size = 67108864

Max_allowed_packet = 16777216

Interactive_timeout = 57600//old newspaper timed out, I set a little bigger

Wait_timeout = 57600//old newspaper timed out, I set a little bigger

Read_buffer_size = 16777216

Read_rnd_buffer_size = 33554432

Sort_buffer_size = 134217728

Join_buffer_size = 134217728

Query_cache_size = 0

Query_cache_type = 0

Server-id = 1//The other one is set to 2

auto_increment_increment=2

Auto_increment_offset=1//Another set to 2

Binlog-do-db = Nzabbix

Binlog-do-db = Szabbix

Binlog-ignore-db = monitor_db

REPLICATE-DO-DB = Nzabbix//data that needs to be synchronized

REPLICATE-DO-DB = Szabbix//data that needs to be synchronized

replicate-ignore-db = MySQL

REPLICATE-IGNORE-DB = sys

REPLICATE-IGNORE-DB = monitor_db//used to see a library for MySQL

Slave-skip-errors=all//Fear of encountering errors stop syncing, ignoring all errors

Sync_binlog = 0//zabbix data is not very important, allow crash to lose data

Master_info_repository = TABLE

Relay_log_info_repository = TABLE

Gtid_mode = ON//Open Gtid, this is a new feature, our copy is based on Gtid, so open

Enforce_gtid_consistency = 1

Binlog_gtid_simple_recovery = 1

Log-slave-updates

# slave

Slave-parallel-type = Logical_clock

Slave-parallel-workers = 0//log old newspaper a mistake, first off parallel

#slave-parallel-workers = 16

####### #innodb settings########

Innodb_buffer_pool_size = 100G//75% more suitable for memory

Innodb_buffer_pool_instances = 16

Innodb_buffer_pool_load_at_startup = 1

Innodb_buffer_pool_dump_at_shutdown = 1

innodb_lru_scan_depth = 2000

Innodb_lock_wait_timeout = 10000

innodb_io_capacity = 4000

Innodb_io_capacity_max = 8000

Innodb_flush_method = O_direct

Innodb_flush_neighbors = 1

Innodb_log_file_size = 4G

Innodb_log_buffer_size = 16777216

Innodb_purge_threads = 4

Innodb_large_prefix = 1

Innodb_thread_concurrency = 64

Innodb_print_all_deadlocks = 1

Innodb_strict_mode = 1

Innodb_sort_buffer_size = 67108864

Innodb_flush_log_at_trx_commit = 2

Innodb_read_io_threads = 16

Innodb_write_io_threads = 16

[mysqld-5.7]

innodb_buffer_pool_dump_pct = 40

Innodb_page_cleaners = 4

Innodb_undo_log_truncate = 1

Innodb_max_undo_log_size = 2G

innodb_purge_rseg_truncate_frequency = 128

Binlog_gtid_simple_recovery=1

Log_timestamps=system

Transaction_write_set_extraction=murmur32

Show_compatibility_56=on

The above is only a part of the configuration, we can check the data on the Internet, and another one to change the place I have marked on the above.

The key is Gtid:

The Gtid is a killer feature launched from 5.6, with the Gtid feature that greatly improves the efficiency and consistency of primary and standby switching.

A new system table gtid_executed was introduced in MySQL5.7.5:

The description of the table refers to the official documentation:

Http://dev.mysql.com/doc/refman/5.7/en/replication-gtids-concepts.html#replication-gtids-gtid-executed-table

Correspondence worklog:http://dev.mysql.com/worklog/task/?id=6559

Iv. Initializing directories

5.7 has not been initialized with mysql_install_db, has been changed to the following command

Mysqld--initialize--datadir=/data0/mysql/

Chmod-r 755/data0/mysql/

Chown-r mysql.mysql/data0/mysql/

Five, set the password

5.7 More annoying, the initial password is not empty, it can be said that the security is higher.

Initialize the password in the initialization log

grep "Temporary password" Error.log

If you log in to MySQL to change your password, you may get an error

Errror 1820 (HY000): You must reset your password using the ALTER USER statement before executing this statement.

In order to not trouble later, to reduce password policy.

MySQL > SET GLOBAL validate_password_policy= ' low ';

mysql > Alter user ' root ' @ ' localhost ' identified by ' Your-password ';

MySQL > Flush privileges;

If MySQL and mysqladmin password line password may also appear a warning

Mysql-u root-p password ' your-password '

MySQL: [Warning] Using a password on the command line interface can is insecure.

There are a number of solutions, which can be consulted:

Http://www.quwenqing.com/read-247.html

One of the solutions is the most reliable

Export Mysql_pwd=password

But this error hint is very annoying, command line no parameters can be closed, I do not want to write the password in the my.cnf. You'll find that Zabbix monitoring MySQL data comes out as a hint, and we also need to change the command line password in the mysql-monitored shell into export form.

Vi. Setting up data synchronization

Add simultaneous users on A and B

Use MySQL;

Create user ' repl ' @ ' 192.168.1.% ' identified by ' Your-password ';

GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 192.168.1.% ';

Flush privileges;

Lock the table, flush the two machine data, and unlock it.

Let's see if Gtid is open.

Mysql> show global variables like '%gtid% ';

+--------------------------+-------+

| variable_name | Value |

+--------------------------+-------+

| enforce_gtid_consistency | On |

|       gtid_executed | |

| Gtid_mode | On |

|       gtid_owned | |

|       gtid_purged | |

+--------------------------+-------+

5 rows in Set (0.10 sec)

Description The Gtid feature has been started.

Gtid synchronization data no longer log the other side of the log file and location, with Master_auto_position=1 on the line, but you use the old method to view master's logfile and Logpos, synchronization is also possible.

Run on both A and b

mysql> Change Master to master_host= ' 192.168.1.2 ', master_user= ' repl ', master_password= ' Your-password ', master_ Auto_position=1;

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

mysql> start slave;

Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> start slave;

View the synchronization status on a and B

Show Slave Status\g

#表示同步的文件和位置

master_log_file:mysql-bin.000035

read_master_log_pos:895597105

#显示下面表示工作正常

Slave_io_running:yes

Slave_sql_running:yes

#表示当前同步的数据库

Replicate_do_db:nzabbix,szabbix

Double master replication is relatively simple, summed up is:

Install mysql-> optimization System--optimization configuration my.cnf-> Initialize mysqld-> Add synchronization user, start synchronization, view sync status.

VI. installation and setup of keepalived

Keepalived installation is relatively simple

Yum Install keepalived

Setting up logs

Cat/etc/sysconfig/keepalived

Keepalived_options= "-d-d-S 0"

Last added in rsyslog.conf

local0.*/var/log/keepalived.log

Restarting Rsyslog and keepalived

Keepliaved is also very simple to configure

Global_defs {

Notification_email {

[Email protected]

}

router_id Mysql-ha85

}

Vrrp_script Check_run {

Script "/data0/keepalived_check_mysql.sh" #监控MySQL的脚本

Interval 3

}

Vrrp_sync_group VG1 {

Group {

Vi_1

}

}

Vrrp_instance Vi_1 {

State BACKUP

Interface Eth0 #指定虚拟IP的网络接口

virtual_router_id #VRRP组名, the settings for two nodes must be the same to indicate that each node belongs to the same VRRP group

Priority #主节点的优先级 (between 1-254), the standby node must be lower than the primary node.

Advert_int 1 #组播信息发送间隔, two node settings must be the same

Nopreempt

Authentication {#设置验证信息, two nodes must be consistent

Auth_type PASS

Auth_pass 1111

}

Track_script {

Check_run

}

virtual_ipaddress {

192.168.1.4 #虚拟IP, provide the IP address of MySQL service externally

}

}

Another priority setting can be set at the bottom, or it can be set to Master-backup to see your own switching needs.

The role of/data0/keepalived_check_mysql.sh is to check whether the current MySQL is working properly

I did a write operation in it to determine if MySQL is normal.

$MYSQL-H $MYSQL _host-u $MYSQL _user-e "use monitor_db; UPDATE monitor_db SET createdate=now () WHERE id=1; ">/dev/null 2>&1

I also wrote a program in Cron, run every minute, why do you want to do this? Main i restart MySQL, it will happen automatically kill keepalived, and IP drift away, this time if MySQL restart good, keepalived can not start themselves. The procedure is as follows:

if [!-Z "$ (/bin/ps aux|grep-v grep|grep ' mysqld--basedir ')"];then

echo "Mysqld is running"

If [-Z] $ (/bin/ps aux|grep-v grep|grep '/usr/sbin/keepalived ') "];then

echo "Keepalived is not running"

Service keepalived Restart

Fi

Fi

is to judge if MySQL is still alive, start keepalived, but this piece of code is a bit primitive, theoretically should determine whether MySQL is still alive, and can write data, and then start keepalived.

This set of MySQL dual master replication +keepalived architecture can be applied to a variety of business, we can use flexibly, extension is also very convenient.

Read the original

MySQL 5.7 Dual master replication +keepalived, general business is generally enough