Tag Archives: mysql

MySQL—Some Handy Know-How

I recently was talking to someone over IRC who was helping me with a PHP
app that was giving me trouble. The extremely helpful individual asked
me to let him know the value of a certain field in a record on my MySQL
server. I embarrassingly admitted that I’d have to install something
like PHPMyAdmin or Adminer in order to find that information. more>>

Read More

HowTo: Install MySQL Workbench on RHEL 5.x / CentOS 5.x | x86_64

Hello What is MySQL Workbench ? MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. MySQL Workbench is available on Windows, Linux and Mac OS X. Sharing article which will help you […]

Read More

10 reasons to migrate to MariaDB (if still using MySQL)

The original MySQL was created by a Finnish/Swedish company, MySQL AB, founded by David Axmark, Allan Larsson and Michael “Monty” Widenius. The first version of MySQL appeared in 1995. It was initially created for personal usage but in a few years evolved into a enterprise grade database and it became the worlds most popular open source relational database software – and it still is. In January 2008, Sun Microsystems bought MySQL for $1 billion. Soon after, Oracle acquired all of Sun Microsystems after getting approval from the European Commission in late 2009, which initially stopped the transaction due to concerns that such a merger would harm the database markets as MySQL was the main competitor of Oracle’s database product.

MariaDB logoOut of distrust in Oracle stewardship of MySQL, the original developers of MySQL forked it and created MariaDB in 2009. As time passed, MariaDB replaced MySQL in many places and everybody reading this article should consider it too.

At Seravo, we migrated all of our own databases from MySQL to MariaDB in late 2013 and during 2014 we also migrated our customer’s systems to use MariaDB.

We recommend everybody still using MySQL in 2015 to migrate to MariaDB for the following reasons:

1) MariaDB development is more open and vibrant

Unlike many other open source projects Oracle inherited from the Sun acquisition, Oracle does indeed still develop MySQL and to our knowledge they have even hired new competent developers after most of the original developers resigned. The next major release MySQL 5.7 will have significant improvement over MySQL 5.6. However, the commit log of 5.7 shows that all contributors are @oracle.com. Most commit messages reference issue numbers that are only in an internal tracker at Oracle and thus not open for public discussion. There are no new commits in the latest 3 months because Oracle seems to update the public code repository only in big batches post-release. This does not strike as a development effort that would benefit from the public feedback loop and the Linus law of “given enough eyes all bugs are shallow”.

MariaDB on the other hand is developed fully in the open: all development decisions can be reviewed and debated on a public mailing list of in the public bug tracker. Contributing to MariaDB with patches is easy and patch flow is transparent in the fully public and up-to-date code repository. The Github statistics for MySQL 5.7 show 24 contributors while the equivalent figure for MariaDB 10.1 is 44 contributors. But it is not just a question of code contributors – in our experience MariaDB seems more active also in documentation efforts, distribution packaging and other related things that are needed in day-to-day database administration.

Because of the big momentum MySQL has had, there is still a lot of community around it but there is a clear trend that most new activities in the open source world revolve around MariaDB.

As Linux distributions play a major role in software delivery, testing and quality assurance, the fact that the both RHEL 7 and SLES 12 ship with MariaDB instead of MySQL increases the likelihood that MariaDB is going to be better maintained both upstream and downstream in years to come.

2) Quicker and more transparent security releases

Oracle only has a policy to make security releases (and related announcements) every three months for all of their products. MySQL however has a new release every two months. Sometimes this leads situations where security upgrades and security information are not synced. Also the MySQL release notes do not list all the CVE identifiers the releases fix. Many have complained that the actual security announcements are very vague and do not identify the actual issues or the commits that fixed them, which makes it impossible to do backporting and patch management for those administrators that cannot always simply upgrade to the latest Oracle MySQL release.

MariaDB however follows good industry standards by releasing security announcements and upgrades at the same time and handling the pre-secrecy and post-transparency in a proper way. MariaDB release notes also list the CVE identifiers pedantically and they even seem to update the release notes afterwards if new CVE identifiers are created about issues that MariaDB has already released fixes for.

3) More cutting edge features

MySQL 5.7 is looking promising and it has some cool new features like GIS support. However, MariaDB has had much more new features in recent years and they are released earlier, and in most cases those features seem to go through a more extensive review before release. Therefore we at Seravo trust MariaDB to deliver us the best features and least bugs.

For example GIS features were introduced already in the 5.3 series of MariaDB, which makes storing coordinates and querying location data easy. Dynamic column support (MariaDB only) is interesting because it allows for NoSQL type functionality, and thus one single database interface can provide both SQL and “not only SQL” for diverse software project needs.

