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
US / Canada
UK / Australia
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs
Subscribe to our ADMIN Newsletters
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
-
Two New Distros Adopt Enlightenment
MX Moksha and AV Linux 25 join ranks with Bodhi Linux and embrace the Enlightenment desktop.
-
Solus Linux 4.8 Removes Python 2
Solus Linux 4.8 has been released with the latest Linux kernel, updated desktops, and a key removal.
-
Zorin OS 18 Hits over a Million Downloads
If you doubt Linux isn't gaining popularity, you only have to look at Zorin OS's download numbers.
-
TUXEDO Computers Scraps Snapdragon X1E-Based Laptop
Due to issues with a Snapdragon CPU, TUXEDO Computers has cancelled its plans to release a laptop based on this elite hardware.
-
Debian Unleashes Debian Libre Live
Debian Libre Live keeps your machine free of proprietary software.
-
Valve Announces Pending Release of Steam Machine
Shout it to the heavens: Steam Machine, powered by Linux, is set to arrive in 2026.
-
Happy Birthday, ADMIN Magazine!
ADMIN is celebrating its 15th anniversary with issue #90.
-
Another Linux Malware Discovered
Russian hackers use Hyper-V to hide malware within Linux virtual machines.
-
TUXEDO Computers Announces a New InfinityBook
TUXEDO Computers is at it again with a new InfinityBook that will meet your professional and gaming needs.
-
SUSE Dives into the Agentic AI Pool
SUSE becomes the first open source company to adopt agentic AI with SUSE Enterprise Linux 16.