Difference between revisions of "Pi MySql"

From OpenCircuits
Jump to navigation Jump to search
 
(4 intermediate revisions by the same user not shown)
Line 127: Line 127:
 
or
 
or
 
# systemctl restart mariadb
 
# systemctl restart mariadb
 +
 +
--- get on ----
 +
 +
mysql -u pi_user -p
 +
    <password >
 +
 +
    use pi_db
 +
=== create db and user ===
 +
 +
<pre>
 +
 +
CREATE DATABASE pi_db;
 +
With that database instance created successfully, I then created the MariaDB user with the relevant privileges for accessing that database instance:
 +
 +
 +
CREATE USER 'pi_user'@'localhost' IDENTIFIED BY 'insert password';
 +
GRANT ALL ON pi_db.* TO 'pi_user'@'localhost';  -- needs to have tcpip address in it for remote addess
 +
GRANT ALL PRIVILEGES ON example.* TO 'pi_user'@'%' IDENTIFIED BY 'the_password';  -- % is the tcip wild card  how about limit to 192 .... think this is correct
 +
 +
there is a flush command I have lost.
 +
 +
use mysql;
 +
update user set password=PASSWORD("password here"), plugin = '' where User='root';
 +
flush privileges;
 +
 +
 +
sudo mariadb -u root -p  -- seemed to work
 +
 +
</pre>
  
 
= Links some useful =
 
= Links some useful =
Line 137: Line 166:
  
  
 +
*'''[https://howtoraspberrypi.com/mariadb-raspbian-raspberry-pi/ How to install MariaDB on Raspbian and the Raspberry Pi ]'''
 +
*'''[https://raspberrypi.stackexchange.com/questions/72846/raspbian-stretch-with-mariadb pi zero - Raspbian Stretch with MariaDB - Raspberry Pi Stack Exchange ]'''
 +
*'''[https://raspberrypi.stackexchange.com/questions/72846/raspbian-stretch-with-mariadb pi zero - Raspbian Stretch with MariaDB - Raspberry Pi Stack Exchange ]'''
 +
*'''[https://howtoraspberrypi.com/mariadb-raspbian-raspberry-pi/ How to install MariaDB on Raspbian and the Raspberry Pi ]'''
 +
*'''[https://www.techcoil.com/blog/setting-up-wordpress-on-raspberry-pi-3-with-raspbian-stretch-lite-nginx-mariadb-and-php-7-as-the-lemp-stack/ Setting up WordPress on Raspberry Pi 3 with Raspbian Stretch Lite, nginx, MariaDB and PHP 7 as the LEMP stack - Techcoil Blog ]'''
 +
 +
 +
*'''[https://mariadb.com/kb/en/library/mariadb-administration/ MariaDB Administration - MariaDB Knowledge Base ]'''
 +
*'''[https://mariadb.com/kb/en/library/applications-supporting-mariadb/ Applications Supporting MariaDB - MariaDB Knowledge Base ]'''
 +
*'''[https://www.devart.com/dbforge/mysql/studio/mariadb-gui-client.html MariaDB GUI - MariaDB Admin and Management Tools for Windows ]'''
 +
*'''[https://www.tutorialspoint.com/mariadb/mariadb_administration.htm MariaDB Administration ]'''
 +
 +
*'''[https://www.raspberrypi.org/forums/viewtopic.php?t=72076 MySql password? - Raspberry Pi Forums ]'''
 +
*'''[https://lb.raspberrypi.org/forums/viewtopic.php?f=91&t=196952 MySQL root password? - Raspberry Pi Forums ]'''
 +
*'''[http://www.raspberry-projects.com/pi/software_utilities/web-servers/mysql MySQL – Raspberry Pi Projects ]'''
 +
*'''[https://mariadb.com/kb/en/library/configuring-mariadb-with-mycnf/ Configuring MariaDB with my.cnf - MariaDB Knowledge Base ]'''
 +
*'''[https://www.cyberciti.biz/faq/tag/etcmysqlmariadb-conf-d50-server-cnf/ /etc/mysql/mariadb.conf.d/50-server.cnf HowTo - Linux / Unix Q & A from nixCraft ]'''
 +
*'''[https://mariadb.org/download/ Download - MariaDB.org ]'''
 +