4) More storage engines

MariaDB in particular excels as the amount of storage engines and other plugins it ships with: Connect and Cassandra storage engines for NoSQL backends or rolling migrations from legacy databases, Spider for sharding, TokuDB with fractal indexes etc. These plugins are available for MySQL as well via 3rd parties, but in MariaDB they are part of the official release, which guarantees that the plugins are well integrated and easy to use.

5) Better performance

MariaDB claims it has a much improved query optimizer and many other performance related improvements. Certain benchmarks show that MariaDB is radically faster than MySQL. Benchmarks don’t however always directly translate to real life situations. For example when we at Seravo migrated from MySQL to MariaDB, we saw moderate 3-5 % performance improvements in our real-life scenarios. Still, when it all adds up, 5% is relevant in particular for web server backends, where every millisecond counts. Faster is always better, even if it is just a bit faster.

6) Galera active-active master clustering

Galera is a new kind of clustering engine which, unlike traditional MySQL master-slave replication, provides master-master replication and thus enables a new kind of scalability architecture for MySQL/MariaDB. Despite that Galera development already started in 2007, it has never been a part of the official Oracle MySQL version while both Percona and MariaDB flavors have shipped a Galera based cluster version for years.

Galera support will be even better in MariaDB 10.1, as it will be included in the main version (and not anymore in a separate cluster version) and enabling Galera clustering is just a matter of activating the correct configuration parameters in any MariaDB server installation.

7) Oracle stewardship is uncertain

Many people have expressed distrust in Oracle’s true motivations and interest in keeping MySQL alive. As explained in point 1, Oracle wasn’t initially allowed to acquire Sun Microsystems, which owned MySQL, due to the EU competition legislation. MySQL was the biggest competitor to Oracle’s original database. The European Commission however approved the deal after Oracle published an official promise to keep MySQL alive and competitive. That document included an expiry date, December 14th 2014, which has now passed. One can only guess what the Oracle upper management has in mind for the future of MySQL.

Some may argue that in recent years, Oracle has already weakened MySQL in subtle ways. Maybe, but in Oracle’s defense, it should be noted that MySQL activities have been much more successful than for example OpenOffice or Hudson, which both very quickly forked into LibreOffice and Jenkins with such a momentum, that the original projects dried up in less than a year.

However, given the choice between Oracle and a true open source project, the decision should not be hard for anybody who understands the value of software freedom and the evolutive benefits that stem from global collaborative development.

8) MariaDB has leapt in popularity

In 2013 there was news about Wikipedia migrating it’s enormous wiki system from MySQL to MariaDB and about Google using MariaDB in their internal systems instead of MySQL. One of the MariaDB Foundation sponsors is Automattic, the company behind WordPress.com. Other notable examples are booking.com and Craigslist. Fedora and OpenSUSE have had MariaDB as the default SQL database option for years. With the releases of Red Hat Enterprise Linux 7 and SUSE Enterprise Linux 12 both these vendors ship MariaDB instead of MySQL and promises to support their MariaDB versions for the lifetime of the major distribution releases, that is up to 13 years.

The last big distribution to get MariaDB was Debian (and based on it, Ubuntu). The “intent to package” bug in Debian was already filed in 2010 but it wasn’t until December 2013 that the bug finally got closed. This was thanks to Seravo staff who took care of packaging MariaDB 5.5 for Debian, from where it also got into Ubuntu 14.04. Later we have also packaged MariaDB 10.0, which will be included in the next Debian and Ubuntu releases in the first half of 2015.

9) Compatible and easy to migrate

MariaDB 5.5 is a complete drop-in-replacement for MySQL 5.5. Migrating to MariaDB is as easy as running apt-get install mariadb-server or the equivalent command on your chosen Linux flavor (which, in 2015, is likely to include MariaDB in the official repositories).

Despite the migration being easy, we still recommend that database admins undertake their own testing and always back up their databases, just to be safe.

10) Migration might become difficult after 2015

In versions MariaDB 10.0 and MySQL 5.6 the forks have already started to diverge somewhat but most likely users can still just upgrade from 5.6 to 10.0 without problems. The compatibility between 5.7 and 10.1 in the future is unknown, so the ideal time to migrate is now while it is still hassle-free. If binary incompatibilities arise in the future, database admins can always still migrate their data by dumping it and importing it in the new database.

With the above in mind, MariaDB is clearly our preferred option.

One of our customers once expressed their interest in migrating from MySQL to MariaDB and wanted us to confirm whether MariaDB is bug-free. Tragically we had to disappoint them with a negative answer. However we did assure them that the most important things are done correctly in MariaDB making it certainly worth migrating to.

