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)