Sorry, this entry is only available in Brazilian Portuguese. Liked? Help and Share!
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 . . .
On my last post I gave a gasp about some key concepts of ProxySQL. Today I will demonstrate how we can import all users from MySQL to ProxySQL To accomplish this, we will make use of,proxysql-admin a tool developed by Percona to help you manage and install ProxySQL. If you are using ProxySQL provided by Percona, this tool is already installed, otherwise, you can download it directly from Github. We will use the –syncusers option, which will connect to the desired MySQL server, read all users from mysql.user table and import or sync them to ProxySQL: proxysql-admin –cluster-username=[mysql-user] –cluster-password=[mysql-password] –cluster-port=[mysql-port] –cluster-hostname=[mysql-host] –syncusers This tool was originally designed to enhance the compatibility between ProxySQL and Percona XtraDB Cluster, so, many terminology mentions cluster, however, the functionality demonstrate on this post is fully compatible with Master-Slave. Here . . .
In this 101 article, I will show how to drop/remove a column from a table in MySQL. In this article I will use an example table: CREATE TABLE tb( c1 INT PRIMARY KEY, c2 char(1), c3 varchar(2) ) ENGINE=InnoDB; To remove a column we will make use of ALTER TABLE command: ALTER TABLE tb DROP COLUMN c2; The command allows you to remove multiple columns at once: ALTER TABLE tb DROP COLUMN c2, DROP COLUMN c3; If you are running MySQL 5.6 onwards, you can make this operation online, allowing other sessions to read and write to your table while the operation is been performed: ALTER TABLE tb DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE; Reference: https://dev.mysql.com/doc/refman/5.7/en/alter-table.html https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html Liked? Help and Share!
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 . . .
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 . . .
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 . . .
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 . . .
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 | . . .
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 . . .