This Raspberry Pi MySQL & PHPMyAdmin tutorial is an extension to the web server tutorial that I did earlier. You don’t need to have done that tutorial to be able to complete this one.
If you’re unfamiliar with MySQL, this is a great way to get learning it. MySQL is a relational database management system and allows you store & maintain data. It is one of the world’s most popular relational database system and is commonly included in most LAMP stacks.
For anyone who doesn’t know PhpMyAdmin is a free tool that has been designed to allow for easy administration of MYSQL. It is hugely popular and used in a lot of web server environments throughout the world.
This is an excellent tutorial if you’re looking to learn more about MYSQL and PHPMyAdmin. Especially if you have project work that requires a lot of SQL work.
As always, it’s important to remember the limitations of the Raspberry Pi so don’t expect it to handle a massive database.
Below is the equipment that you will need to complete this Raspberry Pi MYSQL tutorial.
If you want to see how this is all done visually be sure to check out the video, I have put together below.
The video will take you through from start to finish on how to get this done. As always if you like the video, then please subscribe or follow us on social media.
As always you will need to have Raspbian installed or a similar operating system installed before we can get started. Be sure to head over to my page on installing Raspbian if you haven’t yet learned how to do this.
I have broken this tutorial down into two steps. First, we will install the web server software and also MYSQL. In the second step, we will take a look at installing the PHPMyAdmin. If you have come from the Raspberry Pi web server tutorial, then you can skip the first step as you would have already done this.
Installing Raspberry Pi MySQL
1. Firstly let’s install the MySQL server onto the Raspberry Pi.
sudo apt-get install mysql-server
2. You will be prompted to enter a password for the root user. Make sure you write this down as we will need to use this to access the MYSQL server and connect PHPMyAdmin to it.
3. If you’re not prompted to enter a password, you will need to enter the following command. Answer all the questions so it will be set up to match your security requirements.
4. Now if you want to access and start making changes to the database, enter the following command:
sudo mysql -u root -p
5. It will now prompt you to enter the password we just created.
6. You can now enter MYSQL commands to create, alter, delete databases.
7. You’re able to leave the MYSQL command line by simply entering
If you want to be able to interact with MYSQL in Python, you will need to install the Python bindings as well. You can do this by entering the following command.
sudo apt-get install python-mysqldb
Now we’re done installing the Raspberry Pi MYSQL you may want to install PHPMyAdmin, so you have a nice clean interface to work with.
Creating an MySQL Database User
1. By default, PHPMyAdmin will disallow you to log in using the root login. Instead, you will need to create a new user if you wish to create and access data tables within PHPMyAdmin. To do this first login as root with the password you selected.
sudo mysql -u root -p
2. Now run the following command, replacing username with the username of your choice. Also, replace password with a secure password of your choice.
GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'localhost' IDENTIFIED BY 'password';
3. You can exit by entering
quit. Once done you can proceed to install PHPMyAdmin.
Installing Raspberry Pi PHPMyAdmin
You will find that installing Raspberry Pi PHPMyAdmin is very easy to do and won’t take long at all. If you haven’t already installed a web server, you can learn from our guide on installing Apache or our guide to installing NGINX.
1. I will go on the assumption you have already set up your web server and setup PHP for it. Please note that these steps will differ a bit if you are using Apache or NGINX.
Now let’s install the PHPMyAdmin package, you can do this by entering the following command on your Raspberry Pi.
sudo apt-get install phpmyadmin
2. It will now begin to install. You will be presented with a screen asking the type of web server you want it to run off. Select apache2 even if you are using NGINX since this doesn’t hugely matter for us.
3. Next, we will need to configure PHPMyAdmin to connect to our SQL database server. (The one we set up previously in installing the Raspberry Pi MYSQL step or the web server). To do this select yes at the next prompt.
4. The setup tool will now ask for a password, enter the one you set for root when setting up your SQL Server, this is needed for phpMyAdmin to talk with the SQL Server and manage your databases.
5. Next, it will ask you to set a password for PHPMyAdmin itself. It is best to set this password to something different to your root SQL password. Make sure you remember it as this is the password you will need to access the interface.
6. With that done we can now proceed to configure our web servers for use with phpMyAdmin. These steps differ for both Apache and NGINX so make sure you follow the correct list of steps.
Configuring Apache for phpMyAdmin
7a. To begin setting up Apache for use with phpMyAdmin enter the following command into the terminal:
sudo nano /etc/apache2/apache2.conf
7b. Now at the bottom of this file enter the following line:
Once done save & exit by pressing CTRL +X and then y.
7c. Now restart the Apache service by entering the following command:
sudo /etc/init.d/apache2 restart
Configuring NGINX for phpMyAdmin
7a. To setup NGINX to work with phpMyAdmin all, we need to do is create a link between the phpmyadmin folder and our root html directory. To do this, we simply need to run the following command:
sudo ln -s /usr/share/phpmyadmin /var/www/html
8. Now you should be able to access the PHPMyAdmin from a browser. To test this out, go to the following address in your browser. (Replace the IP with your IP. If you don’t have it run the command hostname –I on your Pi)
9. If it’s all working, then you’re good to go. You will need to log in using a user other than root as this is disabled for security purposes. Further up in this tutorial I show you how to make a database user quickly.
Assuming you’re looking to learn how to setup WordPress on the Raspberry Pi, then you can find that in my web server tutorial linked towards the top of this page.
If you want to enable external access to the either PHPMyAdmin or MYSQL, then you will need to setup port forwarding. To learn how to do this check out my guide on port forwarding. The ports you will need to forward is 80 for PHPMyAdmin and MYSQL is 3306. You will also need to make sure you have enabled outside access to SQL.
If you want to learn more about MYSQL and the commands, you can use to view & edit data then I recommend heading over to w3schools or at codecademy. They have some great beginner tutorials to help bring you up to speed on using MYSQL.
I hope you can get both the Raspberry Pi MYSQL & PHPMyAdmin working from this tutorial. If you do come across something or feel that I have missed some important steps, then please feel free to leave us feedback on the forum.