Menu Close

Author: marceloaltmann

Adding your own collation to MySQL

I’m the kind of DBA that prefers to keep everything simple, BUT, sometimes it’s not possible. Few days ago I’ve faced an issue where none of the collations shipped by default with MySQL would guarantee integrity of my database, and to avoid a massive re-write of application code, we have explored an option that up to the day, I didn’t know about. Add your own collation to MySQL. This option is described on this section of MySQL documentation . On this post I will show how to make MySQL identify volves with acute accent (fada) as a different letter: First, let’s find out where is our character-set folder: mysql [localhost] {msandbox} ((none)) > SHOW VARIABLES LIKE ‘character_sets_dir’; +——————–+—————————————+ | Variable_name | Value | +——————–+—————————————+ | character_sets_dir | /mysql/sources/5.6.26/share/charsets/ | +——————–+—————————————+ . . .

Liked? Help and Share!

MySQL & NoSQL – Memcached Plugin

Many of you have already heard about NoSQL databases and one of the the most used tool is Memcached, where you add a cache layer between the application and database. Since MySQL version 5.6, a new plugin is available to do the integration between MySQL and Memcached. On this article, we will learn how to install it on linux, and some basic configurations of it. Pre-requirements: Install libevent Installation: To install memcached support we will need to create a few tables responsible for MySQL and memcached integration. MySQL already includes the file which creates those tables (innodb_memcached_config.sql), you can find this file in a sub folder of your basedir. To discover where is your basedir, run the bellow command: mysql> SHOW VARIABLES LIKE ‘basedir’; +—————+——-+ | Variable_name | Value | . . .

Liked? Help and Share!

MySQL Fabric – Part 1 – Installing

MySQL Fabric is a tool included on MySQL Utilities that helps you to manage your MySQL instances. It works by basically adding a new layer between your application and MySQL instances, which can provide an easy way to use sharding and build a highly available system. To install our Fabric environment, we will have to configure 4 servers, I will use the follow names and IP on this tutorial: fabric1 (192.168.0.200) – fabric mysql1 (192.168.0.201) – mysql master mysql2 (192.168.0.202) – mysql slave mysql3 (192.168.0.203) – mysql slave Note: I’m running CentOS 6.5 on all servers. 1. Add mysql repo on all 4 machines, please read Installing latest version of MySQL via yum for more info: rpm -i http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm yum update 2. Install mysql mysql-server mysql-utilities: yum install mysql mysql-server . . .

Liked? Help and Share!

MySQL Sandbox

Hi there, today we will learn about an amazing tool that every single MySQL dba must know, I’m talking about MySQL Sandbox. MySQL Sandbox is developed by Giuseppe Maxia (The Data Charmer), it’s a tool that make the installation of MySQL servers very easy. If you need to quickly create a MySQL instance for test or a replication setup(it supports master slave, circular and master master replication), this it the tool. INSTALLATION: Go to http://mysqlsandbox.net/ and get the latest version (I got from launchpad): yum install perl perl-ExtUtils-MakeMaker perl-Test-Simple wget https://launchpad.net/mysql-sandbox/mysql-sandbox-3/mysql-sandbox-3/+download/MySQL-Sandbox-3.0.44.tar.gz tar -zxvf MySQL-Sandbox-3.0.44.tar.gz cd MySQL-Sandbox-3.0.44 perl Makefile.PL make make test make install CREATING A SINGLE SANDBOX: To create a single sandbox, all you need is the mysql package that you want install and the make_sandbox command: [root@localhost ~]# make_sandbox mysql-5.6.17-linux-glibc2.5-i686.tar.gz unpacking /root/mysql-5.6.17-linux-glibc2.5-i686.tar.gz Executing . . .

Liked? Help and Share!

Warm-up InnoDB Buffer Pool

