Technology Guru

While automated backups are important, sometimes you just want to take a quick and dirty snapshot before making a change to your data. When changing files in Linux, you can simply  cp  a file to another name, and  cp  it back if your change does not workout. With MySQL, it is not  quite  that simple, but it is by no means difficult. Creating A Backup The  mysqldump  command is used to create textfile “dumps” of databases managed by MySQL. These dumps are just files with all the SQL commands needed to recreate the database from scratch. The process is quick and easy. If you want to back up a  single database , you merely create the dump and send the output into a file, like so: mysqldump database_name > database_name.sql Multiple databases  can be backed up at the same time: mysqldump --databases database_one database_two > two_databases.sql In the code above,  database_one  is the name of the first datab...

While we do have many blog posts on replication on our blog, such as on replication being single-threaded, on semi-synchronous replication or on estimating replication capacity, I don’t think we have one that covers the very basics of how MySQL replication really works on the high level. Or it’s been so long ago I can’t even find it. So, I decided to write one now. Of course, there are many aspects of MySQL replication, but my main focus will be the logistics – how replication events are written on the master, how they are transferred to the replication slave and then how they are applied there. Note that this is NOT a HOWTO setup replication, but rather a howstuffworks type of thing. Replication events I say  replication events  in this article because I want to avoid discussion about different replication formats. These are covered pretty well in the MySQL manual here. Put simply, the events can be one of two types: Statement base...

MySQL database is the most popular database that already exists to overcome the daily data collection problem. There are countless tricks, options, and functions available for MySQL developers. The developers often are surrounded by many technical and detailed contents which can easily distract them from MySQL's big picture. The problem is that they lack a checklist of all the essential techniques to master. I have developed and normalized many advance MySQL databases (along with IT consulting), which motivated me to write this article. What follows are 15 essential tricks or things that any serious MySQL developer should know: 1. Table Relationships There are three forms of table relationships: one-to-one, One-to-Many (O2M), and Many-to-Many (M2M). My analogy for O2M is it is a parent-children relationship where one parent can have many children but not the other way around. Thus, always remember putting the Foreign Key (FK) in the children (many) table. For M2M, you just...

MySQL offers the ability to use regular expressions to perform complex searches against your data. A regular expression is a tool that provides for a concise and flexible way to identify strings of text based on user-defined patterns. This article will discuss the MySQL regular expression operators, review their use and syntax, and identify the constructs and special characters that can be used in a MySQL regular expression, as well as provide a few examples of their use. MySQL Regular Expression Operators The following operators are used in MySQL to perform regular expression operations. These are used in a WHERE clause similar to the well-known and often used LIKE operator. REGEXP : The pattern matching operator for using regular expressions. NOT REGEXP : The negation of the REGEXP operator. RLIKE : A synonym for the REGEXP operator. MySQL Regular Expression Syntax The basic syntax used for MySQL regular expression operations is: -- For the...

The MySQL has many types of the storage engines and the Federated storage engine is one of them. The Federated storage engine for the MySQL RDBMS (Relational Database Management System) is a storage engine which connects the tables remotely. It treats the table of the remote database as a local data source. Now first we understand: WHAT IS A FEDERATED TABLE? A Federated Table is a table which points to a table in another MySQL database instance (mostly on another server). It can be seen as a view to this remote database table. Other RDBMS have similar concepts for example database links. WHAT CAN WE DO WITH A FEDERATED TABLE? To show what we can do with a federated table let’s assume the following condition: We have two different MySQL situated at different location and we required fetching data from both server and in these data set we required a JOIN which is normally can’t done over two different server. But by federated connection it is possible. To create ...

List of mySQL Date function: NOW()   : It will give you the current date and time. For example: MYSQL> select  NOW() ; Output: 2017-02-25 00:52:57; CURDATE()  : It will give you current date. For example : MYSQL> select  CURDATE() ; Output:  2017-02-25; UNIX_TIMESTAMP()  : It will give you the current date and time in epoch format For example: MYSQL> select  UNIX_TIMESTAMP() ; Output:  1487964177 MYSQL> select  UNIX_TIMESTAMP (‘2017-02-25 00:52:57’); Output: 1487964177 FROM_UNIXTIME () : It will give you DATE and TIME from the epoch time format. For example: MYSQL> SELECT  FROM_UNIXTIME (1447430881); Output:  ‘2015-11-13 10:08:01’ MYSQL> SELECT  FROM_UNIXTIME (1447430881) + 0; Output: 20151113100801 DATE_FORMAT () : It will let you decide in what format you want to show date. For example : MYSQL> select DATE_FORMAT(NOW(),’%m-%d-%Y’) Output: 11-04-201...