MySQL Database Alternative

MySQL Database Alternative

Category : How-to

Get Social!

mysql-logoThere are many alternatives to MySQL that tick some of the boxes you may require. There are plenty of free, open source databases such as PostgreSQL, MogoDB, CouchDB and Apache Derby however many of these databases have a different feature set or are a completely different type of database to the standard MySQL relational data model. Another question is what if I already have MySQL set up in my environment and would like to change? Many people are concerned that the (fairly) recent takeover of Sun Microsystems by Oracle could spell trouble for MySQL.

mariadb-logoMariaDB was created by some of the original developers of MySQL and was created by forking the MySQL code base. It[‘s maintained by the MariaDB Foundation who ensures the free availability of the database software. There are currently 2 main versions of MariaDB available as stable distributions:

  • Version 5.x – which is a drop in replacement for any version 5.x of MSSQL and promises to support all MySQL version 5.x functionality. With this version of MariaDB you can switch your applications to point at it and they will just work. All the connectors and client binaries are compatible and for most scenarios will work right out of the box.
  • Version 10.x – this is the new branch of MariaDB which drifts away from the original MySQL specification and introduces new features. Currently, most of the basics will work, but it no longer guarantees backwards compatibility with MySQL and can no longer be used as a drop in replacement. Eventually this had to happen to enable the MariaDB developers to implement the features they needed to and is the branch of MariaDB that’s being most actively developed.

As you can see, version 5.x is a version you can use straight away if you’re a MySQL 5.x user if you so choose. Version 10.x however, will take a little more time to implement as you’ll need to change the connectors in your applications and ensure that all the features you require from the database are the same. You may also wish to use some of Version 10.x’s features that would otherwise be unavailable.

Take a look at installing MariaDB on Ubuntu or installing a quick and easy database cluster.

 


Installing MariaDB on Ubuntu

Category : How-to

Get Social!

mariadb-logoMariaDB is termed a drop in replacement for MySQL – that means that you can deploy MariaDB without changing all of your client applications as MariaDB is compatible with most MySQL features and commands.

MariaDB was forked from MySQL when Oracle took over Sun Microsystems in 2010 and was born of the fear that Oracle would not adhere to the development ethos that was used by Sun. I discuss this in more detail in my blog post on MySQL alternative. There are a few gotchas with the new versioning system used by MariaDB and I’d recommend reading the blog post to familiarise yourself.

MariaDB has not yet made it into Ubuntu’s main repositories but is available as an add-in repository from MariaDB directly.

Installing MariaDB on Ubuntu couldn’t be easier – follow one of the below instructions for your version of Ubuntu.

Install MariaDB 10 on Ubuntu 14.04

Use the below commands to add the MariaDB repository to your Ubuntu 14.04 installation.

apt-get install software-properties-common
apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
add-apt-repository 'deb http://mirror.stshosting.co.uk/mariadb/repo/10.0/ubuntu trusty main'

Run the following commands to install MariaDB.

apt-get update
apt-get install mariadb-server

Install MariaDB 10 on Ubuntu 12.04

Use the below commands to add the MariaDB repository to your Ubuntu 14.04 installation.

apt-get install python-software-properties
apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
add-apt-repository 'deb http://mirror.stshosting.co.uk/mariadb/repo/10.0/ubuntu precise main'

Run the following commands to install MariaDB.

apt-get update
apt-get install mariadb-server

Install a different version of MariaDB or a use a different target operating system

MariaDB supports all common Linux distributions and they maintain a repository for each. You can see the full list of distro repositories on their repository configuration tool.


MySQL Circular Replication

Category : How-to

Get Social!

mysql-logoI spoke about master – slave replication in my previous blog post and how to set up one way replication. The problem with one way replication is that any changes that are made to the slave would not be replicated to the master and would likely end up causing inconsistencies in future queries. This means that you can only apply updates on your master server.

If only two servers are involved then we can set up each server as both a master and a slave. This allows for two way replication – if changes are made on server 1 they are replicated on server 2, and vice versa. If more than two servers are needed for replication then it can get a little more complicated. Using the circular replication method we set up each server as a master, and as a slave to another master until all the masters have slaves. This results in a circular flow of information as each change is replicated to the next server in the chain. The below diagram indicates how replication changes would travel.

mysql-circular-replication

In the above illustration there are 8 MySQL Server nodes that all replicate changes they are sent directly and changes from the MySQL Server node before it are processed and sent to the next MySQL Server node along. A change made on any one of the MySQL Server nodes would be replicated to the next server along until every server in the chain has processed the update.

There are some drawbacks to to this type of configuration:

  • When many nodes are added it can take a long time for a change to one server to make it round the chain to update all other servers. For example, if a change is made on Server 2, it has to go to Server 3, then server 4, 5, 6 and 7 before Server 8 is notified of the change.
  • Conflicts can occur in MySQL Server circular replication. The way MySQL processes replication updates and direct SQL statements from clients is in parallel. MySQL does not guarantee consistency across all the databases in the chain. This is because a replication request may act on a column at the same time as a direct request from a client. Due to the time it takes to receive a replication update and process it, a direct request from a client could have updated the same row, even though, as far as the overall sequence of events is concerned, the initial event that triggered the replication from another server could have happened first.

Conflicts will have to be considered and affect the way a group of MySQL Servers set up in this way can be used. For example, you may choose to only update each table on one node at a time – Server 1 for the sales table, Server 2 for the payments table and so on. This will have to be built into your process for writing to these MySQL tables.

Setting up MySQL Server circular replication is straightforward – it’s essentially setting up master-slave replication multiple times for how many nodes have.

I’m not going to write out all of the commands for setting up circular replication because all the steps are written down in my blog post about master slave replication. Follow these steps to set up Server 1 as your master and then Server 2 as your slave. Then repeat the steps with Server 2 as your master and Server 3 as your slave. Continue doing this until you make your last server a master and then Server 1 as a slave to complete the circle.

And that’s all there is to it! Just remember to make sure you plan your workloads to protect your data from inconsistency issues.


MySQL Database Replication

Category : How-to

Get Social!

mysql-logoThe MySQL server can replicate a database over TCP to another instance of MySQL to provide a near real time backup for data redundancy. This process is not to be confused with MySQL working in a cluster to share workload and provide high availability. I’ll cover clustering in a later blog post.

MySQL uses a master and slave scenario where the master is where the changes are detected and the slave is where the changes are sent to. This means that changes are only replicated one-way and any changes on the slave will not be replicated on the master. To get round this, you can set up each server as a slave and as a master so that changes are sent both ways. This is called circular replication.

To set up one way replication this post will assume that you have MySQL installed on two servers, one the master and one the slave. If you have not installed MySQL server you can install it on Debian/ Ubuntu with apt-get.

apt-get install mysql-server

The below instructions must be executed on the correct server, either master or slave. Be careful that you are executing the right command on the right server!

MySQL Replication Mode

Before we get into setting up our replication server we need to consider the replication mode to use. There are two types of replication mode, or a third if you include the combination of the two.

  • Statement based replication is where each statement that is sent to the master is also sent to the slave. This means that, in most scenarios, the data will be the same on each server as the same statements have been executed on each server.
  • Row based replication is where each change to a row in a table on the master server is written to a log and sent to the slave. The slave server then updates the required rows with the literal data.
  • Mixed is a combination of the two – the MySQL server chooses which mode to use based on the task being performed.

Configure MySQL Replication

Replication, in this example, is done at a database level and changes will be replicated in one direction from master to slave. This means that any changes made to the slave will not appear on the master.

Master

The below changes should be made on the master MySQL server. A slightly different set of steps will be detailed below for the slave.

Open the my.cnf MySQL configuration file and make the following changes:

vi /etc/my.cnf

Find the bind-address attribute and change it to the IP address of the master server.  You can find your IP address by using ifconfig if you are not sure what it is.

bind-address = 10.1.1.100

Find or add the server-id attribute and make  sure it’s uncommented. You need to assign your master server an ID, let’s use 1 for our master server.

server-id = 1

Find or add the log_bin attribute and make sure it’s uncommented. This is the location where your master server will write all the changes that occur on the database.

log_bin = /var/log/mysql/mysql-bin.log

Add an entry to specify which MySQL database should be included for replication.

binlog_do_db = replication_database

You can add as many databases as you like by repeating the binlog_do_db attribute. For example:

binlog_do_db = replication_database1
binlog_do_db = replication_database2

The next step is to create a user which has the appropriate permission to use the MySQL replication features. Log in to MySQL with the below command, followed by your root MySQL user password.

mysql -u root -p

Create a new user which will be used to connect to the master instance from the slave to transfer the replication data.

GRANT REPLICATION SLAVE ON *.* TO 'mysql_rep'@'%' IDENTIFIED BY '[PASSWORD]';

The above example of granting privileges and creating a user are the easiest to get working but are the least secure. You may need to change this to meet your security requirements. You’ll also need to replace [PASSWORD] with the password you would like to use for the mysql_rep user.

Now lets create the database that will be replicated to our slave server. It’s important that after creating the database that nothing is changed until replication has been completely set up. If you already have a database then you will need to export the database, with all it’s data, and import it into the slave before completing the replication setup. This is because both databases must be in the same state for replication to keep everything in sync.

create database replication_database;

Type quit to exit the MySQL client and restart the MySQL server.

service mysql restart

At this point, we can check that the server is set up to write changes to the log file. Log back into MySQL Server Client and issue the below SHOW command.

mysql -u root -p

SHOW MASTER STATUS;

The output should be similar to the below, and indicates that the master is configured to log the changes.

mysql-show-master-status

And that should be your master MySQL server configured! Onto the slave…

Slave

The slave configuration is very similar to the master, however there are subtle differences.

Open the my.cnf MySQL configuration file and make the following changes:

vi /etc/my.cnf

Find the bind-address attribute and change it to the IP address of the master server.  You can find your IP address by using ifconfig if you are not sure what it is.

bind-address = 10.1.1.200

Find or add the server-id attribute and make  sure it’s uncommented. You need to assign your slave server an ID, let’s use 2 for our slave server. Keep in mind that this has to be unique across your replication environment so you must change it from the default of 1 that will likely be in the file already.

server-id = 2

Find or add the log_bin attribute and make sure it’s uncommented. This is the location where your slave server will write all the changes that occur on the database. In addition to the log_bin you’ll also need a relay-log file on your slave.

log_bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log

Add an entry to specify which MySQL database should be included for replication.

binlog_do_db = replication_database

Save the file and restart the MySQL server.

service mysql restart

We now need to tell the slave server where it can find the master server. Log into MySQL Server Client as the root user.

mysql -u root -p

Run the following command, and substitute your values as below.

  • MASTER_HOST is the IP address of your master server.
  • MASTER_USER is the user on the master that the slave should use to connect.
  • MASTER_PASSWORD is the password for the above user on the master server
  • MASTER_LOG_FILE is the logfile name on the master server that will be used for replication. This was displayed in the image above when running the command SHOW MASTER STATUS.
  • MASTER_LOG_POS is the location within the log file that your slave should start replicating from. The log position is also displayed with the SHOW MASTER STATUS command. Note, this will increase as changes are made to your master database.
CHANGE MASTER TO MASTER_HOST='10.1.1.100',
MASTER_USER='mysql_rep',
MASTER_PASSWORD='[PASSWORD]', 
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=  107;

The final steps are to start our slave, from which point any changes made to the master will be replicated, and check the status.

Execute the below to start replication:

START SLAVE;

And finally show the status of the slave replication to make sure everything is working.

SHOW SLAVE STATUS;

If you now make some changes on your master server, they should be immediately replicated to the slave. After making some changes, run the SHOW SLAVE STATUS command again and you should notice that the Position value has incremented.

You should be aware that almost any changes are replicated – new tables, indexes and changes in data will all be replicated in the same way.


Export MySQL Database into Separate Files per Table

Category : How-to

Get Social!

mysql-logoI have recently been using git to check in an applications database. The database has many tables, some of which are populated with test data and created a fairly large file when exported. I noticed a few issues issues when checking these into git, namely that the large file was uploaded and saved in git as a single large file containing my changes and the other stuff which had not changed.

Instead of using this large file as one and checking it into git, breaking the file into several smaller files means that only the table which changed would be added to the git commit resulting in much smaller uploads.

The below code is a bash script which let’s you export, using mysqldump, all tables in a MySQL database to individual files. This will result in one file per MySQL table in the database. You will need to modify the following attributes:

  • [USER] – the username to use when connecting to the MySQL instance.
  • [PASSWORD] – the password for the above MySQL user.
  • [DATABASE] – the name of the MySQL database to export.
  • [BACKUP_LOCATION] – the location on the MySQL server where the SQL files will be created.
#!/bin/bash
GIT_MYSQL=/[BACKUP_LOCATION]
for T in `mysql -u [USER] -p[PASSWORD] -N -B -e 'show tables from [DATABASE]'`;
do
    echo "Backing up $T"
    mysqldump --skip-comments --compact -u [USER] -p[PASSWORD] [DATABASE] $T > $GIT_MYSQL/$T.sql
done;

Benchmark MySQL server Performance with Sysbench

Get Social!

mysql-logoYou can spend hours tweaking the settings of a MySQL server instance to get the best possible performance for your hardware and environment. The hardest part is to ensure that the changes made are reflected with increased performance.

To ensure each change results in better performance of the MySQL server we need to measure the performance of the MySQL server before and after the change.

There are a verity of tools to automate MySQL benchmarking, one of which is Sysbench. I will be demonstrating the tests on a Debian 7 system however Sysbench will work on most common Linux distributions. Sysbench can be used to test both InnoDB or MyISAM database types in either a single server environment or a clustered environment with a single instance.

Installing Sysbench will differ on each Linux distribution; it can be downloaded and built from source from Sourceforge or installed with apt-get on Ubuntu or Debian.

apt-get install sysbench

Login to MySQL using the CLI or your favorite GUI tool and create a new database which will be used for the test. If you already have a database you can use for the test then you can skip this step. This example will use a database called dbtest for the tests.

create database dbtest;

The next step is to use the prepare statement with sysbench to generate a table in the specified database which will be used when performing tests.

From the command line, run the below command changing [USER] and [PASSWORD] to your MySQL access credentials.

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=dbtest --mysql-user=[USER] --mysql-password=[PASSWORD] prepare

sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Creating table 'sbtest'...
Creating 1000000 records in table 'sbtest'...

This has created a table called sbtest with 1000000 rows of data which will be used for testing. The below commands show the the created table and do not need to be executed.

mysql> use dbtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_dbtest |
+------------------+
| sbtest           |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM sbtest;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.12 sec)

