Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts

Tuesday 29 September 2015

Backup MariaDB using xtrabackup on CentOS7

Percona XtraBackup is an open-source hot backup utility for MariaDB/MySQL-based servers that doesn’t lock your database during the backup. It can back up data from InnoDB, XtraDB, and MyISAM tables on MariaDB/MySQL servers.
This guide looks into how to setup xtrabackup backup system for MariaDB on CentOS7, and how to full backup an MariaDB, and then restore the DB.

First thing, ensure the minimum setup for database config file:
[root@centos7 ~]# vi /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# This group is read by the server
#
[mysqld]
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[root@centos7 ~]# vi /etc/my.cnf.d/server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
Start MariaDB/Mysql
[root@centos7 ~]# service mysql start
Install percona repository, and install xtrabackup
[root@centos7 ~]# yum install https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-3.noarch.rpm
[root@centos7 ~]# yum list|grep percona
[root@centos7 ~]# yum install percona-xtrabackup
We will create a backup directory
[root@centos7 ~]# mkdir /root/backup
Installation done. To proceed with Full backup,we need to issue the innobackupex, with the DB admin user and password, and also the backup directory.
[root@centos7 ~]# innobackupex --user=root --password=PASSWORD /root/backup
To get the backup information for a particular backup, we can check the xtrabackup_checkpoints file.
[root@centos7 backup]# cat /root/backup/2015-09-29_02-01-17/xtrabackup_checkpoints
We have done the Full backup. To restore a Full backup, we have to stop the MariaDB/Mysql server first.
[root@centos7 ~]# service mysql stop
Move the current mysql data directory to somewhere.
[root@centos7 ~]# rm -fr /var/lib/mysql_old
[root@centos7 ~]# mv /var/lib/mysql/ /var/lib/mysql_old
Issue the following command to restore.
[root@centos7 ~]# innobackupex --copy-back /root/backup/2015-09-29_02-01-17/
Change back the ownership of the mysql data directory.
[root@centos7 ~]# chown mysql:mysql /var/lib/mysql -R
And lastly, start the MariaDB/Mysql service.
[root@centos7 ~]# service mysql start

Wednesday 23 September 2015

Setup MariaDB Galera Cluster 10.0 On CentOS7

This guide looks into how to setup MariaDB Galera Cluster on CentOS7. MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB. It is is an easy-to-use, high-availability solution, which provides high system uptime, no data loss and scalability for future growth. It only supports the XtraDB/InnoDB storage engines

Features:
Synchronous replication
Active-active multi-master topology
Read and write to any cluster node
Automatic membership control, failed nodes drop from the cluster
Automatic node joining
True parallel replication, on row level
Direct client connections, native MySQL look & feel

Benefits:
The above features yield several benefits for a DBMS clustering solution, including:
No slave lag
No lost transactions
Both read and write scalability
Smaller client latencies

For this setup, we have 2 nodes.(ie 192.168.1.11,192.168.1.12.) MariaDB will first setup in (.11) and start in bootstrap mode. After (.11) has started, (.12) will setup next. Once the MariaDB service on (.12) started, Galera process will sync database data and transactions from (.11) to (.12), and after in sync, MariaDB in (.12) will turn on. Both servers will be multi masters and in sync constantly.

To begin with the setup, for simplicity, we will switch off selinux and firewall.

[root@centos7-11 ~]#systemctl stop firewalld
[root@centos7-11 ~]#systemctl disable firewalld
[root@centos7-11 ~]#vi /etc/selinux/config
disabled
[root@centos7-11 ~]#reboot
We will add a repository from MariaDB.

[root@centos7-11 ~]# vi /etc/yum.repos.d/mariadb.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
Install the package using YUM.
[root@centos7-11 ~]# yum clean all
[root@centos7-11 ~]# yum install MariaDB-Galera-server
Setup MariaDB.(At this point,the service is still call mysql.)
[root@centos7-11 ~]# service mysql start
[root@centos7-11 ~]# mysql_secure_installation
Stop the mysql service to adjust the MariaDB config file.
[root@centos7-11 ~]# service mysql stop
[root@centos7-11 ~]# vi /etc/my.cnf
!includedir /etc/my.cnf.d
Add replication parameters to server.cnf
[root@centos7-11 ~]# vi /etc/my.cnf.d/server.cnf 
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=192.168.1.11
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
We can start the MariaDB on first node.(.11)
[root@centos7-11 ~]# service mysql start
By now, this node has setup to be the first master database. We shell proceed to setup the second master database.

