Wednesday, October 30, 2013

MySQL in Google Cloud SQL

If you have not yet seen this.. pretty cool...

can "be accessed using MySQL Client, and other administration and reporting tools that work with MySQL databases."

http://gigaom.com/2013/10/30/google-woos-mysql-faithful-to-its-cloud/

"Google Cloud SQL is a MySQL database that lives in Google's cloud. It has all the capabilities and functionality of MySQL, with a few additional features and a few unsupported features as listed below. Google Cloud SQL is easy to use, doesn't require any software installation or maintenance and is ideal for small to medium-sized applications."

https://developers.google.com/cloud-sql/docs/introduction

Friday, October 11, 2013

Percona 5.6 (MariaDB & MySQL) & Debian/Ubuntu

Get up and running easily with Percona 5.6 on Debian.
This example is running Linux Mint 15.



 PERCONA 5.6:
 http://www.percona.com/doc/percona-server/5.5/installation/apt_repo.html
 linuxmint ~ # apt-get install percona-server-server-5.6 percona-server-client-5.6
Reading package lists... Done
Building dependency tree     
Reading state information... Done
The following extra packages will be installed:
  libaio1 libdbd-mysql-perl libdbi-perl libmysqlclient18 libmysqlclient18.1 libnet-daemon-perl libplrpc-perl
  percona-server-common-5.6
Suggested packages:
  tinyca
The following NEW packages will be installed:
  libaio1 libdbd-mysql-perl libdbi-perl libmysqlclient18 libmysqlclient18.1 libnet-daemon-perl libplrpc-perl
  percona-server-client-5.6 percona-server-common-5.6 percona-server-server-5.6
0 upgraded, 10 newly installed, 0 to remove and 3 not upgraded.
Need to get 40.6 MB of archives.
After this operation, 140 MB of additional disk space will be used.
Do you want to continue [Y/n]? Y
linuxmint ~ # mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
linuxmint ~ # mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'" -p
Enter password:
linuxmint ~ # mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'" -p
Enter password:
linuxmint ~ # mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'" -p
Enter password:
linuxmint ~ #
linuxmint ~ # mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 5.6.13-rc61.0 Percona Server (GPL), Release 61.0

Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT VERSION();
+---------------+
| VERSION()     |
+---------------+
| 5.6.13-rc61.0 |
+---------------+
1 row in set (0.04 sec)


MariaDB 10 & 5.5 both resulted with the following
https://downloads.mariadb.org/mariadb/repositories/


linuxmint ~ # apt-get install mariadb-server
Reading package lists... Done
Building dependency tree      
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.

So you need to review
https://mariadb.com/kb/en/installing-mariadb-deb-files/

linuxmint ~ # apt-cache show mysql-common | grep Version
Version: 5.5.33a+maria-1~raring
linuxmint ~ # apt-cache show libmysqlclient18 | grep Version
Version: 5.5.33a+maria-1~raring

linuxmint ~ # apt-get install mariadb-server-5.5 mariadb-client-5.5  libmysqlclient18=5.5.33a+maria-1~raring  mysql-common=5.5.33a+maria-1~raring
Reading package lists... Done
Building dependency tree      
Reading state information... Done

MySQL:
http://dev.mysql.com/downloads/mysql/
It is possible to download the debian packages from the dev.mysql.com/downloads site.



Wednesday, October 9, 2013

Moving from MySQL 5.6 to Percona 5.6

Percona 5.6 was recently released (yesterday at the time of this post). So as simple example what all does it take to move to this "drop in replacement?"

Oct  7 06:47 Percona-Server-56-debuginfo-5.6.13-rel61.0.461.rhel6.x86_64.rpm
Oct  7 06:47 Percona-Server-client-56-5.6.13-rel61.0.461.rhel6.x86_64.rpm
Oct  7 06:47 Percona-Server-devel-56-5.6.13-rel61.0.461.rhel6.x86_64.rpm
Oct  7 06:47 Percona-Server-server-56-5.6.13-rel61.0.461.rhel6.x86_64.rpm
Oct  7 06:47 Percona-Server-shared-56-5.6.13-rel61.0.461.rhel6.x86_64.rpm

Oct  7 06:47 Percona-Server-test-56-5.6.13-rel61.0.461.rhel6.x86_64.rpm
# 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_64


# rpm -Uhv *.rpm

While it would be nice to be able to Move from MySQL to Percona with such a simple command you will get conflicts if you tried it.

Percona-Server-server-56-5.6.13-rel61.0.461.rhel6.x86_64 conflicts with file from package MySQL-server-5.6.13-1.el6.x86_64

So instead you have to do the following.

You should already have backups but just in case create another one.

An extremely simple example below with Xtrabackup
innobackupex ./ 

  • Turn off MySQL 
  • Remove the packages 
  • Install Percona 
  • Start and connect.

# /etc/init.d/mysql stop
Shutting down MySQL..                                      [  OK  ]

# rpm -e MySQL-server MySQL-devel MySQL-client MySQL-shared MySQL-test mysql-workbench-community

# rpm -ihv Percona-Server-*.rpm
Preparing...                ####################################### [100%]
   1:Percona-Server-shared-5################################### [ 17%]
   2:Percona-Server-client-5#################################### [ 33%]
   3:Percona-Server-server-5################################### [ 50%]
Percona Server is distributed with several useful UDF (User Defined Function) from Maatkit.
Run the following commands to create these functions:
mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"
See http://code.google.com/p/maatkit/source/browse/trunk/udf for more details
   4:Percona-Server-test-56 ###################################### [ 67%]
   5:Percona-Server-devel-56###################################### [ 83%]
   6:Percona-Server-56-debug##################################### [100%]
[root@centos64 Percona]# /etc/init.d/m
mdmonitor   memcached   messagebus  mysql       mysqld     
[root@centos64 Percona]# /etc/init.d/mysql start
Starting MySQL (Percona Server)...............             [  OK  ]

# mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"

# mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
# mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

#mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.13-56-log Percona Server (GPL), Release rel61.0, Revision 461

Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.


You are now free to explore Percona 5.6.