Showing entries 1 to 10 of 113
10 Older Entries »
Displaying posts with tag: Scripting (reset)
MySQL 8.0 InnoDB Cluster and Persistent Configurations

There are lots of new features in MySQL 8.0 that was recently released including our Document Store capability.  There are a few core capabilities related to InnoDB Cluster in 8.0 that I’d like to share but some will arrive in some coming blogs.  Primarily here I’ll point out some nice details with InnoDB Cluster 8.0… Read More »

MySQL Enterprise Edition Database Firewall – Control and Monitor SQL Statement Executions

As of MySQL 5.6.24, MySQL Enterprise Edition includes MySQL Enterprise Firewall, an application-level firewall (it runs within the mysql database process) that enables database administrators to permit or deny SQL statement execution based on matching against whitelists of accepted statement patterns. This helps harden MySQL Server against attacks such as SQL injection or attempts to exploit applications by using them outside of their legitimate query workload characteristics.

Each MySQL account registered with the firewall has its own whitelist of statement patterns (a tokenized representation of a SQL statement), enabling protection to be tailored per account. For a given account, the firewall can operate in recording or protecting mode, for training in the accepted statement …

[Read more]
MySQL Yum repo setups for commercial and community use cases

MySQL Package Management Options In this blog we will explore some interesting ways to install MySQL Community and Enterprise Edition binaries using your associated Linux package manager.  In this case we’ll look mostly at the Yum package manager on Oracle Linux.  The benefit of these package managers is that you can install software packages easily,… Read More »

MySQL Enterprise Audit – parsing audit information from log files, inserting into MySQL table via LOAD DATA INFILE and Perl script

The MySQL Enterprise Audit plug-in is part of the MySQL Enterprise Edition (available through a paid license). Basically, Enterprise Audit tracks everything that is happening on your MySQL server, and can be used to protect/detect the misuse of information, and to meet popular compliance regulations including HIPAA, Sarbanes-Oxley, and the PCI Data Security Standard.

MySQL Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query activity executed on specific MySQL servers. Designed to meet the …

[Read more]
southern fried: quick script to fix table auto increments

OK, Y’all.  If you have been a DBA long enough you have run into situations where you’ve had to import DDL from another database instance and forgotten to remove AUTO_INCREMENT on a table definition.  Sometimes this means your auto_increment is way up in the millions and you have 10 rows in the table.  People worried about ID depletion on an integer column will do a “facepalm”.

Making matters worse, having perhaps done this on more than one table, sometimes it’s a real pain to detect and fix the issue of auto_increments being much higher than you want them to be across a big number of tables.  Sometimes the pain is so great that you might want to simply repeat your data importation.

This quick and dirty “southern fried” script will help you report and rectify that.  It detects …

[Read more]
A practical explanation: problems during unicode collation conversion

Introduction

Recently I have been involved in an effort to convert MySQL databases from a utf8 character set to utf8mb4. As a part of this effort, my team evaluated which collations would be best for facilitating a broad range of multi-lingual support.

There have been many recent posts in the MySQL community about better unicode collation support in MySQL 8 such as from the MySQL Server Team’s blog at Oracle, who have also done a good job of showing us how newer collations based on UTF8 9.0.0 will properly group and sort characters according to their case and inheritance. As the title of the latter post suggests, the “devil is” indeed “in the details”.

There is also the matter of the …

[Read more]
Easy-to-use Perl scripts to backup your MySQL database with mysqldump and FTP the files to a remote server

Most users of MySQL utilize the mysqldump utility to backup their database. While mysqldump is handy and easy-to-use (and free), if you have data which is important to your business, then you should take a look at the MySQL Enterprise Edition – and use MySQL Enterprise Backup instead. The MySQL Enterprise Backup allows you to backup your database without the table locking you get with mysqldump. And, it is extremely fast – especially when you have to restore your database. Here is a sample speed comparison between MySQL Enterprise Backup and …

[Read more]
Binary Log Growth Handling in MySQL

So today we look at an aspect of capacity management or planning and how it relates to MySQL and its Logging.  For this blog I’m primarily going to look at MySQL’s binary logs. They have a tendency to grow, and will continue to if not held in check.  Happily, they tend to be easy to… Read More »

MySQL multi-instance Group Replication on systemd

In this blog post, I’d like to take a look at a few different things such as MySQL Group Replication, multi-instance MySQL setups on systemd and shell scripting the whole mess to make it easy to build, and easy to rebuild.  To be honest, it took a little help from Shinguz’s blog to get the… Read More »

Command line QPS (Queries Per Second) Quick and Dirty

Ever wanted to just look at QPS in real time while logged into your server?

Well here’s a little command line hackery to do it quick and dirty.

[user@yourserver ~] $ LASTVAL=0; while true; do CURVAL=`mysql --batch -N -e "show status like 'Quer%';" | awk '{print $2}'`; QPS=`expr $CURVAL - $LASTVAL`; if [ $LASTVAL -ne 0 ]; then echo "$CURVAL $QPS"; fi; LASTVAL=$CURVAL; sleep 1; done

The output looks like this:

65549603430 2439
65549605421 1991
65549606912 1491
65549611219 4307
65549614186 2967
65549618048 3862
65549620853 2805

The first column is just the Query counter value. The second column is the QPS.

This script requires a .my.cnf to exist in your home directory (or that you do something nastily insecure and supply -u user -ppassword to the mysql command in the example above).

Showing entries 1 to 10 of 113
10 Older Entries »