Mysql database engine: Innodb vs Myisam

The 2 major types of table storage engines for MySQL databases are InnoDB and MyISAM. To summarize the differences of features and performance,

  1. InnoDB is newer while MyISAM is older.
  2. InnoDB is more complex while MyISAM is simpler.
  3. InnoDB is more strict in data integrity while MyISAM is loose.
  4. InnoDB implements row-level lock for inserting and updating while MyISAM implementstable-level lock.
  5. InnoDB has transactions while MyISAM does not.
  6. InnoDB has foreign keys and relationship contraints while MyISAM does not.
  7. InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
  8. MyISAM has full-text search index while InnoDB has not.

In light of these differences, InnoDB and MyISAM have their unique advantages and disadvantages against each other. They each are more suitable in some scenarios than the other.

Advantages of InnoDB

  1. InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions.
  2. Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.

Disadvantages of InnoDB

  1. Because InnoDB has to take care of the different relationships between tables, database administrator and scheme creators have to take more time in designing the data models which are more complex than those of MyISAM.
  2. Consumes more system resources such as RAM. As a matter of fact, it is recommended by many that InnoDB engine be turned off if there’s no substantial need for it after installation of MySQL.
  3. No full-text indexing.

Advantages of MyISAM

  1. Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
  2. Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
  3. Full-text indexing.
  4. Especially good for read-intensive (select) tables.

