[MySQL] Database 백업 / 복구

MySQL Backup / Restore

MySQL Backup (using mysqldump)

전체 Database 백업

# mysqldump -u [계정] -p '[비밀번호]' --all-databases > [Backup file name].sql
mysqldump -u root -p 'qwer1234' --all-databases > backup.sql

# DDL 정보만 백업 (데이터 X)
# mysqldump -u [계정] -p '[비밀번호]' --all-databases --no-data > [Backup file name].sql
mysqldump -u root -p 'qwer1234' --all-databases --no-data > backup.sql

특정 Database 백업

# mysqldump -u [계정] -p '[비밀번호]' [Database Instance 명] > [Backup file name].sql
mysqldump -u root -p 'qwer1234' testdb > backup.sql

원격 Database 백업

# All Database Backup
# mysqldump --host='[Host database ip/domain]' -u [id] -p '[password]' --port [port] --all-databases > [Backup file name].sql
mysqldump --host='mysql.backup.com' -u root -p 'qwer1234' --port 3306 --all-databases > backup.sql

# Target Database Instance Backup
# mysqldump --host='[Host database ip/domain]' -u [id] -p '[password]' --port [port] [Database instance name] > [Backup file name].sql
mysqldump --host='mysql.backup.com' -u root -p 'qwer1234' --port 3306 testdb > backup.sql

MySQL Restore

전체 Database 복구

# mysql -u [계정] -p '[비밀번호]' < [Backup file name].sql
mysql -u root -p 'qwer1234' < backup.sql

특정 Database 복구

# mysql -u [계정] -p '[비밀번호]' [Database Instance 명]< [Backup file name].sql
mysql -u root -p 'qwer1234' testdb < backup.sql