Home > bash, CentOS, Linux, MySQL, RHEL > Migrating MySQL 5.5.25a jiradb ERROR 2013 (HY000) on huge single db import

Migrating MySQL 5.5.25a jiradb ERROR 2013 (HY000) on huge single db import

well, I incremented max_allowed_packet from 16M to 512M
anyway, I got the same error on the next clean import.
so decided to find a workaround.
so, how to get the data separated from the ddl statements:

# get the tables names into the insert statement, its better to have that in file for future usage
sed '/^INSERT INTO/!d;s/ VALUES.*$//' jiradb.20130118.sql | sort | uniq > tablas.como.nombres.txt

then how to get the data separated:

root@jiragg:[Fri Jan 18 15:26:33]:[/usr/local/BACKUP]$ cat make.inserts.sh
#!/bin/sh
# trim function thank to http://stackoverflow.com/questions/369758/how-to-trim-whitespace-from-bash-variable
# and http://codesnippets.joyent.com/posts/show/1816
trim() {
    local var=$1
    var="${var#"${var%%[![:space:]]*}"}"   # remove leading whitespace characters
    var="${var%"${var##*[![:space:]]}"}"   # remove trailing whitespace characters
    echo -n "$var"
}

while read tabname
do
        tablename=$(trim $(echo $tabname | sed 's/INSERT INTO//;s/[[:punct:]]*//g'))
        echo "${tabname}:=>${tablename}"
        sed "/^INSERT INTO \`${tablename}\` VALUES/!d" /usr/local/BACKUP/jiradb.20130118.sql | gzip > "/usr/local/BACKUP/${tablename}.jiradb.20130118.sql.gz"
done < tablas.como.nombres.txt

how to get the DDLs:

sed '/^INSERT INTO/d' jiradb.20130118.sql > non.insert.jiradb.20130118.sql

how to import the whole jiradb:

# create the empty database
mysql --defaults-file=~/..credentials.jira -e 'create database jiradb;'
#import the DDLs:
mysql --defaults-file=~/..credentials.jira jiradb < /usr/local/BACKUP/non.insert.jiradb.20130118.sql
# make a list of the complressed datafiles per table:
find /usr/local/BACKUP/ -type f -name "*sql.gz"  > list.import.tables.txt
# execute simple export script 
root@jiragg:[Fri Jan 18 15:35:05]:[/usr/local/BACKUP]$ cat import.tables.sh
#!/bin/sh
while read tablefile
do
	echo -n "importing ${tablefile}: "
	zcat "${tablefile}" |  mysql --defaults-file=~/..credentials.jira jiradb
	echo ""
done < list.import.tables.txt

and that is it
anyway do not forget to increment the max_allowed_packet

Slackware4Life !

Categories: bash, CentOS, Linux, MySQL, RHEL Tags: , , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: