Mysql Database Performance tuning

Mysql provides a configuration file located in /etc/my.cnf. From here you can set all of the memory, table, and connection limits as well as a host of other options.
You can get the default buffer sizes used by the mysqld server with this command:

shell> mysqld –help

This command produces a list of all mysqld options and configurable variables. The output includes the default values and looks something like this:

Some problems with mysql & mariadb

MySQL max_open_files more than 1024

Edit /etc/security/limits.conf and add the following lines mysql soft nofile 65535 mysql hard nofile 65535

 Edit file /usr/lib/systemd/system/mysqld.service ( /usr/lib/systemd/system/mariadb.service  ) and /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf 

Add at end:

LimitNOFILE=65535
LimitNPROC=65535

Install MySQL Enterprise Monitor

1 . Install MySQL Monitor center

[root@Kilo-MySQL-Monitor ~]# unzip V790192-01-MySQL_Enterprise_Monitor_Service_Manager_3.3.1.zip

Archive:  V790192-01-MySQL_Enterprise_Monitor_Service_Manager_3.3.1.zip

 inflating: mysqlmonitor-3.3.1.1112-linux-x86_64-installer.bin  

 inflating: mysqlmonitor-3.3.1.1112-linux-x86_64-update-installer.bin  

 inflating: README_en.txt           

 inflating: READ_ME_ja.txt         

[root@Kilo-MySQL-Monitor ~]# chmod +x mysqlmonitor-3.3.1.1112-linux-x86_64-installer.bin

[root@Kilo-MySQL-Monitor ~]# ./mysqlmonitor-3.3.1.1112-linux-x86_64-installer.bin --mode text

Language Selection

MySQL Enterprise Backup & Restore - Full and Incrimental

1. Create bash script 

#!/bin/bash

#creates and maintains MySQL Enterprise Backup (MEB) backups

#prints usage
usage()
{
    echo "$VERSION"
    echo "
Usage: `basename $0` [command] [MEB options]

Commands:
    full                                make full backup
    incremental                         make incremental backup
    incremental-with-redo-log-only      make incremental backup with redo log only
    verify-to-tape                      verify backup images, then copy to tape
    prepare                             prepare backups
    remove-old                          remove old backups
    "
}

Full Backup using MySQL Enterprise Backup

Install the software

Before we can configure backups and the like, you’ll need to install the MySQL Enterprise Backup software:

$ tar xvzf meb-3.9.0-linux2.6-x86-64bit.tar.gz 
meb-3.9.0-linux2.6-x86-64bit/
meb-3.9.0-linux2.6-x86-64bit/bin/
meb-3.9.0-linux2.6-x86-64bit/bin/mysqlbackup
meb-3.9.0-linux2.6-x86-64bit/README.txt
meb-3.9.0-linux2.6-x86-64bit/LICENSE.mysql
meb-3.9.0-linux2.6-x86-64bit/manual.html
meb-3.9.0-linux2.6-x86-64bit/mysql-html.css

I then placed the mysqlbackup binary in my MySQL “bin” directory :

$ cp meb-3.9.0-linux2.6-x86-64bit/bin/mysqlbackup /usr/local/mysql/bin/
$ which mysqlbackup 
/usr/local/mysql/bin/mysqlbackup

Now we’ve installed the software, we can go on and prepare our database for backup.

How to Back Up MySQL Server using MySQL Enterprise Backup (MEB)

 In order to be able to use MySQL Enterprise Backup to perform a backup of a MySQL Server instance, the following MySQL options must be specified in the configuration file of the server instance, under the [mysqld] section (substitute ... with any valid values):

datadir=...
innodb_data_home_dir=...
innodb_data_file_path=...
innodb_log_group_home_dir=...
innodb_log_files_in_group=...
innodb_log_file_size=...

Alternately you can use a new configuration file, that contains just the options above, and pass it to MySQL Enterprise Backup.

My.cnf examples

[root@kiloccnp ~]# cat /etc/my.cnf
# On Linux you can copy this file to /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
# (@localstatedir@ for this installation) or to
# ~/.my.cnf to set user-specific options.

[mysqld]
datadir=/usr/local/mysql/data
#tmpdir=/var/log/mysqld/
tmpdir=/db/mytmp
log-error=/var/log/mysqld/mysqld.err
# as of MySQL 5.1.29, log-slow-queries is deprecated, use the 2 options below
#log-slow-queries=/var/log/mysqld/mysqld-slow.log
slow-query-log=1
slow-query-log-file=/var/log/mysqld/mysqld-slow.log
performance_schema_consumer_events_statements_history_long = ON

How to Automate Backups on Linux/UNIX Using MySQL Enterprise Backup (MEB)

Goal

Backups should be made regularly.  The easiest way to do so on Linux/UNIX is to write a cron job. In this article MEB users will find two crontab templates: one for weekly full backups and another for daily incremental backups.

Solution

Although MEB allows for the creation of incremental backups, it is still best practice to run full backups periodically.   Below you will find a schedule which makes weekly full backups and daily incremental backups.

Weekly full backup can be done using following crontab command:

How To Setup MariaDB Galera Cluster

MariaDB is a relational database management system (RDBMS) and  MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB. It is available on Linux only, and only supports the XtraDB/InnoDB storage engines. This article explains how to setup MariaDB Galera Cluster 10.0 with 2 nodes running on CentOS 6.5 x86_64 resulting in a HA (high-availability) database cluster.

How to Deploy and Configure MaxScale for SQL Load Balancing with Read-Write Split

There are two models of load balancing: transport and application layer. HAProxy is a great TCP load balancer, but it’s lack of SQL awareness effectively limits its ability to address certain scaling issues in distributed database environments. In the open source world, there’s been a few SQL-aware load balancers, namely MySQL Proxy, ProxySQL and MaxScale, but they all seemed to be in beta status and unfit for production use. So we were pretty excited when the MariaDB team released a GA version of MaxScale earlier this year. In this blog, we’ll have a look at MaxScale and see how it compares with HAProxy.