Menu Close

Author: marceloaltmann

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!

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!