Backups and Recovery in MYSQL ?
1. Physical backup (cold backup/offline backup)
Taking backup of physical files.
2. Logical backups (online backups)
Taking backup of logical objects
3. Incremental backups ( we can perform this on only logical backups only)
Note:
Take backup using MYSQLDUMP utility.
Restore using MYSQLIMPORT or MYSQL utility.
1. Physical backup (cold backup/offline backup)
-Stop the mysql services
service mysqld stop
-Copy the datadir and mysql config file to different locations
select @@datadir;
find / -name *cnf*
find / -name *.cnf*
cp -rf /var/lib/mysql/auto.cnf /root/mysqlcoldbackup_16042022/
cp -rf /var/lib/mysql /root/mysqlcoldbackup_16042022/mysqlbkp
cp -rfv /var/lib/mysql /root/mysqlcoldbackup_16042022/mysql$(date +%s)
service mysqld start
2. Logical backups (online backups)
To check the databases:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| QTEST |
| classicmodels |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
Database Level:
Backup a single database:
mysqldump -u root -p mysql > mysqldump.sql
Backup multiple databases:
mysqldump -u root -p --databases mysql classicmodels > multidatabasesdump.sql
Backup all databases:
mysqldump -u root -p --all-databases > all_databases.sql
Compressed MySQL Database Backup:
mysqldump -u root -p mysql | gzip > mysql.sql.gz
Backup with timestamp option:
mysqldump -u root -p mysql > mysql-$(date +%Y%m%d).sql
Table Level:
Backup a single table:
mysqldump -u root -p mysql servers > singletable.sql
Backup a multiple tables:
mysqldump -u root -p mysql servers db user > multi_table.sql
Restore Commands:
mysql> drop database mysql;
Query OK, 28 rows affected, 2 warnings (0.05 sec)
mysql> use mysql
ERROR 1049 (42000): Unknown database 'mysql'
mysql> create database mysql;
Query OK, 1 row affected (0.00 sec)
Restoring a database using backups:
[root@mqmopensource ~]# mysql -u root -p mysql > mysqldbkp.sql------> using same database backup dump
[root@mqmopensource ~]# mysql -u root -p mysql < all_database.sql ------> using all database backup dump
mysql> use mysql
Database changed
mysql> show tables;
Empty set (0.00 sec)
Restore Command:
mysql -u root -p mysql < mysqldbkp.sql
mysql> use mysql
mysql> show tables;
To restore a single table from full/db backups in MYSQL:
mysql> use mysql;
mysql> show tables;
| user
| columns_priv
| db
| event
| func
| general_log
mysql> drop table user;
Query OK, 0 rows affected (0.00 sec)
No comments:
Post a Comment