### Database MySQL

Hints related to MySQL on the Raspberry PI (RPi).
Reference: http://dev.mysql.com
Install MySQL
Get all packages up-to-date on the RPi
sudo apt-get update && sudo apt-get upgrade
Note: Consider to do a distribute upgrade:
sudo apt-get dist-upgrade
Install MySQL Server
sudo apt-get install mysql-server --fix-missing
Note: A new root password will be requested via dialog.
Install MySQL Client and PHP
sudo apt-get install mysql-client php5-mysql
Thereafter reboot the RPi.
MySQL commands under Rasbian-jessie:
sudo systemctl start mysql
sudo systemctl status mysql
sudo systemctl stop mysql
Create Database Example
Login MySQL as user root
mysql -uroot -hlocalhost -p
msql>ENTER PASSWORD
msql>CREATE DATABASE notes;
msql>CREATE USER 'notesuser'@'localhost' IDENTIFIED BY 'nu';
msql>GRANT ALL PRIVILEGES ON notes.* TO 'notesuser'@'localhost';
msql>FLUSH PRIVILEGES;
The MySQL database 'notes' with user 'notesuser' has been created.
To test the new user notesuser:
Exit or Ctrl+C to exit current session and login as notesuser
mysql -unotesuser -hlocalhost notes -p
msql>ENTER PASSWORD
Note:
The password is 'nu' as set previous in the CREATE USER command.
Create Table
See category MySQL > Full Example Create Table.
Create Table Example with Autoincrement
Login as user 'notesuser'
mysql -unotesuser -hlocalhost notes -p
Create Table
msql>CREATE TABLE notes (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(50),
content VARCHAR(255)
);
Note:
To ensure mandatory value for a column use NOT NULL: description VARCHAR(50) NOT NULL
To set a default value for a column use DEFAULT: description VARCHAR(50) NOT NULL DEFAULT 'Description'
Insert Records
mysql>INSERT INTO notes(description,content) VALUES ('Note 1', 'The content of note 1.');
mysql>INSERT INTO notes(description,content) VALUES ('Note 2', 'The content of note 2.');
Select Records
mysql>SELECT * FROM notes;
Update Record
mysql>UPDATE notes
SET content='The updated content of note 1.'
WHERE id=1;
Create Table Example with Autoincrement and Timestamp
Login as user 'notesuser'
mysql -unotesuser -hlocalhost notes -p
Create Table
mysql>CREATE TABLE notes (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(50),
content VARCHAR(255),
created TIMESTAMP(8)
);
Note:
To get a Timestamp with default NOW:
Change
created TIMESTAMP(8)
To
created TIMESTAMP DEFAULT NOW()
The full create statement:
mysql>CREATE TABLE notes (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,description VARCHAR(50),content VARCHAR(255),created TIMESTAMP DEFAULT NOW());
Insert Records
mysql>INSERT INTO notes(description,content) VALUES ('Note 1', 'The content of note 1.');
mysql>INSERT INTO notes(description,content) VALUES ('Note 2', 'The content of note 2.');
Select Records
mysql>SELECT * FROM notes;
Viewing Table definition
Login as user 'notesuser'
Basic Information
mysql>DESC notes;
Detailed Information
mysql>SHOW CREATE TABLE notes;
Remote Database Access
See details under Category MySQL > Connect to MySQL Remote DB /2
Important is to Grant Remote Access.
Here an example for Server (IP 192.168.0.16) and Client (IP 192.168.0.8).
Setup Remote Access on the RPi
Define in /etc/mysql/my.cnf the "bind-address" and set to 192.168.0.16
Note: use $sudo nano /etc/mysql/my.cnf to make the changes to the file)
Reload & restart the MySQL service:
sudo service mysql reload
sudo service mysql restart
Grant MySQL Access on the RPi
Login to MySQL as root and grant access to the users root and notesuser for the databases and tables.
This is done by telling MySQL the username and the remote IP Address.
Allow access from a fix IP address:
mysql -uroot -hlocalhost -p
mysql> GRANT ALL ON *.* To root@'192.168.0.8' IDENTIFIED BY 'YOUR ROOT PASSWORD';
mysql> flush privileges;
mysql> GRANT ALL ON *.* To notesuser@'192.168.0.8' IDENTIFIED BY 'YOUR USER PASSWORD';
mysql> flush privileges;
Allow access from any IP address
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOURUSERNAME'@'%' IDENTIFIED BY 'YOUR USERPASSWORD';
mysql> flush privileges;
Example for root and the notesuser:
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'YOUR USERPASSWORD';
mysql>flush privileges;
mysql>GRANT ALL PRIVILEGES ON *.* TO 'notesuser'@'%' IDENTIFIED BY 'nu';
mysql>flush privileges;

Changed: 2018-03-26 13:06, Category: Raspberry Pi, File: T6C234M1710.htm, ID: 1710