{"id":1118,"date":"2021-02-10T15:49:33","date_gmt":"2021-02-10T15:49:33","guid":{"rendered":"https:\/\/dft.wiki\/?p=1118"},"modified":"2026-06-09T12:51:58","modified_gmt":"2026-06-09T16:51:58","slug":"managing-mysql-and-mariadb-in-the-command-line-cli","status":"publish","type":"post","link":"https:\/\/dft.wiki\/?p=1118","title":{"rendered":"Managing MySQL and MariaDB in the Command Line (CLI)"},"content":{"rendered":"<p>MySQL and MariaDB are both free, open-source Relational Databases. MariaDB is a fork of MySQL, created by MySQL&#8217;s original developers after Oracle acquired it.<\/p>\n<p>The MariaDB developers aim to maintain compatibility with MySQL as much as possible, making it a great low-risk migration alternative.<\/p>\n<hr \/>\n<p><strong>BONUS KNOWLEDGE<\/strong><\/p>\n<p>Besides <strong>SQL<\/strong> and <strong>NoSQL<\/strong> definitions, databases can be grouped into 7 paradigms (but not limited to):<\/p>\n<ul>\n<li><strong>Relational<\/strong> (RDB) (<strong>SQL<\/strong>): MySQL, MariaDB, Oracle, SQLite, MSSQL, PostgreSQL, AWS Aurora, CockroachDB&#8230;<\/li>\n<li><strong>Key-Value<\/strong> (single value per entry) (<strong>NoSQL<\/strong>): Redis, ElasticCache&#8230;<\/li>\n<li><strong>Wide Column<\/strong> (multiple values per entry) (<strong>NoSQL<\/strong>): ScyllaDB, Apache Cassandra, Apache HBase, AWS DynamoDB, Azure CosmosDB, Google BigTable&#8230;<\/li>\n<li><strong>Document<\/strong> (JSON format) (<strong>NoSQL<\/strong>): MongoDB, Firestore, CouchDB&#8230;<\/li>\n<li><strong>Graph<\/strong> (<strong>NoSQL<\/strong>): Neo4j, DGraph, Janus Graph&#8230;<\/li>\n<li><strong>Search Engine<\/strong> (<strong>NoSQL<\/strong>): ElasticSearch, Algolia, MeiliSearch&#8230;<\/li>\n<li><strong>Multi-model<\/strong> (<strong>SQL<\/strong> and\/or <strong>NoSQL<\/strong>): FaunaDB, CosmosDB, MongoDB, Redis&#8230;<\/li>\n<\/ul>\n<hr \/>\n<p><strong>MANAGING MySQL \/ MariaDB<\/strong><\/p>\n<p>Keep the following points in mind, as they often frustrate beginners:<\/p>\n<ol>\n<li>Commands may differ depending on the <strong>version<\/strong> of the application.<\/li>\n<li>Forgetting the <strong>semicolon<\/strong> at the end of a command will leave the prompt waiting for it.<\/li>\n<li>MySQL and MariaDB are <strong>not exactly the same<\/strong>, though they are compatible most of the time.<\/li>\n<\/ol>\n<p>Before starting, check the application version:<\/p>\n<pre>mysql --version<\/pre>\n<p>OR<\/p>\n<pre>mysql> SELECT VERSION();<\/pre>\n<p>If you have MySQL 5.7.6+ or MariaDB 10.1.20+, some commands may differ from older versions.<\/p>\n<p>Log in to MySQL as root:<\/p>\n<pre>mysql -u root -p<\/pre>\n<p>Managing databases:<\/p>\n<pre>mysql> SHOW DATABASES;\r\nmysql> CREATE DATABASE databaseName;\r\nmysql> CREATE DATABASE IF NOT EXISTS databaseName;\r\nmysql> DROP DATABASE databaseName;\r\nmysql> DROP DATABASE IF EXISTS databaseName;<\/pre>\n<p>Managing users:<\/p>\n<pre>mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';\r\nmysql> CREATE USER IF NOT EXISTS 'user'@'localhost' IDENTIFIED BY 'password';<\/pre>\n<p>For <strong>newer<\/strong> versions:<\/p>\n<pre>mysql> ALTER USER 'user'@'localhost' IDENTIFIED BY 'password';<\/pre>\n<p>For <strong>older<\/strong> versions:<\/p>\n<pre>mysql> SET PASSWORD FOR 'user'@'localhost' = PASSWORD('password');<\/pre>\n<p>Listing and deleting users:<\/p>\n<pre>mysql> SELECT user, host FROM mysql.user;\r\nmysql> DROP USER 'user'@'localhost';\r\nmysql> DROP USER IF EXISTS 'user'@'localhost';<\/pre>\n<p>Managing privileges:<\/p>\n<pre>mysql> GRANT ALL PRIVILEGES ON databaseName.* TO 'user'@'localhost';\r\nmysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';\r\nmysql> GRANT ALL PRIVILEGES ON databaseName.tableName TO 'user'@'localhost';\r\nmysql> GRANT SELECT, INSERT, DELETE ON databaseName.* TO 'user'@'localhost';\r\nmysql> REVOKE ALL PRIVILEGES ON databaseName.* FROM 'user'@'localhost';\r\nmysql> SHOW GRANTS FOR 'user'@'localhost';<\/pre>\n<p>If you forgot your root password:<\/p>\n<pre>sudo systemctl stop mysql\r\nsudo systemctl stop mariadb\r\nsudo mysqld_safe --skip-grant-tables &\r\nmysql -u root<\/pre>\n<p>For <strong>newer<\/strong> versions:<\/p>\n<pre>mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';\r\nmysql> FLUSH PRIVILEGES;<\/pre>\n<p>If <code>ALTER USER<\/code> does not work, try:<\/p>\n<pre>mysql> UPDATE mysql.user SET authentication_string = PASSWORD('password') WHERE User = 'root' AND Host = 'localhost';\r\nmysql> FLUSH PRIVILEGES;<\/pre>\n<p>For <strong>older<\/strong> versions:<\/p>\n<pre>mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');\r\nmysql> FLUSH PRIVILEGES;<\/pre>\n<p>Grant the privilege to manage users and permissions (Ansible example):<\/p>\n<pre>GRANT ALL ON *.* TO '_Ansible'@'%' WITH GRANT OPTION;<\/pre>\n<p>Grant the privileges required for management tools (Percona example):<\/p>\n<pre>CREATE USER 'pmm'@'%' IDENTIFIED BY '*********' WITH MAX_USER_CONNECTIONS 10;\r\nGRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm'@'%';<\/pre>\n<p>Show users and grants:<\/p>\n<pre>SELECT user, host FROM mysql.user;\r\nSHOW GRANTS;\r\nSHOW GRANTS FOR user@localhost;<\/pre>\n<p>Restart the service:<\/p>\n<pre>sudo systemctl restart mysql\r\nsudo systemctl restart mariadb<\/pre>\n<p>Try logging in as root:<\/p>\n<pre>mysql -u root -p<\/pre>\n<hr \/>\n<p><strong>ADDITIONALLY<\/strong><\/p>\n<p>Consider periodically running the following commands:<\/p>\n<ul>\n<li><span class=\"code\" spellcheck=\"false\">ANALYZE TABLE<\/span> &#8211; analyzes the table and updates its statistics, which the MySQL optimizer uses to build query execution plans and make better decisions about how to run queries.<\/li>\n<li><span class=\"code\" spellcheck=\"false\">OPTIMIZE TABLE<\/span> &#8211; rebuilds the table and its indexes, reclaiming unused space, defragmenting data files, repairing any corruption, and updating the same statistics as <span class=\"code\" spellcheck=\"false\">ANALYZE TABLE<\/span>.<\/li>\n<\/ul>\n<p><strong>Note:<\/strong> these operations can be resource-intensive. Test in a sandbox environment first to avoid impacting database performance.<\/p>\n<hr \/>\n<p><strong>BONUS<\/strong><\/p>\n<p>Not all applications support MySQL 8 out of the box.<\/p>\n<p>Follow these steps to install MySQL 5.7 on Ubuntu 20.04 and 22.04:<\/p>\n<pre>wget https:\/\/dev.mysql.com\/get\/mysql-apt-config_0.8.12-1_all.deb\r\nsudo dpkg -i mysql-apt-config_0.8.12-1_all.deb\r\nsudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys B7B3B788A8D3785C\r\nsudo apt update && sudo apt-cache policy mysql-server\r\nsudo apt install -f mysql-client=5.7* mysql-community-server=5.7* mysql-server=5.7* -y\r\nsudo mysql_secure_installation<\/pre>\n<p><strong>SQLite<\/strong><\/p>\n<pre>sqlite3 database_filename.db<\/pre>\n<pre>sqlite> .help\r\nsqlite> .tables\r\nsqlite> PRAGMA table_info(table_name);\r\nsqlite> SELECT * FROM table_name;\r\nsqlite> .quit<\/pre>\n<p><strong>Note:<\/strong> SQLite has unique navigation commands, but its querying is very similar to MySQL.<\/p>\n<p><strong>PostgreSQL<\/strong><\/p>\n<p>Getting help:<\/p>\n<pre>psql --help<\/pre>\n<pre>postgresql=# help\r\npostgresql=# \\?<\/pre>\n<p>Basic navigation and querying:<\/p>\n<pre>postgresql=# \\l                     <strong>-- List databases<\/strong>\r\npostgresql=# CREATE DATABASE abcDB;\r\npostgresql=# \\c abcDB               <strong>-- Connect to a DB<\/strong>\r\npostgresql=# CREATE TABLE tableName ( id int NOT NULL PRIMARY KEY, column1 varchar(10) NOT NULL, column2 TIMESTAMP );\r\npostgresql=# \\d                     <strong>-- List tables<\/strong>\r\npostgresql=# \\dt                    <strong>-- List tables in the current schema<\/strong>\r\npostgresql=# \\d tableName           <strong>-- Describe table<\/strong>\r\npostgresql=# INSERT INTO tableName ( column1, column2 ) VALUES ( 'ABC', NOW() );\r\npostgresql=# SELECT * FROM tableName;\r\npostgresql=# DROP DATABASE abcDB;<\/pre>\n<p><strong>Note:<\/strong> PostgreSQL has unique navigation commands, but its querying is very similar to MySQL.<\/p>\n<p>Connecting from the command line:<\/p>\n<pre>psql -h localhost -p 5432 -U userName -W abcDB<\/pre>\n<p>Importing and exporting files:<\/p>\n<pre>postgresql=# \\i importFile.sql\r\npostgresql=# \\c ( SELECT * FROM tableName ) TO 'exportFile.sql' DELIMITER ',' CSV HEADER;<\/pre>\n<p>Users, Roles, and Groups:<\/p>\n<ul>\n<li><strong>User<\/strong>: an account (user or service) that can log in to a database by default.<\/li>\n<li><strong>Role<\/strong>: a collection of privileges that can be assigned to users or other roles, and can optionally be configured to log in like a user.<\/li>\n<li><strong>Group<\/strong>: essentially a role that cannot log in. There is no practical advantage to using this over roles.<\/li>\n<\/ul>\n<p>Schema:<\/p>\n<ul>\n<li><strong>Schema<\/strong>: a namespace (logical grouping) for managing database objects, helping avoid naming conflicts and control access to data.\n<ul>\n<li>Tables, functions, and other objects are created within a schema, not directly in the database (if no schema is specified, the <code>default<\/code> schema is used).<\/li>\n<li>Objects like tables can share the same name within the same database as long as they belong to different schemas.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>MySQL and MariaDB are both free, open-source Relational Databases. MariaDB is a fork of MySQL, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-1118","post","type-post","status-publish","format-standard","hentry","category-web"],"_links":{"self":[{"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/posts\/1118","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dft.wiki\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1118"}],"version-history":[{"count":19,"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/posts\/1118\/revisions"}],"predecessor-version":[{"id":5787,"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/posts\/1118\/revisions\/5787"}],"wp:attachment":[{"href":"https:\/\/dft.wiki\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1118"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dft.wiki\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1118"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dft.wiki\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1118"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}