KnowMySQL : database

MySQL is having a good features as binlog files.When ever the transaction happens in the server this binlog files have the transaction details.

Here am going to explain how to restore records from mysqlbinlog file.

For example:

First check bin log is enabled or not.

login to mysql,check it by,

mysql>show binary logs ;
+------------------+-----------+
| Log_name   | File_size |
+------------------+-----------+
| mysql-bin.000001 | 25071488 |
| mysql-bin.000002 |       150 |
| mysql-bin.000003 |    131262 |
| mysql-bin.000004 |    463122 |
| mysql-bin.000005 |   1040619 |
+------------------+-----------+
5 rows in set (0.00 sec)

So binlog is enabled in this server.If not enabled do the below,

Remove "#" for log-bin=mysql-bin from my.cnf file

Restart mysql.

Now,I have a database name "fruits" inside I have table name is "apple" it contains 15 records as shown below.

mysql>use fruits;
Database changed

mysql> show tables;
+------------------+
| Tables_in_fruits |
+------------------+
| apple            |
| banana        |
| mango         |
| orange        |
+------------------+

mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
|   1 | abc      | 1389335150 |
|   2 | abc      | 1389335163 |
|   3 | abc      | 1389335165 |
|   4 | prs      | 1389335167 |
|   5 | abc      | 1389335167 |
|   6 | abc      | 1389335169 |
|   7 | xyz      | 1389335176 |
|   8 | xyz      | 1389335178 |
|   9 | KLM      | 1389335181 |
| 10 | KLM      | 1389335315 |
| 11 | xyz      | 1389335327 |
| 12 | RES      | 1389335329 |
| 13 | xyz      | 1389335331 |
| 14 | RES      | 1389335333 |
| 15 | RES      | 1389335335 |
+-----+----------+------------+
15 rows in set (0.00 sec)

I am taking a backup of this apple table now,

root@knowmysql]# mysqldump -u root -p fruits apple > fruits_apple.sql

After taking backup I am inserting some rows in the apple table like below,

mysql>insert into apple (id,username,timestamp) values('16','ILK','1389335345');

Query OK, 1 row affected (0.00 sec)

mysql> insert into apple (id,username,timestamp) values('17','bcd','1389335355');
Query OK, 1 row affected (0.00 sec)

mysql> insert into apple (id,username,timestamp) values('18','brd','1389335375');
Query OK, 1 row affected (0.00 sec)

mysql> insert into apple (id,username,timestamp) values('19','elf','1389335395');
Query OK, 1 row affected (0.00 sec)

mysql> insert into apple (id,username,timestamp) values('20','OPQ','1389335413');
Query OK, 1 row affected (0.01 sec)

Now totally we have 20 rows in the table.

mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
|   1 | abc      | 1389335150 |
|   2 | abc      | 1389335163 |
|   3 | abc      | 1389335165 |
|   4 | prs      | 1389335167 |
|   5 | abc      | 1389335167 |
|   6 | abc      | 1389335169 |
|   7 | xyz      | 1389335176 |
|   8 | xyz      | 1389335178 |
|   9 | KLM      | 1389335181 |
| 10 | KLM      | 1389335315 |
| 11 | xyz      | 1389335327 |
| 12 | RES      | 1389335329 |
| 13 | xyz      | 1389335331 |
| 14 | RES      | 1389335333 |
| 15 | RES      | 1389335335 |
| 16 | ILK      | 1389335345 |
| 17 | bcd      | 1389335355 |
| 18 | brd      | 1389335375 |
| 19 | elf      | 1389335395 |
| 20 | OPQ      | 1389335413 |
+-----+----------+------------+
20 rows in set (0.00 sec)

Now am deleting last five records from apple table.

mysql>delete from apple where id in('16','17','18','19','20');
Query OK, 3 rows affected (0.00 sec)

Now this table is having 15 records,

mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
|   1 | abc      | 1389335150 |
|   2 | abc      | 1389335163 |
|   3 | abc      | 1389335165 |
|   4 | prs      | 1389335167 |
|   5 | abc      | 1389335167 |
|   6 | abc      | 1389335169 |
|   7 | xyz      | 1389335176 |
|   8 | xyz      | 1389335178 |
|   9 | KLM      | 1389335181 |
| 10 | KLM      | 1389335315 |
| 11 | xyz      | 1389335327 |
| 12 | RES      | 1389335329 |
| 13 | xyz      | 1389335331 |
| 14 | RES      | 1389335333 |
| 15 | RES      | 1389335335 |
| +-----+----------+------------+