Similarly, we will switch off selinux and firewall.

[root@centos7-12 ~]#systemctl stop firewalld
[root@centos7-12 ~]systemctl disable firewalld
[root@centos7-12 ~]vi /etc/selinux/config
disabled
[root@centos7-12 ~]reboot
We will add a repository from MariaDB.

[root@centos7-12 ~]# vi /etc/yum.repos.d/mariadb.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
Install the package using YUM.
[root@centos7-12 ~]# yum clean all
[root@centos7-12 ~]# yum install MariaDB-Galera-server
At this point, we just need to configure the MariaDB config files
[root@centos7-12 ~]# vi /etc/my.cnf
!includedir /etc/my.cnf.d
Add replication parameters to server.cnf
[root@centos7-12 ~]# vi /etc/my.cnf.d/server.cnf
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.11
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=192.168.1.12

#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
We can start the MariaDB on second node.(.12)
[root@centos7-12 ~]# service mysql start
And thats it, 2 node multi masters MariaDB clusters.

Friday 24 October 2014

How To Install MySQL On CENTOS 7

Below instructions are steps to install mysql server on CentOS 7.

The default replacement for mysql server is MariaDB. MariaDB is a community-developed fork of the MySQL relational database management system. For whatever reasons you might like to install the previous MySQL server, this guide walk you through the process of the installation. The steps:

Setup MySQL repository
[root@localhost ~]# sudo rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
Install Server:
[root@localhost ~]# yum install mysql-server
Start Server:
[root@localhost ~]# systemctl start mysqld
Enable Server on boot:
[root@localhost ~]# systemctl enable mysqld
Change root password:
[root@localhost ~]# mysqladmin -u root password

Wednesday 10 April 2013

How to setup MySQL Query Cache

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.

By default query cache size is 0, which is deactivated. To enable it, you can set the query_cache_size to 1000000.

mysql> SET GLOBAL query_cache_size = 1000000;
Query OK, 0 rows affected (0.04 sec) 

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+--------+ 
|Variable_name     | Value  | 
+------------------+--------+ 
| query_cache_size | 999424 | 
+------------------+--------+ 
1 row in set (0.00 sec)

Monday 8 April 2013

How to use memcache with php


Previously I posted an article on how to install Memcache. This time I will show how to use Memcache with PHP. This program will start off with a SQL query statement, hashing the statement and check if the data is stored in memcache.If found, the data will be retrieve from memcache. If it is not found in memcache, then it will query from MySQL database.


<?php
require('connect_db.php'); 
$memcache_host="127.0.0.1";
$memcache_port="11211";
$memcache = new Memcache;
$memcache->connect($memcache_host,$memcache_port) or die ("Cannot connect");

$version = $memcache->getVersion();
echo "Version: ".$version;

$query="select * from exampleDB limit 1;";
$key = md5($query); 
$get_result = $memcache->get($key);

if ($get_result) {   
    print_r($get_result);
    echo "Data retrieved from memcache";
}
else {    
    $result = mysql_query($query);
    if (!$result) {
        die('Invalid query: ' . mysql_error()) 
      }
    $row = mysql_fetch_array($result);
    print_r($row);  
    $memcache->set($key, $row, MEMCACHE_COMPRESSED, 10); 
    echo "Data retrieved from database";
}  
?>

Tuesday 2 April 2013

Install the latest mysql server on CENTOS6

How to install the latest mysql server on CENTOS6? CENTOS6 shipped with mysql server 5.1.67 version. To install latest mysql server 5.6.10, you can download the rpm from mysql official download site, and install the rpm individually.

Firstly, navigate to "http://www.mysql.com/downloads/mysql/#downloads" , choose "Oracle and Red Hat Linux 6". Download the rpms as shown below:

wget http://www.mysql.com/get/Downloads/MySQL-5.6/MySQL-client-5.6.10-1.el6.x86_64.rpm/from/http://cdn.mysql.com/
wget http://www.mysql.com/get/Downloads/MySQL-5.6/MySQL-shared-5.6.10-1.el6.x86_64.rpm/from/http://cdn.mysql.com/
wget http://www.mysql.com/get/Downloads/MySQL-5.6/MySQL-server-5.6.10-1.el6.x86_64.rpm/from/http://cdn.mysql.com/
wget http://www.mysql.com/get/Downloads/MySQL-5.6/MySQL-shared-compat-5.6.10-1.el6.x86_64.rpm/from/http://cdn.mysql.com/


Then use md5sum, to check md5 hash on the download site:

