sed tricks

http://www.haidongji.com/2014/12/29/sed-tricks/

I helped a charity to rebuild a MySQL server and to restore a database with a lot of data of longblob type in the last two days. Fortunately there was a dump backup file for the database in question. However, tables with longblob column(s) were not defined with “ROW_FORMAT=COMPRESSED”. I’d like to restore that database with row compression before inserting the data. Therefore I need to modify the dump sql file. The problem is that the file is 2.5 GB and the server only has 4 GB memory. So editing it is a challenge. Fortunately, Linux has sed to save the day. Don’t you love open source free software? I am power Vi/Vim user, so I am familiar with sed and have used it in the past. But there are still a few things that I searched around for quick answers. So I’ll record noteworthy points here. I couldn’t remember how many times my own past blog entries helped me over the years. And I hope you’ll find this helpful too! The -n switch is important. sed is a stream editor. In many cases you’d like to supress data streaming to stdout, and -n does that. This was especially important in my case, because a) the file is large, b) it contains blob that may or may not “fit to print”; To see a particular line, say line n, use the p (print) command: sed -n ‘np’ file To see all lines between line m and n: sed -n ‘m,np’ file To see multiple, non-adjacent lines, say line a, e, g: sed -n ‘ap;ep;gp’ file To edit big files, you’d like to make in-place changes. Therefore the -i switch. For example, to put in InnoDB row compression, this is the command I used: sed -i ‘s/CHARSET=utf8;/CHARSET=utf8 ROW_FORMAT=COMPRESSED;/’ file Similarly, to delete line n: sed -i ‘nd’ file You can also do range delete as well By the way, when restore InnoDB database with a lot of blob data, it makes a lot of sense to enable the following settings in my.cnf, if they are not enabled already. It’ll make administration much easier down the road: innodb_file_per_table innodb_file_format = Barracuda You may also need to tweak the max_allowed_packet and innodb_log_file_size parameters for successful restore.

from Planet MySQL http://www.planetmysql.org/