15 rows in set (0.00 sec)

Now I am going to restore the deleted records from binlog,

Take a backp of apple table now and drop it from the server.

root@test]# mysqldump -u root -p fruits apple > fruits_apple11.sql

mysql>drop table apple;

mysql> show tables;
+------------------+
| Tables_in_fruits |
+------------------+
| banana        |
| mango         |
| orange        |
+------------------+

apple table dropped.

Import our old backup inside the fruit databases.

root@test]#mysql -u root -p fruits < fruits_apple.sql

Now the table is having only 15 records.

mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
|   1 | abc      | 1389335150 |
|   2 | abc      | 1389335163 |
|   3 | abc      | 1389335165 |
|   4 | prs      | 1389335167 |
|   5 | abc      | 1389335167 |
|   6 | abc      | 1389335169 |
|   7 | xyz      | 1389335176 |
|   8 | xyz      | 1389335178 |
|   9 | KLM      | 1389335181 |
| 10 | KLM      | 1389335315 |
| 11 | xyz      | 1389335327 |
| 12 | RES      | 1389335329 |
| 13 | xyz      | 1389335331 |
| 14 | RES      | 1389335333 |
| 15 | RES      | 1389335335 |
+-----+----------+------------+
15 rows in set (0.00 sec)

Now check the last binlog file from the data directory.(Binlog default location is mysql data directory)

Here am using row format for binlog in my.cnf.For row format below format is used to view the binlog file.

root@knowmysql]#mysqlbinlog -v --base64-output=decode-rows /var/lib/mysqldata/mysql-bin.000005|less

From binlog file search at which position the deleted records were inserted.

I have found the binlog position that insertion happens in the server for id 16,17,18,19,20 in the apple table.

starting position is, 941107 and ending position is 942025.

Now am going to restore from binlog,

root@test]#mysqlbinlog --start-position=941107 --stop-position=942025 /var/lib/mysqldata/mysql-bin.000005 |mysql -u root -p fruits

Records were restored,Now check the records in apple table.

mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
|   1 | abc      | 1389335150 |
|   2 | abc      | 1389335163 |
|   3 | abc      | 1389335165 |
|   4 | prs      | 1389335167 |
|   5 | abc      | 1389335167 |
|   6 | abc      | 1389335169 |
|   7 | xyz      | 1389335176 |
|   8 | xyz      | 1389335178 |
|   9 | KLM      | 1389335181 |
| 10 | KLM      | 1389335315 |
| 11 | xyz      | 1389335327 |
| 12 | RES      | 1389335329 |
| 13 | xyz      | 1389335331 |
| 14 | RES      | 1389335333 |
| 15 | RES      | 1389335335 |
| 16 | ILK      | 1389335345 |
| 17 | bcd      | 1389335355 |
| 18 | brd      | 1389335375 |
| 19 | elf      | 1389335395 |
| 20 | OPQ      | 1389335413 |
+-----+----------+------------+
20 rows in set (0.00 sec)

Now Apple table is having 20 records and restored the deleted 17,18,19 records from binlog file.

We can restore data from mysqlbinlog by date and time like below.

mysqlbinlog --start-datetime="2014-01-15 10:00:00" \ --stop-datetime="2014-01-15 10:01:04 \ /var/lib/mysqldata/mysql-bin.000005 > /data/apple.sql

mysqlbinlog --start-position=941327 /var/lib/mysqldata/mysql-bin.000005 |mysql -u root -p tablename

This way is little bit tough but we can restore all the records without missing any record.

I hope it will help you.

Thank you.

It is important to back up your databases so that you can recover your data and be up and running again in case problems occur, such as system crashes, hardware failures, or users deleting data by mistake.

Types of Backup:

