#! /bin/bash # # Author: Ricardas Stoma # Year: 2016-2021 # About: This script uses percona toolkit to update calls/calls_old table without blocking to v4 # To alter table calls_old execute script with argument 'calls_old' . /usr/src/m2/framework/bash_functions.sh . /usr/src/k_framework/helpers/percona/percona_functions.sh exec > >(tee -a /var/log/m2/percona_calls.log) exec 2> >(tee -a /var/log/m2/percona_calls.log >&2) VERSION="1.2.0" SCRIPT_NAME="Calls table update with Percona to v4" BACKUP_PATH="/usr/local/m2/backups" PERCONA_LOG="/var/log/m2/percona.log" # redundant - all this is retrieved by k_config_details PERCONA_DB_PASSWORD=`cat /etc/m2/system.conf | grep dbsecret | awk '{print $3}'` PERCONA_DB_USERNAME=`cat /etc/m2/system.conf | grep dbuser | awk '{print $3}'` DB_NAME=`cat /etc/m2/system.conf | grep dbname | awk '{print $3}'` DB_HOST=`cat /etc/m2/system.conf | grep dbhost | awk '{print $3}'` ALTER_QUERY="" START_DATE=`date` TABLE="calls" FORCE=0 NO_SCREEN=0 SKIP_BACKUP=0 k_start ############################################### # PERCONA TABLE UPDATE WITHOUT DOWNTIME # ############################################### k_config_details # Install percona toolkit /usr/src/k_framework/helpers/percona/percona_install.sh if [ "$?" != "0" ]; then k_exit 1 fi # Check arguments for arg in "$@" do # if passed argument is "calls_old" then switch to calls_old table if [ "$arg" == "calls_old" ]; then TABLE=calls_old fi # if passed argument is "force" then do not ask for confirmation if [[ "$arg" == "force" || "$arg" == "FORCE" ]]; then FORCE=1 fi if [ "$arg" == "NO_SCREEN" ]; then NO_SCREEN=1 fi # if passed argument is "force" then do not ask for confirmation if [ "$arg" == "SKIP_BACKUP" ]; then SKIP_BACKUP=1 fi # if passed argument is "force" then do not ask for confirmation if [ "$arg" == "SKIP_CORE_CHECK" ]; then SKIP_CORE_CHECK=1 fi done if [[ $NO_SCREEN == 0 ]]; then if ! k_we_are_inside_screen; then report "You have to be in 'screen' to continue..." 1 k_exit 1 fi fi report "Will update table: $TABLE" 3 report "--- DB SETTINGS CHECKS ---" 8 # Check if we can connect to mysql percona_check_root_mysql_connection # Check if mysql users has PROCESS privilege percona_check_process_privilege report "--- [$TABLE] TABLE STRUCTURE CHECK ---" 8 report "Checking if [$TABLE] table needs update..." 3 PERCONA_VERBOSE=0 # should we show what we are checking? # Add new columns # v2 fields percona_add_column "pdd" "DECIMAL(18,9) DEFAULT 0" percona_add_column "src_user_id" "MEDIUMINT UNSIGNED DEFAULT 0" percona_add_column "terminated_by" "ENUM('unknown', 'originator', 'terminator', 'system') DEFAULT NULL" percona_add_column "answer_time" "TIMESTAMP NULL DEFAULT NULL" percona_add_column "end_time" "TIMESTAMP NULL DEFAULT NULL" percona_add_column "originator_codec" "ENUM('unknown', 'G.711 A-law', 'G.711 u-law', 'G.722', 'G.723.1', 'G.726', 'G.729', 'GSM', 'Speex', 'OPUS') DEFAULT NULL" percona_add_column "terminator_codec" "ENUM('unknown', 'G.711 A-law', 'G.711 u-law', 'G.722', 'G.723.1', 'G.726', 'G.729', 'GSM', 'Speex', 'OPUS') DEFAULT NULL" percona_add_column "pai" "VARCHAR(40) DEFAULT NULL" #v3 fields percona_add_column "rpid" "VARCHAR(40) NULL DEFAULT NULL" percona_add_column "tp_src" "VARCHAR(40) NULL DEFAULT NULL" percona_add_column "tp_dst" "VARCHAR(40) NULL DEFAULT NULL" percona_add_column "dc" "SMALLINT UNSIGNED DEFAULT 0" percona_add_column "op_dc" "SMALLINT UNSIGNED DEFAULT 0" percona_add_column "tp_dc" "SMALLINT UNSIGNED DEFAULT 0" percona_add_column "routing_attempt" "TINYINT UNSIGNED DEFAULT 0" percona_add_column "op_signaling_ip" "INT UNSIGNED DEFAULT 0" percona_add_column "tp_signaling_ip" "INT UNSIGNED DEFAULT 0" percona_add_column "op_media_ip" "INT UNSIGNED DEFAULT 0" percona_add_column "tp_media_ip" "INT UNSIGNED DEFAULT 0" percona_add_column "op_codec" "TINYINT UNSIGNED DEFAULT 0" percona_add_column "tp_codec" "TINYINT UNSIGNED DEFAULT 0" percona_add_column "op_user_agent_id" "SMALLINT UNSIGNED DEFAULT 0" percona_add_column "tp_user_agent_id" "SMALLINT UNSIGNED DEFAULT 0" percona_add_column "mos" "TINYINT UNSIGNED DEFAULT 0" percona_add_column "mos_packetloss" "MEDIUMINT UNSIGNED DEFAULT 0" percona_add_column "mos_jitter" "MEDIUMINT DEFAULT 0" percona_add_column "mos_roundtrip" "MEDIUMINT UNSIGNED DEFAULT 0" #v4 fields percona_add_column "did_id" "MEDIUMINT UNSIGNED DEFAULT 0" percona_add_column "did_customer_id" "MEDIUMINT(8) UNSIGNED DEFAULT 0" percona_add_column "did_vendor_id" "MEDIUMINT(8) UNSIGNED DEFAULT 0" percona_add_column "did_selling_billsec" "MEDIUMINT(8) UNSIGNED DEFAULT 0" percona_add_column "did_buying_billsec" "MEDIUMINT(8) UNSIGNED DEFAULT 0" percona_add_column "did_selling_price" "DECIMAL(18,9) UNSIGNED DEFAULT 0.000000000" percona_add_column "did_selling_rate" "DECIMAL(18,9) UNSIGNED DEFAULT 0.000000000" percona_add_column "did_buying_price" "DECIMAL(18,9) UNSIGNED DEFAULT 0.000000000" percona_add_column "did_buying_rate" "DECIMAL(18,9) UNSIGNED DEFAULT 0.000000000" percona_add_column "src_number_pool_id" "MEDIUMINT(8) UNSIGNED DEFAULT 0" percona_add_column "src_prefix" "VARCHAR(40) DEFAULT NULL" # Drop columns percona_drop_column "date" percona_drop_column "channel" percona_drop_column "accountcode" percona_drop_column "processed" percona_drop_column "did_price" percona_drop_column "card_id" percona_drop_column "reseller_id" percona_drop_column "reseller_rate" percona_drop_column "reseller_billsec" percona_drop_column "reseller_price" percona_drop_column "partner_id" percona_drop_column "partner_rate" percona_drop_column "partner_billsec" percona_drop_column "partner_price" percona_drop_column "callertype" percona_drop_column "peerip" percona_drop_column "recvip" percona_drop_column "sipfrom" percona_drop_column "uri" percona_drop_column "useragent" percona_drop_column "peername" percona_drop_column "t38passthrough" percona_drop_column "did_inc_price" percona_drop_column "did_prov_price" percona_drop_column "did_provider_id" #PERCONA_VERBOSE=1 # Change column: column_name type default percona_change_column "uniqueid" "VARCHAR(255)" "NULL" percona_change_column "duration" "MEDIUMINT(8) UNSIGNED" "0" percona_change_column "billsec" "MEDIUMINT(8) UNSIGNED" "0" percona_change_column "src_device_id" "MEDIUMINT(8) UNSIGNED" "0" percona_change_column "dst_device_id" "MEDIUMINT(8) UNSIGNED" "0" percona_change_column "provider_id" "MEDIUMINT(8) UNSIGNED" "0" percona_change_column "provider_rate" "DECIMAL(18,9)" "0.000000000" percona_change_column "provider_billsec" "MEDIUMINT(8) UNSIGNED" "0" percona_change_column "provider_price" "DECIMAL(18,9)" "0.000000000" percona_change_column "user_id" "MEDIUMINT(8) UNSIGNED" "0" percona_change_column "user_rate" "DECIMAL(18,9)" "0.000000000" percona_change_column "user_billsec" "MEDIUMINT(8) UNSIGNED" "0" percona_change_column "user_price" "DECIMAL(18,9)" "0.000000000" percona_change_column "server_id" "MEDIUMINT(8) UNSIGNED" "0" percona_change_column "hangupcause" "SMALLINT(5) UNSIGNED" "0" percona_change_column "real_duration" "DECIMAL(18,9)" "0.000000000" percona_change_column "real_billsec" "DECIMAL(18,9)" "0.000000000" percona_change_column "pdd" "DECIMAL(18,9)" "0.000000000" percona_change_column "dst_user_id" "MEDIUMINT(8) UNSIGNED" "0" percona_change_column "src_user_id" "MEDIUMINT(8) UNSIGNED" "0" #PERCONA_VERBOSE=0 # Drop old indexes percona_drop_index "id" percona_drop_index "id_2" percona_drop_index "id_3" percona_drop_index "id_4" percona_drop_index "id_5" percona_drop_index "id_6" percona_drop_index "id_7" percona_drop_index "id_8" percona_drop_index "id_9" percona_drop_index "src_device_id" percona_drop_index "dst_device_id" percona_drop_index "src" percona_drop_index "provider_id" percona_drop_index "card_id" percona_drop_index "disposition" percona_drop_index "hgcause" percona_drop_index "resellerid" percona_drop_index "uniqueidindex" percona_drop_index "calldateindex" percona_drop_index "dateindex" percona_drop_index "calldate" percona_drop_index "accountcode_index" percona_drop_index "originator_codec_index" percona_drop_index "terminator_codec_index" # Add new indexes percona_add_index "src_device_id_index" "(src_device_id)" percona_add_index "dst_device_id_index" "(dst_device_id)" percona_add_index "server_id_index" "(server_id)" percona_add_index "src_index" "(src(6))" percona_add_index "dst_index" "(dst(6))" percona_add_index "localized_dst_index" "(localized_dst(6))" percona_add_index "prefix_index" "(prefix(6))" percona_add_index "provider_id_index" "(provider_id)" percona_add_index "user_id_index" "(user_id)" percona_add_index "disposition_index" "(disposition)" percona_add_index "hangupcause_index" "(hangupcause)" percona_add_index "dst_user_id_index" "(dst_user_id)" percona_add_index "uniqueid_index" "(uniqueid(6))" percona_add_index "calldate_index" "(calldate)" percona_add_index "src_user_id_index" "(src_user_id)" percona_add_index "did_id_index" "(did_id)" percona_add_index "did_customer_id_index" "(did_customer_id)" percona_add_index "did_vendor_id_index" "(did_vendor_id)" # Check if table update is required if [ "$ALTER_QUERY" == "" ]; then report "[$TABLE] table is up to date" 0 k_exit 0 else report "[$TABLE] table update is needed" 2 if [ "$1" == "CHECK" ]; then k_exit 0 fi report "Following changes will be applied" 3 echo "" echo "$ALTER_QUERY" echo "" fi # Check if replication is present and if we can connect to it report "--- REPLICATON CHECK ---" 8 percona_check_replication_connection if [[ $SKIP_BACKUP == 0 ]]; then report "--- DB [$TABLE] TABLE BACKUP ---" 8 if [ "$FORCE" == "0" ]; then BACKUP_CONFIRM="CONFIRM" fi HOSTNAME=`hostname` if [ "$HOSTNAME" != "dev.kolmisoft.com" ]; then # Make backup (free space is checked while dumping database) k_dump_db_table "$TABLE" "$BACKUP_PATH" $BACKUP_CONFIRM STATUS="$?" if [ "$STATUS" != "0" ] && [ "$STATUS" != "3" ]; then report "Will not proceed percona [$TABLE] update as backup creation failed" 1 k_exit 1 fi fi report "--- DB MAIN TABLES BACKUP ---" 8 if [ ! -f /usr/bin/pigz ]; then report "Install pigz: yum -y install pigz" 1 k_exit 1 fi CURRENT_DATE="$(date +"%Y%m%d_%H%M%S")" FILE="$BACKUP_PATH/m2_main_$CURRENT_DATE.sql.gz" report "Making main tables backup to: $FILE" 3 MYSQL_PWD=$DB_PASSWORD mysqldump -u $DB_USERNAME $P_OPT "$DB_NAME" --ignore-table=m2.calls --ignore-table=m2.aggregates --ignore-table=m2.calls_old --ignore-table=m2.server_loadstats --single-transaction | pigz --fast > $FILE if [[ -f $FILE ]]; then FILESIZE=$(stat -c%s "$FILE") FILESIZE_MB=$(($FILESIZE / 1024 / 1024)) report "DB main tables backup prepared successfully. File size: $FILESIZE_MB MB" 0 else report "Backup failed. Aborting." 1 k_exit 1 fi else report "Backup skipped" 2 fi report "--- PERCONA MAGIC ---" 8 TABLE_SIZE=$(MYSQL_PWD=$DB_PASSWORD /usr/bin/mysql -h "$DB_HOST" -u $DB_USERNAME $P_OPT "$DB_NAME" --disable-column-names -B -e "SELECT ROUND(data_length/1024/1024) as SIZE from information_schema.tables where table_schema = '$DB_NAME' and table_name = '$TABLE'") # 1515 MB / min with 128GB calls table test " APPROX_TIME=$(($TABLE_SIZE / 170 + 1)) if [[ $APPROX_TIME -gt 60 ]]; then REPORT_LEVEL=2 else REPORT_LEVEL=3 fi report "Table size in the DB : $TABLE_SIZE M" $REPORT_LEVEL report "Approximate Percona work time: $APPROX_TIME min" $REPORT_LEVEL if [ "$FORCE" == "0" ]; then echo "Execute [$TABLE] update procedure? (y/n)" read -t 60 EXECUTE_PROCEDURE if [ "$EXECUTE_PROCEDURE" != "y" ]; then report "Update procedure was cancelled" 3 k_exit 0 fi BACKUP_CONFIRM="CONFIRM" fi #report "To monitor the progress do in separate window: tail -f $PERCONA_LOG" 3 start_time="$(date +"%Y-%m-%d %H:%M:%S")" report "$start_time - Starting $TABLE table update procedure. This may take some time..." 3 if [[ $TABLE == "calls" ]]; then # Drop insert_date trigger /usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" --password="$PERCONA_DB_PASSWORD" $DB_NAME -e "DROP TRIGGER insert_date" &> /dev/null fi if [ "$PERCONA_DB_PASSWORD" == "" ]; then PERCONA_PASS_CMD="" else PERCONA_PASS_CMD="--password=$PERCONA_DB_PASSWORD" fi # Do table update #PTDEBUG=1 put this in front of for debug # output to log, nothing on the screen #pt-online-schema-change --progress=time,30 --execute --print --nocheck-replication-filters --user="$PERCONA_DB_USERNAME" $PERCONA_PASS_CMD --alter "$ALTER_QUERY" D="$DB_NAME",t=$TABLE > $PERCONA_LOG 2>&1 # output to the screen and to the log rm -fr $PERCONA_LOG pt-online-schema-change --progress=time,30 --execute --nocheck-replication-filters --user="$PERCONA_DB_USERNAME" $PERCONA_PASS_CMD --alter "$ALTER_QUERY" D="$DB_NAME",t=$TABLE > >(tee -a $PERCONA_LOG) 2> >(tee -a $PERCONA_LOG >&2) end_time="$(date +"%Y-%m-%d %H:%M:%S")" report "$end_time - Finished. (Started: $start_time)" 3 STATUS=`grep -F 'Successfully altered' $PERCONA_LOG | wc -l` if [ "$STATUS" == "1" ]; then report "[$TABLE] table Percona update was successful" 0 if [[ $SKIP_CORE_CHECK != 1 ]]; then k_delay 2 m2 reload # this did not work somehow, needs testing k_delay 3 # check core db version support VER=`m2 show status | grep 'Calls table version:' | awk -F ":" '{print $2}' | xargs` if [[ $VER == 4 ]]; then report "Core version support: 4" 0 else report "Core version support: $VER, should be 4" 2 report " Do: m2 reload and recheck again with m2 show status" 2 EXIT_CODE=2 fi fi else report "Failed to update [$TABLE] table with percona. You can find DB backup in $BACKUP_PATH" 1 report "Check $PERCONA_LOG for more information. Last lines:" 1 echo tail -10 $PERCONA_LOG echo k_exit 1 fi k_exit $EXIT_CODE