Thursday, December 31, 2020

New Years Eve 2020

 Wherever you are, and how ever you're celebrating, I hope you have a happy and safe New Years Eve!



Wednesday, December 30, 2020

mySQL: Sample Databases

 How do I load customer agnostic sample databases into mySQL?


The issue with demos and/or general purpose query learning is that it requires data. Sometimes, different types of data.


For example, for general purpose query learning, the enron dataset is amazing. Especially for windowing functions, too, where having a large number of rows in a single table is what you want. I found it here:

http://www.ahschulz.de/enron-email-data/


However, for other purposes, especially those with more of a business flavor such as aggregates and groups, something like the Northwind database is ideal.

The GitHub user jpwhite has published the Northwind sample database as a set of mySQL scripts.

https://github.com/jpwhite3/northwind-MySQL


Tuesday, December 29, 2020

mySQL: Basic aggregates from a table

In mySQL, how do I get the basic aggregates from a table? 



mySQLExplanationSQL Server
use enron;
select COUNT(*) as counted from recipientinfo;
select MIN(rid) as rid_min from recipientinfo;
select MAX(rid) as rid_max from recipientinfo;
select SUM(rid/10.0) as rid_summed from recipientinfo;
select AVG(rid/10.0) as rid_avgd from recipientinfo;
No real surprises here - they work the same in both places. use Enron;
go
select COUNT(*) as counted from dbo.recipientinfo;
select MIN(rid) as rid_min from dbo.recipientinfo;
select MAX(rid) as rid_max from dbo.recipientinfo;
select SUM(rid/10.0) as rid_summed from dbo.recipientinfo;
select AVG(rid/10.0) as rid_avgd from dbo.recipientinfo;

Monday, December 28, 2020

mySQL: How do I find the number of rows in my table

 How do I find the number of rows in my table?


In mySQL:


select count(*) from enron.message;

or

show table status;



SQL Server:


use enron;

go

select count(*) from dbo.message;

or

use enron;

go

select * from sys.partitions where object_id = OBJECT_ID('dbo.message');


Friday, December 25, 2020

Merry Christmas!

 It's Christmas - on a Friday :)


Merry Christmas. I hope you and your family are safe and healthy at this time of year. 


Especially this year.



Thursday, December 24, 2020

NORAD tracks Santa Claus

 This very important community service is brought to us by NORAD.


https://www.noradsanta.org/




Wednesday, December 23, 2020

Ubuntu: How do I get the total free space on my drive?

 In Ubuntu, how do I get the total free space on my drive?


sudo df -h --total





Tuesday, December 22, 2020

DBCC Page and DBCC IND

The tools DBCC Page and DBCC IND are undocumented.


People smarter than me about these tools have written a ton about this. For example:

https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-using-dbcc-page-and-dbcc-ind-to-find-out-if-page-splits-ever-roll-back/


General syntax:

DBCC IND (‘DBName’ or DBID, ‘TableName’ or ObjectId, NOn Clustred Index ID)

and

DBCC PAGE ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])


So, using the Enron database, we would have something like this:

DBCC IND ('Enron', 'message', 1);

GO

DBCC TRACEON (3604);

GO

DBCC PAGE (Enron, 1, 320, 3);

GO

DBCC TRACEOFF (3604);

GO

Monday, December 21, 2020

mySQL: Get status of slaves in replication

How do I get the status of my slaves in replication?

 


SHOW SLAVE HOSTS 

    Run on the master


SHOW STATUS


SHOW SLAVE STATUS 

    Run on each slave


SHOW PROCESSLIST 

    Look for "Command: Binlog Dump"


From the Checking Replication Status page


Friday, December 18, 2020

mySQL: Raspberry Pi based mySQL server

How to build a stand alone mySQL server on a RaspberryPi?


Although, technically, it's going to be MariaDB, but still, I personally found this tutorial really interesting. 

Seems like not only a good way to use a RaspberryPi for something when you're not streaming media to your TV from it, but also to learn.

Couple this with the Enron mySQL database, and it could be a pretty good playground for learning SQL.


Setup a Raspberry Pi MYSQL Database