Read More

PyMySQL, a pure-Python client library for MySQL

By Vasudev Ram

I came across PyMySQL on github today. Excerpt from the PyMySQL Github page:

[ This package contains a pure-Python MySQL client library. The goal of PyMySQL is to be a drop-in replacement for MySQLdb and work on CPython, PyPy, IronPython and Jython. ]

In other words, PyMySQL is a pure-Python MySQL driver, and as the docs say, it aims to be a drop-in replacement for MySQLdb, which is a Python driver for MySQL, but implemented as a Python C extension. One of the advantages of using a pure-Python library instead of a C extension, is that you do not have to build it from source, and also, potentially, that it may work without any build steps or changes to the code, on various versions of Python, such as CPython, PyPy, etc., as the excerpt above says. Also see this Stack Overflow question:

What actually is pymysql and how it differs from mysqldb?

Here is PyMySQL on PyPI, (the Python Package Index).

The statistics for PyMySQL on PyPI show this:

Downloads (All Versions):
104 downloads in the last day
7168 downloads in the last week
49124 downloads in the last month

So I tried out PyMySQL a bit, on CPython 2.7.8 on Ubuntu Linux.

To install PyMySQL, do:

$ pip install pymysql

Checked whether it was properly installed with:

$ python
>>> import PyMySQL

Oops, didn’t work. I then tried:

>>> import pymysql

That did work.
Here is the schema of the MySQL table (called sales, in a database also called sales) that I used to try PyMySQL:

$ mysql -u root
mysql> use sales;

Database changed
mysql> desc sales;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | YES | | NULL | |
| region | varchar(6) | YES | | NULL | |
| item | varchar(15) | YES | | NULL | |
| unit_price | int(11) | YES | | NULL | |
| quantity | int(11) | YES | | NULL | |
5 rows in set (0.01 sec)


Basically, it is a table to record sales data by region, for items with unit prices and quantities sold.
And here is my Python program to try PyMySQL:

$ cat test_pymysql.py

import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='some_user', passwd='some_password', db='sales')
curs = conn.cursor()
curs.execute('select * from sales')
# Added this to give a proper header for the output, with field names.
field_names = [ item[0] for item in curs.description ]
for field_name in field_names:
print field_name.rjust(12),
for row in curs:
for col in row:
print str(col).rjust(12),

I ran the program and got the correct output:

$ python test_pymysql.py
id region item unit_price quantity
1 North Chair 100 2
2 North Table 200 3
3 South Desk 300 1
3 South Shelf 400 2

I also ran this other SQL query with the GROUP BY clause, to get the region-wise sales:

curs.execute('select region, sum(unit_price * quantity) as value from sales group by region order by 1')

and got the expected output:

$ python test_pymysql.py
region value
North 800
South 1100

The PyMySQL Github page shows how it works: PyMySQL implements the client side of the MySQL Client/Server Protocol. The MySQL database server listens for database requests from clients on port 3306, and the clients (which may be on the same machine or on another machine on the network), send the request to that server at that port. The protocol also supports UNIX domain sockets, so the connect method has a named parameter unix_socket=None.

Related links:

PyPy, a Python interpreter and Just-In-Time compiler

IronPython, Python for .NET

Jython, Python for the JVM


Vasudev Ram – Dancing Bison Enterprises

Signup for email about new products that I create.

Contact Page

var addthis_config = {“data_track_clickback”:true};

Read More

MySQL: Drop all tables from Database using Script / Linux

Hello, Few days back, I got a task to do, Task was Keep the database as it is, So that we do not have to add database users, privileges and everything again and again, Just Drop all the databases tables inside the database. If there are only few tables like 5 or 10 than it […]

Read More

Reset MySQL Root Password Easily

Sometimes a system administrator forgets the MySQL root password! It happens, but what to do when you’re unable to login to your MySQL server? It’s simple!

All it takes is 5 simple steps!

Step 1: Stop the MySQL Server

We do this with:
/etc/init.d/mysql stop or service mysql stop

Step 2: Start the MySQL Server

mysqld_safe –skip-grant-tables &

We do this so MySQL ignores the root password we do not have! If you do not launch this command, resetting the MySQL password will not work!

Step 3: Login to MySQL Server

mysql -u root

Everything should be fine if you get the MySQL prompt!

Step 4: Change the MySQL Password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

NEW-ROOT-PASSWORD is your new root password that you want!

Step 5: Restart MySQL Server

/etc/init.d/mysql stop
/etc/init.d/mysql start

Now, you should have your new MySQL root password! I recommend you write it down this time!