MAKING RAW MyISAM BACKUP IN MySQL (Article -19).

MAKING RAW MyISAM BACKUP IN MySQL (Article -19).

MyISAM was the default storage engine for the MySQL relational database management system versions prior to 5.5 released in December 2009.

it is based on the older ISAM code.

Filesystem

Each MyISAM table is stored on disk in three files (if it is not partitioned). The files have names that begin with the table name and have an extension to indicate the file type. MySQL uses a .frm file to store the definition of the table, but this file is not a part of the MyISAM engine; instead it is a part of the server. The data file has a .MYD (MYData) extension. The index file has a .MYI (MYIndex) extension.

MyISAM FEATURES 

MyISAM tables have the following characteristics:

All data values are stored with the low byte first. This makes the data machine and operating system independent. The only requirements for binary portability are that the machine uses two’s-complement signed integers and IEEE floating-point format. These requirements are widely used among mainstream machines. Binary compatibility might not be applicable to embedded systems, which sometimes have peculiar processors.There is no significant speed penalty for storing data low byte first; the bytes in a table row normally are unaligned and it takes little more processing to read an unaligned byte in order than in reverse order. Also, the code in the server that fetches column values is not time critical compared to other code.

All numeric key values are stored with the high byte first to permit better index compression.

Large files (up to 63-bit file length) are supported on file systems and operating systems that support large files.

There is a limit of (2³²)² (1.844E+19) rows in a MyISAM table.

The maximum number of indexes per MyISAM table is 64.The maximum number of columns per index is 16.

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.

When rows are inserted in sorted order (as when you are using an AUTO_INCREMENT column), the index tree is split so that the high node only contains one key. This improves space utilization in the index tree.

Internal handling of one AUTO_INCREMENT column per table is supported. MyISAM automatically updates this column for INSERT and UPDATE operations. This makes AUTO_INCREMENT columns faster (at least 10%). Values at the top of the sequence are not reused after being deleted. (When an AUTO_INCREMENT column is defined as the last column of a multiple-column index, reuse of values deleted from the top of a sequence does occur.) TheAUTO_INCREMENT value can be reset with ALTER TABLE or myisamchk.

Dynamic-sized rows are much less fragmented when mixing deletes with updates and inserts. This is done by automatically combining adjacent deleted blocks and by extending blocks if the next block is deleted.

MyISAM supports concurrent inserts: If a table has no free blocks in the middle of the data file, you can INSERT new rows into it at the same time that other threads are reading from the table. A free block can occur as a result of deleting rows or an update of a dynamic length row with more data than its current contents. When all free blocks are used up (filled in), future inserts become concurrent again. See Section 8.11.3, “Concurrent Inserts”.

You can put the data file and index file in different directories on different physical devices to get more speed with the DATA DIRECTORY and INDEX DIRECTORY table options to CREATE TABLE. See Section 13.1.20, “CREATE TABLE Syntax”.

BLOB and TEXT columns can be indexed.

NULL values are permitted in indexed columns. This takes 0 to 1 bytes per key.

Each character column can have a different character set. See Chapter 10, Character Sets, Collations, Unicode.

There is a flag in the MyISAM index file that indicates whether the table was closed correctly. If mysqld is started with the --myisam-recover-optionsoption, MyISAM tables are automatically checked when opened, and are repaired if the table wasn’t closed properly.

How to create tables in MyISAM storage engine ?

MySQL 5.6 on-wards , it is normally necessary to use ENGINE to specify the MyISAM storage engine because InnoDB is the default engine.

Now lets create an object inside AJAY_MyISAM database.

Here we created a object called test1 under MyISAM storage engine.

Now check table information.

Lets create one more table under InnoDB storage and check the differences with physical files.

Goto datadir and check the files

There is directory create for AJAY_MyISAM database with the same name .

Here Test1 object craeted with three files which is under MyISAM.

Now will see how we will backup MyISAM Storage Engine tables.

Backup procedure :

1.Lock the tables while server is running

2.Perform copy in physical location.

3.Release locks on tables  after copy.

Step 1 :

Lock the tables which you want to take the backup.

How to get locked tables information ?

To check locked tables information under database.

Here In_use shows 1 for test1 table which is locked .

Step 2:

Perform copy of a table files.

Step 3 :

Release locks on tables  after copy.

Now we will try to perform restore of test1 table.

Step 4 : 

Drop the object test1 which you took backup.

In physical location also files has been deleted.

Now we need to copy the files from backup location

Now connect to mysql and check tables are appear or not ?

Lets try to select the table test1.

The above error will occur after restoring the MyISAM storage engine files.

To troubleshoot the error we need to change the permissions of files.

I have copied all the files as ROOT user so files permissions has been changed.

Lets change the permission to mysql user and mysql group.

Now connect to database and check table test1 info.

 

Thank you…………………