Skip to navigation

Set up MySQL on a Banana Pi

MySQL is an SQL database which is often used for web applications.  MySQL is one of the most widely used databases, so it's worth learning how to use it.  Follow this link for a basic introduction to MySQL on Raspberry Pi servers.  I'm using Raspbian in these tutorials, but these instructions should work well on Ubuntu.

Install MySQL server and Python libraries

This command installs MySQL server, and the Python libraries necessary to write scripts that can access the database:

sudo apt-get install mysql-server python-mysqldb

You will be prompted to enter a password for the database server's root account.  When you've entered a password, you'll be prompted to enter it again for confirmation.  The MySQL server should start automatically after it's installed.

Secure MySQL

There are some settings that can be tweaked to improve the server's security.  The following command guides you through this process:

/usr/bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): <your password>
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] n
 ... skipping.

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist
 ... Failed!  Not critical, keep moving...
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

Access the database from the command line

Make sure you can connect to the server using the MySQL command line client.  Type this command to start a MySQL shell:

mysql -u root -p

Enter the root password that you used when you installed MySQL.  We can use the command line client to create a database and create a MySQL user:


mysql> CREATE DATABASE my_db;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER 'db_user'@'localhost' IDENTIFIED BY 'mypassword';
Query OK, 0 rows affected (0.00 sec)

The CREATE USER command creates a user named 'db_user' with password 'mypassword'.  This user is created with permission to make database requests from the same host where the database is running.  The next command grants all permissions to db_user to make requests to my_db from the localhost:

mysql> GRANT ALL ON my_db.* TO 'db_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

The FLUSH PRIVILEGES command saves the changes made using the GRANT command.

Accessing the database from a Python script

I've written a script that connects to the database server and performs a few basic operations like creating tables and adding records to those tables.  It's a very simple script that's intended to demonstrate how to use MySQL with Python.  You can download the script here.  Transfer it to your home directory on your Pi.  Make it executable and run it with these commands:

chmod +x db-script.py 
./db-script.py

The first time you run this script you'll see some warnings generated by the MySQL server:

./db-script.py:11: Warning: Unknown table 'pages'
  curs.execute('DROP TABLE IF EXISTS pages')
./db-script.py:12: Warning: Unknown table 'categories'
  curs.execute('DROP TABLE IF EXISTS categories')
./db-script.py:13: Warning: Unknown table 'users'
  curs.execute('DROP TABLE IF EXISTS users')

When you run the script in future you should see output like this:

Setting up database...
Dropped tables

Created tables

Added User

('Steve', 'password', '[email protected]', datetime.datetime(2014, 8, 30, 0, 0))
DB Connection closed

The script deletes any tables that were created last time the script ran. Then it creates new tables, adds a record to one of them.  

Setting up remote access to a MySQL database

In the previous article about setting up MySQL on a Banana Pi I covered the basics of setting up MySQL, and I used a script called db-script.py to access the database. The next step is to set up remote access so that the database can be used from a second Banana Pi. Db-script.py will run on that Pi and access the database on the original Pi via the local network. 

Set up the MySQL client

Set up another Banana Pi with Raspbian, and give it a static IP address.  In my case, the server's IP address is 192.168.0.8, so I set the client Pi's IP address to 192.168.0.9.  I have a monitor, keyboard and mouse connected to the server, so I'm going to use ssh to login to the client.

On the server, modify the script used to access the database.  On line 73 of db-script.py, replace 'localhost' with the IP address of your MySQL server:

    conn = MySQLdb.connect('192.168.0.8', 'db_user', 'mypassword', 'my_db')

Save this modification, and transfer the file to the client Pi using the scp command:

scp ./db-script.py [email protected]:./

Db-script.py will be copied to /home/bananapi on the second Pi.  Log into the client node via ssh, and make the script executable:

ssh [email protected]
chmod +x db-script.py

Install the MySQL client and Python libraries on the client:

sudo apt-get install python-mysqldb mysql-client

Change settings on the MySQL server

