多实例mysql

最新推荐文章于 2024-07-24 14:35:13 发布

qq_34145309

最新推荐文章于 2024-07-24 14:35:13 发布

阅读量119

点赞数

分类专栏： Linux shell

本文链接：https://blog.csdn.net/qq_34145309/article/details/106535881

版权

Linux shell 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

#!/bin/bash

set -o errexit
set -o nounset

function logger()
{
	echo $(date "+%F %H:%M:%S %N") $1 $2 $3 >> ${g_log}
}

function prepare()
{
	local i=0
	for i in 6 7 8 9
	do
		rm -rf /usr/local/mysql/data_330${i} &
	done
	wait
}

function config_mysql()
{
	local i=0
	for i in 6 7 8 9
	do
		cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql_330${i}
		sed -i "s/Provides:.*/Provides: mysql_330${i}/" /etc/init.d/mysql_330${i}
		sed -i 's/Default-Start:.*$/Default-Start: 2 3 5/' /etc/init.d/mysql_330${i}
		sed -i "s/\(\$bindir\/mysqld_safe\) --datadir=\"\$datadir\" --pid-file=\"\$mysqld_pid_file_path\"/\1 --defaults-file=\/etc\/mysql_330${i}\.cnf/" /etc/init.d/mysql_330${i}
		sed -i "s/^mysqld_pid_file_path=.*/mysqld_pid_file_path=\/usr\/local\/mysql\/data_330${i}\/mysql.pid/" /etc/init.d/mysql_330${i}
		chkconfig --add mysql_330${i}
		
		echo "${my_cnf_str//3306/330${i}}" > /etc/mysql_330${i}.cnf
		sed -i "s/auto-increment-offset.*/auto-increment-offset=$((i-5))/" /etc/mysql_330${i}.cnf
		sed -i "s/server-id.*/server-id=$((i-5))/" /etc/mysql_330${i}.cnf
	done
}

function init_mysql()
{
	local i=0
	for i in 6 7 8 9
	do
		${my_base}/bin/mysqld --defaults-file=/etc/mysql_330${i}.cnf --initialize-insecure --user=mysql &
	done
	wait
	
	for i in 6 7 8 9
	do
		${my_base}/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data_330${i} &
	done
	wait
	
	for i in 6 7 8 9
	do
		chown mysql:mysql ${my_base}/data_330${i}/*.pem &
	done
	wait
}

function start_mysql()
{
	local i=0
	for i in 6 7 8 9
	do
		service mysql_330${i} "start $@" &
	done
	wait
}

function stop_mysql()
{
	local i=0
	for i in 6 7 8 9
	do
		service mysql_330${i} stop &
	done
	wait
}

function add_repl_user()
{
	local cmd="
CREATE USER 'repl'@'%' IDENTIFIED BY 'mysql';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
RESET MASTER;
"
	local i=0
	for i in 6 7 8 9
	do
		${my_base}/bin/mysql --defaults-file=/etc/mysql_330${i}.cnf -e "${cmd}" &
	done
	wait
}

function config_repl()
{
	local cmd="
CHANGE MASTER TO
	MASTER_HOST = 'localhost',
	MASTER_PORT = 3306,
	MASTER_USER = 'repl',
	MASTER_PASSWORD = 'mysql',
	MASTER_AUTO_POSITION = 1
FOR CHANNEL '3306';

START SLAVE
FOR CHANNEL '3306';
"
	${my_base}/bin/mysql --defaults-file=/etc/mysql_${1}.cnf -e "${cmd//3306/${2}}"
}

function combination()
{
	local arr=(3306 3307 3308 3309)
	local i=0
	local j=0
	for ((i=0;i<${#arr[*]}-1;i++))
	do
		for ((j=i+1;j<${#arr[*]};j++))
		do
			echo -n ${arr[i]}${arr[j]}" "
			config_repl ${arr[i]} ${arr[j]} && config_repl ${arr[j]} ${arr[i]}
		done
		echo
	done
}

function clean()
{
	stop_mysql
	chkconfig -del mysql_330{6,7,8,9}
	rm /etc/init.d/mysql_330{6,7,8,9}
	rm /etc/mysql_330{6,7,8,9}.cnf
	rm -rf ${my_base}/data_330{6,7,8,9}
}

function all()
{
	clean || true
	prepare
	config_mysql
	init_mysql
	start_mysql --skip-slave-start=on
	add_repl_user
	combination
}

my_cnf_str='
[server]
port=3306
datadir=/usr/local/mysql/data_3306
socket=/usr/local/mysql/data_3306/mysql.sock
pid_file=/usr/local/mysql/data_3306/mysql.pid
explicit_defaults_for_timestamp=on
log_timestamps=SYSTEM

[client]
port=3306
socket=/usr/local/mysql/data_3306/mysql.sock

[mysqld]
log-bin=mysql-bin
log-bin-index=mysql-bin
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin
auto-increment-offset=1
auto-increment-increment=4
server-id=1

gtid_mode=on
enforce-gtid-consistency=on
master_info_repository=table
relay_log_info_repository=table
slave_net_timeout=60
master-retry-count=0
log_slave_updates=on
relay_log_recovery=on

[mysqld_safe]
mysqld-safe-log-timestamps=SYSTEM
'

my_base=/usr/local/mysql

case $1 in
	prepare|config_mysql|init_mysql|start_mysql|stop_mysql|add_repl_user|combination|config_repl|clean|all)
		$@
		;;
esac

已执行事务的gtid实时写入到全局变量@@GLOBAL.gtid_executed
周期性持久化到系统表mysql.gtid_executed
如果异常断电，会有部分gtid没来得及持久化到系统表mysql.gtid_executed
再次启动时会从binlog恢复出来，写入到表里

slave上start slave后，slave起一个io线程和sql线程
io线程通知master起一个binlog_dump线程
binlog_dump线程读取master上binlog，发送给slave的io线程
slave上io线程把收到的数据写入到slave上的relay_log
最后slave上的sql线程回放relay_log

@@GLOBAL.gtid_owned存放的是slave上正在执行的gtid
slave上sql线程回放relay_log逐条执行事务
首先把该事务gtid写入到会话变量@@gtid_next
检查@@gtid_next是否在@@GLOBAL.gtid_owned里，如果是就忽略这条事务
如果不是，写入到slave上的binlog并提交该事务
binlog回滚或服务关停时，binlog中的gtid持久化到系统表mysql.gtid_executed
如果slave未开启binlog，直接写入到系统表mysql.gtid_executed
紧接着该事务gtid也加入到slave的@@GLOBAL.gtid_executed里

@@GLOBAL.gtid_purged是指本机上已经commit过但是不在binlog里的gtid集合，有以下三种来源
已经写入binlog，但该binlog已被清理
从机上回放的gtid
手动赋值