Thursday, December 17, 2020

mySQL: How to restore a full backup of a database

How to restore a full backup of a mySQL database?


mysql -u [user] -p [database_name] < [filename].sql


From here:

https://dev.mysql.com/doc/refman/8.0/en/reloading-sql-format-dumps.html


Wednesday, December 16, 2020

mySQL: How to take a full backup of a database

How to take a full backup of a database? 


/* Will create the CREATE DATABASE statement*/

mysqldump -u [username] -p[password] --databases [database_name] > [dump_file.sql]

OR

/* Will NOT create the CREATE DATABASE statement*/

mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]


From the page


Tuesday, December 15, 2020

mySQL: Open the firewall for access

How to open the firewall for access to the mySQL database server?

Allow connections to MySQL from any IP address with: 

sudo ufw allow 3306

As always, know the firewall rules you need when performing on production.


Monday, December 14, 2020

mySQL: Give user access to everything

How to give a user access to everything in mySQL?

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';


And in SQL Server:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [SQLLoginName]


Know what you need, and investigate alternatives, before "Granting The World".

Friday, December 11, 2020

mySQL: Don't log on as root

 Don't log on as root.


That's it. Just what it says on the tin. Make a user instead.


Thursday, December 10, 2020

mySQL: How to create a local user?

 How to create a local user?

mySQLExplanationSQL Server
CREATE USER 'username'@'localhost' IDENTIFIED BY 'passwordstring';Create a new user, local to the server machineCREATE LOGIN username WITH PASSWORD=N'passwordstring', DEFAULT_DATABASE=[master];

In my experience, if you want a login specific to only the local machine, you would create a SQL Server login, and then connect to the local instance of SQL Server using SQLCMD or OSQL, which is what I've scripted here.

In thinking about this, I suppose you could create a local machine user, and then create a SQL login for that local user. However, as logins for that login would be exclusively from the local machine, the only use cases I can think of for this approach would be either a) development scenarios, or b) embedded systems.

The syntax for that would look like

CREATE LOGIN [machinename\username] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]



Wednesday, December 9, 2020

Remakes I Like

 I keep losing these, so I want to collect them all in one non-playlist place.

Song Original Artist Remake Artist YouTube Link
Take Me To Church Hozier Leo Link
The Sound Of Silence Simon & Garfunkel Disturbed Link
Adagio For Strings Samuel Barber DJ Tiesto Link

Tuesday, December 8, 2020

mySQL: How do I restart mySQL server?

 How do I restart mySQL server?


mySQLExplanationSQL Server
(Ubuntu)
# /etc/init.d/mysql restart

OR

# systemctl restart mysql

OR

(RedHat / CentOS / Debian)
# /etc/init.d/mysqld restart

OR

# systemctl restart mysqld

OR

(FreeBSD)
# /usr/local/etc/rc.d/mysql-server restart

OR

# service mysql-server restart

Restart the relevant service On the console of the machine:

net stop mssqlserver

 

Named instance?

net stop mssql$instancename.




Monday, December 7, 2020

mySQL: What IP are you listening on?

 What IP address is mySQL listening on?

mySQLExplinationSQL Server
From a command line:
mysql -u username -p -e "SHOW GLOBAL VARIABLES like 'bind_address'"
Displays the IP the server is listening onYou can use SQL Server Configuration Manager to make changes to the networking stack
OR
If you can get a connection to the server, you can what it's already listening to/on/for:

SELECT distinct local_tcp_port,net_transport,protocol_type,auth_scheme FROM sys.dm_exec_connections where client_net_address <> '<local machine>'





Friday, December 4, 2020

mySQL: Get a table's structure

How do I find out all the columns in my table?

 

mySQLExplanationSQL Server
desc mysql.user;

Gets the information for the table mentioned. exec sp_help 'sys.syslogins';



Thursday, December 3, 2020

mySQL: Find users

 How do I find the list of users in my server?

mySQLExplinationSQL Server
select * from mysql.user;

Gets the list of users in the serverselect * from sys.syslogins;

** Don't forget to end your mySQL query with "\G" if running from the command line, as the user table is wide