Install MySQL Database on Raspberry pi and access it from Windows PC
The simple guide to get it working, darn its hard if you're following wrong steps!!!
Introduction
If you’re a raspberry pi user and you haven’t hit a point which asks you to use database, then you’re still experimenting :) Sooner or later, everyone comes across a project that requires usage of a database like mysql or mariadb. I myself have dodged it for quite some time and tried using things like sqlite3 but it eventually caught up with me where I needed a proper data storage and query support which can only be achieved with mysql.
So, how to install mysql on raspberry pi? You can’t, without purchasing 😉😉
MySQL is a proprietary product by Oracle (until this article is being written) and if you want to use it for your projects, you’ll have to buy it. So what’s the alternative to mysql? Its called mariadB.
MariaDB
MariaDB is one of the most popular open source relational databases. It’s made by the original developers of MySQL and (as written on their website ) guaranteed to stay open source. It is also present in most cloud database offerings
You can use mariaDB for pretty much any kind of projects that needs RDBMS functionality. Its free, open source and no strings attached, meaning you don’t have to pay anyone anything for using mariaDB. That’s the beauty of open source software that we all developers and tinkerers love.
Installation of mariaDB on Raspberry Pi
At this point, you must’ve installed Raspberry Pi OS (Aka Raspbian os) on your raspberry pi computer. If you want guided tutorial on how to do that, feel free to check this video. It has exact steps to install operating system on raspberry pi without using a monitor and still connect to your home wifi network. This will help if you’ve already not setup your raspberry pi computer.
Once the Operating System is installed on your raspberry pi computer, you can head on to install mariadb server and configure it for first time use.
Install and Configure MariaDB
At this point, assuming that you’re aware of the terminal in raspberry pi OS, these are the steps / commands to be executed from terminal to install mariaDB on Raspberry Pi.
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install mariadb-serverRoot Access
sudo mysql_secure_installationPress enter to continue (no password by default)
Then type “Y” to set a new password, and enter your own password, keep it simple to remember
Now, press “Y” three times to:
Remove anonymous users
Disallow root login remotely
Remove the test database
And finally, press “Y” again to reload the privileges
Connect to Mariadb as Root
mysql -uroot -pOnce connected you can use all the usual Database commands like SELECT, CREATE, SHOW etc…
Creating new User on MariaDB
Keep all quotes as it is from below code and just remove <username> with something like sam, remove <dbname> with something like mydb, keep everything else as it is.
sudo mysql -uroot -p
CREATE DATABASE <dbname>;
CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';
GRANT ALL PRIVILEGES ON <dbname>.* TO '<username>'@'localhost';
FLUSH PRIVILEGES;Connect as new user to Mariadb
sudo mysql -u<username> -pConfigure Mariadb
We want to access this database remotely from a windows computer, in order to do that we need to configure this database, for that, open the configuration file in a text editor like geany or nano and make changes to the property bind-address. You need to comment this line to allow connecting from any computer.
MariaDB is now installed on raspberry pi and ready to use!!!
Kitflix creates number of different online courses for anyone to learn and excel in technologies, check all the kitflix courses here. A great news is, there are many free courses and moreover even for every paid course, 50% of the course is available as free preview. Don’t forget to check them out.
Keep experimenting.