How to converting MYSQL table from InnoDB engine to MyISAM

I have problem of table repair in InnoDB so I need to convert InnoDB to the engine MyISAM.

I converted a MySQL table’s InnoDB to the engine MyISAM.

The code I found for conversion:

ALTER TABLE Tablename ENGINE=MYISAM

You can replace MYISAM engine to InnoDB, ISAM, HEAP and etc.

Compression is widely used to save storage space and for faster data transmission over networks and the Internet. A large file (or a number of files) is shrunk into a compact file that can be “expanded” later to get back the original files. In the case of lossy compression (we’ll come to that in a minute), you get a much smaller MP3 file from a huge WAV file (for example). So the question is: how does data compression work?

Lossless Compression

Let’s talk about lossless compression first, basically what happens when a number of files are zipped. Here, you get back all the original data when you unzip it. The simple fact is that data files have redundancy—the same information is represented repetitively.

For example, in a text file, pronouns, prepositions, punctuations, and such are repeated throughout the document. The redundancy can be removed through compression by listing the bits of repeated information or common elements (for example, patterns or shapes in the case of image files) once, instead of listing them again and again.

One of the simplest ways to understand the working of compression is by considering a text file. In the phrase “A penny saved is a penny earned,” each letter, space and punctuation mark occupies one unit of space in the storage media. This file would occupy 32 units of space—comprising 25 letters, 6 spaces, and 1 full stop. If we look for redundancies, the words “a” and “penny”, which are repeated twice each, can be replaced with the numbers 1 and 2 using a coding scheme. So this scheme is: A=1, Penny=2. The coding scheme thus consists of 8 characters (6 letters and 2 numbers). This scheme itself has to be saved in the resulting compressed file so the compression program knows how to unzip the data. The phrase, after applying the coding scheme, would read “1 2 saved is 1 2 earned,” which would occupy 23 units of space. This is saved by the compression program along with the coding scheme to form a compressed file. The size of this file would be 31 units, which consist of 23 characters (with spaces) of the new coded phrase and 8 characters (numbers and letters) of the coding scheme.

So, as compared to the original file size, that is, 32 units, the compressed file size now requires 31 units of space. Here, the dictionary takes up comparatively more space since the original phrase is small. But in the case of a much larger text file, the overhead will reduce: the dictionary will be comparatively smaller, and there will be more repeated patterns in the data. For instance, in the above example, if the next sentence were “So don’t waste a penny!”, we add only 3 items to the dictionary instead of 5. Lossless Compression uses a number of algorithms such as run length coding, the Burrows-Wheeler transform, dictionary coders, prediction by partial matching, context mixing, and entropy coding. Of these, Lempel and Ziv’s (LZ) dictionary-based algorithm for data compression is the most popular and widely-used.

Lossy Compression

When you’re talking to someone on the phone, what matters more—the clarity with which you can hear their words, or whether the phone line reproduces the deep baritone of his voice? It’s this same premise that gave people the idea of lossy compression—compressing files in a way that eliminates some of the “frills” from the file, but keeping its heart intact.

A good example is JPEG compression—tries saving a JPEG at maximum compression through a program like IrfanView—it looks awful, but you can still tell that it’s a dog or a pig or whatever.

The advantage of lossy compression over lossless is the fact that with the freedom of destroying some information from the file itself, you can achieve smaller file sizes. Such stunts aren’t tried with things like text compression, of course—all the data is essential! Lossy compression finds itself used most with images, audio and video—all one needs to do is provide a threshold beyond which all information will be destroyed. Once compressed, you can’t regain the quality of the original file, and if you compress an already compressed file, you lose even more quality, no matter how generous you are with the threshold.

Some MySQL commands are given below to manage your MySQL databases 

Step 1 

Create database 

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

    [create_specification] ...

create_specification:

    [DEFAULT] CHARACTER SET [=] charset_name

  | [DEFAULT] COLLATE [=] collation_name

Create database db_name;

Step 2

