Binary logs recovery in MYSQL Server
---cold backup
mkdir cold
/var/lib/mysql#cp -rf * /root/cold
/etc/mysql#cp my.cnf /root/cold
only a table cannot able to restore
Full Backup
mysqldump -u root -pmysql123 --all-databases --events --routines --master-data=2> /backups/fullbkp.dmp
insert into student values (1,'venkat',60);
insert into student values (2,'rama',70);
insert into student values (3,'ramana',63);
insert into student values (4,'sriram','50');
Incremental Backups from binlog
mysqldump -u root -pmysql123 --all-databases --single-transaction --flush-logs --master-data=2 > /backups/incfull.sql
mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into student values (5,'lokesh','90');
Query OK, 1 row affected (0.04 sec)
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 0 |
| mysql-bin.000003 | 177 |
| mysql-bin.000004 | 177 |
| mysql-bin.000005 | 177 |
| mysql-bin.000006 | 177 |
| mysql-bin.000007 | 177 |
..
| mysql-bin.000019 | 5038 |
| mysql-bin.000020 | 3900 |
| mysql-bin.000021 | 177 |
| mysql-bin.000022 | 1206 |
| mysql-bin.000023 | 472 |
| mysql-bin.000024 | 427 |
+------------------+-----------+
24 rows in set (0.00 sec)
mysql> create table teacher(id int,name char(20));
Query OK, 0 rows affected (0.19 sec)
mysql> drop database prod;
Query OK, 18 rows affected (0.65 sec)
mysql> create database prod;
Query OK, 1 row affected (0.05 sec)
find / -name full.sql
mysql -u root -pmysql123 prod < /home/dba/full.sql
/var/log/mysql# mysqlbinlog mysql-bin.000023 mysql-bin.000024 | mysql -u root -p prod
shell> mysqlbinlog mysql-bin.000023 | mysql -u root -p prod
shell> mysqlbinlog binlog.000001 > /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
shell> mysql -u root -p -e "source /tmp/statements.sql"
You can use -v option to make .sql file in READABLE format.
For Example
mysqlbinlog -v /var/lib/mysql-bin.000016 >> /logs/allbinlog.sql
mysqlbinlog --start-position=486 --stop-position=1008 mysql-bin.000018 | mysql -u root -pmysql123 mon
Tvenkat
I have 16+ years of experience in IT and 11+ years of experience in training. I am currently working in Bangalore and occasionally in Hyderabad as well. I have worked in various domains including Telecom, Healthcare, Pharma, Insurance, Retail, and Storage. Currently, I am working as a Cloud Principal Consultant in an MNC Product based company.
