SQL DBA Blog

Category: MySQL

MySQL Blog Archives, by Peter Whyte (SQL Server Database Admin). Includes MySQL posts from a SQL Server DBA’s perspective!

Introducing DBASco: SQL Server Database Admin Blog
Greetings Tech People!
If you’ve been following my technical journey on peter-whyte.com, I’m excited to share a new platform that dives into the world of Database Reliability Engineering and beyond – welcome to DBASco.com!
At DBASco.com, I share my latest technical writings, focusing on a spectrum of topics catering to IT enthusiasts, database/system administrators, and those passionate about the software behind reliability engineering. The primary categories align with the ones you’re familiar with at peter-whyte.com, ensuring a seamless transition for any dedicated readers.
This site here is now my secondary tech blog site. If you’ve been on here before you will have seen a lot of display ads, which are disabled for the time being. I was using Ezioc, and I was earning around $50-60 per month, until I neglected things for too long during 2023 and there was a drop in earnings. The site certificate had issues during 2023 Q4, and we are back fully operational as of this week.
My enjoyment is the technical writing, refining, and seeing where tests lead me, exploring my passion for the things I do as a career and on the side. I hope you enjoy what I write, and it serves a purpose if visiting via Online Search to understand a concept or resolve an issue.
DBASco Blog Categories
DBASco: SQL Server: Explore Microsoft SQL Server with in-depth insights into queries, optimization techniques, and best practices. Uncover the holistic experience, including the realm of Windows Administration that intertwines seamlessly with the life of a SQL Server DBA.
DBASco: PowerShell: Dive into the enchanting world of PowerShell scripting with hands-on guides, expert tips, and powerful tricks. Unearth the magic of automation as you streamline your workflow with carefully crafted scripts and comprehensive guides.
DBASco: WSL and Linux: Journey into the world of Windows Subsystem for Linux (WSL) and Linux, discovering the synergy between these platforms and unraveling the potential for seamless integration. Benefit from real-world experiences and extensive testing, providing you with valuable insights into cross-OS platform development.
DBASco: Other: From RDMS’s like MySQL, Postgres, Redshift to semi-related technologies like AWS, and even technical guides on WordPress and website management – expect a wealth of knowledge that keeps you at the forefront of the ever-evolving tech landscape.
DBASco Latest Articles
RedShift: Script to Show Running Queries
Published on January 24, 2024
Uncover the power of Amazon Redshift, a cloud-based data warehousing giant. Learn how to efficiently manage and analyze extensive datasets with a detailed script to reveal running queries.
RedShift: Creating a User and Promoting to Superuser
Published on January 24, 2024
Unlock the secrets of Amazon RedShift as we guide you through creating a user and elevating their privileges to superuser status. Essential insights for effective cluster management.
PowerShell Script: List Files with Sizes and Dates
Published on January 23, 2024
Master a practical PowerShell script that simplifies listing files in a directory. Dive into efficient file management with sizes and dates at your fingertips.
Backing Up a Database in SQL Server Using Command
Published on January 22, 2024
Ensure the safety and integrity of your data with a comprehensive guide on backing up a database in SQL Server using command-line tools. A crucial aspect of database management unveiled.
SQL Server Script: Get Last Backup Dates
Published on January 22, 2024
Stay informed about the last backup times, a critical element in maintaining data integrity. Enhance your SQL Server skills with a script that simplifies tracking backup dates.
PowerShell: Counting Files, Words and Rows
Published on January 22, 2024
Embark on a PowerShell journey with a script that counts files, words, and rows. Discover the power of PowerShell for efficient data processing and analysis.
Adding a Filegroup in SQL Server
Published on January 21, 2024
Learn the process of adding a new filegroup to a database in SQL Server. Elevate your database architecture skills with a step-by-step guide.
January 25, 2024
Why Use WHERE 1=2 in SQL
Some time ago I wrote a blog post on why use WHERE 1=1 in SQL. This time it’s why use WHERE 1=2, but really this can be WHERE 1=9 or anything that isn’t a 1, we just want the false statement.
WHERE 1=1 is TRUE; has no difference on execution times and can be used for ease of adding/removing comments & commas on queries that you are building.
WHERE 1=2 is FLASE; is usually used for building the structure of a table by copying it from another, without copying any of the rows. It does not copy keys & constraints.
The following example SQL should help explain this, and the SQL Syntax used below can be run on any of the SQL Systems tagged in this post (MySQL, MS SQL, Redshift, Postgres & more).
The syntax will work on other SQL systems, however, the table names used in the SQL below will not be available on all platforms. Please amend SQL table names as necessary.
```
-- Create table copy from another table
CREATE TABLE infoschema_tables AS (SELECT * FROM REMOVEinformation_schema.tables WHERE 1=1);

-- Count rows of created table 
-- (not required in this psql terminal example as it shows row counts)
SELECT COUNT(*) FROM infoschema_tables;

-- Drop table
DROP TABLE infoschema_tables;

-- Create table copy from another table but do not copy rows
CREATE TABLE infoschema_tables AS (SELECT * FROM REMOVEinformation_schema.tables WHERE 1=2);

-- Count rows of created table
SELECT COUNT(*) FROM infoschema_tables;

-- Clean-up drop table
DROP TABLE infoschema_tables;
```
May 5, 2022
Why use WHERE 1=1 in SQL Queries
A common SQL question is, why would anyone use WHERE 1=1 in their queries? And what does it do?
The WHERE 1=1 condition means WHERE TRUE. It returns the same query result as it would without the WHERE Clause. There is no impact on query execution time.
This is something you can add to a SQL query for convenience for adding & replacing conditions on the statement. If you have WHERE 1=1 on a SQL query you are writing, all conditions thereafter will contain AND, so it’s easier when commenting out conditions on exploratory SQL queries.
Example: WHERE 1 Equals 1
```
/* where 1=1 example */
SELECT *
FROM TABLE
WHERE 1=1
--  and column1='blah'
    and column2='more_blah'
--  and column3 is not null
```
You can see this would be easier for commenting out WHERE conditions in the SQL ^
Example (Extra): Commenting Columns
This is similar to another SQL querying technique where you have commas before column names, rather than after the column name.
Again, this might work out well for you when commenting out columns on a work-in-progress SQL query.
```
SELECT
     Column1
--  ,Column2
    ,Column3
    ,Column4
--  ,Column5
FROM TABLE
```
Personally, I often make use of both of the above techniques when I write SQL queries.
Convenience is everything when working behind a computer. We have to keep exploring new short keys and any general ways of working to optimize our efficiency. Some things you might not like and won’t adopt, and sometimes GUI is better than CLI. It’s all personal preference, in the end, choose what you are most optimal with.
If you wonder what would happen if we change the WHERE 1=1 to WHERE 1=2, have a look at my other blog post – Why Use WHERE 1=2 in SQL
September 21, 2020
Creating a Linked Server with a MySQL Database
This is my 3^rd Linked Server demo post, and this time it’s to a MySQL database.
Noteworthy weblinks:
# My Linked Server Blog Posts.
# Microsoft Docs – Linked Servers.
Here is a simple diagram of the setup in this post.
The above was setup similar to what’s included in some of my previous posts:
# Install SQL Server on Windows.
# Install MySQL on Windows.
# Test Connectivity to Remote Servers.
Once the above is done, this post runs through the following steps to get a Linked Server configured:
MySQL
# Create MySQL Linked Server Login.
SQL Server
# Configure ODBC Data Source Settings.
# Create a Linked Server to a MySQL Database.
Create Linked Server Login on MySQL
As always, this kind of task can be done using a management tool like MySQL WorkBench or via command.
First up, I’ll get connected to MySQL via CLI.
CREATE USER and then GRANTING SELECT capabilities for all tables* within a ‘milk’ database. The first SQL steps here for me are to create the test database & table.
```
-- Create test database & table
CREATE DATABASE milk;
USE milk;
CREATE TABLE animal (id INT, type VARCHAR(50), breed VARCHAR(50), age DATE);
INSERT INTO animal (1,'cow','white with black spots','2009-10-06');
INSERT INTO animal VALUES (2,'cow','black with white spots','2011-12-16');

-- Create Linked Server user
CREATE USER 'linked_server_sql1'@'172.31.1.%' IDENTIFIED BY 'eCh0Ch4rl1E';
GRANT SELECT ON milk.* TO 'linked_server_sql1'@'172.31.1.%';
```
My connections are within the 172.31.1.0 network, so I’m adding in a wildcard (%) for all addresses on this range to be allowed to connect with this login.
If I want to test this new MySQL login locally, I can create the user using ‘localhost’ too. Below I’m opening the mysql.exe file rather than using the MySQL Command Line Client application which logs in as root.
Put on the User hat and do what you need to do.
I only wanted read-only access, which I can confirm with a quick write test.
That’s our Linked Server MySQL login ready!
The above can also be done with a tool like MySQL WorkBench too; run the same commands above in a query window or via the Users and Privileges window. The new user account below would be able to connect from any host address.
Configure ODBC Data Source Settings
The MySQL ODBC driver download can be found here. Download & install on the SQL Server host machine.
Note; at the time of writing this the most recent MySQL ODBC Driver version is 8.0.20. I’ve had to install version 8.0.18 as the 2 most recent versions were showing an error message on install.
Run ODBC Data Sources as Administrator.
Click into the System DSN tab and click Add.
Select the MySQL ODBC Unicode driver.
Enter all details required as shown. If you can see the database names the connection is all good so click OK.
Create Linked Server to a MySQL Database
Within SSMS; expand Server Objects, right-click Linked Servers and select New Linked Server.
The Linked server name at the top of the next window is what you’ll need to include in queries, so choose that wisely.
Then choose a provider for ODBC and enter the Data Source name which you named within the ODBC driver settings above.
Enter the MySQL login details as created above within this post.
I’m leaving all Server Options as defaults.
Before I hit OK I’m scripting this out to code.
```
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
	@server = N'MYSQL_SERVER_1', 
	@srvproduct=N'', 
	@provider=N'MSDASQL', 
	@datasrc=N'MySQL_Linked_Server'

USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin 
	@rmtsrvname = N'MYSQL_SERVER_1', 
	@locallogin = NULL , 
	@useself = N'False', 
	@rmtuser = N'linked_server_sql1', 
	@rmtpassword = N'eCh0Ch4rl1E'
GO
```
When ready, take a breath, then hit OK or execute the SQL.
Have a look in the SSMS Object Explorer to view the new Linked Server. We should be able to drill into the database & objects we have permission to.
Now run queries to our MySQL Server using OPENQUERY.
July 15, 2020
How to Create MySQL Databases & Tables
This blog post is a guide on how to create databases and tables in MySQL, from a Windows and Linux computer. The information included here should help beginners who are new to MySQL.
To create a new database in MySQL, and many other relational database systems we use the CREATE DATABASE SQL Statement.
The meaning behind the word ‘database’ can vary, and often be referred to the same thing as a Schema. In MySQL, Databases and Schemas are both one of the same things.
The following MySQL tips are covered in this post:
# Create a MySQL Database
# The USE DATABASE Command
# Close MySQL Queries with Semicolons
# How to Exit from MySQL CLI
Create a MySQL Database
We can create a MySQL Database by running CREATE DATABASE databaseName
The create database example below is being done using the GUI option, MySQL WorkBench. For more information on SQL syntax and parameters of the CREATE DATABASE statement in MySQL, we can refer to the MySQL Dev Docs.
After running the above, refresh Schemas within the Navigator to the left and open the new database.
If you’re instance running on Linux, database names are case-sensitive, whereas on Windows this isn’t an issue. Below, I’m creating a database on a local MySQL install (Windows 10) and then creating a MySQL table within it using a Capital letter on the schema/database name.
Below is an example of doing the same on Linux using the MySQL Command-Line Client.
The above is us logging in with the ec2-user to an Amazon Linux EC2 instance. Then we create a new table using a Capital letter on the database schema name.
A common best practice is to stick to the one case for object naming in SQL for this reason. For example, use finance_database instead of Finance_Database, or use FINANCE_DATABASE.
The USE DATABASE Command
The USE DATABASE command in SQL is used to set your statements to use the specified database as your default (current) database.
To show an example of USE in SQL, I create a new database and table MySQL Workbench below. The CREATE TABLE and INSERT INTO statements include the schema name (butter), and then I attempt to run a SELECT statement on the table without including the schema name.
The returns an error – Error Code: 1046. No database selected. Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar.
As described in this error message, we can set our default schema in the MySQL Workbench Navigator.
That’s one solution. The other is to use the USE DATABASE statement. I’m adding USE butter; to the TSQL in the MySQL Workbench query window.
All queries executed after running the USE command will assume the database specified when running queries without a schema name.
Close MySQL Queries with Semicolons
Semicolons are a way to determine when your SQL statement ends in MySQL.
A query does not run by hitting return in a MySQL terminal, it requires a semicolon to know when the SQL statement is complete.
The screenshot below shows a Linux client connected to MySQL. The first command includes a semicolon and returns a list of databases. The second command (DROP DATABASE) displays the example of a command not being executed.
The command does not run until we add a semicolon and hit return, as marked with a red cross on the screenshot.
To further help understand semicolons in MySQL I also will show an example in MySQL Workbench. I’m creating a new table and inserting a row of data into the table on the next row.
The CREATE TABLE statement returns an error message – Error Code: 1064: You have an error in your SQL syntax; check the manual that the corresponds to your MySQL...
The table did not get created here. If I add the semicolon at the end of the CREATE TABLE line the table will get created and a row will be inserted.
If you’re running queries line-by-line then the semicolon isn’t as much required. For more information, see this MySQL documentation link.
How to Exit MySQL CLI
This part shows you how to bug out of MySQL CLI.
To exit MySQL CLI, type QUIT and hit return in your terminal window.
October 8, 2019
Installing MySQL 8.0 on Windows
A simple run-through of installing MySQL Community Server 8.0, including MySQL Workbench on Windows.
Don’t forget to checkout the MySQL Installation documentation, and here’s a link to prerequisites.
Installing MySQL
Open the MySQL installer exe as downloaded from the link above.
Accept License Agreement and Next.
I would say always choose a Custom install – only ever install what you know you need.
I’m installing MySQL Server, MySQL Workbench and the ODBC Driver.
You may have an additional step to install Microsoft Visual C++ Redistributable packages – execute those within the wizard when prompted.
Hit Exectute to install all features selected.
Now it’s time to configure the MySQL Server, starting off with High Availability which I don’t need on my local test environment here.
On the next page, the Config Type is Development Computer, although here’s the options for reference;
No need to deviate from the default port number or other config here.
Unless you have MySQL 5.x Servers in your stack, choose the recommended authentication method.
Enter the MySQL Root Password – we can create new users later if required.
This is where we select which account the MySQL Service will run as.
Now we’re ready for the configuration update – hit Execute.
We can have a browse in the log file to view what’s actually going on in the background.
All is now complete!
MySQL Workbench
Click to add a new MySQL Connection.
Enter a name for the connection and no need to change connection details as this is all local on the default port.
Click to Test Connection and enter the root user password as entered during installation.
Once the above is OK’d, this new connection will show up in the Workbench connections area – click to connect.
And I’ll now a random query to finish this off…
October 3, 2019