Pages

Tuesday, April 19, 2022

Backups and Recovery in MYSQL ?

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