Differences

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

--- linux:mariadb:mariadb-setup [2019/09/13 09:54] – external edit 127.0.0.1
+++ linux:mariadb:mariadb-setup [Unknown date] (current) – removed - external edit (Unknown date) 127.0.0.1
@@ Line 1: / Line 1: @@
-===== Install MariaDB =====
-To install MariaDB, run the command below;
-   # apt-get install mariadb-server mariadb-client
-When installed, MariaDB is started and enabled to run on system boot.
-   # systemctl is-enabled mariadb
-   enabled
-   # systemctl status mariadb
-   ● mariadb.service - MariaDB 10.3.15 database server
-      Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
-      Active: active (running) since Thu 2019-07-18 15:31:35 EDT; 9min ago
-        Docs: man:mysqld(8)
-              https://mariadb.com/kb/en/library/systemd/
-    Main PID: 2356 (mysqld)
-      Status: "Taking your SQL requests now..."
-       Tasks: 31 (limit: 1150)
-      Memory: 76.4M
-      CGroup: /system.slice/mariadb.service
-              └─2356 /usr/sbin/mysqld
-After installing MariaDB database server, the commands below can be used to stop, start and enable MariaDB service to always start up when the server boots.
-    # systemctl stop mariadb.service
-    # systemctl start mariadb.service
-    # systemctl enable mariadb.service
-==== Database location ====
-Database is located at:
-    # cd /var/lib/mysql/
-==== Check location used config file ====
-The location of the used config file can be found by:
-   /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
-==== MariaDB initial Security ====
-By default, MariaDB uses unix_socket plugin for authentication and thus, it doesn’t require password to login. You can simply run mysql or mysql -u root to login to MariaDB server.
-   # mysql
-   Welcome to the MariaDB monitor.  Commands end with ; or \g.
-   Your MariaDB connection id is 48
-   Server version: 10.3.15-MariaDB-1 Debian 10
-   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
-   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
-   MariaDB [(none)]>
-You can now run the mysql_secure_installation script to remove MariaDB test databases, anonymous users and disallow remote root login. To run the script, just run the command below
-   # mysql_secure_installation
-When prompted, answer the questions below by following the guide.
-Enter current password for root (enter for none): Just press the Enter
-  * Set root password? [Y/n]: Y
-  * New password: Enter password
-  * Re-enter new password: Repeat password
-  * Remove anonymous users? [Y/n]: Y
-  * Disallow root login remotely? [Y/n]: Y
-  * Remove test database and access to it? [Y/n]:  Y
-  * Reload privilege tables now? [Y/n]:  Y
-  * Restart MariaDB server
-To test if MariaDB is installed, type the commands below to logon to MariaDB server
-    # sudo mysql -u root -p
-Then type the password you created above to sign on… if successful, you should see MariaDB welcome message
-==== Remote Access ====
-By default the remote access is disabled. In order to enable remote access make the following changes to the configuration file:
-<code>
-# nano /etc/mysql/mariadb.conf.d/50-server.cnf
-Disabled the line: bind-address = 127.0.0.1.
-#bind-address            = 127.0.0.1
-#And add 2 new lines:
-skip-networking=0
-skip-bind-address
-</code>
-===== 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