Create user
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
    [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
Create user db_user identified by ‘db_passswd’;

Step 3

Grant privileges
GRANT priv_type [(column_list)]
[, priv_type [(column_list)]] …
ON [object_type]
{
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}
TO user [IDENTIFIED BY [PASSWORD] ‘password’]
GRANT ALL ON *.* TO ‘someuser’@'somehost’;
GRANT SELECT, INSERT ON *.* TO ‘someuser’@'somehost’;

Step 4
Remove database user
DROP USER user;

Step 5

Set password for database users
SET PASSWORD FOR ‘alan’@'%.loc.gov’ = PASSWORD(‘newpass’);

That is equivalent to the following statements:
UPDATE mysql.user SET Password=PASSWORD(‘newpass’)
WHERE User=’alan’ AND Host=’%.loc.gov’;
FLUSH PRIVILEGES;

Optimization is an integral part of database administration. When database is heavily used having too many records and hence there are too many inserts/deletes or retrievals from the database in that case MySQL uses most of system resources.

There are two many options you have to optimize the query or MySQL databases.

Those options are illustrated in this article.

If querying is taking lot of time, and database connections and backlogs are growing, then site performance as well as server performance goes down causing high load sometimes or database crashed which indicate that it is time to optimize your database.

mysqladminpr command is used to check which mysql queries are responsible for heavy system resources.

You can modify MySQL configuration file (my.cnf) to log slow queries. It will check the slow running queries and the possible causes.

This could be related to indexes like no index or improper index for database columns. Here is an attempt to describe which commands and concepts we can use to optimize MySQL so that it will improve the performance.

Step 1:

Use of indexes

Using indexes makes the query faster in the same way as an index in a book helps in looking for a particular word.

To know how and which indexes are used in a given query, use EXPLAIN command as a prefix to select query.

EXPLAIN SELECT employee_code FROM emp WHERE dept = ‘IT’;

This command returns following information:

Table – Which table(s) are used in the query
Type – JOIN type. Values can be system, const, eq_ref, ref, range, index, all
Possible Keys – All keys which can be used for indexes
Key – The key actually used for index
Key Length – Shorter keys are better
Ref – Other Columns used with key to get the results
Rows – Number of rows from where data will come
Extra – Extra information. Some possible values can be using index, using where, using temporary, using filesort

You should reconsider your table structure if Possible Keys contain NULL value.
A NULL value in Key field indicates no index is being used. Using no indexes will deter the performance if there are too many records in the table because, for any query, all the records will be searched for the given condition.

Use ALTER command to add index as given below:

ALTER TABLE table_name ADD INDEX (column_name);

You can do indexing on multiple columns using:

ALTER TABLE table_name ADD INDEX (column_name1,..,column_nameN);

MySQL uses leftmost prefixing, so automatically indexes will be added for column_name1, (column_name1,column_name2),…(column_name1,..,column_NameN-1). This is quite helpful in situations like searching based on surname so an index on (surname, firstname, middlename) will automatically add multiple indexes used in faster querying.

Points to remember:

• Choose the column(s) for indexing very carefully.

• Indexed fields should be used for searching and query should be re-framed if any calculation is performed on indexed fields.

• Index itself takes storage space.

• Each operation on database requires updating index as well.

• A smaller Key Length reported by EXPLAIN command is better. Smaller key length means lesser storage space is required by index file and also that time to search in index is less. For this, indexes can be set on part of columns. For example, INDEX(col1(10),col2(20)). Again, you have to judiciously decide what length of columns to be used for indexing.

Step 2:

Using ANALYZE command

ANALYZE command is used to generate key distribution for the table to be used by MySQL query optimizer to decide which indexes are best to use in a query.

ANALYZE TABLE table_name;

Step 3:

Using OPTIMIZE command

If there are too many inserts/deletes in a table then OPTIMIZE command must be frequently used to optimize disk head movement during retrieval. This is particularly useful if variable size columns are used like varchar, text, blob etc. OPTIMIZE command does defragmentation of the fragmentation caused by variable length fields like VARCHAR.

OPTIMIZE TABLE table_name;

Step 4:

Using special functions for loading data

For loading data from a file into a database table, using LOAD DATA INFILE is much faster than reading data from file iteratively and using INSERT command. The syntax for this command is:

LOAD DATA INFILE ‘impdata.dat’ INTO TABLE table_name (col1,col2,…,colM) FIELDS TERMINATED BY ‘|’”);

Step 5:

Setting PRIORITY of commands

If there are more queries than data insertions, you may lower priority of INSERT command using:

INSERT LOW_PRIORITY or SELECT HIGH_PRIORITY