*'''[https://en.wikipedia.org/wiki/MariaDB MariaDB - Wikipedia ]'''
 +
*'''[http://www.opencircuits.com/Pi_MySql#Possible_useful_sql_commands Pi MySql - OpenCircuits ]'''
  
  
 
[[Category:Arduino/RaspberryPi]]
 
[[Category:Arduino/RaspberryPi]]

Latest revision as of 08:10, 21 January 2018

next mysql 2018 streach update:[edit]

Well it may not be mysql any more it may be marinadb which is an open source version and therefor to be preferred by me. It is mostly compatible but does make some differences in configuration and other details.



crashed at end but seemed to work >>
sudo apt-get install mysql-server --fix-missing  

how do I get to this??>>
set password ****** same as for others

did not work>>
sudo apt-get install mysql-client php5-mysql

ran fine >>
sudo apt-get install mysql-workbench

mySql workbench now on menu for programming.

what is default pass and id ?? I finally found a solution. The answer is NO password is set. The best way to access mysql is to sudo su which makes you root. Then you can run the command mysql -u root -p and it will log you in. At this point your are command line connected to mysql.

now to change the password:

password at this point may be blank change?


looks good



Enable remote access[edit]

I find it is easiest to set up a user and root and then use sql-workbench, which right now is not working on the pi, remotely.


There are 2 parts to remote connection:

  • the listen on bind-address which is further confused by the cnf file name, I looked at and changed several.
  • and the grant of rights which also depends on the ip address that you are coming from.

this suggests where you might look for the cnf file ( why is it so complicated ) https://mariadb.com/kb/en/library/configuring-mariadb-with-mycnf/#location-in-linux-unix-mac

I browse all the .cnf files under /etc/mysql and its subs and changed them all this finally worked.

old[edit]

If your issue is not able to remotely connect with MySQL on Raspberry Pi, then try below steps. I had the same issue and got it resolved by performing below commands.

sudo leafpad /etc/mysql/my.cnf

# bind-address = 127.0.0.1 // comment this line out
 #add this line just below above line 
bind-address = 0.0.0.0         
//restart mysql
sudo /etc/init.d/mysql restart 

make non root user[edit]

This can be done in the workbench -- but workbench did not install correctly for me and when it does work it still complains about connecting. how do we allow them remote access and access to our db and table?

misc[edit]

Restarting The MySQL service

sudo service mysql restart

make db[edit]



schema   env_data_1


CREATE TABLE `env_data_table_1` (
  `gh_time` decimal(14,2) NOT NULL,
  `temp_1` float DEFAULT NULL,
  `temp_2` float DEFAULT NULL,
  `humid_1` float DEFAULT NULL,
  `humid_2` float DEFAULT NULL,
  `door_1` int(11) DEFAULT NULL,
  `door_2` int(11) DEFAULT NULL,
  `door_3` int(11) DEFAULT NULL,
  `door_4` int(11) DEFAULT NULL,
  `light` float DEFAULT NULL,
  PRIMARY KEY (`gh_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

for python[edit]

and that did it the pip now worked then

pip install pymysql 

Possible useful sql commands[edit]

To Restart MySQL/MariaDB server

Use the below commands to restart the MySQL/MariaDB server in Linux.

  1. For Sysvinit Systems #
  2. service mysql restart

or

  1. /etc/init.d/mysql restart
  1. For Systemd Systems #
  2. systemctl restart mariadb.service

or

  1. systemctl restart mysql.service

or

  1. systemctl restart mariadb

--- get on ----

mysql -u pi_user -p

   <password >
   use pi_db

create db and user[edit]


CREATE DATABASE pi_db;
With that database instance created successfully, I then created the MariaDB user with the relevant privileges for accessing that database instance:

 
CREATE USER 'pi_user'@'localhost' IDENTIFIED BY 'insert password';
GRANT ALL ON pi_db.* TO 'pi_user'@'localhost';   -- needs to have tcpip address in it for remote addess 
GRANT ALL PRIVILEGES ON example.* TO 'pi_user'@'%' IDENTIFIED BY 'the_password';   -- % is the tcip wild card  how about limit to 192 .... think this is correct

there is a flush command I have lost.

use mysql;
update user set password=PASSWORD("password here"), plugin = '' where User='root';
flush privileges;


sudo mariadb -u root -p  -- seemed to work

Links some useful[edit]