logical backup	This type of backup is created by saving information that represents the logical database structures using SQL statements like create database, create table and insert. This type of backup is ideal when you want to upgrade from one version of MySQL to another however it is a slower method of backing up.
physical backup	This type of backup is a backup of the actual database files or disk partitions, this type of backup can be very fast to backup and restore.
full backup	A full backup is a standalone backup containing everything in the database, this could then be restored on another server. A full backup can be either logical or physical.
incremental backup	This type of backup only contains the data that has changed from the last backup. The advantage of this type of backup is that it is faster as there is not some much data to backup, however the disadvantage is that it takes longer to recover.
consistent backup	This is a backup at an exact moment in time, generally you shutdown the database (or quiescent mode) then take the backup.
hot backup	This type of backup is taken when the database is running, during the backup both reads and writes are not blocked
warm backup	This type of backup is taken when the database is running, however reads are not blocked but writes are prohibited from making any modifications to the database.
cold backup	Similar to a consistent backup as the database is shutdown before the backup begins
point-in-time restore	It is a restoration of a database to a specified date and time , some databases use a full backup and recovery logs to restore to that point-in-time, others can only use the last full backup which means that data might have to be re-keyed into the system.

Types of Backup tools:

Backup tools for MySQL
Backup method	Storage engine	Impact	Backup speed	Recovery speed	Recovery granularity
mysqldump	ALL	WARM	MEDUIM	SLOWEST	MOST FLEXIBLE
mysqldump	INNODB	HOT	MEDUIM	SLOWEST	MOST FLEXIBLE
select into outfile	ALL	WARM	SLOW	SLOW	MOST FLEXIBLE
mk-parallel-backup	ALL	WARM	MEDUIM	MEDUIM	FLEXIBLE
ibbackup	INNODB	HOT	FAST	FAST	FLEXIBLE
ibbackup	ALL	WARM	FAST	FAST	FLEXIBLE
**backup command in mysqld**	ALL	HOT	FAST	FAST	FLEXIBLE
filesystem (copy files)	ALL	COLD	FASTEST	FASTEST	NOT FLEXIBLE
snapshot (using LVM, ZFS, VMWare)	ALL	ALMOST HOT	FAST	FAST	LEAST FLEXIBLE
mysqlhotcopy	MyISAM	MOSTLY COLD	FAST	FAST	FLEXIBLE

MySQLdump:

mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT into sql-statements of the source database. To restore the database, execute the *.sql file on destination database.

mysqldump

# backup all databases
mysqldump --user=root --password --all-databases > backup_<date>_all.sql

# backup a specific database
mysqldump --user=root --password <database_name> > backup_<date>_<database_name>.sql
# backup multiple databases
mysqldump --user=root --password <database_name>,<database_name> > backup_<date>.sql

# backup a table from a database
mysqldump --user=root --password <database_name> <table_name> > backup_<date>_<database_name>_<table_name>.sql
# backup some specific data
mysqldump --user=root --password <database_name> <table_name> --where "last_name='VALLE' order by first_name > backup_<date>.sql
# dumping from one database to another
mysqldump --databases <database_name> | mysql -h <destination_host> <database_name>

restore a mysqldump

# all databases
mysql --user=root --password < backup.sql

# specific database
mysql --user=<user> --password <database_name> < backup_<dataabse_name>.sql

SELECT INTO OUTFILE

The SELECT INTO OUTFILE SQL statement is actually a variant of the SELECT query. It is used when you want to direct query output to a text file. This file can then be opened by a spreadsheet application, or imported into another database like Microsoft Access, Oracle, or any other software that supports delimitation.

Ex

SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;

LOAD DATA INFILE

The

LOAD DATA
      INFILE

statement reads rows from a text file into a table at a very high speed. The file name must be given as a literal string.

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

mysqlhotcopy

mysqlhotcopy is a Perl script that was originally written and contributed by Tim Bunce. It uses FLUSH TABLES, LOCK TABLES, and cp or scp to make a database backup. It is a fast way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables.

Ex

#backup a database
mysqlhotcopy <database_name> /backups

# backup multiple databases
mysqlhotcopy <database_name> accounts /backups

#backup a database to to another server
mysqlhotcopy --method=scp <database_name> \ username@backup.server:/backup

# use pattern match to backup databases and tables
mysqlhotcopy <database_name>./^employees/ /backup

10 Ways to Automatically & Manually Backup MySQL Database

KnowMySQL

Friday, January 17, 2014

Restoring records from MySQL binlog files.

Thursday, March 7, 2013

BACKUP AND RECOVER MySQL DATABASE