The next step is to being the performance tests. There are multiple parameters which can be changed to alter the test performed but we will do a simple read write test. Again you will need to change [USER] and [PASSWORD] to your MySQL access credentials.

sysbench --test=oltp --oltp-table-size=1000000 --oltp-test-mode=complex --oltp-read-only=off --num-threads=6 --max-time=60 --max-requests=0 --mysql-db=dbtest --mysql-user=[USER] --mysql-password=[PASSWORD] run

To perform a read only test, change the above parameter oltp-read-only=off to oltp-read-only=on.

The results will look similar to the below output. The main statistic to look for is transactions which shows the number of transactions the test managed to complete, and how many per second.

sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 6

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 5 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            456680
        write:                           163100
        other:                           65240
        total:                           685020
    transactions:                        32620  (543.63 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 619780 (10329.05 per sec.)
    other operations:                    65240  (1087.27 per sec.)

Test execution summary:
    total time:                          60.0036s
    total number of events:              32620
    total time taken by event execution: 359.8823
    per-request statistics:
         min:                                  1.66ms
         avg:                                 11.03ms
         max:                                981.94ms
         approx.  95 percentile:              15.13ms

Threads fairness:
    events (avg/stddev):           5436.6667/31.44
    execution time (avg/stddev):   59.9804/0.00

Finally, you need to clean up your test area. If you can drop the entire database which was used for testing then login to MySQL and run the below command.

drop database dbtest;

If you are unable to drop the whole database then Sysbench comes with a cleanup command. Again you will need to change [USER] and [PASSWORD] to your MySQL access credentials.

sysbench --test=oltp --mysql-db=dbtest --mysql-user=[USER] --mysql-password=[PASSWORD] cleanup

 


Visit our advertisers

Quick Poll

How many Proxmox servers do you work with?

Visit our advertisers