In this article, we will see the steps to create a snapshot of the existing RDS MySql Instance. Then we will see the steps to restore the snapshot which creates a new RDS Instance. At last, we will clean up the instances and snapshots.
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]
While I’m switching labs next term after more than a decade with more comprehensive lab set, I’m hoping the new exercises build the students’ core SQL skill set. Next term, I hope to see whether the change is successful. I’ve opted for using Alan Beaulieu’s Learning SQL: Generate, Manipulate, and Retrieve Data, 3rd Edition, because it’s a great book and uses the MySQL database.
One exercise that the students will lose is a data migration
exercise from a badly designed common_lookup
table
to a well designed common_lookup
table. The starting
point is shown below on the left and the fixed version is on the
right.
It’s always interesting when I upgrade from one release to the next. I learn new things, and in the case of MySQL’s installation and maintenance I become more grateful for the great team of developers working to produce MySQL 8.
A warning that caught my eye in MySQL 8 (8.0.21) was this one on
Unicode with the utf8
character code:
Warning (code 3719): 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
Currently, a character alias for utf8mb3
is
an alias for the deprecated utf8mb3
(a 3-byte
character set) until it is removed. When the
utf8mb3
character set is …
One of the questions you often will be faced with operating a Linux-based system is managing memory budget. If a program uses more memory than available you may get swapping to happen, oftentimes with a terrible performance impact, or have Out of Memory (OOM) Killer activated, killing process altogether.
Before adjusting memory usage, either by configuration, optimization, or just managing the load, it helps to know how much memory a given program really uses.
If your system runs essentially a single user program (there is always a bunch of system processes) it is easy. For example, if I run a dedicated MySQL server on a system with 128GB of RAM I can use “used” as a good proxy of what is used and “available” as what can still be used.
root@rocky:/mnt/data2/mysql# free -h …[Read more]
A student question: Does JavaScript make context switching for web-based applications obsolete? Wow! I asked what that meant. He said, it means JavaScript replaces all other server-side programming languages, like PHP, C#, or Python. I asked the student why he believed that. His answer was that’s what two interviewing managers told him.
I thought it would be interesting to put the idea to a test.
Below is a Node.js script that acts as a utility that
queries the MySQL database with substitution variables in query.
It also returns a standard out (stdout
) stream of
the MySQL query’s results. It also supports three flag and value
pairs as arguments, and optionally writes the results of the
MySQL query to a log file while still returning result as the
stdout
value. All errors are written to the standard
error (stderr
) stream.
The Node.js solution is completely portable between Windows …
[Read more]Sometimes, you just half to chuckle. A couple folks felt that I didn’t give enough information in my post showing how to configure a small Node.js application that could access a MySQL database. Specifically, they wanted me to explain the following:
- Configure your Express.js and MySQL development in a single Node.js application.
- How to convert the list of
RowDataPacket
objects as elements of data, which is really just simple JavaScript knowledge. - How to bind variables into the query.
Like the other blog post, this one assumes you’ve performed a global install of Node.js on a Linux server. If you’re unfamiliar with how to perform a global Node.js installation, I cover how to do it in this …
[Read more]These are my notes for creating a small Node.js application that queries a MySQL database. The post will show you how to:
- Configure your Node.js development directory.
- Build a small application to test a MySQL connection.
- Build a small application that connects to the MySQL database and queries data.
This blog post assumes you’ve performed a global install of Node.js on a Linux server. If you’re unfamiliar with how to perform a global Node.js installation, I cover how to do it in this earlier blog post.
Before you write the Node.js applicaiton, you need to setup a
db
developer directory. Then, create a
node_modules
symbolic link to the
/usr/local/lib/node_modules
directory in the
db
directory. You can use the following command …
When trying to do some Linux tuning for MySQL, there are a few options that will greatly influence the speed of MySQL. Below are some of the most important of these settings to help you get started.
Swappiness
The first thing to look at is what swappiness is set to. This will determine the tendency of the kernel to swap out memory pages. In may cases, you will want to set this to “1” to keep the swapping to a minimum. A value of “0” will disable it entirely.
You can determine the current value with the following command:
cat /proc/sys/vm/swappiness
If this is not set to “1”, you should consider making the change by using one of the following options:
# Make sure you are root and set swappiness to 1 echo 1 > /proc/sys/vm/swappiness # Or, you can use sysctl to do the same sysctl vm.swappiness vm.swappiness = 1
If the change helps, you will want to …
[Read more]
In April, when I updated from MySQL 8.0.17 to MySQL 8.0.19, I
found that my Java connection example failed. That’s because of a
change in the JDBC driver, which I blogged about then. Starting yesterday, I
began updating a base Fedora 30 configuration again to MySQL
8.0.20. I wrote a testing program for the Java JDBC file last
time, and when I ran it this time it told me that I didn’t have
the JDBC driver installed, or in the $CLASSPATH
. My
Java diagnostic script, MySQLDriver.java
, returned
the following error message:
Error: Could not find or load main class MySQLDriver
The Java JDBC test program code is in the prior post. It simply loads the user, password, database, host, and port statically for my student …
[Read more]