In this article, we will learn how to change the MySQL Data Directory or relocate the MySQL Database data to the new location, this situation may be used when the database is growing very fast, or for some security reasons we want to move the data directory to the new location.PrerequisitesAn Ubuntu machine with a non-root user with Sudo permission.MySQL installed and working.A new volume or location where we want to move the database data location, the new location will be /mnt/data_vol/MySQL as the data_vol is the new volume attached to the machine or server.Changing the MySQL Data Folder LocationBefore ... Read More

In this tutorial, I will be explaining about – how to set up a secure connection to MySQL server using an SSH connection for encryption so that data in the database will be in safe and which is impossible for hackers to steal the data. SSL is used to verify the means of SSL certificates which can protect against phishing attacks. This will also show you – how to enable SSL on MySQL server also.Enabling SSL SupportConnect to the MySQL server and check that SSL status of the MySQL server# mysql -u root -p mysql> show variables like '%ssl%'; Output: ... Read More

In this article, we will learn about how to install and setup Sphinx on Ubunt 16.04, Sphinx is an open source search engine which allows for full test searches, and is best in performing searches with a huge data very effectively where the data can be from any sources, (Ex − SQL Databases, plain text files, etc., )Features of the SphinxAdvanced indexes and good tool for querying.High Searching performance and indexes.Advances result for post-processing.Easily scalable with advanced searches.Can be integrated with SQL and XML sources.Can be scalable for handing the huge data with 1000’s of queries.PrerequisitesBefore we begin we needed ... Read More

Magento is a fastest growing open source eCommerce platform that uses MySQL and Zend PHP databases. Magento is a very flexible eCommerce platform that facilitates powerful marketing, Management of multiple websites, catalogue management, and integration of Google Website Optimizer and over 50 payment gateways. It gives the flexibility for the users to control the content, look and functionality of the ecommerce.Magento was originally developed by Varien Inc., a US private company in California. Today, some of their largest users are Burger King, Nestle, Murad, BevMo, and Coca-Cola. Magento open source CMS platform provides increased control in terms of merchandising to ... Read More

With so many databases in the market, the mind wars have begun and it is the right time to understand the difference and importance of top 3 relational databases in the market – Microsoft SQL Server, Oracle PL/SQL, and MySQL. The relational database management systems have currently become the backbone of the industry and with so many options available, it is difficult to figure out which one to choose.The relational database management systems was introduced in 1980’s. This article is focussing on exploring the history and features of three popular RDBMS in the industry: Microsoft SQL Server, Oracle, and MySQL. ... Read More

To convert a date format, use STR_TO_DATE() −mysql> create table DemoTable2010 (    DueDate varchar(20) ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2010 values('12/10/2019 12:34:00'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable2010 values('12/12/2011 11:00:20'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable2010 values('31/01/2017 11:00:20'); Query OK, 1 row affected (0.23 sec)Display all records from the table using select statement −mysql> select * from DemoTable2010;This will produce the following output −+---------------------+ | DueDate             | +---------------------+ | ... Read More

Let us first create a table −mysql> create table DemoTable2009 (    Name varchar(20) ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2009 values('John Doe'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable2009 values('Adam Smith'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable2009 values('John Smith'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable2009 values('David Miller'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select * from DemoTable2009;This will produce the following output ... Read More

Let us first create a table −mysql> create table DemoTable2008 (    Value int ); Query OK, 0 rows affected (10.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2008 values(10); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable2008 values(20); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable2008 values(30); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable2008 values(-31); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable2008 values(-28); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement −mysql> select ... Read More

To insert multiple records quickly, use a single INSERT and follow the below syntax −insert into yourTableName values(yourValue1, yourValue2, ...N), (yourValue1, yourValue2, ...N).....N;To understand the above syntax, let us create a table −mysql> create table DemoTable2007 (    Amount1 int,    Amount2 int,    Amount3 int ); Query OK, 0 rows affected (1.36 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2007 values(450, 600, 700), (1000, 200, 3000),    (800, 900, 1200), (1300, 1500, 2000), (40000, 50000, 6700); Query OK, 5 rows affected (0.11 sec) Records: 5  Duplicates: 0  Warnings: 0Display all records from the table ... Read More

For this, you can use ORDER BY CAST(). Let us see an example −mysql> create table DemoTable2006 (    UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    UserCode    varchar(20) ); Query OK, 0 rows affected (1.14 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2006(UserCode) values('John_12'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable2006(UserCode) values('John_34'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2006(UserCode) values('John_56'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable2006(UserCode) values('Chris_101'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable2006(UserCode) values('Chris_103'); Query ... Read More