===== MariaDB Common Queries =====
===== Query Databases =====
<code>
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nextcloud          |
| performance_schema |
| sitedatabase       |
+--------------------+
</code>
===== Select a database =====
<code>
MariaDB [(none)]> use sitedatabase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [sitedatabase]> 
</code>


===== Working with databases =====
Show all available databases in the current MySQL database server
  SHOW DATABASES;
Create a database with a specified name if it does not exist in the database server
  CREATE DATABASE [IF NOT EXISTS] database_name;
Use a database or change the current database to another database that you are working with:
  USE database_name;
Drop a database with a specified name permanently. All physical files associated with the database will be deleted.
  DROP DATABASE [IF EXISTS] database_name;
===== Working with tables =====
Show all tables in a current database.
  SHOW TABLES;
Create a new table
  CREATE TABLE [IF NOT EXISTS] table_name( column_list );
Add a new column into a table:
  ALTER TABLE table 
  ADD [COLUMN] column_name;
Drop a column from a table:
  ALTER TABLE table_name
  DROP [COLUMN] column_name;
Add index with a specific name to a table on a column:
  ALTER TABLE table 
  ADD INDEX [name](column, ...);
Add primary key into a table:
  ALTER TABLE table_name 
  ADD PRIMARY KEY (column_name,...);
Remove the primary key of a table:
  ALTER TABLE table_name
  DROP PRIMARY KEY;
Drop a table:
  DROP TABLE [IF EXISTS] table_name;
Show the columns of a table:
  DESCRIBE table_name;
Show the information of a column in a table:
  DESCRIBE table_name column_name;
===== Query Users =====
<code>
MariaDB [(none)]> select host, user, password from mysql.user;
+-----------+----------+-------------------------------------------+
| host      | user     | password                                  |
+-----------+----------+-------------------------------------------+
| localhost | root     | *AF926838A9A0501BA7017D135C123C007497F85B |
| localhost | www-data | *AF926838A9A0501BA7017D135C123C007497F85B |
| localhost | nc-user  | *AF926838A9A0501BA7017D135C123C007497F85B |
+-----------+----------+-------------------------------------------+

MariaDB [(none)]> SELECT User, Db, Host from mysql.db;
+----------+--------------+-----------+
| User     | Db           | Host      |
+----------+--------------+-----------+
| nc-user  | nextcloud    | localhost |
| www-data | sitedatabase | localhost |
+----------+--------------+-----------+
</code>
===== Create Users =====
<code>
CREATE USER 'www-data'@'localhost' IDENTIFIED BY 'your_password';
CREATE USER 'nc-user'@'%' IDENTIFIED BY 'your_password';
</code>
===== Drop Users =====
Revoke the granted permissions before issuing:
<code>
DROP USER 'www-data'@'localhost';
DROP USER 'nc-user'@'%';
</code>
===== Query Users Rights =====
<code>
MariaDB [(none)]> show grants for 'root'@'localhost';
+------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                      |
+------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                  |
+------------------------------------------------------------------------------------------------+

MariaDB [(none)]> show grants for 'www-data'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for www-data@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'www-data'@'localhost' IDENTIFIED BY PASSWORD '*AF926838A9A0501BA7017D135C123C007497F85B' |
| GRANT ALL PRIVILEGES ON `sitedatabase`.* TO 'www-data'@'localhost'                                              |
+-----------------------------------------------------------------------------------------------------------------+

MariaDB [(none)]> show grants for 'nc-user'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for nc-user@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'nc-user'@'localhost' IDENTIFIED BY PASSWORD '*AF926838A9A0501BA7017D135C123C007497F85B' |
| GRANT ALL PRIVILEGES ON `nextcloud`.* TO 'nc-user'@'localhost'                                                 |
+----------------------------------------------------------------------------------------------------------------+
</code>
===== Grand Users Rights =====
<code>
GRANT ALL PRIVILEGES ON `sitedatabase`.* TO 'www-data'@'localhost'
GRANT ALL PRIVILEGES ON `nextcloud`.* TO 'nc-user'@'localhost'
</code>
===== Migrate Databases =====
Dump the databases on the original server. Better extract them one-by-one so we don't migrate anything unnecessary. To dump as single databases, use the following commands:
    # mysqldump -u root -p --all-databases > all_databases.sql          <--- [All Databases]
    # mysqldump -u [user] -p --opt [database name] > database_name.sql  <--- [Single Database]
    
    # mysqldump -u root -p --opt sitedatabase > sitedatabase_dump.sql
    # mysqldump -u root -p --opt nextcloud > nextcloud_dump.sql

Once the dump is completed, you are ready to transfer the databases. But databases need to be created first on target server.
    # mysql -u root -p 
    CREATE DATABASE sitedatabase;
    CREATE DATABASE nextcloud;
    quit;
Retore the data to the newly creates databases:
    # mysql -u [user] -p --all-databases < all_databases.sql           <--- [All Databases]
    # mysql -u [user] -p newdatabase < DUMP-SITE-DATABASE.sql          <--- [Single Database]
    
    # mysql -u root -p sitedatabase < sitedatabase_dump.sql
    # mysql -u root -p nextcloud < nextcloud_dump.sql