Menu Close

Category: MySQL

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!

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!

MySQL Replication with SSL

Hi folks, let’s continue talk about replication, you can see the basics in this other post First of all, lets create the ssl certificates, go to the master server: Create CA certificate: openssl genrsa 2048 > ca-key.pem openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem Create server certificate: openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem Create client certificate: openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem copy ca-cert.pem, client-req.pem, client-cert.pem to slave server Adjust the cnf file: ssl ssl-ca=/etc/mysql/sslcerts/ca-cert.pem ssl-cert=/etc/mysql/sslcerts/server-cert.pem ssl-key=/etc/mysql/sslcerts/server-key.pem Make sure you restart the MySQL service, and check . . .

Liked? Help and Share!

Date format in MySQL

Hi guys, today let’s talk about how to format date in MySQL For this tutorial I’m using mysql server 5.5 Basically you need to know how to use a function called DATE_FORMAT Let’s create a table and insert some rows: CREATE TABLE IF NOT EXISTS `dates` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; INSERT INTO `dates` (`id`, `date`) VALUES (1, '2012-10-17 13:39:55'), (2, '2012-10-09 09:23:11'), (3, '2012-08-24 16:47:07'); OK? then let’s select this values and show it formated SELECT id, DATE_FORMAT( `date` , '%d/%c/%Y %H:%i:%s' ) AS `date` FROM `dates` It’s easy, just remember which the format is always %ONE_LETTER, where this letter is the field which you want to show (see . . .

Liked? Help and Share!

How to change user password on MySQL

Today let’s talk about how to change MySQL user password We can use 2 ways, 1 – mysqladmin, 2 – linguagem SQL This is a step by step video of this post: 1. mysqladmin: The syntax is easy: mysqladmin -u USER -p password NEWPASSWORD Let’s then change the password of ‘marcelo’ user to ‘123’ mysqladmin -u marcelo -p password '123' For this command, we have 3 problems: . You can just change your own user . You need SUPER PRIVILEGES to run this command . If you share you linux user account with other users, this command will appear on historic, to avoid it we can edit ~/.bash_history and delete this lines 2. SQL (the best on my opinion): To change the password, we’ll just run an update on user’s . . .

Liked? Help and Share!

Using the Mysql FullText Index Search

Today let’s talk about a resource very useful on MySQL, the FullText Index and Search This resource is very powerful, today on versions 5.5 is just available to MyISAM engine, but, like we can see on MySQL FullText documentation, it will be available also to InnoDB on MySQL 5.6 Usually when we want to search for a word or expression, we use LIKE ‘%word%’, in case we are looking for more than one word we use LIKE ‘%word1%word2%’, what many people don’t know is for this kind of search is expensive and not optimized to our MySQL, in this cases we solve our problems with FullText Index the syntax is easy, MATHC() … AGAINST (), where MATCH we specified the name(s) of column(s) which we are looking for, yes, we . . .

Liked? Help and Share!