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
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
-
EU Open Source Strategy Plays Key Role in Tech Sovereignty Package
Comprehensive measures adopted by the European Commission aim to reduce dependency on non-EU countries.
-
Linux Foundation Report Indicates AI Driving Tech Hiring
Within growing security and skills gaps, AI has been found to be a positive driving force behind tech hiring trends in Europe.
-
United Nations Open Source Portal Goes Live
A new open source portal seeks to coordinate and scale open source efforts across the United Nations system.
-
KDE Linux Drops AUR
KDE Linux developers have dropped the Arch User Repository from the build pipeline due to security concerns; other distributions should consider doing the same.
-
California May Exempt Linux from Its Age-Verification Law
After backlash from the Linux community, California may be backing off on its promise to force all operating systems to verify age, but one platform may still have to comply.
-
Another Logic Bug Found in Linux Kernel
Qualys has discovered a vulnerability in the Linux kernel that can be used to elevate standard user privileges.
-
Ubuntu Core 26 Offers Game-Changing Enterprise Features
Ubuntu Core 26 could be a game-changer for organizations looking for increased security and reliability.
-
AI Flooding the Linux Kernel Security Mailing List
AI is giving Linus Torvalds a headache, but not in the way you might think.
-
Top Priorities for Open Source Pros Seeking a New Job
Professional fulfillment tops the list, according to LPI report.
-
Container-Based Fedora Hummingbird Designed for Agent-First Builders
Fedora Hummingbird brings the same approach to the host OS as it does to containers to level up security.