You need to edit the settings for the MySQL server to allow it to accept requests from other IP addresses besides the loopback address.  On the Pi running the MySQL server, open the MySQL configuration file in a text editor:

sudo leafpad  /etc/mysql/my.cnf 

Look for the bind address and change it to 0.0.0.0: 

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 0.0.0.0

Restart the MySQL server:

sudo service mysql restart
[ ok ] Stopping MySQL database server: mysqld.
[ ok ] Starting MySQL database server: mysqld . ..
[info] Checking for tables which need an upgrade, are corrupt or were 
not closed cleanly..

Change the database user settings to allow remote access for user db_user:

mysql -u root -p

mysql> GRANT ALL ON my_db.* TO 'db_user'@'192.168.0.9' IDENTIFIED BY 'mypassword';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

The FLUSH PRIVILEGES command commits the changes to the datbase.  

Run the script on the client

Log into the client node via ssh:

ssh [email protected]

Now run the database script on the client:

./db-script.py
Setting up database...
Dropped tables

Created tables

Added User

('Steve', 'password', '[email protected]', datetime.datetime(2014, 8, 30, 0, 0))
DB Connection closed

You should see the same output as when you ran the script on the server.  

Setting up Master-Slave MySQL Replication

There are times when one MySQL server isn't enough.  If you have a high traffic web site on a cluster of several web servers, you will need a cluster of database servers to handle all the SQL queries generated by the web servers.  One of the most commonly used methods of synchronizing servers in a MySQL cluster is replication.  One server is set up as a master, and the other servers in the cluster are slaves.  Any changes made to the master database will be replicated on the slave servers. 

I'm going to build a cluster of two MySQL servers, one master, and one slave.  I started with two fresh installations of Raspbian on each Banana Pi.  I have a monitor, mouse and keyboard attached to one of them, and I connected to the other Banana Pi using ssh.

Both servers have static IP addresses.  The master's IP address is 192.168.0.30, and the slave's IP address is 192.168.0.31.  

I started by making sure both servers were up to date by running this command on both servers:

sudo apt-get update && sudo apt-get upgrade -y

I also installed MySQL client and server packages, along with the Python libraries for MySQL:

sudo apt-get install mysql-server mysql-client python-mysqldb -y

Set up the Master Server

You need to edit the MySQL server config file.  Open it with this command:

sudo nano /etc/mysql/my.cnf 

Look for the bind address and change it to 0.0.0.0:

bind-address = 0.0.0.0

Further down the file, several lines need to be uncommented.  Look for these lines and uncomment them:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = my_db

Type control-O and press return to save the changes you've made, and type control-x to exit from nano.  Restart the MySQL server:

sudo service mysql restart
[ ok ] Stopping MySQL database server: mysqld.
[ ok ] Starting MySQL database server: mysqld . ..
[info] Checking for tables which need an upgrade, are corrupt or were 
not closed cleanly..

Now enter the MySQL shell with this command:

mysql -u root -p

Create a database:

mysql> CREATE DATABASE my_db;
Query OK, 1 row affected (0.00 sec)

mysql> USE my_db

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

Add some sample tables and data to the database:

mysql> CREATE TABLE users (username TEXT, password TEXT, email_addr TEXT, joined DATETIME);
mysql> COMMIT;

mysql> INSERT INTO users values('Steve', '123456', '[email protected]', CURRENT_DATE());
mysql> COMMIT;

Grant permission to replicate data to the slave server:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'192.168.0.31' IDENTIFIED BY 'mypassword';
mysql> FLUSH PRIVILEGES;

Lock the database tables, and use the 'SHOW MASTER STATEMENT' command to show some information about the state of the database:

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     4711 | my_db        |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

In another terminal window, use this command to dump the contents of the database in a file called my_db_dump.sql:

mysqldump -u root -p --opt my_db > my_db_dump.sql

Now use this command to copy the database dump to the slave server:

scp ./my_db_dump.sql [email protected]:./

Back in the MySQL shell, unlock the database tables:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

Set up the Slave Server

The next step is to set up the slave server.  I started by setting up a copy of the database on the slave server and importing the database dump from the master server.  

Log into the slave server via ssh:

