09 Apr

mysql database backup shell script with status email

This post is for the backup script for MySQL database on Linux with mail. It’s a linux shell script for taking logical backup using mysqldump and sending status email.

The backup shell script works as follows:
– The script takes backup using mysqldump and compresses it.
– Upon success, it will attempt to ship the backup to specified offsite location.
– Upon detecting failure in any of the above step, it will send out failure email.
– Upon overall success, it will sendout success email with execution time and present backups list.

You might be looking for setting up physical backups for mysql using Holland backup framework.

Following is the code for MySQL backup shell script using mysqldump with status email.

07 Sep

MySQL 8 Resource Group – introduction and dynamic allocation

MySQL 8 is GA and it has a variety of new features. Recently, we happened to work with resource groups to restrict resource utilization for a MySQL thread.

That’s why I thought of writing this blog post: to give you a quick introduction to resource groups, an idea of how to dynamically allocate them and to discuss a related bug report.

Introduction To Resource Groups:

A resource group, a new feature in MySQL 8, is the entity that defines the allowed resources consumption for threads pertaining to that group. Group attributes enable control over resources and hence the database threads get restricted with those limits.

Resource groups introduce the following associated components:
1. SQL to manage the resource groups (create, alter, assign, drop).
2. Resource groups related privileges.
3. The information_schema.resource_groups table, which holds groups details.
4. A new column was added to the performance_schema.threads table to show us the assignments of threads to respective resource groups.

13 Aug

Galera cluster to AWS Aurora migration & HA_ERR_FOUND_DUPP_KEY

In this post we will see a case study of a Galera Cluster migration to AWS Aurora and quick solution to the replication issue.

A friend received an error in a Master-Master replication as follows:

Could not execute Write_rows event on table _database._table; Duplicate entry '65eJ8RmzASppBuQD2Iz73AAy8gPKIEmP-2018-08-03 08:30:03' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin-changelog.000010, end_log_pos 1107814

We talked about it and I immediately started blabbering cons of writing on both masters, how to handle, roles of apps and so on. He intervened and revealed, it is Galera Cluster replicated to Aurora and he is not writing on Aurora.

He was actually migrating his Galera Cluster to Aurora and master-master was to support his rollback plans. Consider following diagram for simplicity of understanding:

galera to aws migration – sample architecture

29 Jun

Restore A Table / Database From Full Backup – Yet Another Way

Restore requests are common and so are the restores of specific entities: a database, or one or more table(s). This has been discussed a lot and we have plenty of tools and solutions already available.
In this blog post we will cover an interesting solution that I came across when I received a restoration request from a client with a specific scenario.

The scenario? Well, the client was on a Windows server with 400GB of mysqldump and wanted to restore a table.

As Linux players we already know of some tools and techniques to export a table or database from mysqldump – for example, using sed command or using the script mysqldumpsplitter (based on sed itself). But on Windows we are powerless by not being able to use sed (we’re sad without sed.) Also, there was no cygwin to ease up the pain.

We had to come-up with a solution that works on Windows as well. During this discussion, my Pythian colleague, Romuller, suggested a cool but simple trick which enlightens us and offers one more way of exporting or recovering a table from a full mysqldump.

So the trick here is as follows:

29 Jan

Export MySQL database table to CSV (delimited / Excel) file

Today lets talk a little about converting a MySQL table to CSV (Excel). My friend was looking to export MySQL to Excel, I saw couple of questions for export MySQL tables to CSV on forums. Since I saw the question often, I thought of writing out all the ways I can think of for exporting Delimited (CSV / TSV / …) data from MySQL table. Pretty chewed & basic but frequent topic.

Following are the ways to export CSV data from MySQL database / table(s).

1. Using SELECT INTO … OUTFILE statement to export from MySQL to CSV

SELECT ... INTO OUTFILE writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format. Just to mention, SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE, which you may use to load CSV (generally speaking delimited) files to MySQL.

Here’s a sample command to export “tablename” table of “db” database as a CSV:

SELECT * INTO OUTFILE '/csv_files/db.tablename.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM db.tablename;