A MySQL MHA architecture build process

"Introduction to the Environment"

System environment: Red Hat Enterprise Linux 7 + 5.7.18 + MHA version 0.57

System	Ip	Host Name	Note	Version
XX system	192.168.142.111	Mysqlmha1	Main Library	5.7.18-log MySQL Community Server (GPL)
	192.168.142.112	Mysqlmha2	Standby library (pre-master library)
	192.168.142.113	Mysqlmha3	Standby Library/mha MGM
	192.168.142.111	Mysqlmha3	Vip

"Build Steps: Software Deployment"

User Directory Creation

#groupadd MySQL

#useradd mysql-g MySQL

#mkdir/home/mysql/logs//create log directory

#mkdir/home/mysql/tmp//Create PID Directory

Place it under/var/lib and unzip the installation package

#tar-XVF mysql-5.7.18-linux-glibc2.12-x86_64.tar.gz

#mv mysql-5.7.20-linux-glibc2.12-x86_64 MySQL//renamed to MySQL directory

#mkdir-P/var/lib/mysql/data//Create data Directory

#chown-R mysql:mysql/var/lib/mysql/data/home/mysql/tmp/home/mysql/logs//Modify Permissions

Initializing MySQL Database

# Cd/var/lib/mysql

#./bin/mysqld--initialize--user=mysql--basedir=/var/lib/mysql--datadir=/var/lib/mysql/data--innodb_undo_ Tablespaces=3--explicit_defaults_for_timestamp

Create configuration file, note the file directory and server_id, other parameters can be adjusted according to the specific situation

Cat >/ETC/MYMHA.CNF
#For advice on how to change settings
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[Mysqld]
#
# Remove Leading # and set to the amount of RAM for the most important data
# Cache in MySQL. Start at 70% of all RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove Leading # to turn on a very important data integrity option:logging
# Changes to the binary log between backups.
# Log_bin
#
# Remove Leading # To set options mainly useful for reporting servers.
# The server defaults is faster for transactions and fast selects.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
Datadir=/mysqldata/mysql01
Socket=/mysqldata/mysql01/mysql01.sock
Port = 3306
# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0

Log-error=/mysql/mysql57/logs/mysql01.err
Pid-file=/mysqldata/mysqldb01.pid

Server-id = 111
Basedir=/mysql/mysql57
Explicit_defaults_for_timestamp

Default_storage_engine=innodb
Default_tmp_storage_engine=innodb
Character_set_server=utf8
User=mysql
Log_timestamps=system
Sync_binlog=1

Innodb_flush_log_at_trx_commit=1
innodb_buffer_pool_size=1g
Innodb_thread_concurrency=32
Innodb_flush_method=o_direct
innodb_io_capacity=200
Innodb_file_per_table=1

max_connections=1000
max_user_connections=100
Long_query_time=1
Lower_case_table_names=1
Slow_query_log=1
Slow_query_log_file=/mysqlslowlog/slow01.log
Tmpdir=/mysql/mysql57/tmp
wait_timeout=300
thread_cache_size=100
Expire_logs_days=30

#binlog Set
Log-bin=/mysql/mysql57/logs/binlog01
Relay-log=/mysql/mysql57/repaylog/relaylog01
Binlog-format = ROW
Gtid-mode = On
Enforce-gtid-consistency = On
Log-slave-updates = On
Master-info-repository = TABLE
Relay-log-info-repository = TABLE
Binlog-checksum = NONE

Slave-parallel-workers=4
Slave-preserve-commit-order=1
Slave-parallel-type=logical_clock

Set the boot up and start the MySQL service

#cp/var/lib/mysql/support_files/mysql.server/etc/init.d/mysql

#service MySQL Start

#service MySQL Status

Configure environment variables and load environment variables

#vi/etc/profile

Path=/var/lib/mysql/bin: $PATH

#source/etc/profile

Or use MySQL to start the specified configuration file directly, you can use this method to start a host of multiple MySQL instances

#mysqld--DEFAULTS-FILE=/ETC/MYMHA.CNF &

To view the database initialization password

#cat/home/mysql/logs/mysql.err|grep-i ' temporary password '

Change password after accessing the database using the root user

Mysql>set Password = password (' mysql ');

Other host installation MySQL step is consistent with the above, Note modify the server_id parameter in/etc/my.cnf

Other repositories or directly using the Copy method to install MySQL

#scp-R/var/lib/mysql 192.168.142.xxx:/var/lib/

#chown-R mysql:mysql /var/lib/mysql

#vi/etc/my.cnf

server_id = xxx

#cd/var/lib/mysql/data

#rm auto.cnf

Initializing MySQL Database

#./bin/mysqld--initialize--user=mysql--basedir=/var/lib/mysql--datadir=/var/lib/mysql/data--innodb_undo_ Tablespaces=3--explicit_defaults_for_timestamp

Change password after accessing the database using the root user

#cat/home/mysql/logs/mysql.err|grep-i ' temporary password '

Mysql>set Password = password (' mysql ');

Complete the MySQL installation after performing the above

"Build step: Master-slave Configuration"

All nodes create synchronous user repl,ip% representation range (1 to many) with password Repl

mysql>create user [email protected] ' 192.168.142.% ' identified by ' repl ';

Permissions that give the user the ability to synchronize tables for all databases

Mysql>grant REPLICATION SLAVE On * * to [email protected] ' 192.168.142.% ';

Mysql>flush privileges;

Two standby node execution

Mysql>change Master to master_user= ' Repl ', master_host= ' 192.168.142.111 ', master_password= ' repl ', master_port= 3306,master_auto_position=1;

Start from library service

mysql> slave start;

View Master-slave replication status

Mysql> Show Slave Status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.142.111
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:binlog01.000011
read_master_log_pos:529
relay_log_file:relaylog01.000002
relay_log_pos:395
relay_master_log_file:binlog01.000011
Slave_io_running:yes
Slave_sql_running:yes

......

Executed_gtid_set:42f239e7-5908-11e8-8214-000c2926d694:1,
4651522f-5908-11e8-807d-000c293193c4:1,
8d7abed9-d4cd-11e7-a165-000c29c913a2:1-7,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-15:1000002-1000075
Auto_position:1
replicate_rewrite_db:
Channel_name:
Master_tls_version:
1 row in Set (0.00 sec)

Check that slave_io_running and slave_sql_running are functioning properly, complete the master-slave copy Construction

"Configure MHA High Availability"

A MySQL MHA architecture build process