MySQL

Copy database or table from one MySQL server to another, clever way.

Posted on September 1, 2018 by admin

This simple bash script can copy database or a table from one remote server to another. Can be used as a backup, but also as a one way sync on a database or table.

ssh -fNg… <- creates a permanent tunnel to the remote server on port 3307. So now from the local server you can access both local and remote MySQL.

export MYSQL_PWD=… <- Helps hide password, as long as you make this script secure, the password it not accessible.

Last one-liner does the rest, if you remove <DB_TABLE_NAME> it will send the entire DB over to the remote, so be carefuller. But you already new that and have a backup, right?

Posted in Linux, MySQL Leave a comment

MySQL PDO exec vs execute vs query

Posted on December 15, 2017 by admin

Use PDO::exec to issue one-off non-prepared statements that don’t return result sets.
Use PDO::query to issue one-off non-prepared statements that return result sets.

Both of these are useful if the statements are only executed one time and/or if they are constructed dynamically in a way that is not supported by prepared statements. Usually requires additional tooling to properly construct statements (and avoid things like SQL injection vulnerabilities). That coupled to the fact their flexibility is seldom needed means that it’s often preferred to:

Use PDOStatement::prepare and PDOStatement::execute to prepare statements and execute them, regardless of whether they return results or not. Useful if executed multiple times and/or in a hot path. Also doesn’t require additional tooling to handle statement construction. Almost always recommended whenever possible.

Posted in MySQL Leave a comment

Partitioning on MySql

Posted on August 14, 2017 by admin

Imagine you need to execute one ore more queries on a big size table, containing the history of the sales of an e-commerce point.

If your hardware resources are limited, scanning the whole table could require several minutes, and this in turn could imply long table locks and waste of time to the users.

Since version 5.1, MySql supports partitioning, a mechanism allowing data to be divided according to access needs.

There are several partitioning types. The most popular mechanisms are:

– RANGE: rows are divided based upon a specified column range

– HASH: the column hash is calculated based upon the record position in the partition resulting from the operation

– LIST: works like RANGE but the values are not surely adjacent

The partitioning of a table works in two different manners:

– during the table creation

CREATE TABLE sales (
    id int NOT NULL,
    order_date DATETIME NOT NULL,
    user bigint NOT NULL,
    total float NOT NULL DEFAULT '0',
    receipt_id bigint NULL
) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) ( 
  PARTITION p_history VALUES LESS THAN (2014), 
  PARTITION p_data VALUES LESS THAN MAXVALUE 
);

during the modification of the table structure

ALTER TABLE sales PARTITION BY RANGE(YEAR(order_date)) (
	PARTITION p_history VALUES LESS THAN (2014),
	PARTITION p_data VALUES LESS THAN MAXVALUE
);

An important tool for debugging the extraction operations is the following command

EXPLAIN PARTITIONS;

The command output shows the partition used by MySql for execution of the specified query.

The partitioning is a very effective tool, but it may give no results, if you don’t choose the proper partitioning strategy: partitioning a sales table according to the creation date and then filtering data based on users, might be useless, independently on the partition criteria, or could even worst the situation.

Posted in MySQL 2 Comments

Event scheduler in MySql

Posted on August 14, 2017 by admin

Since Mysql version 5.1.6, the system supports the option to schedule the execution of some events, without need to use external software (cron, at, Task Scheduler, etc.) or to change data as required for trigger implementation.

To activate the schedule function in MySql, you have simply to execute in a client the following command:

SET GLOBAL event_scheduler = 1;

Then you can verify the correct system working, by executing the following command:

SHOW PROCESSLIST;

A task can be executed once or more times and can be defined via a SQL syntax.

CREATE EVENT DELETE_BASKET_DATA
  ON SCHEDULE EVERY 1 MINUTE 
  DO DELETE FROM basket where created > DATE_SUB(NOW(), INTERVAL 30 MINUTE);

When a task is executed, all data older than 30 minutes will be deleted.

To show how many and which tasks have been defined on the database, you need to simply execute the command “SHOW EVENTS”.

To erase a task, you can simply execute the following command

DROP EVENT DELETE_BASKET_DATA;

By using this function, you can perform some maintenance tasks on data, without affecting other parts of your application. With such options, MySql was able to fill its gap with some of the most important relational databases currently available.

The Blog

MySQL

Copy database or table from one MySQL server to another, clever way.

MySQL PDO exec vs execute vs query

Partitioning on MySql

Event scheduler in MySql

Permanently changing MySQL mode

Permanently changing SQL mode

All Systems Go.