This Raspberry Pi MYSQL & PHPMyAdmin tutorial is an extension to the webserver 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 of 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.
If you want to see how this is all done visually be sure to check out the video, I have put together below. It will take you through from start to finish on how to get this done. As always if you like the video then please subscribe to my channel!
Below is the equipment that you will need in order to complete this Raspberry Pi MYSQL tutorial.
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 learnt 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 2nd step we will take a look at installing the PHPMyAdmin. If you have come from the Raspberry Pi web server tutorial, then you can simply 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 setup to match your security requirements.
4. Now if you want to access and start make changes to the database simply 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 a MYSQL Database User
1. By default PHPMyAdmin will disallow you to login using the root login. Instead you will need to create a new user if you wish to create and access datatables 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, replcing 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 installing PHPMyAdmin.
Installing Raspberry Pi PHPMyAdmin
You will find that installing Raspberry Pi PHPMyAdmin is very easy to do any 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 on installing NGINX.
1. I will go on the assumption you have already setup your own 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 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 simply 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 simply 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 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 login using a user other than root as this is disabled for security purposes. Further up in this tutorial I show you how to quickly make a database user.
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 are able to 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 drop a comment below.