≡ Menu

MySQL

By default MySQL database will be created under /var/lib/mysql directory. This might be Ok if you are running a small database that doesn’t take much space. But on production, for a larger database, you might not have enough space under root partition. In that case, you may want to move your MySQL database from root [...]

{ 1 comment }

How to Optimize MySQL Tables and Defragment to Recover Space

If your application is performing lot of deletes and updates on MySQL database, then there is a high possibility that your MySQL data files are fragmented. This will result in lot of unused space, and also might affect performance. So, it is highly recommended that you defrag your MySQL tables on an ongoing basis. This [...]

{ 5 comments }

How to Shrink MySQL ibdata1 Size using innodb_file_per_table

In MySQL, when you are using InnoDB, all the tables and indexes are stored under the MySQL system tablespace. MySQL system tablespace is ibdata1, which is located under /var/lib/mysql The single ibdata1 file contains all the tables and indexes in your MySQL database. So, if you have a big database, this file size will grow [...]

{ 3 comments }

12 Essential MySQL Insert Command Examples

One of the common MySQL operation is to insert records into a table. This tutorial explains how to use MySQL insert command with several practical and useful examples. The following example will connect to devdb database with username devuser and password mysecretpwd # mysql -u devuser -pmysecretpwd devdb mysql> For this tutorial, we'll insert values [...]

{ 4 comments }

This tutorial explains how to restore MySQL tables when all or some of the tables are lost, or when MySQL fails to load table data. One of the reason for this to happen is when the table data is corrupted. In this particular scenario, when you connect to the MySQL database server, you cannot see [...]

{ 4 comments }

When you migrate from MS SQL to MySQL, apart from migrating the data, you should also migrate the application code that resides in the database. Earlier we discussed how to migrate MS SQL to MySQL database using the WorkSQL Workbench tool. As part of the migration, it will only convert tables and copy the data, [...]

{ 2 comments }

How to Migrate Microsoft SQL Server to MySQL Database

If you are using mostly open source in your enterprise, and have few MS SQL server database around, you might want to consider migrating those to MySQL database. The following are few reasons why you might want to consider migrating Microsoft SQL Server to MySQL database: To avoid huge License and support fees of MS [...]

{ 9 comments }

If you've decided to use MySQL database for your enterprise application, you should wisely choose the MySQL storage engine type that will be appropriate for your situation. Depending on the situation, a particular storage engine can be a best fit or worst fit. Once you understand the differences between all MySQL storage engines, it will [...]

{ 10 comments }