ssh [email protected]

Open the MySQL shell, and create a database:

mysql -u root -p

mysql> CREATE DATABASE my_db;
Query OK, 1 row affected (0.00 sec)

mysql> QUIT;
Bye

At the bash prompt, type this command to import data from the dump file:

mysql -u root -p my_db < my_db_dump.sql 

Next, you need to set up the configuration file for the MySQL slave server:

sudo nano /etc/mysql/my.cnf 

Most of the changes that you need to make are the same as the changes for the master.  You need to change the bind-address:

bind-address = 0.0.0.0

And uncomment the following variables:

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = my_db

Note that the server-id on the slave needs to be set to 2.  You also need to add an extra line:

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

As before, type control-O and press return to save the changes you've made, and type control-x to exit from nano.  Restart the MySQL server:

sudo service mysql restart
[ ok ] Stopping MySQL database server: mysqld.
[ ok ] Starting MySQL database server: mysqld . ..
[info] Checking for tables which need an upgrade, are corrupt or were 
not closed cleanly..

Go back into the MySQL shell, and type this command to tell the MySQL server to use the master server:

CHANGE MASTER TO MASTER_HOST='192.168.0.30', MASTER_USER='slave_user', MASTER_PASSWORD='mypassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4711;

This command tells the slave what username and password it should use when it connects to the master server, and it uses the infomation from the SHOW MASTER STATUS command to specify the position of data in the log file on the master.

Start replicating the master database on the slave with this command:

START SLAVE;

Test Replication

Test that replication is working by creating a new user on the master, and then checking to see if you can search for that record on the slave.  

On the master in the mysql shell, type this command to add a record to the user table

INSERT INTO users values('Dave', '123456', '[email protected]', CURRENT_DATE());
COMMIT;

In the mysql shell on the slave, type this command to make sure the new record can be accessed on the slave:

SELECT * FROM my_db.users WHERE username='Dave';
+----------+----------+------------------+---------------------+
| username | password | email_addr       | joined              |
+----------+----------+------------------+---------------------+
| Dave     | 123123   | [email protected] | 2014-09-02 00:00:00 |
+----------+----------+------------------+---------------------+
1 row in set (0.00 sec)

 

Automate MySQL replication on four nodes

In the previous post I talked about setting up MySQL replication so that two database servers can be kept synchronized with each other.

Sometimes it's necessary to replicate a database on several servers.  The more servers there are, the more queries can be handled.  I'm going to set up a cluster of four database servers, and use three of them as slaves.  Each of the slaves is replicated from the master.

Instead of setting up the databases on all servers by entering a lot of commands at the MySQL prompt, I have written a Python script to set them up.  The script will also set up replication.

The master is at IP address 192.168.0.35, and the slaves are at 192.168.0.36, 192.168.0.37 and 192.168.0.38.  On each server, I created two users - one for replicating data between servers, and another user for executing queries.

I'm using fifth Banana Pi as a control node to manage the cluster.  This Banana Pi is at IP address 192.168.0.8, and I connected a keyboard monitor and mouse to it.  I opened four terminal windows and used each one to ssh to one of the MySQL servers.

Each Banana Pi had an 8GB class 10 memory card with Raspbian installed on it.

Set up the control node

I set the host name and password using bpi-config, and expanded the root partition.  Next I installed the MySQL client, and the Python bindings for it:

sudo apt-get install python-mysqldb mysql-client -y

Set up each server

You can set up one server, and copy the sd card image.  The downside of this is that you have to copy an 8GB image to the other SD cards which takes longer, and then I still needed to log into each Pi to change the IP address and host name.  I just installed the default Raspbian image for the Banana Pi on four different SD cards, and set up each one individually.

You need to go throught the following steps on each server.  

Set a static IP address in /etc/network/interfaces 

auto lo

iface lo inet loopback
iface eth0 inet static
address 192.168.0.35
netmask 255.255.255.0
gateway 192.168.0.1

allow-hotplug wlan0
iface wlan0 inet manual
wpa-roam /etc/wpa_supplicant/wpa_supplicant.conf
iface default inet dhcp

