Differences

This shows you the differences between two versions of the page.

--- linux:mariadb:mariadb-queries [2021/03/01 20:27] – oscar
+++ linux:mariadb:mariadb-queries [Unknown date] (current) – removed - external edit (Unknown date) 127.0.0.1
@@ Line 1: / Line 1: @@
-===== 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'@'localhost' 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