Online schema changes with MySQL
New Scheme
© KIKOO KIKOO, 123RF.com
Making online schema changes to your database can be tricky, but the right tools can help. We show how to change even the largest tables in an orderly and efficient manner.
From your smartphone to your banking institution, relational databases are everywhere. They store many kinds of data for us – and they store it in a specific, well-defined format. Unlike many of their newer "NoSQL" brethren, relational databases have a fixed schema – they don't accept arbitrary data. In theory, a smart designer will make entirely correct schema choices, which will then be put into production with no changes or alterations needed, because everything was perfect in the first place.
In practice, though, relational databases often go through many changes. For example, it is often found that mistakes were made in the initial schema. These mistakes need to be corrected and, at times, these corrections do not happen until long after the system is deployed. Other times, business needs change; even if the database was designed correctly in the beginning, it is often impossible to predict how a system will be used in the future.
As a result of such situations, admins often need to modify the schema of an exiting database. In the simple case, this task is trivial; most open source databases – including MySQL, which will be the focus of this article – include functionality for modifying existing table schemas. A relatively simple ALTER TABLE statement can be used to perform tasks such as adding columns, removing a column, changing a column's type, and so forth. This functionality is built in to MySQL, and it works fine – if your database is small.
[...]
Buy this article as PDF
(incl. VAT)
Buy Linux Magazine
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs
Subscribe to our ADMIN Newsletters
Digital Autonomy
• How the Fediverse Found Parts for Its Platforms
• The Answer Was Already on the Shelf
• Changing the Chip Industry: How Public Investment Has Grown Open Silicon
• United Nations Open Source Portal Goes Live
• EU Open Source Strategy Plays Key Role in Tech Sovereignty Package
• France Says “Au Revoir” to Microsoft
Support Our Work
Linux Magazine content is made possible with support from readers like you. Please consider contributing when you’ve found an article to be beneficial.
News
-
Working Exploit for a Linux Kernel Flaw Made Public
Even before a bug received its CVE identifier or an upstream fix, the local privilege escalation vulnerability exploit found its way into the wild.
-
r/opensource Bans AI-Assisted and AI-Related Projects
The de facto subreddit for all things open source has not only banned AI-related projects but also users who "don't speak English."
-
Bottlenecks Discovered by Meta Engineers Lead to Scalability Improvements
Thanks to a small group of engineers at Meta, the Linux kernel module kernfs has been improved, which has led to some serious gains.
-
PwnEscape Poses VM Vulnerability
A guest-to-host escape flaw in Linux KVM, named PwnEscape, might be yet another means of attacking Linux.
-
Linux Mint 27 Set for a Christmas Release
The developers behind Linux Mint have announced a Christmas release, and it looks like Santa's sack will be brimming with features.
-
Is Linus Torvalds Vibe Coding?
The creator of Linux has added AI into his process for Linux kernel development, and it's more than just for debugging.
-
openKylin 3.0 Released with Expanded AI Integration
The latest release features a pre-installed AI agent to help perform tasks.
-
DigitalOcean Ends Open Source Credits Program
In a change that has surprised many open source developers, DigitalOcean has decided to sunset its Open Source Credits Program.
-
Office 365 Available for Testing on Linux via Bottles
Linux users can now test the official Office 365, thanks to Mirko Brombin's work on Bottles.
-
Dutch Government Turns to Open Source for Digital Sovereignty
Following in the footsteps of other European countries, the Netherlands is looking to increase its sovereign digital infrastructure with open source software.