Set up MySQL one-master and multi-slave environment minute by minute (R12 notes day 31)
I wrote an article about building a MySQL Group Replication test environment minute by minute. If we want to build a master and multi-slave test environment on a server, how can we do it minute by minute? in fact, it can be done by taking a little time to write a script. It is nothing more than integrating the stylized things and simplifying them. It is good to be able to improve efficiency.
To build a master-slave environment, we still prepare a configuration file init2.lst, which is mainly port and node identification.
24801 s1 Y
24802 s2 N
24803 s3 N
24804 s4 N
24805 s5N, for example, I created five nodes, the port is the first column, the second column is a sign of the node, the generated node directory name is to refer to this, and the third column is the role of the node, such as one master and four slaves. The master is P and the slave is N
Unified parameter file, this place is worthy of our improvement, because the individual configuration parameters are different, there is no need to specify multiple parameter files, can be generated dynamically. # cat s2.cnf
[mysqld]
# server configuration
Datadir=$ {base_data_dir} / ${node_name}
Basedir=$ {base_dir}
Port=$ {port}
Socket=$ {base_data_dir} / ${node_name} / ${node_name} .sock
Server_id=$ {port}
Gtid_mode=ON
Enforce_gtid_consistency=ON
Master_info_repository=TABLE
Relay_log_info_repository=TABLE
Binlog_checksum=NONE
Log_slave_updates=ON
Log_bin=binlog
Binlog_format=ROW
And the protagonist of this function is the whole script content.
The script is as follows:
Base_dir=/usr/local/mysql
Base_data_dir=/home/data
Pri_port= `cat init2.lst | grep Y | awk'{cat $1}'`
Pri_ip=127.0.0.1
Pri_node= `cat init2.lst | grep Y | awk'{cat $2}'`
Function init_node
{
Port=$1
Node_name=$2
Primary_flag=$3
${base_dir} / bin/mysqld-initialize-insecure-basedir=$ {base_dir}-datadir=$ {base_data_dir} / ${node_name}-explicit_defaults_for_timestamp
Chown-R mysql:mysql ${base_data_dir} / ${node_dir}
Cp ${base_data_dir} / s2.cnf ${base_data_dir} / ${node_name} / ${node_name} .cnf
Sed-I's base_data_dir ${base_data_dir}:'"${base_data_dir}: G"'${base_data_dir} / ${node_name} / ${node_name} .cnf
Sed-I's base_dir ${base_dir}:'"${base_dir}: G"'${base_data_dir} / ${node_name} / ${node_name} .cnf
Sed-I's node_name ${node_name}:'"${node_name}: G"'${base_data_dir} / ${node_name} / ${node_name} .cnf
Sed-I's port ${port}:'"${port}: G"'${base_data_dir} / ${node_name} / ${node_name} .cnf
Chown-R mysql:mysql ${base_data_dir} / ${node_name}
${base_dir} / bin/mysqld_safe-- defaults-file=$ {base_data_dir} / ${node_name} / ${node_name} .cnf &
Sleep 5
${base_dir} / bin/mysql-P ${port}-S ${base_data_dir} / ${node_name} / ${node_name} .sock-e "show databases"
If [[${primary_flag} ='Y']]; then
Mysql-P ${port}-S ${base_data_dir} / ${node_name} / ${node_name} .sock-e "
CREATE USER rpl_user@'%'
GRANT REPLICATION SLAVE ON *. * TO rpl_user@'%' IDENTIFIED BY 'rpl_pass'
FLUSH PRIVILEGES
"
Elif [[${primary_flag} ='N']]; then
Echo ${pri_node} "primary"
Echo ${pri_port}
Mysqldump-P$ {pri_port}-S ${base_data_dir} / ${pri_node} / ${pri_node} .sock-- default-character-set=utf8-- single-transaction-R-- triggers-Q-- all-databases | mysql-P$ {port}-S ${base_data_dir} / ${node_name} / ${node_name} .sock
Echo "CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass', MASTER_HOST='127.0.0.1',MASTER_PORT=" ${pri_port} ", MASTER_AUTO_POSITION = 1" | mysql-P$ {port}-S ${base_data_dir} / ${node_name} / ${node_name} .sock
Mysql-P ${port}-S ${base_data_dir} / ${node_name} / ${node_name} .sock-e "
Start slave
Show slave status\ G
"
Else
Echo 'Please check variable primary_flag'
Fi
}
# MAIN
While read line
Do
Echo ${seed_list}
Init_node $line
Done
