New Years Eve 2020

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

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

mySQL: Basic aggregates from a table

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

mySQL	Explanation	SQL 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;

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');

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.

NORAD tracks Santa Claus

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

https://www.noradsanta.org/

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

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

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

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

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

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

1.4.1 Dumping Data in SQL Format with mysqldump

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.

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".

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.

mySQL: How to create a local user?

 How to create a local user?

mySQL	Explanation	SQL Server
CREATE USER 'username'@'localhost' IDENTIFIED BY 'passwordstring';	Create a new user, local to the server machine	CREATE 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]

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

mySQL: How do I restart mySQL server?

 How do I restart mySQL server?

mySQL	Explanation	SQL 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.

mySQL: What IP are you listening on?

 What IP address is mySQL listening on?

mySQL	Explination	SQL Server
From a command line: mysql -u username -p -e "SHOW GLOBAL VARIABLES like 'bind_address'"	Displays the IP the server is listening on	You 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>'

mySQL: Get a table's structure

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

 

mySQL	Explanation	SQL Server
desc mysql.user;	Gets the information for the table mentioned.	exec sp_help 'sys.syslogins';

mySQL: Find users

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

mySQL	Explination	SQL Server
select * from mysql.user;	Gets the list of users in the server	select * 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