Showing entries 1 to 10 of 14
4 Older Entries »
Displaying posts with tag: mysql client (reset)
MySQL macOS Docker

While you can download MySQL as a DMG package, a number of users would prefer to install it as a Docker instance. You won’t find the macOS downloads on the same web site as other downloads. You can use the following macOS download site.

After installing Docker on your macOS, you can pull a copy of the current MySQL Server with the following command:

docker pull mysql/mysql-server

You should create a mysql directory inside your ~/Documents directory with this command:

mkdir ~/Documents/mysql

Then, you should use the cd command to change into the ~/Documents/mysql directory and run this command:

pwd

It should return the following directory: …

[Read more]
MySQL sakila Database

While I thought my instructions were clear, it appears there should have been more in my examples for using the MySQL MSI. A key thing that happened is that students opted not to install:

Samples and Examples 8.0.22

Unfortunately, they may not have read the Preface of Alan Beaulieu’s Learning SQL, 3rd Edition where he explains how to manually download the files from the MySQL web site. Here are those, very clear, instructions (pg. XV) with my additions in italics for the MySQL Shell:

First, you will need to launch the mysql command-line client or the mysqlsh command-line shell, and provide a password, and then perform the following steps:

  1. Go to https://dev.mysql.com/doc/index-other.html and download the files for the …
[Read more]
MySQL Self-Join

I’m switching to MySQL and leveraging Alan Beaulieu’s Learning SQL as a supporting reference for my Database Design and Development course. While reviewing Alan’s Chapter 5: Querying Multiple Tables, I found his coverage of using self-joins minimal.

In fact, he adds a prequel_film_id column to the film table in the sakila database and then a single row to demonstrate a minimal self-join query. I wanted to show them how to view a series of rows interconnected by a self-join, like the following:

SELECT   f.title AS film
,        fp.title AS prequel
FROM     film f LEFT JOIN film fp
ON       f.prequel_id = fp.film_id
WHERE    f.series_name = 'Harry Potter'
AND      fp.series_name = 'Harry Potter'
ORDER BY f.series_number;

It returns the following result set:

[Read more]
MySQL Client in 8.0.21+

Having recently installed a fresh copy of MySQL 8.0.21 on Windows, I took careful note of parsing changes in the MySQL Shell. It was tedious that we lost multiple statement processing, which is supported in the MySQL Client and MySQL Workbench because it uses MySQL Client.

It was frustrating when I subsequently discovered that the MySQL Shell took away the ability to write log files by removing the TEE and NOTEE commands. I suspected that since MySQL Workbench was still using the MySQL Client that it should be in the code tree. In fact, the mysql.exe client is in this directory:

C:\Program Files\MySQL\MySQL Server 8.0\bin

So, I immediately created a batch file to put the MySQL Client into my %PATH% environment variable when needed. I used this time tested DOS command: …

[Read more]
How to Setup a WordPress MySQL Database in the Cloud

WordPress is the largest website builder platform in the world, supporting over 34% of all websites on the internet today. MySQL is a free open source relational database management system that is leveraged across a majority of WordPress sites, and allows you to query your data such as posts, pages, images, user profiles, and more. As any WordPress developer knows, each installation requires a database in the backend, and MySQL is the database of choice for storing and retrieving your WordPress data.

In order for your WordPress website to be able to access, store and retrieve the data in your MySQL database, it needs to be hosted online through a cloud computing service. ScaleGrid offers a convenient way to setup and configure MySQL hosting for your …

[Read more]
Configuring and Managing SSL On Your MySQL Server

In this blog post, we review some of the important aspects of configuring and managing SSL in MySQL hosting. These would include the default configuration, disabling SSL, and enabling and enforcing SSL on a MySQL server. Our observations are based on the community version of MySQL 5.7.21.

Default SSL Configuration in MySQL

By default, MySQL server always installs and enables SSL configuration. However, it is not enforced that clients connect using SSL. Clients can choose to connect with or without SSL as the server allows both types of connections. Let’s see how to verify this default behavior of MySQL server.

When SSL is installed and enabled on MySQL server by default, we will typically see the following:

  1. Presence of *.pem files in the MySQL data directory. These are the various client and server certificates and keys that are in …
[Read more]
The mysql client, and some improvements

The mysql client is a tool which I use every day as a DBA. I think it's a great tool. When I used a client of several other SQL and NoSQL databases I was quickly reminded of all the features of the mysql client. Note that psql (PostgreSQL client) is also very nice.

Some other interesting things about the mysql client: It is build from the same mysql-server repository as MySQL Server. The source is in client/mysql.cc. In addition to the server version it also reports 14.14 as its version. The previous version (14.13) was around the time of MySQL 5.1, so this version is mostly meaningless.
If you start it it identifies itself as "MySQL monitor", not to be confused with MySQL Enterprise Monitor.
The version of the client is not tightly coupled with the …

[Read more]
Encrypt your –defaults-file

Encrypt your credentials using GPG

This blog post will look how to use encryption to secure your database credentials.

In the recent blog post Use MySQL Shell Securely from Bash, there are some good examples of how you might avoid using a ~/.my.cnf – but you still need to put that password down on disk in the script. MySQL 5.6.6 and later introduced the  –login-path option, which is a handy way to store per-connection entries and keep the credentials in an encrypted format. This is a great improvement, but as shown in Get MySQL Passwords in Plain Text from .mylogin.cnf, …

[Read more]
Use of DECLARE and SET in MySQL stored program

DELIMITER $$                    -- Change the delimiter

DROP PROCEDURE IF EXISTS demo$$ -- Remove if previously exists
CREATE PROCEDURE demo()         -- Create Stored Procedure
BEGIN                           -- Start of definition
    DECLARE my_int INT;         -- Declare variables
    DECLARE my_big_int BIGINT;
    DECLARE my_text TEXT;

    DECLARE my_name VARCHAR(30) -- Declare variables with 
        DEFAULT 'Rookie Dev';   -- default value set

    SET my_int = 20;            -- Set variables to values

[Read more]
How to execute mysql query from a file in your mysql client terminal?

Being a terminal fan myself, I usually find myself running queries in the mysql client instead of a UI interface as it is much faster. You get to see the results instantaneously.

One thing which is pretty tedious is editing a big query again after once running it as the whole multi-line formatted query now appears on a single line, thus reducing its readability.

But no problems, you can edit your query from a file and run the file from your mysql client terminal as many times as you want with as many edits.

To do so, follow the below steps:

1. Open your terminal and cd into the folder you want to store our sample mysql file. Then save your query in a sample file called my_query.sql

$ cd /path/to/folder
$ vim my_query.sql

Save a sample query like:

SELECT * FROM employees
WHERE type LIKE …

[Read more]
Showing entries 1 to 10 of 14
4 Older Entries »