MYSQL Backup and Restoration

MYSQL Backup and Restoration

##### database backup ######

1.> MYSQL DB Backup

mysqldump -uroot -p databasename > database.sql

2.> mysql backup table only with data

mysqldump -uroot -p dbname (dbname) table (tablename)

mysqldump -uroot -p dbname table > table.sql

3.> export table from database.sql

sed -n -e '/CREATE TABLE.*tablename/,/CREATE TABLE/p' database.sql > mytable.sql

4.> database backup excluding table or ignoring table

mysqldump -u root -p -h localhost --ignore-table=databasename.tablename > dbname.sql

5.> 

mysqldump -u root -p -h localhost --ignore-table=databasename.tablename --ignore-table=databasename.tablename2 > dbname.sql

###### database restore   ######

# mysql -uroot -p

mysql> create database dbname;

mysql> use dbname;

mysql> source database.sql