Menu Close

Category: MySQL

ProxySQL Tutorial – Master and Slave

Continuing on the series of blog posts about ProxySQL. Today I will demonstrate how to put ProxySQL in front of your master and slave topology and distribute reads and writes between the servers, without the need of changing your application code. To install ProxySQL and some of its basic concepts, please read this blog post. On this tutorail, we will use 4 servers: ProxySQL (192.168.112.60) Node1 – Master (192.168.112.61) Node2 – Slave-1 (192.168.112.62) Node3 – Slave-3 (192.168.112.63)   Servers With ProxySQL already installed, we will connect into the admin interface, add our 3 servers and configure Replication Hostgroup. Basically, we will tell ProxySQL which HG will be our master and which HG will be our slaves. ProxySQL differs master and slaves based on server  read_only variable: $ mysql -u admin -padmin -h 127.0.0.1 -P . . .

Liked? Help and Share!

Understanding ProxySQL

In this post, I will cover a bit of ProxySQL. ProxySQL is a proxy which implements MySQL protocol, allowing it to do things that other proxies cannot do. ProxySQL is gaining a lot of traction nowadays and it’s capable to integrate with a variety of products from MySQL ecosystems, such as replication (master – slave / master – master), Percona XtraDB Cluster and Group Replication. One of its many functionalities (which IMHO makes it awesome) is the ability to do read/write split seamless to the application. You can start sending reads to your slave without doing a single line of code. In this article, I will cover a few important points to understand how it works. Instalation The instalation is easy, you can do it by: Downloading the corresponding package for . . .

Liked? Help and Share!

MySQL Online Backup with xtrabackup

My last post about backups was a while ago. Although it is still valid, it’s not advisable to use it as daily backup. For that we can use XtraBackup. With XtraBackup, you can take online backups without interrupt the server workload(it will require a small lock to take binlog coordinates). Today I’ll show how to take a complete backup. Install: To install it, I advise you to use Yum / Apt-get repos: Centos / Redhat: sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm sudo yum install percona-xtrabackup-24 Debian / Ubuntu wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo apt-get update sudo apt-get install percona-xtrabackup-24 Backup: To take the backup, we will use innobackupex script: innobackupex –user=root –password=’Passw0rd!’ /backups/ The script will produce some log messages, we need to verify if it ends with . . .

Liked? Help and Share!

MySQL 8.0 – SET PERSIST command

Hi guys. MySQL 8 DMR was released today and it has some cool new features. One of those is the ability to persist dynamic changed variables/configurations across restarts. It’s very usefull if you change variables dynamically. It’s saves you the trouble of edit a cnf file every time you run a SET on mysql (or even when you don’t have access to those files). The new syntax will be as follow: SET PERSIST option=value; SET @@persist.option=value; MySQL will create a new file named mysqld-auto.cnf located on it’s DATADIR folder. This file will contain all PERSISTENT variables and will be loaded after all other files (my.cnf / –defaults-file / ~/.my.cnf / …) Which means that values from mysqld-auto.cnf will take place in case of the same option be present on multiple . . .

Liked? Help and Share!

MySQL 5.7 using vagrant

Hi there. Today I will write a quick post to show how to easily configure a brand new mysql 5.7 machine using vagrant. Vagrant is a command line utility that provides tools to manage virtual machines for different virtualization technologies, for example virtualbox. You can see more details about it here. Navigate to installation if you want to know how to install it. I’ve created a vagrant script to boot a centos 7 + mysql 5.7 machine. You can find it on github. See how you can get it: git clone https://github.com/altmannmarcelo/mysql-vagrant.git cd mysql-vagrant vagrant up vagrant ssh mysql57 mysql If you look the files you have cloned from git, there is a file called bootstrap.sh. This file will configure mysql yum repository, install mysql 5.7 and reset the password . . .

Liked? Help and Share!

Invalid datetime when converting to timestamp

Hi there. Today I faced an issue that from first looking at it I was almost sure I`ve found a bug, but putting a bit more research on it, it makes totally sense. I had a table, that I`ll call here as t1. This table has a field that is datetime. Due to a normalization project that we are working on, we are changing some datetime fields to timestamp. When I tried to convert t1, I got the bellow error: mysql> show create table t1; +——-+——————————————————————————————-+ | Table | Create Table | +——-+——————————————————————————————-+ | t1 | CREATE TABLE `t1` ( `date` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +——-+——————————————————————————————-+ 1 row in set (0,00 sec) mysql> select * from t1; +———————+ | date | +———————+ | 2010-03-28 01:28:20 | . . .

Liked? Help and Share!

Forcing deadlock rollback victim transaction

If you use a storage engine that supports transactions, you probably have faced or heard of deadlock’s. From MySQL Documentation: “Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.” At work, we had an important job that sometimes were failing due to dead lock. I wanted to enhance it, so it will do what the documentation says ( Retry the transaction ). In order to do that, I wanted to have a scenario where I was able to reproduce the deadlock and the victim transaction was the one from the job I was fixing. Create a deadlock is simple, you just need to have 2 sessions that each one holds a lock that the other is waiting for. For . . .

Liked? Help and Share!

MySQL 5.7 auto-generated root password

Hi There. You may have noticed something different when you install a fresh copy of MySQL 5.7 either via yum or using binary source. The root password is auto-generated. Ok and where can I find it ? Centos cat /var/log/mysqld.log | grep “A temporary password is generated for” | awk ‘{print $NF}’ Example: [root@master ~]# cat /var/log/mysqld.log | grep “A temporary password is generated for” | awk ‘{print $NF}’ a3BGf#TY.pBj Binary distribution Once you initialization the datadir mysqld –initialize you will see the bellow log printed on screen: . . . 2016-01-13T21:05:03.070322Z 1 [Note] A temporary password is generated for root@localhost: vL8n>Hs%kr>s . . . You will be required to change the password on the first login. You can do it following step 2 of this article. That’s it. Now . . .

Liked? Help and Share!

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!