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.


Manually Install Frappe on Ubuntu 14.04 With a Remote SQL Server

Get Social!

frappeFrappe is a full stack web framework based on Python and Javascript which can be used to build and deploy web applications. The main focus of this post is to set up a Frappe environment that can be used for ERPNext.

Before starting, install MariaDB 10.x on a remote host (or local, but that’s less common in a production environment) as we’ll use that later.

Create a user for Frappe – here we’ll use frappe but you can change this for anything you like. If you do change the user remember to update the home directory.

Set the password for the frappe user with passwd.

Install the required dependencies. Notice that we’ve not included an SQL server as we’ll use a remote MariaDB SQL Server.

Download and install the latest version of wkhtmltopdf from Sourceforge.

Download Frappe from Github as the frappe user.

Run the pip installer for Frappe.

Create a new Bench as the frappe user.

Add the default site configuration file with the remote SQL database hostname/ IP and port number.

And add:

Before you create any sites, you’ll need to apply a few settings to your SQL server. This guide assumes you have a remote MariaDB SQL Server – log into it and add the below to your my.conf file.

And restart your SQL server for the changes to take affect.

Copy the Supervisor config file into place so that Frappe automatically starts up with the system.


Bash Script to Install a mariadb-galera-server Cluster on Multiple Servers

Get Social!

The following script will install the MariaDB Galera Server binaries on multiple servers with a basic cluster configuration.

Let me start by saying it’s a quick and dirty script – it may not work in all scenarios and was written on a set of Debian servers. Use it at your own risk! It may work on a MySQL Server cluster but you’ll have to remove or change the repository locations to the MySQL Server ones.

Before running the script you’ll need to have SSH key Authentication set up and working between all the nodes. See my post on Create ssh key authentication between nodes for setting up server key authentication.

You’ll also need to change a few variables in the script to match your environment.

The first variable IP is an array of all the IP addresses you’d like to use to install Maria DB Server. Currently there are 3 IPs specified – replace them with your own IP addresses and add any more if you have more than 3 servers.

The other two variables CLUSTER_NAME which is the name MariaDB will use for your cluster; and MAINT_USER_PASSWORD which is the password that will be set for the maintenance user.


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.