现有Gbase8a集群字符集修改实施步骤（含脚本）

在原有集群基础上，调整字符集配置为utf8mb4后，新建同结构的utf8mb4临时表，把原utf8表的数据插入到utf8mb4临时表后，把临时表更改为主表，完成字符集的改造。
实施步骤如下：
1、停止集群服务；
2、修改所有管理节点gcluster配置文件中字符集参数，由utf8调整为utf8mb4；
3、修改所有计算节点gnode配置文件中字符集参数，由utf8调整为utf8mb4；
4、修改系统库、用户库的字符集，由utf8调整为utf8mb4；
5、在用户库下，新建同结构的utf8mb4的临时表；
6、把数据从utf8表插入到utf8mb4的临时表中；
核对数据，确认插入无误后，把utf8表改成备份表，把utf8mb4临时表改成主表，完成字符集改造。

本文所涉及步骤，仅针对集群配置文件、系统库、用户库调整，具体实施步骤如下：

1）业务应用关停确认（gbase用户）

sh ~/showprocesslist.sh c all
sh ~/showprocesslist.sh n all

2）集群状态及event检查（gbase用户）

gcadmin;
gcadmin showcluster;
gcadmin showddlevent |grep count; gcadmin showdmlevent |grep count; gcadmin showdmlstorageevent |grep count;

3）执行字符集修改脚本（gbase用户）

#脚本说明：同时修改gcluster和gnode的conf文件，以及修改系统库、用户库的字符集
#传参说明dbaUserName dbaUserPasswd  character
sh set_character.sh gbase gbase20110531 utf8mb4

4）重启集群服务（gbase用户）

cexec all: 'gcluster_services all stop';
cexec all: 'gcluster_services all start';

5）验证集群字符集（gbase用户）

#数据库字符集参数均调整成新的字符集
gccli -ugbase -pgbase20110531 -e "show variables like '%character%'"

附件：字符集调整脚本set_character.sh

#!/bin/bash
###############################################################################
##creator : tujunbing                                                        
##create time: 2024-06-01                                                    
##Decription: Set the character for gbase cluster  
##  **** Notice：It must be turned on with an dba user (gbase)         ****  
##Method: sh set_character.sh userName userPasswd  character                                  
##Version: 1.0                                                               
###############################################################################

#配置文件路径
configDir=/opt/gbase_workspace/scripts/config
#脚本路径
scriptsDir=/opt/gbase_workspace/scripts/set_character

#参数设置
userName=$1
userPasswd=$2
character=$3

#读取配置信息
config()
{
	#客户端命令
	gccli_command="gccli -u${userName} -p${userPasswd}"
}

#读取所需IP集合
get_iplist()
{
	if [ "" == "$userName" ] || [ "" == "$userPasswd" ] || [ "" == "$character" ];then echo "Usage: sh $0 userName userPasswd character"; exit 1; fi
	
	source /home/gbase/.bash_profile
	if ! isCmdExist gcadmin; then echo "  Failed: execute command (gcadmin) denied to the user"; exit 1; fi
	
	gbaseMode=$(gcadmin showcluster d|grep "virtual cluster" |wc -l)
	vcList=$(gcadmin showcluster d|grep "virtual cluster" | awk -F: '{print $2}' |sed 's/,//g')
	
	gcadmin |grep coordinator[1-9] |awk '{print $4}' > ${configDir}/coor.list
	if [ ${gbaseMode} -gt 0 ];then
		> ${configDir}/data.list
		for vcname in ${vcList}
		do
    		gcadmin showcluster vc ${vcname}|grep node[1-9]|awk '{print $4}' >> ${configDir}/data.list
		done
		gcadmin showcluster |grep FreeNode[1-9] | awk '{print $4}' >> ${configDir}/data.list
	else
		gcadmin |grep node[1-9]|awk '{print $4}' > ${configDir}/data.list
	fi
	
	versiDesc=$(gclusterd -V)
	if [ $(echo ${versiDesc} |grep '8\.6\.2' |wc -l) -gt 0 ]; then 
		version=86
	elif [ $(echo ${versiDesc} |grep '9\.5\.2' |wc -l) -gt 0 ]; then 
		version=952
	elif [ $(echo ${versiDesc} |grep '9\.5\.3' |wc -l) -gt 0 ]; then 
		version=953
	else 
		version=""
	fi
}