If client is not interested in results of INSERT, then it can be immediately freed using following command:

INSERT DELAYED.

This makes the system faster by batching insertions.

Step 6:

DELETE vs. TRUNCATE

DELETE command use to deletes row by row, while TRUNCATE use to deletes all rows at once. So, if you are not interested in number of rows deleted from a table as result of DELETE command, then use TRUNCATE with following syntax:

TRUNCATE TABLE table_name;

Step 7:

Lowering permission checking overhead

Simpler permissions reduce permission checking overhead. Use GRANT command to set the permissions. General syntax for GRANT is:

GRANT priv_type [(column_list)]

[, priv_type [(column_list)]] …

ON [object_type]

{

*

| *.*

| db_name.*

| db_name.tbl_name

| tbl_name

| db_name.routine_name

}

TO user [IDENTIFIED BY [PASSWORD] ‘password’]

For example:

GRANT SELECT,UPDATE ON emp_sal TO hr@localhost IDENTIFIED BY ’password’;

If user account ‘hr’ is not there, then it will be created on localhost (although user can access data from any workstation) and password ‘password’ will be assigned to it.

Step 8:

Use BENCHMARK() function

To know how much a given MySQL function or expression is taking time, use MySQL built-in function:

BENCHMARK(loop_count, expression)

It always returns 0 but also prints time taken by the expression.

Step 9:

Synchrozing data types

If columns in tables contain identical information, then make their data types also same so that joins can be performed faster.

Step 10:

Database optimization is not one time job. You have to optimize it every after 2-3 months.

Configuring VPN (routing & remote access) on a windows VPS is a very simple task.

First we will have to enable the VPN module for the VPS using following command on the node.

vzctl set VEID –vpn on –save (Here VEID is the vps ID on the node)

Restart the VPS once the VPN module is enabled.

Now enable the “Routing and Remote Access” service on the VPS. This can be done from Go to Start >> RUN >> Type “services.msc” .

Also stop the Windows Firewall on the VPS.

Configure Routing and Remote Access:

On VPS  go to  Start >> Settings >> Control Panel >> Administrative Tools >>  “Routing and Remote Access”. Right click on the computer name & you will see the option “Configure and Enable remote and routing access”.

On the configuration wizard procced with the configuration, click Next >> select “Custom Configuration”, click Next >> select  VPN Access & NAT and Basic Firewall Option, click Next >> Now press Finish to end the configuration.

The above configuration has enabled PPTP & L2TP VPN access to your firewall with private routing capability.

Route Private Traffic to Public Interface:

As the VPS does not have second network interface, we will use the NAT (Network Address Translation) using the Microsoft Loopback adapter to route private traffic to public interface.

From the Routing and Remote Access panel >> Expand ComputerName (Local) >> Expand Ip Routing >> Right click on “NAT/Basic Firewall” & use the New interface to add network translation. First add the “Internal” Inferface which is used for private network access with default settings and on second attempt add your main adapter to the NAT list and select the options says “Public Interface connected to this inferface” and select the option says “Enable NAT on this interface”.

The routing & remote access on your VPS is configured, just restart the routing & remote access service & VPN is ready for you.

The streaming experience has improved over the years as our Internet connections have gotten faster. Not everyone understands that streaming actually depends on technologies and protocols different from those used for viewing Web pages or for downloading files.

What Is Streaming?

Streaming refers to the technique of continuous and steady digital data (audio, video, or graphics) transfer as “packets” in real-time from a data server through the Internet to a user’s computer. Media files can played in a browser using an embedded plug-in or in a media player. The smoothness of the media stream depends upon the speed of the connection. Multiple versions in terms of quality (high, medium, or low) can be made available for different connection speeds. For slow connections, glitches in frames and delayed or no audio will occur.

A key factor is the compression method used for the media files so they can be streamed seamlessly. Due to compression, some data quality is compromised through perceptual encoding, that is, the audio/video is stripped down in such a way that the changes cannot be easily perceived. Usually, perceptual encoding refers to lossy audio encoding in which psychoacoustics is used to determine what audio signals to encode and what to snip out.

Large media files are encoded using codecs to smaller sizes. Thus you have MOV, RM, etc.

Real Networks, QuickTime, Windows Media and Macromedia Flash are the most common streaming technologies. Windows Media and RealNetworks are the most popular, and broadcasters assume that the player plug-in is installed on the viewer’s browser.

