Change Listening Port of MySQL or MariaDB Server

Change Listening Port of MySQL or MariaDB Server

Category : How-to

Get Social!

mysql-logoThe MySQL and MariaDB server both use a file called my.cnf for parameters that are used to configure the server. This is where the port number and, if you use it, the local socket can be configured.

A local socket is the prefered method of connecting to a database as it removes much of the overhead of creating a TCP connection and transferring data. This comes with the limitation that it can only be used if the application accessing the database is on the same machine. In larger or highly available systems this may not be possible.

A TCP connection is the only option of connecting to your MySQL or MariaDB database from a remote machine. It incurs a small penalty over a local socket and therefore slightly higher latencies. MySQL server and MariaDB can be configured to use a local socket, TCP connections or both.

We’ll be editing the my.cnf file for the following sections. Open the file in your favourite editor.

Configuring local socket use

The socket option indicates the filesystem path to the location of the socket you’d like to use. Specify a filesystem path, usually /var/run/mysql/mysqld.sock and the socket will be created when the server next starts. Remove or comment (#) the line to disable socket access.

Restart the server for the changes to take effect.

Setting or changing the TCP port

The port option sets the MySQL or MariaDB server port number that will be used when listening for TCP/ IP connections. The default port number is 3306 but you can change it as required. Use the port option with the bind option to control the interface where the port will be listening. Use to listen on all IP addresses on the host, or specify a single one directly to listen on a single interface. Omit both of these options to disable TCP/ IP connections.

Restart the server for the changes to take effect.

Reset The root MYSQL/ MariaDB Password

Category : How-to

Get Social!

mysql-logoIf you’ve lost or forgotten the root user password on a MySQL or MariaDB server you’ll want to reset it and leave all the other accounts and data intact. Fortunately it’s possible, but you’ll need access to an SSH account hosting the instance and the ability to stop and start the database service.

Before going any further, make sure your instance of MySQL or MariaDB is shutdown.

Start the server in safe mode and don’t load the table grants and permissions.

Log into the local instance with the root user.

Run the below commands SQL, once connected, and reset your password. Be sure to substitute new-password with the new password for your root account.

Finally, start the SQL server instance and use your new root account password.


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.

Run the following commands to install MariaDB.

Install MariaDB 10 on Ubuntu 12.04

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

Run the following commands to install MariaDB.

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.


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.

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.


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:

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.

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.

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.

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

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

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.

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

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.

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

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.

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


And that should be your master MySQL server configured! Onto the 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:

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.

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.

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.

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

Save the file and restart the MySQL server.

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

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.

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:

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

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.