set_character()
{
	echo -e "  ****************************\033[1;36m Modify the character \033[0;30m************************************"
	echo "  1). Modify Gcluster Node character"
	for cip in `cat ${configDir}/coor.list`
	do
		echo "    ============== Coor: ${cip} =============="
		if [ "${version}" -eq "953" ]; then fip="/${cip}"; else fip=""; fi
		
		ssh ${cip} "cp /opt${fip}/gcluster/config/gbase_8a_gcluster.cnf /opt${fip}/gcluster/config/gbase_8a_gcluster.cnf.$(date +%Y%m%d)"
		
		ssh ${cip} "sed -i 's/^.*default_character_set.*$/default_character_set=${character}/' /opt${fip}/gcluster/config/gbase_8a_gcluster.cnf"
	done
	
	echo "  2). Modify Gnode Node character"
	for dip in `cat ${configDir}/data.list`
	do
		echo "    ============== Data: ${dip} =============="
		if [ "${version}" -eq "953" ]; then fip="/${dip}"; else fip=""; fi
		
		ssh ${dip} "cp /opt${fip}/gnode/config/gbase_8a_gbase.cnf /opt${fip}/gnode/config/gbase_8a_gbase.cnf.$(date +%Y%m%d)"
		
		ssh ${dip} "sed -i 's/^.*default_character_set.*$/default_character_set=${character}/' /opt${fip}/gnode/config/gbase_8a_gbase.cnf"
		
	done
	
	echo "  3). Modify Database character"
	if [ "${character}" != "" ]; then
		if [ ${gbaseMode} -gt 0 ];then
			echo -e "    gbase"
			$gccli_command -Ns -e "alter database vc00001.gbase charset ${character}"
			
			echo -e "    gctmpdb"
			$gccli_command -Ns -e "alter database vc00001.gctmpdb charset ${character}"
			
			db_names=$($gccli_command -Ns -e "select distinct concat(table_vc,'.',table_schema) as dbname from information_schema.tables where table_schema not in ('information_schema','performance_schema','gbase','gctmpdb')")
			for db_name in ${db_names}
			do
				echo -e "    ${db_name}"
				$gccli_command -Ns -e "alter database ${db_name} charset ${character}"
				
			done
		else
			echo -e "    gbase"
			$gccli_command -Ns -e "alter database gbase charset ${character}"
			
			echo -e "    gctmpdb"
			$gccli_command -Ns -e "alter database gctmpdb charset ${character}"
			
			db_names=$($gccli_command -Ns -e "select distinct table_schema as dbname from information_schema.tables where table_schema not in ('information_schema','performance_schema','gbase','gctmpdb')")
			for db_name in ${db_names}
			do
				echo -e "    ${db_name}"
				$gccli_command -Ns -e "alter database ${db_name} charset ${character}"
				
			done
		fi
		
	fi
	

	
	echo -e "  **************************************************************************************\r\n"
}