Run bpi-config to expand the root partition and change the hostname and password:

sudo bpi-config

Set DNS servers in /etc/resolv.conf

nameserver 8.8.8.8
nameserver 8.8.4.4

(These are Google's DNS servers.  You can use different name servers if you want.)

Update Linux:

sudo apt-get update && sudo apt-get update -y

Install MySQL: 

sudo apt-get install python-mysqldb mysql-server -y

Set up MySQL configuration files

On the control node, get a copy of the MySQL server config file from one of the servers, and copy it to the control node:

scp [email protected]:/etc/mysql/my.cnf ./my.cnf

Open my.cnf in a text editor and set the bind address:

bind-address        = 0.0.0.0

Uncomment the following variables, and set the values of server-id and binlog_do_db:

server-id        = 1
log_bin            = /var/log/mysql/mysql-bin.log
binlog_do_db        = my_db

Add this line just below the lines with binlog_do_db:

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

Now copy my.cnf from the control node back to each server (make sure to update the server-id value for each server):

scp ./my.cnf [email protected]:./my.cnf
scp ./my.cnf [email protected]:./my.cnf
scp ./my.cnf [email protected]:./my.cnf

On each server, change the owner of the config file to root, and copy it to the correct place:

sudo chown root:root my.cnf ; sudo cp  my.cnf /etc/mysql/my.cnf

Restart the MySQL server on each node:

sudo service mysql restart

The next step is to grant privileges to the MySQL root user on each server for requests from the control node at IP address 192.168.0.8.  You can do this by logging in to each server and using the GRANT command:

mysql -u root -p

mysql> GRANT ALL ON *.* TO 'root'@'192.168.0.8' IDENTIFIED BY 'T4nk3r!12' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Now each server can be managed from the control node.  Check that you can connect from the control node to a server by typing this command:

bananapi@control ~ $ mysql -h 192.168.0.35 -u root -p

Once you've entered the root user's database password, you should see a MySQL command prompt.

In the last post on setting up replication, I described how to dump the contents of the master into a file and load that file into the slave database. You only need to do this if you're setting up replication on an existing database.  In this case, I'm setting up replication before any data has been added to the master database, so I can skip this step.

At this point the servers have been set up, but there's no database on any of them, and replication hasn't been set up yet.  The next step is to create a database and its tables on each server.  I could create the database by logging into the MySQL shell on each server and manually typing commands to create the database, but it's easier to write a scipt to do it.

You can download the code that I used here:

  • db-cluster-init.py
  • clean_cluster.py

Note that this is sample code to illustrate the basics of automating MySQL replication, it is not a finished application.  There are two scripts: db_cluster_init.py, which sets up databases on each node and sets up replication, and there's a script called clean_cluster.py, which deletes the databases and returns the servers to their original state.

Configuring the Python script

There are some configuration options that you need to set in db-cluster.py before you run it. There's no GUI for this program, it has to be configured by editing global variables at the top of the file.

The first one is the name of the database, and the next two are the IP address of the control node and the master server:

# the name of the database to create
database_name = "my_db"

# the IP address of the control node
control_host = "192.168.0.8"

# the IP address of the master server
master_ip = "192.168.0.35"

The next three variables are passwords for the three users that will be used on this server:

# root user's pass word 
root_pw = "somepassword"

# database user's password
db_user_pw = "mypassword"

# slave user's password
slave_user_pw = "mypassword"

The IP addresses of the slave servers need to be contained in a list:

# a list of IP addresses of the slave servers
slave_ip_list = ["192.168.0.36", "192.168.0.37", "192.168.0.38"]

There are two variables that you shouldn't change, mysqlbin and binlog.  These variables store a location in the master server's log file.  The code to initialize a master server will capture values from the master and store them in these variables:

# These strings will store information about the master server's
# log position. Leave them set to "". 
mysqlbin = ""
binlog = ""

The slave servers will start replicating data from this point in the log.  Earlier entries in the master's log will be ignored.

The program starts at the main function, which calls the function init_server for each server.  This function creates a database, creates some tables, and creates three users:

def init_server (host, root_pw, master):
    """
    Initialize a MySQL server as either a master or slave
    """

    conn = MySQLdb.connect(host, 'root', root_pw, '')
    curs = conn.cursor()

    create_db (curs, database_name)
    print "Created database"

    create_users (curs)
    print "Created users"

    # create the database tables
    try:
        create_tables(curs)
        conn.commit()
    except Exception, e:
        conn.rollback()
        print "Failed to create tables"
        print_exception(e)

    if master == True:
        # set up this server as the master
        set_as_master (curs, host)

    else:
        # this server is a slave
        set_read_only(curs)
        # tell the slave which master to use
        use_master (curs, master_ip, slave_user_pw, mysqlbin, binlog)
        start_slave (curs)

    conn.commit()

    # close the connection to the database
    conn.close()
    print "DB Connection closed\n"

Note that create_tables creates some sample tables, not tables from a real application. This function can be customized to create tables for different applications.

The master parameter passed to init_server is a boolean variable which determines whether the server will be set up as a master or slave.  If the server is a master, a function called set_as_master will be called:


def set_as_master (curs, host):
    """
    Make a host a master
    """

    global mysqlbin
    global binlog

    # Grant replication rights to slaves
    for host in slave_ip_list:
        grant_replication (curs, 'slave_user', host, slave_user_pw)

    # get master status
    curs.execute("FLUSH TABLES WITH READ LOCK")
    curs.execute("SHOW MASTER STATUS")
    master_status = curs.fetchall()
    curs.execute("UNLOCK TABLES")

    mysqlbin = str(master_status[0][0])
    binlog = str(master_status[0][1])

This function uses the SHOW MASTER STATUS query to get the position of the master server's log file.  It also grants replication privileges to the slave_user on the master server.

If the server is a slave, the following functions are called:

        # this server is a slave
        set_read_only(curs)
        # tell the slave which master to use
        use_master (curs, master_ip, slave_user_pw, mysqlbin, binlog)
        start_slave (curs)

These functions make slave servers read only.  The use_master function tells a slave which server to use as master, and it tells slaves where to start reading the master's log.  The start_slave function starts the slave IO thread.

When you run db-cluster-init.py on the control node, you should see output like this:

./db-cluster-init.py 
Init Master
Created database my_db
Created database
Created user db_user
Granted privileges
Created user slave_user
Granted privileges
Created users
Created tables
Granted replication privilege to [email protected] id'd by mypassword
Granted replication privilege to [email protected] id'd by mypassword
Granted replication privilege to [email protected] id'd by mypassword
DB Connection closed

MySQL bin: mysql-bin.000001
Bin log: 3771
Init server 192.168.0.36
Created database my_db
Created database
Created user db_user
Granted privileges
Created user slave_user
Granted privileges
Created users
Created tables
Set Master
DB Connection closed

Init server 192.168.0.37
Created database my_db
Created database
Created user db_user
Granted privileges
Created user slave_user
Granted privileges
Created users
Created tables
Set Master
DB Connection closed

Init server 192.168.0.38
Created database my_db
Created database
Created user db_user
Granted privileges
Created user slave_user
Granted privileges
Created users
Created tables
Set Master
DB Connection closed

At this point, the databases are set up, and replication is enabled.  Check the status of each slave using the following command:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.35
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysql-relay-bin.000008
                Relay_Log_Pos: 210
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 512
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

There still isn't any data in the database, so on the master, add a record to one of the tables:

USE my_db;
INSERT INTO users VALUES ('Dave', '123456', '[email protected]', CURRENT_DATE());
COMMIT;

On any of the slaves, use these commands to make sure that the entry has been replicated:

mysql> USE my_db
Database changed
mysql> SELECT * FROM users;
+----------+----------+----------------+---------------------+
| username | password | email_addr     | joined              |
+----------+----------+----------------+---------------------+
| Dave     | 123456   | [email protected] | 2014-09-23 00:00:00 |
+----------+----------+----------------+---------------------+
1 row in set (0.00 sec)

 

Share this page: