Add TRUNCATE TABLE command in mysqldump before CREATE TABLE IF NOT EXIST

Question

I need some help, I have this command:

mysqldump -u myusername -pmypassword --skip-add-drop-table --no-data --single-transaction database_name | sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' > db.sql

that can add CREATE TABLE IF NOT EXISTS in my mysqldump, but I also want to add TRUNCATE TABLE command before the CREATE TABLE IF NOT EXISTS command, how should I do this?

Answer 1

Just add a little more to your regex in sed:

mysqldump -u myusername -pmypassword --skip-add-drop-table --no-data --single-transaction database_name | sed -r 's/CREATE TABLE (`[^`]+`)/TRUNCATE TABLE \1; CREATE TABLE IF NOT EXISTS \1/g' > db.sql