Differences

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

--- linux:mariadb:mariadb-queries [2019/09/13 09:59] – 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>
-===== 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>
-===== 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