Mysql dump commands

Mysql dump


Copy/Import SQL to DB:

• mysql -uroot -pekdo123 test_db < test_db.sql
• mysql --max_allowed_packet=1000M -uroot -pekdo123 test_db < /tmp/test_db.sql

MySQL Dump:

• mysqldump -uroot -pekdo123 test_db > test_db.sql
• mysqldump -t -u MyUserName -pMyPassword MyDatabase MyTable --where="ID = 10"

MySQL Dump - Avoid a TABLE

• mysqldump -uroot -pekdo123 <database> --ignore-table=<database>.table1 > database.sql
• mysqldump -uroot -pekdo123 test_db --ignore-table=test_db.table1 > database.sql

Ignore multiple/heavy/not-so-important tables while dumping/backup DB:
e.g. session tables like django_session

• mysqldump --host=<> --port=3306 -u<root> -p<password> test_db --ignore-table=test_db.table1 --ignore-table=test_db.table2 > /tmp/test_db.sql

MySQL Dump - only Insert queries

• --no-create-info - This will not write create table (drop & create)
• mysqldump -u root -pekdo123 test_db table1 --no-create-info --where="g_id = 43818 and id < 691672 and is_cancelled = 0" > /tmp/test_db_table1.sql
• --lock-tables=false
• mysqldump -u root -pekdo123 test_db table1 --lock-tables=false --no-create-info --where="id1 in (select id from table2 where gid = 43818 and id < 691672 and is_cancelled = 0)" > /tmp/test_db_table2.sql

For more information:

• mysqldump --help