As we know, one of the most important config for InnoDB is the innodb_buffer_pool_size, it basically store the innodb data and indexes in memory, when MySQL receives a query and the InnoDB pages involved on that query are stored in the buffer, it does not need to go to the disk to return the result, which is much faster (memory speed vs disk speed). As it is stored in memory, every time you restart your MySQL server it starts with a clean/empty buffer pool and usually it take some time to warm-up the buffer. To speed up this process, we can configure 2 variables that will dump and reload the pages reference stored in the buffer, this is a new functionality added on MySQL 5.6 (it was presented on previous . . .

Liked? Help and Share!

Installing latest version of MySQL via yum

Usually when you try to install MySQL via yum, by default, the version that comes with your OS is always an old version (not the latest version available from mysql.com/downloads). In order to have the latest version, you always needed to go to mysql.com/downloads and manually download and install MySQL packages right? Not anymore, MySQL recently released the MySQL YUM Repository. Basically, it will allows you to install the latest GA version of MySQL Community Server, Workbench and Connector/ODBC. HOW TO INSTALL? You can basically install it in 2 ways: 1. Using the RPM package Download the .rpm from dev.mysql.com/downloads/repo/ and install it: wget http://dev.mysql.com/get/mysql-community-release-el6-3.noarch.rpm/from/http://repo.mysql.com/ sudo yum localinstall -y mysql-community-release-el6-3.noarch.rpm 2. Manually adding a file under /etc/yum.repos.d/ folder Add the bellow content to a new file named mysql-community.repo : [mysql-community] . . .

Liked? Help and Share!

Auditing MySQL With Mcafee Audit Plugin

Audit MySQL isn’t an easy task by default, you can use some technics like tcpdump, write a parser for general log, use MySQL proxy, or you can use some of audit plugins available out there(Mcafee MySQL Audit Plugin or MySQL Enterprise Audit Log Plugin for example). On this post I’ll cover the Mcafee MySQL Audit Plugin (https://github.com/mcafee/mysql-audit), on a follow-up post I’ll talk about MySQL Enterprise Audit Log Plugin. The installation is easy and require just a few steps, I’m using MySQL 5.5 32 bits, so I’ll download the files for my MySQL version and architecture from https://github.com/mcafee/mysql-audit/downloads [root@mysql-audit marcelo]# wget https://github.com/downloads/mcafee/mysql-audit/audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip [root@mysql-audit marcelo]# unzip audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip Archive: audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip creating: audit-plugin-mysql-5.5/ creating: audit-plugin-mysql-5.5/lib/ inflating: audit-plugin-mysql-5.5/lib/libaudit_plugin.so inflating: audit-plugin-mysql-5.5/COPYING inflating: audit-plugin-mysql-5.5/THIRDPARTY.txt inflating: audit-plugin-mysql-5.5/README.txt Next step is to copy the libaudit_plugin.so to MySQL plugin . . .

Liked? Help and Share!

Multi-Source Replication with MySQL 5.7 – example

Complementing this previous post, today we are going to configure 1 slave receiving updates from 2 master with Multi-source replication. As mention before, this feature is only available on labs.mysql.com. To configure it, is very simple, we are going to nees 2 masters with GTID enabled (see this post to know how to configure) and a slave with crash save options enabled. Master 1 e 2: gtid-mode=on enforce-gtid-consistency Slave master_info_repository=TABLE relay_log_info_repository=TABLE gtid-mode=on enforce-gtid-consistency Let’s first create our replication user: master1 [localhost] {msandbox} ((none)) > GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’127.0.0.1’ IDENTIFIED BY ‘123’; master2 [localhost] {msandbox} ((none)) > GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’127.0.0.1’ IDENTIFIED BY ‘123’; Now we are going to configure our slave normally, there is a new option named FOR CHANEL, that will identify which . . .

Liked? Help and Share!

MySQL Multi Source Replication

Last week, durring MySQL Connect, MySQL 5.7.2 DMR was launched, one of the new functionality is the multi source replication. At the moment, MySQL can have only one master per slave (you can archive multi source replication via some hacks, but like the name says, it’s a hack). See how to configure here To clarify, there is a difference between multi-master replication and multi source replication, see the bellow pictures to understand the difference: Multi Master Replication – In the above picture, we have 2 master’s and 1 slave, where, master 1 is master of master 2, master 2 is master of master 1 and also of slave, in order to slave receive updates from master 1, all this updates must past to master 2 to later one be replied . . .

Liked? Help and Share!