Editing Pi MySql

= next mysql =

2018 streach update:

<pre>

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

</pre>

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
* http://www.raspberry-projects.com/pi/software_utilities/web-servers/mysql




== Enable remote access ==
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 ===
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.
<pre>
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 
</pre>

* in 2018 i found this useful https://websiteforstudents.com/configure-remote-access-mysql-mariadb-databases/
* later might want to look at https://serverfault.com/questions/139323/mysql-bind-to-more-than-one-ip-address

== make non root user ==
This can be done in the workbench
how do we allow them remote access and access to our db and table?

== misc ==



Restarting The MySQL service
<pre>
sudo service mysql restart
</pre>

== make db ==
<pre>


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;
    

</pre>

== for python ==
and that did it the pip now worked then 

<pre>
pip install pymysql 
</pre>

= Possible useful sql commands =

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

# For Sysvinit Systems #
# service mysql restart
or
# /etc/init.d/mysql restart

# For Systemd Systems #
# systemctl restart mariadb.service
or
# systemctl restart mysql.service
or
# 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 =


*'''[https://mariadb.com/kb/en/library/configuring-mariadb-for-remote-client-access/ Configuring MariaDB for Remote Client Access - MariaDB Knowledge Base ]'''
*'''[https://stackoverflow.com/questions/21664091/mariadb-not-allowing-remote-connections mysql - MariaDB not allowing remote connections - Stack Overflow ]'''
*'''[https://mariadb.com/kb/en/library/configuring-mariadb-with-mycnf/#location-in-linux-unix-mac Configuring MariaDB with my.cnf - MariaDB Knowledge Base ]'''
*'''[https://howtoraspberrypi.com/enable-mysql-remote-connection-raspberry-pi/ How to Enable MySQL Remote Connection on Your Raspberry Pi ]'''


*'''[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]]