usefull scripts for all-inkll servers

copy_database.sh

#!/usr/bin/env bash

#
# Script copies the whole database from one server to another on the same host
#
# on import-time occours an error 1227 '... Access denied; you need the SUPER privilege(s) ...' 
# if you have views in your source db.
# the reason is an enabled the binary log on the sql server.
#
# because only the `DEFINER user@host` line is the problem, we fix id with sed-replacement
# replacing the definer with `DEFINER=CURRENT_USER` makes the sql-import working without error.
#
#
# @see: https://aws.amazon.com/de/premiumsupport/knowledge-center/error-1227-mysqldump/
# @see: https://github.com/soundcloud/lhm/issues/76#issuecomment-350643685
# @see: https://dbperf.wordpress.com/2010/04/12/removing-definer-from-mysql-dump/
#

source_db="d......."
source_pw=""

target_db="d......."
target_pw=""



echo "`date +'%Y-%m-%d %H.%M.%S'`: Starting copy from $source_db to $target_db"

mysqldump -v -u $source_db -p$source_pw  $source_db |\
  sed -e 's/DEFINER=`[a-z0-9A-Z]*`@`[a-z0-9A-Z%]*`/DEFINER=CURRENT_USER /g' |\
  mysql -u $target_db -p$target_pw $target_db