md5sum MySQL-shared-compat-5.6.10-1.el6.x86_64.rpm
md5sum MySQL-server-5.6.10-1.el6.x86_64.rpm
md5sum MySQL-shared-5.6.10-1.el6.x86_64.rpm
md5sum MySQL-client-5.6.10-1.el6.x86_64.rpm

If the md5 hash is ok, we can install the rpm in following sequence:

rpm -Uvh MySQL-shared-compat-5.6.10-1.el6.x86_64.rpm
rpm -ivh MySQL-shared-5.6.10-1.el6.x86_64.rpm
rpm -ivh MySQL-server-5.6.10-1.el6.x86_64.rpm
rpm -ivh MySQL-client-5.6.10-1.el6.x86_64.rpm

To turn mysql on when boot:
chkconfig mysql on


To start the mysql service:
service mysql start


To obtain the temporary password:
cat /root/.mysql_secret


To change the mysql root password:
mysqladmin -uroot -p password


Login to the newly installed server using its client:
mysql -uroot -p
These are the steps to install latest mysql on CENTOS. Cheers!

Benchmark PHP mysql vs PHP mysqli

In PHP coding, many of my php scripts are still using php mysql extension. Before taking the time to migrate to php mysqli extension, I have decided to run a simple benchmark between the two. I have written 2 scripts and time it. The first script is "test_mysql.php", the second script is "test_mysqli.php". The result is, the first script took an average of 1.01 sec, the second script took 0.86 sec. "test_mysqli.php" is 17.87% faster than "test_mysql.php".

The scripts were tested on Linux 3.8.4, PHP 5.4.12, MySQL 5.5.30.

First script, test_mysql.php

<?php
//-----Start Timer-------------------------------------------------//       
    $time = microtime();
    $time = explode(" ", $time);
    $time = $time[1] + $time[0];
    $top = $time;
//-----Start Timer-------------------------------------------------//   
for($j=0;$j<1000;$j++)
{  
    $link = mysql_connect($host,$user,$password) or die('Error in Server information');   
    mysql_select_db($db,$link) or die('Can not Select Databasse');   
    $query="select * from com_list where field1='value1';";
    $res = mysql_query($query);  //1 is ok
    if (!$res) {
        die('Invalid query: ' . mysql_error()); }

    while($row = mysql_fetch_array($res)){
    $i++;
    }
    mysql_free_result($res);
    mysql_close($link);
}
    echo "Total lines :".$i;
//-----End Timer-------------------------------------------------//   
    $time = microtime();
    $time = explode(" ", $time);
    $time = $time[1] + $time[0];
    $bottom = $time;
//-----End Timer-------------------------------------------------//
    $loadtime = ($bottom-$top);
    echo ("<br> <br>This page generated in $loadtime seconds");  
?>
Second script, test_mysqli.php
<?php
//-----Start Timer-------------------------------------------------//       
    $time = microtime();
    $time = explode(" ", $time);
    $time = $time[1] + $time[0];
    $top = $time;
//-----Start Timer-------------------------------------------------//       
for($j=0;$j<1000;$j++)
{  
    $link = mysqli_connect($host,$user,$password,$db) or die('Error in Server information');
    $query="select * from com_list where field1='value1';";
    $res = mysqli_query($link,$query);  //1 is ok
    if (!$res) {
        die('Invalid query: ' . mysql_error()); }
    while($row = mysqli_fetch_array($res)){        
    $i++;
    }   
    mysqli_free_result($res);
    mysqli_close($link);
}
    echo "Total lines :".$i;
//-----End Timer-------------------------------------------------//       
    $time = microtime();
    $time = explode(" ", $time);
    $time = $time[1] + $time[0];
    $bottom = $time;
//-----End Timer-------------------------------------------------//           
    $loadtime = ($bottom-$top);
    echo ("<br> <br>This page generated in $loadtime seconds");  
?>



Tuesday 26 March 2013

Upgrade mysql (5.1) to mysql55 on CENTOS6

To upgrade from mysql (default ver 5.1) to mysql55 (IUS ver 5.5):

Replace residual mysql component to mysql55:
[root@localhost ~]# yum replace mysql-libs --replace-with mysql55


Install server and client:
[root@localhost ~]# yum install mysql55-server mysql55


Start server upon reboot:
[root@localhost ~]# chkconfig mysqld on 


Start server service:
[root@localhost ~]# service mysqld start


Change root password:
[root@localhost ~]# mysqladmin -u root password

search iomeweekly