TEST DATA

wget <https://downloads.mysql.com/docs/sakila-db.zip>
[root@lee ~]# unzip sakila-db.zip

[root@lee sakila-db]# mysql -uroot -proot < sakila-schema.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@lee sakila-db]# mysql -uroot -proot < sakila-data.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

Export

[root@lee backup]# mysqldump --single-transaction --triggers --routines --events --no-create-db --master-data=2 -uroot -proot sakila | gzip > leeyun.sql.gz

Import

  1. 백업 끝났을 떄의 Binary Log 시점 확인
[root@lee backup]# zcat leeyun.sql.gz | head -30
(생략)
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=1360568;

--
-- Table structure for table `actor`
--

DROP TABLE IF EXISTS `actor`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
  1. Sakila_backup DB 생성
[root@lee backup]# mysql -uroot -proot -e "create database sakila_backup"