{"id":1118,"date":"2021-02-10T15:49:33","date_gmt":"2021-02-10T15:49:33","guid":{"rendered":"https:\/\/dft.wiki\/?p=1118"},"modified":"2024-04-04T09:44:52","modified_gmt":"2024-04-04T13:44:52","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. In fact, MariaDB is a fork of MySQL by original developers of MySQL when it was acquired by Oracle.<\/p>\n<p>The intention of the MariaDB developers is to maintain compatibility to MySQL as much as possible, which makes a great low-risk alternative for migration.<\/p>\n<hr \/>\n<p><strong>BONUS KNOWLEDGE<\/strong><\/p>\n<p>Besides <strong>SQL<\/strong> and <strong>NoSQL<\/strong> definitions, databases can be sorted 7 database paradigms (but not limited to):<\/p>\n<ul>\n<li><strong>Relational<\/strong> (RDB) (<strong>SQL<\/strong>): MySQL, MariaDB, Oracle, SQLite, MSSQL, PostgreeSQL, 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, Pache HBase, AWS DynamoDB, Azure CostmosDB, 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 \/ MariaSQL<\/strong><\/p>\n<p>Make notes of the following points that might frustrate beginners:<\/p>\n<ol>\n<li>One is when the <strong>version<\/strong> of the application has different commands.<\/li>\n<li>Another is when you forget to type the <strong>semicolon<\/strong> at the end (it will hand waiting for the character that ends the command).<\/li>\n<li>Do not forget that MySQL and MariaDB <strong>are not exactly the same<\/strong> but compatible (most times).<\/li>\n<\/ol>\n<p>Before starting check the version of the application:<\/p>\n<pre>mysql --version<\/pre>\n<p>OR<\/p>\n<pre>mysql&gt; SELECT VERSION();<\/pre>\n<p>If you have MySQL 5.7.6+ or MariaDB 10.1.20+ the commands may be different than if they are older than that.<\/p>\n<p>Log-in the MySQL as root:<\/p>\n<pre>mysql -u root -p<\/pre>\n<p>Managing databases:<\/p>\n<pre>mysql&gt; SHOW DATABASES;\r\nmysql&gt; CREATE DATABASE databaseName;\r\nmysql&gt; CREATE DATABASE IF NOT EXISTS databaseName;\r\nmysql&gt; DROP DATABASE databaseName;\r\nmysql&gt; DROP DATABASE IF EXISTS databaseNname;<\/pre>\n<p>Managing users:<\/p>\n<pre>mysql&gt; CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';\r\nmysql&gt; CREATE USER IF NOT EXISTS 'user'@'localhost' IDENTIFIED BY 'password';<\/pre>\n<p>For <strong>newer<\/strong>:<\/p>\n<pre>mysql&gt; ALTER USER 'user'@'localhost' IDENTIFIED BY 'password';<\/pre>\n<p>For <strong>older<\/strong>:<\/p>\n<pre>mysql&gt; SET PASSWORD FOR 'user'@'localhost' = PASSWORD('password');<\/pre>\n<p>Listing and deleting users:<\/p>\n<pre>mysql&gt; SELECT user, host FROM mysql.user;\r\nmysql&gt; DROP USER 'user'@'localhost';\r\nmysql&gt; DROP USER IF EXISTS 'user'@'localhost';<\/pre>\n<p>Managing privileges:<\/p>\n<pre>mysql&gt; GRANT ALL PRIVILEGES ON databaseName.* TO 'user'@'localhost';\r\nmysql&gt; GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';\r\nmysql&gt; GRANT ALL PRIVILEGES ON databaseName.tableName TO 'user'@'localhost';\r\nmysql&gt; GRANT SELECT, INSERT, DELETE ON databaseName.* TO 'user'@'localhost';\r\nmysql&gt; REVOKE ALL PRIVILEGES ON databaseName.* FROM 'user'@'localhost';\r\nmysql&gt; 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 &amp;\r\nmysql -u root<\/pre>\n<p>For <strong>newer<\/strong>:<\/p>\n<pre>mysql&gt; ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';\r\nmysql&gt; FLUSH PRIVILEGES;<\/pre>\n<p>In case ALTER USER does not work try:<\/p>\n<pre>mysql&gt; UPDATE mysql.user SET authentication_string = PASSWORD('password') WHERE User = 'root' AND Host = 'localhost';\r\nmysql&gt; FLUSH PRIVILEGES;<\/pre>\n<p>For <strong>older<\/strong>:<\/p>\n<pre>mysql&gt; SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');\r\nmysql&gt; FLUSH PRIVILEGES;<\/pre>\n<p>Grant the privilege of manage users and grant permissions (for Ansible, in this example):<\/p>\n<pre>GRANT ALL ON *.* TO '_Ansible'@'%' WITH GRANT OPTION;<\/pre>\n<p>Grant the privileges required for management tools (for Percona, in this 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 to enter as root now:<\/p>\n<pre>mysql -u root -p<\/pre>\n<hr \/>\n<p data-pm-slice=\"1 1 []\"><strong>ADDITIONALLY<\/strong><\/p>\n<p>Consider periodically executing the following commands:<\/p>\n<ul class=\"ak-ul\">\n<li><span class=\"code\" spellcheck=\"false\">ANALYZE TABLE<\/span> &#8211; analyzes the table and updates the table statistics, which the MySQL optimizer uses to make query execution plans and influences the optimizer to make good decisions about how to execute a query.<\/li>\n<li><span class=\"code\" spellcheck=\"false\">OPTIMIZE TABLE<\/span> &#8211; it rebuilds the table and its indexes, which can reclaim unused space + defragment the data files, check and repair any corruption in the table, and updates the statistics that <span class=\"code\" spellcheck=\"false\">ANALYZE TABLE<\/span> updates.<\/li>\n<\/ul>\n<p><strong>Note:<\/strong> it can be resources intense. Sandbox tests and validation are necessary to prevent killing the resources of the DB.<\/p>\n<hr \/>\n<p><strong>BONUS<\/strong><\/p>\n<p>Unfortunately not all applications are capable of working with MySQL 8 out of the box.<\/p>\n<p>Follow the installation steps for 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 &amp;&amp; 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&gt; .help\r\nsqlite&gt; .tables\r\nsqlite&gt; PRAGMA table_info(table_name);\r\nsqlite&gt; SELECT * FROM table_name;\r\nsqlite&gt; .quit<\/pre>\n<p><strong>Note:<\/strong> the navigation is unique for this DB but the querying has lots of similarities to MySQL.<\/p>\n<p><strong>PostgreSQL<\/strong><\/p>\n<p>Gathering 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>-- Connects 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> the navigation is unique for this DB but the querying has lots of similarities 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 from and to 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>: a user account or a service account that by default can login to a database.<\/li>\n<li><strong>Role<\/strong>: a collection of privileges\/permissions\/grants that can be assigned to users or other roles and can be set to login like a user.<\/li>\n<li><strong>Groups:<\/strong> are essentially roles that cannot login. No advantage in using this.<\/li>\n<\/ul>\n<p>Schema:<\/p>\n<ul>\n<li><strong>Schema<\/strong>: a namespace (logical organisation) for managing database objects, helping to avoid naming conflicts and control access to data.\n<ul>\n<li>Tables, functions, and other objects are not created directly to a DB but to a schema in the database (if not specified it uses schema called the <code>default<\/code>).<\/li>\n<li>Objects like tables can have the exact same name in the same database as long as they are in different schema.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>MySQL and MariaDB are both free open-source Relational Databases. In fact, MariaDB is a fork [&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":18,"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/posts\/1118\/revisions"}],"predecessor-version":[{"id":4058,"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/posts\/1118\/revisions\/4058"}],"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}]}}