QuickTime is installed on all Macs. Also, installation of Macromedia Flash is required in most cases.

Types of Streaming

Streaming technology thus encompasses media content, the streaming server, plug-in, and encoding software. Streaming is of two types—progressive and real-time. During progressive streaming, the media file can be viewed or listened to while the file is in being downloaded. In the case of data loss, re-transmission of lost packets is possible. Media files streamed using the progressive technique gets saved on the viewer’s hard drive, which raises the problem of redistribution. HTTP streaming is a type of progressive streaming where the media file begins to play before it is entirely downloaded. In the case of HTTP streaming, a request for data remains open even after the data is received by the client, so that the server can respond at any time.

In real-time streaming, media content gets downloaded temporarily to the user’s computer. Almost-live broadcast of content is possible. Content streamed real time can adjust according to the user’s connection capacity; if the connection is too slow, the transmission of data would break.

Media streams can also be distinguished as “on demand” or “live.” The former are stored on servers for long periods of time, becoming available to be transmitted to the user upon request. Live streams are available only at a particular time—like the streaming of a live T V broadcast.

Streaming server software package, the Real Time Streaming Protocol (RTSP) to control the interaction, and a matching client is needed for real-time streaming.

Transmission Protocols

Internet Protocols play an important role in media file transmission. Transmission protocols such as Transmission Control Protocol (TCP), User Datagram Protocol (UDP), RTSP, and Real-time Transport Protocol (RTP) are used.

TCP is “reliable”: data transmission happening via TCP is not blocked, and every bit is guaranteed to be transmitted. However, UDP is efficient since priority is given to continuous streaming of data rather than re transmission of lost packets. The user can suffer streaming glitches, but by error-correction techniques, lost data can be recovered. UDP is widely used for real-time streaming of audio, video, and graphics files.

RTSP and RTP are widely used for real-time media delivery over the Internet. Through RSTP, the user can communicate with the streaming server; it is used for simple one-to-one streaming. The user also gets the preference of device control—for viewing any part of the stream. This protocol is a good performer for one-to-

one viewing and larger audiences as well.

This protocol is usually used for streams via unicast (for transmission to a single client computer) or multicast (for transmission to multiple client computers) servers. Unicast is the term for when data is transferred from one point to another point, that is, one client and one server. Multicast is where data is transferred from one or more points to multiple points.

RTP is used for transmitting live streams to multiple users, but the users do not enjoy any sort of control like selective play of the media stream.

Legalities

Legal issues revolve around users being able to record the streaming of copyrighted content. It is difficult to stop such recording. Broadcasters sometimes use encryption for media content to make it difficult to record content.

Why most of the users prefer VPS and Not Shared Hosting….

VPS provides some interesting feature which are also available with Dedicated servers.

* Full Root Access & ability to install customized script.
* Define the server environment for customer.
* Isolated and protected from other domain on the server which may be abusing the server or overusing server resources.
* Dedicated mail, MySQL, FTP, http server.
* Dedicated Outgoing MailServer IP.

We can consider SHARED HOSTING is like living in an apartment building and sharing your neighbours’ problems. You hear their music through the wall. If one of them leaves the main door open, your security is at risk.

On a shared server, all the website hostings share the operating system and resources. Problems with your neighbours’ website hostings can slow down the server performance or require it to be shut down while the problem is being fixed. The slowdowns may also resulted from having too many website hostings on a server. Your site’s security depends in part on decisions made by your web host and, perhaps, your neighbours too.

Depending on your neighbours and, to a larger extent, your web host, you may have few or no problems with a shared hosting account. By sharing the resources, the cost is more economical than a VPS Hosting. Furthermore, for a basic website hosting, you require little or no technical knowledge to maintain your website hosting.

The majority of website hostings are on shared servers.

Where VPS Hosting HOSTING is more like living in a townhouse with your own private yard, and without ever hearing your neighbours. The only common area you share is the parking area and entrance; all traffic goes through one network port. In contrast to apartments or shared hosting accounts, you pay more for a VPS Hosting, but you can do what you want with the space, and your neighbors’ problems aren’t your problems.

