Kubuntu Focus: Database Admin Guided Solution

Database Admin

Command-line and GUI

Use command-line and GUI tools to administer MySQL and PostGreSQL databases.

Move Large Databases
CLI - MySQL
CLI - PostgreSQL
GUI - DataGrip
Troubleshooting
Revisions
Disclaimer

Design and manage databases with software such as MySQL.

Please read the disclaimer before proceeding. We review and update guided solutions regularly. If you have suggestions or requests, please write support@kfocus.org.

Database servers generally save database information into a fixed location (such as /var/lib/mysql). Usually this will result in those databases being stored on the root filesystem. In Kubuntu 24.04, the root file system is BTRFS, and this is fine if you are running relatively small databases, up to a few GB, for example.

However, if you are running larger databases with production intent or use, you can improve performance and reduce BTRFS snapshot size by moving from a copy-on-write BTRFS volume to the /home volume or partition. You might also move it to a separate ext4 partition mounted at /srv/data, for example.

The instructions below show how to move MySQL database files to the /home volume or partition. The same concepts should apply to most database server software for Ubuntu.

1. Ensure the database server is stopped.

sudo systemctl stop mysql.service

2. Move the mysql database directory to the desired alternate location (here, /home).

cd /home sudo mv /var/lib/mysql ./

3. Symlink the new database repo location to the original location.

cd /var/lib sudo ln -s /home/mysql

4. Adjust AppArmor permissions so that the MySQL server can access the database repo at the new location.

echo ' # 2024-09-15 Allow mysql from /home alias /var/lib/mysql/ -> /home/mysql/, ' |sudo tee -a /etc/apparmor.d/tunables/alias sudo systemctl restart apparmor.service

5. Start the database server again.

sudo systemctl start mysql.service

CLI - MySQL

Use the official MySQL client, mysql, or consider mycli which provides auto-complete and syntax highlighting. Install the latter by using sudo apt install mycli. It has Vim and Emacs modes, but may be slower in some situations.

Access MySQL databases with mycli.

CLI - PostgreSQL

Use official PostgreSQL client psql, or consider pgcli which provides auto-complete and syntax highlighting. Install the latter by using sudo apt install pgcli. It has Vim and Emacs modes, but may be slower in some situations.

Access PostgreSQL databases with pgcli.

GUI - DataGrip

JetBrains provides the DataGrip suite of database tools with impressive visualization and design capabilities. If you are a professional database or application developer, an advanced tool like this can really help improve productivity, especially during design. Simpler tools such as those shown above are still preferred for remote administration or debugging where heavy IDE tools are impractical, clumsy, or impossible to install.

Visualize, design, and work with databases using DataGrip.

Troubleshooting

Content will be added as needed.

Revisions

This is a partial revision history. See the git repository for all entries.

2024-09-15 7c90ed72 Add instructions to move large databases
2023-11-18 e770b1c6 Add search and help bar
2023-06-19 a7af817b Add 3 new images
2021-10-25 9036f3fe Overhaul table and links
2021-10-11 f726f2bf Secure links
2021-10-10 5728326e Reformat to 2-column
2021-09-22 dc862884 Update link and headline colors
2021-08-23 681261b4 Review and update codeblocks
2021-07-28 1b9cf29b Revise units to have a space after number
2020-06-10 c4ed9299 Restructure layout
2020-05-15 0684cd5c Proof reading and adjustment
2020-01-31 70b4aa40 First publication

Disclaimer

We try hard to provide a useful solution validated by professionals. However, we cannot anticipate every situation, and therefore cannot guarantee this procedure will work for your needs. Always backup your data and test the solution to determine the correct procedure for you.

THIS SOLUTION IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS “AS IS” AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOLUTION, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.