Disadvantages of MyISAM

  1. No data integrity (e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers.
  2. Doesn’t support transactions which is essential in critical data applications such as that of banking.
  3. Slower than InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.

The comparison is pretty straightforward. InnoDB is more suitable for data critical situations that require frequent inserts and updates. MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.

important mysql command, backup, dump restore

Connest to mysql server

mysql -u USERNAME -h ‘HOST_IP’ -p

enter password – here

mysql> show databases;

mysql> use DATABASE_NAME;

mysql> your query here. 🙂

Dumping a mySQL to a sql file

mysqldump -l --opt databasename > /root/file/location/filename.sql  -u user --password=whateverthepass 

Importing mySQL dump file

mysql databasename < /root/file/location/filename.sql -u user --password=whateverthepass 

Copying Entire Folder of Files

cp - Ru /root/file/location/* /where/it/should/go --reply=yes 

Making a tgz archive of an entire folder for FTP export

tar zcf localfolder.tgz localfolder/ 

Copying an entire folder to another server

tar zcf - localfolder/  | ssh "cd folder/to/copy/to; tar zpxvf -" 

If you want to extract one file from the .tar.gz file use

gzip -dc file.tar.gz | tar xf - pathname/filename
The pathname and filename should be exactly as given in the .tar.gz file. If you want more than one file append their names, again include pathname, at the end of the command. 

Mysql master slave server database load balancing


1. Creating a User for Replication

Each Slave must connect to the Master using a standard username and password. The user that you use for this operation can be any user, providing they have been granted the REPLICATION SLAVE privilege.

You do not need to create a specific user for replication. However, you should be aware that the username and password will be stored in plain text within the file. Therefore you may want to create a user that only has privileges for the replication process.

To create a user or grant an existing user the privileges required for replication use the GRANT statement. If you create a user solely for the purposes of replication then that user only needs the REPLICATION SLAVE privilege. For example, to create a user, repl, that allows all hosts within the domain to connect for replication:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'' IDENTIFIED BY 'slavepass';

2. Setting the Replication Master Configuration

For replication to work you must enable binary logging on the master. If binary logging is not enabled, replication will not be possible as it is the binary log that is used to exchange data between the master and slaves.

Each server within a replication group must have a unique server-id. The server-id is used to identify individual servers within the group, and must be positive integer between 1 and (232)-1). How you organize and select the numbers is entirely up to you.

To configure both these options you will need to shut down your MySQL server and edit the configuration of the my.cnf or my.ini file.

You will need to add the following options to the configuration file within the [mysqld] section. If these options already exist, but are commented out, uncomment the options and alter them according to your needs. For example, to enable binary logging, using a log filename prefix of mysql-bin, and setting a server ID of 1:


3. Setting the Replication Slave Configuration

The only option you must configure on the slave is to set the unique server ID. If this option is not already set, or the current value conflicts with the value that you have chosen for the master server, then you should shut down your slave server, and edit the configuration to specify the server id. For example:


If you are setting up multiple slaves, each one must have a unique server-id value that differs from that of the master and from each of the other slaves. Think of server-id values as something similar to IP addresses: These IDs uniquely identify each server instance in the community of replication partners.

If you do not specify a server-id value, it is set to 1 if you have not defined master-host; otherwise it is set to 2. Note that in the case of server-id omission, a master refuses connections from all slaves, and a slave refuses to connect to a master. Thus, omitting server-id is good only for backup with a binary log.

You do not have to enable binary logging on the slave for replication to be enabled. However, if you enable binary logging on the slave then you can use the binary log for data backups and crash recovery on the slave, and also use the slave as part of a more complex replication topology.

4. Obtaining the Master Replication Information

To configure replication on the slave you must determine the masters current point within the master binary log. You will need this information so that when the slave starts the replication process, it is able to start processing events from the binary log at the correct point.

If you have existing data on your master that you want to synchronize on your slaves before starting the replication process, then you must stop processing statements on the master, obtain the current position, and then dump the data, before allowing the master to continue executing statements. If you do not stop the execution of statements then the data dump, the master status information that you use will not match and you will end up with inconsistent or corrupted databases on the slaves.

To get the master status information, follow these steps:

1. Start the command line client and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:


2. Use the SHOW MASTER STATUS statement to determine the current binary log name and offset on the master:

      mysql > SHOW MASTER STATUS;
      | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
      | mysql-bin.003 | 73       | test         | manual,mysql     |

The File column shows the name of the log and Position shows the offset within the file. In this example, the binary log file is mysql-bin.003 and the offset is 73. Record these values. You need them later when you are setting up the slave. They represent the replication coordinates at which the slave should begin processing new updates from the master.

If the master has been running previously without binary logging enabled, the log name and position values displayed by SHOW MASTER STATUS or mysqldump –master-data will be empty. In that case, the values that you need to use later when specifying the slave’s log file and position are the empty string () and 4.

You now have the information you need to enable the slave to start reading from the binary log in the correct place to start replication.

5. Creating a Data Snapshot Using Raw Data Files

If your database is particularly large then copying the raw data files may be more efficient than using mysqldump and importing the file on each slave.

To create a raw data snapshot of MyISAM tables you can use standard copy tools such as cp or copy, a remote copy tool such as scp or rsync an archiving tool such as zip or tar, or a file system snapshot tool such as dump, providing that your MySQL data files exist on a single filesystem. If you are only replicating certain databases then make su

re you only copy those files that related to those tables. (For InnoDB, all tables in all databases are stored in a single file unless you have the innodb_file_per_table option enabled.)

You may want to specifically exclude the following files from your archive:

  • Files relating to the mysql database.
  • The file.
  • The master’s binary log files.
  • Any relay log files.

To get the most consistent results with a raw data snapshot you should shut down the server during the process, as below:

1. Acquire a read lock and get the master’s status. 2. In a separate session, shut down the MySQL server:

      shell> mysqladmin shutdown

3. Take a copy of the MySQL data files. Examples are shown below for common solutions – you need to choose only one of these solutions:

      shell> tar cf /tmp/db.tar ./data
      shell> zip -r /tmp/ ./data
      shell> rsync --recursive ./data /tmp/dbdata

4. Start up the MySQL instance on the master.

If you are not using InnoDB tables, you can get a snapshot of the system from a master without shutting down the server as described in the following steps:

1. Acquire a read lock and get the master’s status. See Section, “Obtaining the Master Replication Information”. 2. Take a copy of the MySQL data files. Examples are shown below for common solutions – you need to choose only one of these solutions:

      shell> tar cf /tmp/db.tar ./data
      shell> zip -r /tmp/ ./data
      shell> rsync --recursive ./data /tmp/dbdata

3. In the client where you acquired the read lock, free the lock:

      mysql> UNLOCK TABLES;

Once you have created the archive or copy of the database, you will need to copy the files to each slave before starting the slave replication process.

6. Setting the Master Configuration on the Slave

To set up the slave to communicate with the master for replication, you must tell the slave the necessary connection information. To do this, execute the following statement on the slave, replacing the option values with the actual values relevant to your system:

    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

7. Starting the slave threads

Start the slave using:


After you have performed this procedure, the slave should connect to the master and catch up on any updates that have occurred since the snapshot was taken. You also find error messages in the slave’s error log if it is not able to replicate for any other reason.

Once a slave is replicating, you can find in its data directory one file named and another named The slave uses these two files to keep track of how much of the master’s binary log it has processed. Do not remove or edit these files unless you know exactly what you are doing and fully understand the implications. Even in that case, it is preferred that you use the CHANGE MASTER TO statement to change replication parameters. The slave will use the values specified in the statement to update the status files automatically.