VPS Hosting gives you the features and functionality of a dedicated server without the cost of building and maintaining one. If you need to host unlimited domains, have complete control of your environment and run your own applications, then you will benefit from having a VPS Hosting. In addition, a VPS Hosting provides the ability to manage your dedicated environment directly through SSH or Remote Desktop Connection. In addition, you can add a control panel like cPanel to our Linux VPS Hosting Plus plan. The control panel enables the administrative user to perform actions to their VPS Hosting immediately, without having to contact support to have a technician to complete any necessary requests.

I have made nice doc for those want to use free email virus checker instead of Dr. Web. (why should i pay more

First check in the System/Services if Dr.Web AV is stopped. Dr.Web AV needs license above the 15th email. There is tested and free alternative.

ClamAV – is available in windows in 2 variants – ClamWIN which is more user friendly and more aimed to scanning computers – and exact replica of Linux ClamAV which is the only working variant supported by Plesk:

http://www.sosdg.org/clamav-win32

Go to the page, download the latest version and install it. Run freshclamav once in a while or even better setup a scheduler to do it once daily.

Then go to System/Services and find the clamav service. Click on properties and set it up to run automatically.

Restart the VPS.

Go to Plesk and login as administrator.

Click on System-Server

Click Defaults Components (should it be Default components I would say?)

ClamAV should be one of the available antivirus options. Select it, and (maybe) restart VPS once again.

That would be it. If you want to know how to use antivirus from plesk then consult their manuals.

I have managed to run DNS without plesk dns. I hope it other will help this document to run dns without plesk panel.

Step are as follows:-
1. Manage Server -> Add Role -> DNS
2. Manage Server -> Manage this DNS server
3. Expand Forward Looup Zones on Left Hand Tree
4. Expand YourDomain.com
5. In Right Pane -> Right Click -> New Host (A)…
6. Enter * in Name field, enter your VPS IP in IP field, and click Save
7. In Right Pane -> Right Click -> New Host (A)…
8. Leave Name field blank, enter your VPS IP in IP field and click Save
9. Control Panel -> Windows Firewall
10. Add exceptions for port 53 UDP and TCP.

Make sure you have registered nameservers at domain registrar end.  Enjoy

Following steps to configure the Virtual Private Network on Windows Virtual Private Server.

1. For windows Virtual Private Server, First you need to enable “NAT” for your Virtual Private Server. This must be done from the Main Hardware Node. We will do it on our Main Hardware Node.

2. After enabling “NAT” for your Virtual Private Server, Go to Start >> RUN >> Type “services.msc” in you Virtual Private Server
On the services list, select “Routing and Remote Access” and go to properties. Make the startup type automatic and apply. After that you should have the option to “Start” this service. Start this service as we are going to use this service basically to route our traffic

3. Now go to Start >> Settings >> Control Panel >> Administrative Tools >> Click on the shortcut says “Routing and Remote Access”. It should open the configuration panel of Routing and Remote Access.

4. Now right click on your computer name the click the option says “Configure and Enable remote and routing access”. Before doing this, make sure your Firewall service is stopped and disabled.

5. Now on the configuration wizard click Next to proceed >> In the configuration list select “Custom Configuration”, Press Next >> Select Virtual Private Network Access & NAT and Basic Firewall Option, Press Next >> Now press Finish to end the wizard.

This wizard should enable the PPTP & L2TP Virtual Private Network access to your firewall with private routing capability. Now you need to configure your Virtual Private Server to route the private Traffic to the Public Interface. To do this, we need to have any of the following two:

1. Two network interface to route one to another. or
2. We can use NAT (Network Address Translation) using the Microsoft Loopback adapter.

We will basically work with the 2nd one as Virtual Private Server don’t come with two network interfaces. To continue with the NAT configuration, go to the Routing and Remote Access panel >> Expand ComputerName (Local) >> Expand Ip Routing >> You should find a option says “NAT/Basic Firewall”. Simply right click on that option and use the New interface to add network translation. Now first add the Inferface says “Internal” which is basically for private network access with default settings and on second attempt add your main adapter to the NAT list and select the options says “Public Interface connected to this inferface” and select the option says “Enable NAT on this interface”.

Now your network should have the address translation working, that means your private requests should be now translated and you can use this Virtual Private Network as your internet gateway.

Now to allow your users to use Virtual Private Network, add a new user and from the properties allow its Dial In permission. User with Dial-In permission should be able to login using Virtual Private Network.