{"id":2399,"date":"2024-06-02T16:52:43","date_gmt":"2024-06-02T13:52:43","guid":{"rendered":"https:\/\/www.fibacloud.com\/community\/tutorials\/?p=2399"},"modified":"2026-10-04T22:57:46","modified_gmt":"2026-10-04T19:57:46","slug":"how-to-create-and-manage-a-mysql-database","status":"publish","type":"post","link":"https:\/\/www.fibacloud.com\/community\/tutorials\/en\/how-to-create-and-manage-a-mysql-database\/","title":{"rendered":"MySQL 8.4: Create and Manage a Database"},"content":{"rendered":"\t\t<div data-elementor-type=\"wp-post\" data-elementor-id=\"2399\" class=\"elementor elementor-2399\" data-elementor-post-type=\"post\">\n\t\t\t\t<div class=\"elementor-element elementor-element-d6c8315 e-con-full e-flex e-con e-parent\" data-id=\"d6c8315\" data-element_type=\"container\">\n\t\t\t\t<div class=\"elementor-element elementor-element-48818b2 elementor-widget elementor-widget-text-editor\" data-id=\"48818b2\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<style>\/*! elementor - v3.21.0 - 08-05-2024 *\/\n.elementor-widget-text-editor.elementor-drop-cap-view-stacked .elementor-drop-cap{background-color:#69727d;color:#fff}.elementor-widget-text-editor.elementor-drop-cap-view-framed .elementor-drop-cap{color:#69727d;border:3px solid;background-color:transparent}.elementor-widget-text-editor:not(.elementor-drop-cap-view-default) .elementor-drop-cap{margin-top:8px}.elementor-widget-text-editor:not(.elementor-drop-cap-view-default) .elementor-drop-cap-letter{width:1em;height:1em}.elementor-widget-text-editor .elementor-drop-cap{float:left;text-align:center;line-height:1;font-size:50px}.elementor-widget-text-editor .elementor-drop-cap-letter{display:inline-block}<\/style>\t\t\t\t<p>This guide uses a <strong>running MySQL 8.4 LTS server<\/strong> to create an application database, a restricted account and a sample table. You will exercise CRUD operations, transaction rollback and restoration into a separate database. If you need installation first, follow the <a href=\"https:\/\/www.fibacloud.com\/community\/tutorials\/en\/mysql-installation-and-management-on-ubuntu\/\">Ubuntu MySQL guide<\/a>.<\/p>\n<figure class=\"fc-editorial-figure\" data-fc-visual=\"20260920\" aria-labelledby=\"fc-editorial-caption-2399\"><a href=\"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-content\/uploads\/community-editorial-20260920\/fc-database-en-inline-20260920.webp\" target=\"_blank\" rel=\"noopener\" aria-label=\"Open full-size illustration\"><picture><source media=\"(max-width: 600px)\" srcset=\"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-content\/uploads\/community-editorial-20260920\/fc-database-en-mobile-20260920.webp\" width=\"720\" height=\"1140\"><img loading=\"lazy\" src=\"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-content\/uploads\/community-editorial-20260920\/fc-database-en-inline-20260920.webp\" srcset=\"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-content\/uploads\/community-editorial-20260920\/fc-database-en-inline-20260920.webp 1440w, https:\/\/www.fibacloud.com\/community\/tutorials\/wp-content\/uploads\/community-editorial-20260920\/fc-database-en-inline-20260920-300x242.webp 300w, https:\/\/www.fibacloud.com\/community\/tutorials\/wp-content\/uploads\/community-editorial-20260920\/fc-database-en-inline-20260920-1024x825.webp 1024w, https:\/\/www.fibacloud.com\/community\/tutorials\/wp-content\/uploads\/community-editorial-20260920\/fc-database-en-inline-20260920-768x619.webp 768w\" sizes=\"auto, (max-width: 900px) 100vw, 820px\" width=\"1440\" height=\"1160\" alt=\"Users, databases and privilege boundaries\" class=\"wp-image-2566\"  decoding=\"async\"><\/picture><\/a><figcaption id=\"fc-editorial-caption-2399\"><strong>Users, databases and privilege boundaries<\/strong> Limit the application account to the database it needs. Read-only accounts receive no write access; backup-account permissions depend on the backup method.<details><summary>Step-by-step explanation<\/summary><ol><li>Separate: Keep admin and application accounts apart.<\/li><li>Limit: Scope privileges to the relevant database.<\/li><li>Test: Check allowed and denied operations.<\/li><li>Review: Remove permissions that are no longer needed.<\/li><\/ol><\/details><\/figcaption><\/figure>\n<h2>Separate administrator and application accounts<\/h2>\n<p>You need an authorized administrator account. Your application must not use its password. The names <code>appdb<\/code>, <code>appdb_restore<\/code> and <code>fc_app<\/code> below identify <strong>new training resources<\/strong>. Stop if they conflict with an existing database or user. Enter the administrator password interactively with <code>mysql -u root -p<\/code>, rather than placing it in a command.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-6714f94 elementor-widget elementor-widget-text-editor\" data-id=\"6714f94\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-sql\">SELECT VERSION(), CURRENT_USER();\nSHOW DATABASES;\nSELECT User, Host FROM mysql.user WHERE User='fc_app';<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-6519156 elementor-widget elementor-widget-text-editor\" data-id=\"6519156\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>A cloud server, managed-database offering or HA option does not by itself establish MySQL replication, automatic failover, backups or a particular RPO guarantee. Check the current scope and prices of <a href=\"https:\/\/www.fibacloud.com\/products\/\">FibaCloud services<\/a>; do not assume particular portal or purchase options are prerequisites for these SQL steps.<\/p>\n<h2>Create a utf8mb4 schema and sample table<\/h2>\n<p>In the administrator session:<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-d07e1b9 elementor-widget elementor-widget-text-editor\" data-id=\"d07e1b9\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-sql\">CREATE DATABASE appdb\n  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;\nCREATE TABLE appdb.tasks (\n  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,\n  title VARCHAR(200) NOT NULL,\n  done BOOLEAN NOT NULL DEFAULT FALSE\n) ENGINE=InnoDB;\nCREATE USER 'fc_app'@'localhost' IDENTIFIED BY RANDOM PASSWORD;\nGRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'fc_app'@'localhost';\nSHOW GRANTS FOR 'fc_app'@'localhost';<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-9eeb22b elementor-widget elementor-widget-text-editor\" data-id=\"9eeb22b\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Store the generated password securely. This user can read\/change data only within <code>appdb<\/code>; it cannot create accounts or change table structure. The application does not need <code>GRANT ALL ON *.*<\/code> or <code>WITH GRANT OPTION<\/code>. Use a separate, narrowly scoped account for schema migrations.<\/p>\n<h2>Read actual data and roll back a transaction<\/h2>\n<p>Connect from another terminal as the application account:<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-408aedc elementor-widget elementor-widget-text-editor\" data-id=\"408aedc\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-bash\">mysql --protocol=SOCKET --default-character-set=utf8mb4 -u fc_app -p appdb<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-60bc2c5 elementor-widget elementor-widget-text-editor\" data-id=\"60bc2c5\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-sql\">INSERT INTO tasks(title) VALUES\n  ('\u0130stanbul yede\u011fi \ud83d\ude80'), ('Verify restore');\nSELECT id, title, done FROM tasks ORDER BY id;\nSTART TRANSACTION;\nSELECT id, title, done FROM tasks WHERE id=1 FOR UPDATE;\nUPDATE tasks SET done=TRUE WHERE id=1;\nSELECT ROW_COUNT();\nSELECT id, title, done FROM tasks WHERE id=1;\nROLLBACK;\nSELECT id, title, done FROM tasks WHERE id=1;<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-604c915 elementor-widget elementor-widget-text-editor\" data-id=\"604c915\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>The update should affect one row; after <code>ROLLBACK<\/code>, <code>done<\/code> must be 0 again. In a real operation, deliberately choose <code>COMMIT<\/code> only after verifying the target rows and affected count. This transaction example uses InnoDB; do not confuse it with the implicit-commit behavior of DDL statements. Before a DELETE, also SELECT using the same WHERE condition. Do not experiment with unrestricted deletion on production data.<\/p>\n<h2>Prove that the privilege boundary works<\/h2>\n<p>In the application session, <code>CREATE TABLE permission_probe(id INT);<\/code> must fail with a privilege error. Access to another schema and an attempt to <code>CREATE USER<\/code> should also be denied. Check that an incorrect password is rejected. These expected denials are acceptance tests for the access model.<\/p>\n<p>Local socket connections do not need TLS configuration. For network access, verify the server&#8217;s TLS certificate and allow only the necessary private-network\/source addresses. Installing phpMyAdmin is outside this guide: its <a href=\"https:\/\/docs.phpmyadmin.net\/en\/latest\/setup.html#securing-your-phpmyadmin-installation\" rel=\"nofollow noopener\" target=\"_blank\">official security guidance<\/a> requires HTTPS, restricted access and an update plan.<\/p>\n<h2>Restore a backup into a separate database<\/h2>\n<p>In the administrator shell, create a private backup directory. Check that the following destination will not overwrite an existing backup. Scheduled jobs should use unique, timestamped filenames.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-babfcff elementor-widget elementor-widget-text-editor\" data-id=\"babfcff\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-bash\">umask 077\nbackup_dir=\"$HOME\/fc-backups\"\ninstall -d -m 700 \"$backup_dir\"\nmysqldump -u root -p --single-transaction --routines --events --triggers \\\n  --set-gtid-purged=OFF --no-tablespaces appdb &gt; \"$backup_dir\/appdb.sql\"<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-9618a76 elementor-widget elementor-widget-text-editor\" data-id=\"9618a76\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Confirm a successful exit and a nonempty file. For this InnoDB example, <code>--single-transaction<\/code> obtains a consistent data snapshot; do not run DDL during the dump. It is not sufficient by itself for nontransactional tables. This example omits <code>--databases<\/code> so that the dump can be loaded into a separate destination.<\/p>\n<p>Create that destination in the administrator MySQL session:<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-6fea57a elementor-widget elementor-widget-text-editor\" data-id=\"6fea57a\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-sql\">CREATE DATABASE appdb_restore\n  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-c03a9d4 elementor-widget elementor-widget-text-editor\" data-id=\"c03a9d4\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-bash\">mysql -u root -p appdb_restore &lt; \"$backup_dir\/appdb.sql\"<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-a946b3f elementor-widget elementor-widget-text-editor\" data-id=\"a946b3f\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-sql\">SELECT * FROM appdb.tasks ORDER BY id;\nSELECT * FROM appdb_restore.tasks ORDER BY id;<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-5e7c3bb elementor-widget elementor-widget-text-editor\" data-id=\"5e7c3bb\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Both rows, their titles and <code>done<\/code> values must match. A real application also needs checks for indexes, triggers, routines, events, application behavior and privilege dependencies. The dump does not contain user\/grant records; retain those securely and separately. Never overwrite the source database for a restore exercise. A backup left on the same server is not a disaster-recovery plan: retain an independently protected copy and prepare a <a href=\"https:\/\/www.fibacloud.com\/community\/tutorials\/en\/how-to-create-a-daily-backup-plan\/\">daily backup plan<\/a>.<\/p>\n<h2>Troubleshooting and cleanup<\/h2>\n<p>For <code>Access denied<\/code>, check the password, <code>User@Host<\/code>, connection protocol and <code>SHOW GRANTS<\/code> together; do not grant global access. For <code>Unknown database<\/code>, verify the destination name. Investigate a failed restoration in the test destination instead of modifying live data. Remove training resources only after checking their names, backup and dependencies; this guide does not run automatic DROP cleanup.<\/p>\n<h2>Sources<\/h2>\n<ul>\n<li><a href=\"https:\/\/docs.oracle.com\/cd\/E17952_01\/mysql-8.4-en\/create-user.html\" rel=\"nofollow noopener\" target=\"_blank\">MySQL 8.4: creating users<\/a><\/li>\n<li><a href=\"https:\/\/docs.oracle.com\/cd\/E17952_01\/mysql-8.4-en\/grant.html\" rel=\"nofollow noopener\" target=\"_blank\">MySQL 8.4: granting privileges<\/a><\/li>\n<li><a href=\"https:\/\/docs.oracle.com\/cd\/E17952_01\/mysql-8.4-en\/commit.html\" rel=\"nofollow noopener\" target=\"_blank\">MySQL 8.4: COMMIT and ROLLBACK<\/a><\/li>\n<li><a href=\"https:\/\/docs.oracle.com\/cd\/E17952_01\/mysql-8.4-en\/mysqldump.html\" rel=\"nofollow noopener\" target=\"_blank\">MySQL 8.4: mysqldump options and limitations<\/a><\/li>\n<li><a href=\"https:\/\/docs.phpmyadmin.net\/en\/latest\/setup.html#securing-your-phpmyadmin-installation\" rel=\"nofollow noopener\" target=\"_blank\">phpMyAdmin: securing your installation<\/a><\/li>\n<\/ul>\n<p class=\"fc-article-review-date\">Technical content and sources reviewed on October 4, 2026. Recheck the relevant official documentation when a version or provider changes.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>Create a MySQL 8.4 database with restricted users, CRUD operations, transaction rollback and a verified dump restore to a separate database.<\/p>\n","protected":false},"author":1,"featured_media":2565,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[80,103,247],"class_list":["post-2399","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials-en","tag-databases","tag-mysql","tag-sql"],"lang":"en","translations":{"en":2399,"tr":1908},"pll_sync_post":[],"_links":{"self":[{"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/posts\/2399","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/comments?post=2399"}],"version-history":[{"count":4,"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/posts\/2399\/revisions"}],"predecessor-version":[{"id":2404,"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/posts\/2399\/revisions\/2404"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/media\/2565"}],"wp:attachment":[{"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/media?parent=2399"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/categories?post=2399"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/tags?post=2399"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}