check_character()
{
	echo -e "  *****************************\033[1;36m Check the character \033[0;30m************************************"
	echo -e "  1). Check Gcluster Node character"
	for cip in `cat ${configDir}/coor.list`
	do
		if [ "${version}" -eq "953" ]; then fip="/${cip}"; else fip=""; fi
		
		message=$(ssh ${cip} "cat /opt${fip}/gcluster/config/gbase_8a_gcluster.cnf | grep character | tr -s '\n' ' '")
		echo -e "    Coor: ${cip}  ${message}"
	done
	
	echo -e "  2). Check Gnode Node character"
	for dip in `cat ${configDir}/data.list`
	do
		if [ "${version}" -eq "953" ]; then fip="/${dip}"; else fip=""; fi
		
		message=$(ssh ${dip} "cat /opt${fip}/gnode/config/gbase_8a_gbase.cnf | grep character | tr -s '\n' ' '")
		echo -e "    Data: ${dip}  ${message}"
		
	done
	
	echo -e "  3). Check Database character"
	if [ "${character}" != "" ]; then
		if [ ${gbaseMode} -gt 0 ];then
			echo -e "    $($gccli_command -Ns -e 'show create database gbase')"
			echo -e "    $($gccli_command -Ns -e 'show create database gctmpdb')"
			
			db_names=$($gccli_command -Ns -e "select distinct concat(table_vc,'.',table_schema) as dbname from information_schema.tables where table_schema not in ('information_schema','performance_schema','gbase','gctmpdb')")
			for db_name in ${db_names}
			do
				showresult=$($gccli_command -Ns -e "show create database ${db_name}")
				echo -e "    ${showresult}"
				
			done
		else
			echo -e "    $($gccli_command -Ns -e 'show create database gbase')"
			echo -e "    $($gccli_command -Ns -e 'show create database gctmpdb')"
			
			db_names=$($gccli_command -Ns -e "select distinct table_schema as dbname from information_schema.tables where table_schema not in ('information_schema','performance_schema','gbase','gctmpdb')")
			for db_name in ${db_names}
			do
				showresult=$($gccli_command -Ns -e "show create database ${db_name}")
				echo -e "    ${showresult}"
				
			done
		fi
		
	fi
	echo -e "  **************************************************************************************\r\n"
}

#设置字符集前核对
before_set_character()
{
	if ! isCmdExist $gccli_command; then echo "  Failed: execute command (gccli) denied to the user"; exit 1; fi
	
	echo "  ---------------------------------------------------------------------------------------------"
	oldCharacterStr=`$gccli_command -Ns -e "show variables like '%character_set_database%';"`
	
	if [ "${oldCharacterStr}" != "" ]; then 
		oldCharacterValue=`echo ${oldCharacterStr} | awk '{print $2}'`
		if [ "${oldCharacterValue}" == "${character}" ]; then 		
			echo -e " 当前集群的字符集为\033[0;34m ${oldCharacterValue}\033[0;30m, 与输入的字符集\033[0;35m ${character} \033[0;30m一致，无须进行调整。"
			exit 1
		else
			echo -e " 当前集群的字符集为\033[0;34m ${oldCharacterValue}\033[0;30m, 调整后集群的字符集为\033[0;35m ${character}\033[0;30m"
		fi
		
		while true
		do
			read -r -p "  Are you sure to change the character to ${character}? [y/n] " input

			case $input in
				[yY][eE][sS]|[yY])
					echo "  Your option is YES, we continue to do it."
					set_character
					check_character
					echo -e " \033[0;31m ** 重启集群服务后，字符集才能生效！** \033[0;30m \r\n"
					exit 1
					;;

				[nN][oO]|[nN])
					echo "  Your option is NO, we now exit."
					exit 1	       	
					;;

				*)
					echo "  Invalid input..., please inpu the Y(y/YES/yes) or N(n/No/no)"
					;;
			esac
		done
	fi
	
}

#校验密码有效性
check_password()
{
	userPasswdLen="${#userPasswd}"
	sqlResult=""
	sqlResultLen="" 
	
	if [ "$userName" != "gbase" ]; then echo "  Exit! You have to enter the DBA user (gbase)."; exit 1; fi
	
	if [ "${userPasswdLen}" -eq 0 ];then 
		sqlResult="null password"
		echo " * You didn't enter the password for DBA user (gbase)! * "
		exit 1
	else 
		sqlResult="$(${gccli_command} -u${userName} -p${userPasswd} -e quit 2>&1)"
	fi
	
	sqlResultLen="${#sqlResult}"
	if [ "${sqlResultLen}" -ne 0 ]; then
		echo " * The password is incorrect, the GBase 8a cannot be connected! * "
		exit 1
	fi 
}

#校验命令是否有效
isCmdExist() {
	local cmd="$1"
  	if [ -z "$cmd" ]; then
		echo "Usage isCmdExist yourCmd"
		return 1
	fi

	which "$cmd" >/dev/null 2>&1
	if [ $? -eq 0 ]; then
		return 0
	fi

	return 2
}

main()
{
	config
	check_password
	get_iplist
	before_set_character
}

main $@