Backup mysql 5 each database to each file and change structure to mysql 8

backup_mysql.sh

MYSQL_USER=xxxx
MYSQL_PASS=yyyyy
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"

/usr/local/mysql/bin/mysql ${MYSQL_CONN} -N -e 'show databases' \
	| while read dbname; \
		do  /usr/local/mysql/bin/mysqldump ${MYSQL_CONN} --complete-insert --routines --triggers --single-transaction --databases "$dbname" \
			| sed -r "s/ENGINE=MyISAM/ENGINE=InnoDB/g" \
			| sed -r "s/COLLATE=(latin1|utf8_bin|utf8|utf8_unicode_ci|utf8_unicode_ci|)/COLLATE=utf8mb4_0900_ai_ci/g" \
			| sed -r "s/COLLATE (latin1|utf8_bin|utf8|utf8_general_ci|utf8_unicode_ci)/COLLATE utf8mb4_0900_ai_ci/g" \
			| sed -r "s/DEFAULT CHARSET=(utf8)/DEFAULT CHARSET=utf8mb4/g" \
			| sed -r "s/CHARACTER SET utf8/CHARACTER SET utf8mb4/g" \
			| sed -r "s/ROW_FORMAT=FIXED/ROW_FORMAT=DYNAMIC/g" \
			> "dump_sql/$dbname".sql; done

tar -czvf dump_sql.tar.gz dump_sql/