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.
This command installs MySQL server, and the Python libraries necessary to write scripts that can access the database:
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.
There are some settings that can be tweaked to improve the server's security. The following command guides you through this process:
Make sure you can connect to the server using the MySQL command line client. Type this command to start a MySQL shell:
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:
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:
The FLUSH PRIVILEGES command saves the changes made using the GRANT command.
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:
The first time you run this script you'll see some warnings generated by the MySQL server:
When you run the script in future you should see output like this:
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.
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 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:
Save this modification, and transfer the file to the client Pi using the scp command:
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:
Install the MySQL client and Python libraries on the client:
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:
Look for the bind address and change it to 0.0.0.0:
Restart the MySQL server:
Change the database user settings to allow remote access for user db_user:
The FLUSH PRIVILEGES command commits the changes to the datbase.
Log into the client node via ssh:
Now run the database script on the client:
You should see the same output as when you ran the script on the server.
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:
I also installed MySQL client and server packages, along with the Python libraries for MySQL:
You need to edit the MySQL server config file. Open it with this command:
Look for the bind address and change it to 0.0.0.0:
Further down the file, several lines need to be uncommented. Look for these lines and uncomment them:
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:
Now enter the MySQL shell with this command:
Create a database:
Add some sample tables and data to the database:
Grant permission to replicate data to the slave server:
Lock the database tables, and use the 'SHOW MASTER STATEMENT' command to show some information about the state of the database:
In another terminal window, use this command to dump the contents of the database in a file called my_db_dump.sql:
Now use this command to copy the database dump to the slave server:
Back in the MySQL shell, unlock the database tables:
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:
Open the MySQL shell, and create a database:
At the bash prompt, type this command to import data from the dump file:
Next, you need to set up the configuration file for the MySQL slave server:
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:
And uncomment the following variables:
Note that the server-id on the slave needs to be set to 2. You also need to add an extra line:
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:
Go back into the MySQL shell, and type this command to tell the MySQL server to use the master server:
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:
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
In the mysql shell on the slave, type this command to make sure the new record can be accessed on the slave:
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.
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:
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
Run bpi-config to expand the root partition and change the hostname and password:
Set DNS servers in /etc/resolv.conf
(These are Google's DNS servers. You can use different name servers if you want.)
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:
Open my.cnf in a text editor and set the bind address:
Uncomment the following variables, and set the values of server-id and binlog_do_db:
Add this line just below the lines with binlog_do_db:
Now copy my.cnf from the control node back to each server (make sure to update the server-id value for each server):
On each server, change the owner of the config file to root, and copy it to the correct place:
Restart the MySQL server on each node:
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:
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:
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:
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.
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 next three variables are passwords for the three users that will be used on this server:
The IP addresses of the slave servers need to be contained in a list:
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:
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:
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:
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:
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:
At this point, the databases are set up, and replication is enabled. Check the status of each slave using the following command:
There still isn't any data in the database, so on the master, add a record to one of the tables:
On any of the slaves, use these commands to make sure that the entry has been replicated:
Share this page: