Mysql

Which version is running from Bash?

$ mysql -V
mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using  EditLine wrapper

To access the MySQL Remote Monitor, as the user (-u) “mcmillan”, call that user’s password (-p), then choose the db named “mcmillan”:

$ mysql -u mcmillan -p
Enter password: 
$ mysql>

$ mysql.server start $ mysql.server stop

$ mysql -u root -p

DB connect file

vi dbconnect.php

then put this in it:

<?php // set database server access variables: $host = “localhost”; $user = “mcmillan”; $pass = “guessm3”; $db = “mcmillan”;

// open connection $connection = mysql_connect($host, $user, $pass) or die (“Unable to connect!”);

// select database mysql_select_db($db) or die (“Unable to select database!”);

Sample HTML Form with Call to DB

vi mysqltest.php

"; // Get number of columns, their names, and put into table heads $num_fields = mysql_num_fields($result); echo ""; for ($i=0; $i < $num_fields; $i++) { echo ''.mysql_field_name($result, $i).''; } echo ""; // Fill table rows with data from columns while($row = mysql_fetch_array($result)) { echo ""; echo "".$row['id'].""; echo "".$row['name'].""; echo "".$row['building'].""; echo "".$row['locationcode'].""; echo "".$row['floor'].""; echo "".$row['seating'].""; echo "".$row['capacity'].""; echo "".$row['bookable'].""; echo "".$row['roomtype'].""; echo "".$row['displaytype'].""; echo "".$row['phone'].""; echo "".$row['phonetype'].""; echo "".$row['devicecontrol'].""; echo "".$row['other'].""; echo "Edit"; echo ""; } echo ""; // free result set memory mysql_free_result($result); // close connection mysql_close($connection); ?>

Sample sql queries

select * from artist; use music; select database(); show tables; show columns from artist; select * from artist; select artist_name from artist; select * from artist where artist_name = “New Order”; select artist_name from artist where artist_id = 4; select artist_name from artist where artist_id < ‘M’; select album_name from album where -> album_name > “C” and album_name < “M”; select artist_name, album_name from artist inner join album -> using (artist_id); insert into artist values (7, “Barry Adamson”); delete from played; update artist set artist_name = upper(artist_name);

Import an entire database from an external file

mysql> source /Users/gmcmilla/webDev/mysql/music.sql Query OK, 0 rows affected, 1 warning (0.01 sec) Query OK, 1 row affected (0.00 sec) …

GUI mysql management tool

http://dev.mysql.com/downloads/workbench/

Start workbench
Database > Manage connections > New > name it “local mysql”
Connection Method: Local Socket/Pipe (for local db)
Test connection
Go back to dashboard
Database > query database > choose “local mysql”
SQL Editor displays the DBs available

Install

$ pkg install mysql57-server

which installed these two packages:

mysql57-server: 5.7.10_3
mysql57-client: 5.7.10_3

To enable MySQL server as a service, add mysql_enable=”YES” to the /etc/rc.conf file.

Start/stop/restart mysql:

$ service mysql-server start
$ service mysql-server stop
$ service mysql-server restart

$ mysql -u root -p

Change root’s password from inside the interpreter:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('guessm3');

Node.js. See docs/nodejs.

Python connector: http://stackoverflow.com/questions/372885/how-do-i-connect-to-a-mysql-database-in-python

Ember Data connector: http://stackoverflow.com/questions/22293741/ember-js-with-mysql-connection

Remember to run mysql_upgrade the first time you start the MySQL server after an upgrade from an earlier version.

Initial password for first time use of MySQL is saved in $HOME/.mysql_secret ie. when you want to use “mysql -u root -p” first you should see password in /root/.mysql_secret

Monitor login