Wednesday, August 14, 2013

MySQL Workbench 6.0 on CentOS 6.4

I am not really a GUI DBA.  They have their place and can do nice things but I just do not use them much.  You can tell based on what I have upgraded. 

# cat /etc/*-release
CentOS release 6.4 (Final)

# rpm -qa | grep MySQL
MySQL-server-5.6.13-1.el6.x86_64
MySQL-devel-5.6.13-1.el6.x86_64
MySQL-shared-compat-5.6.13-1.el6.x86_64
perl-DBD-MySQL-4.013-3.el6.x86_64
MySQL-python-1.2.3-0.3.c1.1.el6.x86_64
MySQL-client-5.6.13-1.el6.x86_64
MySQL-shared-5.6.13-1.el6.x86_64
MySQL-test-5.6.13-1.el6.x86_6

# rpm -qa | grep mysql
php-mysql-5.3.3-23.el6_4.x86_64
mysql-workbench-gpl-5.2.47-1el6.x86_64


The mysql-workbench does happen to be the latest version in the distro but the latest version is mysql-workbench-community-6.0.6-1.el6 . So I was curious after I saw the latest MySQL workbench blog post : http://mysqlworkbench.org/2013/08/mysql-workbench-6-0-6-ga-released/

So time to upgrade.... 
First, mysql-workbench-community is dependent on libtinyxml so you have to make sure that is installed or gets installed when you upgrade/install.

Others have dealt with this before :
So after I looked and found the tinyxml package...

 yum install tinyxml-2.6.1-1.el6.x86_64.rpm mysql-workbench-community-6.0.6-1.el6.x86_64.rpm

Installed:
  mysql-workbench-community.x86_64 0:6.0.6-1.el6                             tinyxml.x86_64 0:2.6.1-1.el6                           

Replaced:
  mysql-workbench-gpl.x86_64 0:5.2.47-1el6                                                                                          

Complete!


Ok ... so it starts up and has a new look.
Being a shell geek I go to the shell first. Of course it complains that mysql-utilities are not installed. It can take you to the download page but might as well just go ahead of time because you might need to install the Connector-Python as well. So basically... go get these packages and install as well...
mysql-connector-python-1.0.12-1.el6.noarch.rpm
mysql-utilities-1.3.4-1.el6.noarch.rpm
# yum install mysql-*.rpm

So... all of that and I have a GUI that opens a terminal window :)

The following MySQL Utilities are available:
Launching console ...

Utility           Description                                             
----------------  ---------------------------------------------------------
mysqlauditadmin   audit log maintenance utility                           
mysqlauditgrep    audit log search utility                                
mysqldbcompare    compare databases for consistency                       
mysqldbcopy       copy databases from one server to another               
mysqldbexport     export metadata and data from databases                 
mysqldbimport     import metadata and data from files                     
mysqldiff         compare object definitions among objects where the      
                  difference is how db1.obj1 differs from db2.obj2        
mysqldiskusage    show disk usage for databases                           
mysqlfailover     automatic replication health monitoring and failover    
mysqlfrm          show CREATE TABLE from .frm files                       
mysqlindexcheck   check for duplicate or redundant indexes                
mysqlmetagrep     search metadata                                         
mysqlprocgrep     search process information                              
mysqlreplicate    establish replication with a master                     
mysqlrpladmin     administration utility for MySQL replication            
mysqlrplcheck     check replication                                       
mysqlrplshow      show slaves attached to a master                        
mysqlserverclone  start another instance of a running server              
mysqlserverinfo   show server information                                 
mysqluserclone    clone a MySQL user account to one or more new users
 


So moving beyond that I started to look around.
The Administration has a nice new look with some decent graph information easily available for you.  

 The sakila model does look very nice though. The modeller is a handy feature in the GUI I do admit.

Well that is about all I did with the new MySQL workbench but I am upgraded now :) 



Friday, August 9, 2013

Create a Slave ( secondary) server with Percona Xtrabackup



So first you might just save yourself some time and read the Percona example for this:
http://www.percona.com/doc/percona-xtrabackup/2.1/howtos/setting_up_replication.html

But just in case here is an example based on a real situation.

PRIMARY SERVER

# innobackupex /tmp/  <---- this is whatever directory you want to store the backup in. This is a very basic no fluff hot backup.

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
.........
130809 14:40:11  innobackupex: Connection to database server closed
130809 14:40:11  innobackupex: completed OK!

Make sure you see the xtrabackup_binlog_info file. If you do not you will not easily have the position and log information. You will have to dig into the binlogs based on time and etc. Which is more work than needed.

 innobackupex --apply-log /tmp/<Timestamp Directory Here>

Now up to you. You can rsync the directory to the slave or tar[gzip] then scp to the slave. Regardless of method to move to slave, you have a hotbackup created and ready to go.


SECONDARY SERVER

# /etc/init.d/mysql stop
mv /var/lib/mysql  /var/lib/mysql_ORIG

However you moved the file from the master to the slave, put the contents into the datadir folder, assumed for example: /var/lib/mysql .

# chown -R mysql:mysql mysql
 /etc/init.d/mysql start
Starting MySQL...                                          [  OK  ]

Now in your slave MySQL server, you can set the replication user information easily.

CHANGE MASTER TO
MASTER_HOST='<MASTER_HOST>',
MASTER_USER='<MASTER_USER>',
MASTER_PASSWORD='<MASTER_PASSWORD>',
MASTER_CONNECT_RETRY = 10 ;

Get the log and position from the xtrabackup file.

# more xtrabackup_binlog_info
<BinLog info> <POSITION INFO>

CHANGE MASTER TO MASTER_LOG_FILE='<BinLog info>', MASTER_LOG_POS=<POSITION INFO>;

Start slave;


That is it in a nutshell. For more information review the Percona url given at the start.