View source for MySQL / MariaDB

MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system.

ADD: https://dev.mysql.com/doc/refman/8.0/en/memory-use.html

==== Installation ====
----


 yum install mariadb-server mariadb -y
 apt-get install mariadb mariadb-server


==== Post-install Configuration ====
----

(Recommend running these in order)

Start the MySQL service:
 # sysVinit
 service mysqld start
 service mariadb start 

 # Systemd
 systemctl start mysql
 systemctl start mysql


Enable service to start it upon boot up:
 systemctl enable mariadb
 chkconfig mysql on


To configure your mysql/mariadb for the very first time to secure it, run the following command below:
:* Set mysql root password.
:* Remove root accounts that are accessible from outside the local host.
:* Remove anonymous-user accounts.
:* Remove the test database, which by default can be accessed by anonymous users.
:* [https://mariadb.com/kb/en/mysql_secure_installation/ More info.]
 mysql_secure_installation


Login mariadb/mysql as root:
 mysql -u root -p


At the database prompt, run the following commands for basic setup:
 # Create the user
 CREATE USER 'admin'@'localhost' IDENTIFIED BY 'PASSwoRD';
 
 # Create the database
 CREATE DATABASE database_name;
 
 # Grant privileges to newly created DB
 GRANT ALL PRIVILEGES ON database_name.* TO 'admin'@'localhost';
 FLUSH PRIVILEGES;
 
 # Confirm DB was created:
 SHOW DATABASES;
 
 # Confirm grants:
 SHOW GRANTS FOR 'admin'@'localhost';
 
 # Quit
 exit


Other useful commands, for instance to see what is your “site_url” option_id 1 and “home” option_id 2.
 SELECT * FROM wp_options WHERE option_value=“https://siteurl.com”;
 SELECT * FROM wp_options WHERE option_id=“1”;
 SELECT * FROM wp_options WHERE option_id=“2”;


====Configuration Files====
----


For most systems the MySQL configuration file is: <b>/etc/my.cnf</b>

The default <b>port</b> for MySQL is <b>3306</b>.

Database files: <b>/var/lib/mysql</b>
* Usually <b>/var/lib/mysql</b> (or /var/db/mysql) directory used to store database and tales under UNIX like operating systems. You can use the following command to locate MySQL datadir: 
 $ grep datadir /etc/my.cnf
 datadir=/var/lib/mysql
(In the Filesystem Hierarchy Standard, /var/lib is listed as containing: State information. Persistent data modified by programs as they run, e.g., databases, packaging system metadata, etc.)


=====Bitnami Configuration files=====
----


MySQL Version
 mysqld --version

MySQL Configuration File
 /opt/bitnami/mysql/my.cnf
* Some configuration overrides are stored in 
 /opt/bitnami/mysql/bitnami/my.cnf.

MySQL Socket
* On Unix, MySQL clients can connect to the server in the local machine using an Unix socket file at 
 /opt/bitnami/mysql/tmp/mysql.sock

MySQL Process Identification Number
* The MySQL .pid file allows other programs to find out the PID (Process Identification Number) of a running script. Find it at 
 /opt/bitnami/mysql/data/mysqld.pid

MySQL Error Log
* The log-error file contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running. If mysqld notices a table that needs to be automatically checked or repaired, it writes a message to the error log.
 /opt/bitnami/mysql/data/mysqld.log


Reference:
https://docs.bitnami.com/bch/infrastructure/mysql/get-started/understand-default-config/


====How to create a back up (dump file) and restore DB====
----

*[[Bitnami#How_to_Backup_and_Restore_Bitnami_Mysql_Database |<b>Bitnami</b> way]]: https://docs.bitnami.com/ibm/apps/redmine/administration/backup-restore-mysql-mariadb/

* The way I did it for my site:
 $ mysqldump --user=<user>--password=<password> <DB Table name> > dikapedia.sql


https://phoenixnap.com/kb/how-to-backup-restore-a-mysql-database </br>
https://wordpress.org/support/article/restoring-your-database-from-backup/

Can also use PhpMyAdmin to backup and restore if they are using that. </br>
https://support.managed.com/kb/a2034/how-to-backup-and-or-restore-your-mysql-database-using-phpmyadmin.aspx

If you run into issues with importing using PhpMyAdmin because the .sql file is large, see: phpMyAdmin - Error > Incorrect format parameter?</br>
https://stackoverflow.com/questions/50690076/phpmyadmin-error-incorrect-format-parameter
</br> It is suggested to increase the values of two variables in php.ini file. Change following in php.ini
 upload_max_filesize=64M
 post_max_size=64M


====How to drop database (delete database)====
----

https://mariadb.com/kb/en/drop-database/

 DROP DATABASE bufg;
 Query OK, 0 rows affected (0.39 sec)
 
 DROP DATABASE bufg;
 ERROR 1008 (HY000): Can't drop database 'bufg'; database doesn't exist
 
  \W
 Show warnings enabled. 
 
 DROP DATABASE IF EXISTS bufg;
 Query OK, 0 rows affected, 1 warning (0.00 sec)
 Note (Code 1008): Can't drop database 'bufg'; database doesn't exist



====Troubleshooting====
----


===== MySQL Crashing due to Out of Memory =====
----

Ever get this message when trying to reach this wiki?

<B>Sorry! This site is experiencing technical difficulties.</B></br>
Try waiting a few minutes and reloading.

(Cannot access the database)


Check database status:
 systemctl status mariadb

Notice the MariaDB database server failed:
 ● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Thu 2020-01-16 04:22:48 UTC; 1min 31s ago
  Process: 27485 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=1/FAILURE)
  Process: 27484 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)
  Process: 27449 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 Main PID: 27484 (code=exited, status=0/SUCCESS)
 
 Jan 16 04:22:47 ip-172-31-33-239.ec2.internal systemd[1]: Starting MariaDB database server...
 Jan 16 04:22:47 ip-172-31-33-239.ec2.internal mariadb-prepare-db-dir[27449]: Database MariaDB is probably 
 initialized in /var/lib/mysql already, nothing is done.
 Jan 16 04:22:47 ip-172-31-33-239.ec2.internal mysqld_safe[27484]: 200116 04:22:47 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
 Jan 16 04:22:47 ip-172-31-33-239.ec2.internal mysqld_safe[27484]: 200116 04:22:47 mysqld_safe Starting 
 mysqld daemon with databases from /var/lib/mysql
 Jan 16 04:22:48 ip-172-31-33-239.ec2.internal systemd[1]: mariadb.service: control process exited, code=exited status=1
 Jan 16 04:22:48 ip-172-31-33-239.ec2.internal systemd[1]: Failed to start MariaDB database server.
 Jan 16 04:22:48 ip-172-31-33-239.ec2.internal systemd[1]: Unit mariadb.service entered failed state.
 Jan 16 04:22:48 ip-172-31-33-239.ec2.internal systemd[1]: mariadb.service failed.

Why did it fail? Check the logs: /var/log/mariadb/mariadb.log
 cat /var/log/mariadb/mariadb.log
 cat /var/log/mariadb/mariadb.log | grep -i error
 tail /var/log/mariadb/mariadb.log

Out of Memory; "Needed 128917504 bytes":
 # cat mariadb.log | grep -i error
 200115  4:20:05 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
 200115  4:20:05 [ERROR] mysqld: Out of memory (Needed 96681984 bytes)
 200115  4:20:05 InnoDB: Fatal error: cannot allocate memory for the buffer pool
 200115  4:20:05 [ERROR] Plugin 'InnoDB' init function returned error.
 200115  4:20:05 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
 200115  4:20:05 [ERROR] Unknown/unsupported storage engine: InnoDB
 200115  4:20:05 [ERROR] Aborting
 200116  4:20:27 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
 200116  4:20:27 [ERROR] mysqld: Out of memory (Needed 96681984 bytes)
 200116  4:20:27 [ERROR] mysqld: Out of memory (Needed 72499200 bytes)
 200116  4:20:27 InnoDB: Fatal error: cannot allocate memory for the buffer pool
 200116  4:20:27 [ERROR] Plugin 'InnoDB' init function returned error.
 200116  4:20:27 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
 200116  4:20:27 [ERROR] Unknown/unsupported storage engine: InnoDB
 200116  4:20:27 [ERROR] Aborting

Check your system's memory... Hmm.. but it says we have 145M available... 
 [root@ip-172-31-33-239 mariadb]# free -mh
               total        used        free      shared  buff/cache   available
 Mem:           461M        303M         57M        504K         99M        145M
 Swap:            0B          0B          0B
 [root@ip-172-31-33-239 mariadb]# free -h
               total        used        free      shared  buff/cache   available
 Mem:           461M        303M         57M        504K         99M        145M
 Swap:            0B          0B          0B
 [root@ip-172-31-33-239 mariadb]# free -m
               total        used        free      shared  buff/cache   available
 Mem:            461         303          57           0          99         145
 Swap:             0           0           0


The issue is due to <b>'Performance Schema'</b> [1]. The MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level [2]. 

When starting up, it allocates all the RAM it needs. By default, it will use around 400MB of RAM, which is quite significant for a small virtual machine (t3a.micro). If you add in default InnoDB buffer pool setting of 128MB, then you're well over 512MB RAM, not including other things from the OS. 

As a fix, according to document [1], disable performance schema under the [mysqld] section of the mysql/mariadb configuration file '<b>/etc/my.cnf</b>'. (I've just disabled this, so lets see if it actually fixes it... Today is 1/16/2020 :
 [mysqld]
 performance_schema=off


For more information about performance schema:</br>
[1] [https://mariadb.com/resources/blog/starting-mysql-on-low-memory-virtual-machines/ Starting MySQL On Low Memory Virtual Machines]</br>
[2] [https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html Chapter 26 MySQL Performance Schema]</br>
[3] [https://dev.mysql.com/doc/refman/5.6/en/performance-schema-startup-configuration.html 22.3 Performance Schema Startup Configuration]</br>
[4] [https://serverfault.com/questions/564748/mysql-mariadb-crashes-frequently MySQL (MariaDB) crashes frequently]




===== MySQL error: 'Access denied for user 'root'@'localhost' (Regain Root access)=====
----

I have worked cases where users were unable to login using their 'root' user and password. Users would get the error: <b>'Access denied for user 'root'@'localhost'</b>.

To fix this, follow the steps below:

1) Edit the MySQL configuration file to add line "skip-grant-tables" under the [mysqld] section:
 $ sudo vi /opt/bitnami/mysql/my.cnf
 
 # Added line "skip-grant-tables" like so:
 [mysqld]
 ...
 skip-grant-tables
 ...

2) Restarted the MySQL service:
 $ sudo systemctl restart mysqld
 $ sudo service mysqld restart
 
 # Bitnami
 $ sudo /opt/bitnami/ctlscript.sh restart

3) Login to MySQL using 'root' user:
 $ mysql -u root mysql

4) Update root password and flush privileges by running the following commands:
 mysql> update user set plugin='mysql_native_password' where User='root';
 mysql> flush privileges;
 mysql> exit;

5) Restart the MySQL service:
 $ sudo /opt/bitnami/ctlscript.sh restart

6) You were then able to login using the new 'root' user's password:
 $ mysql -u root -p

7) *NOTE* We did not do this step during our conversation but I recommend doing so. Please comment out the line "skip-grant-tables" under the [mysqld] section of the file "/opt/bitnami/mysql/my.cnf" as it is no longer needed.


References:</br>
https://superuser.com/questions/603026/mysql-how-to-fix-access-denied-for-user-rootlocalhost</br>
https://intellipaat.com/community/10852/mysql-error-access-denied-for-user-root-localhost</br>
https://stackoverflow.com/questions/1708409/how-to-start-mysql-with-skip-grant-tables/51111334</br>
<b>[Bitnami]</b> Modify the MySQL administrator password: https://docs.bitnami.com/aws/apps/processmakerenterprise/administration/change-reset-password/</br>

 Understand the default MySQL configuration 
https://docs.bitnami.com/bch/infrastructure/mysql/get-started/understand-default-config/

 Start or stop services 
https://docs.bitnami.com/bch/apps/wordpress/administration/control-services/


====How to check wp_options 'siteurl' and 'home'====
----

Reference: https://mariadb.com/resources/blog/how-to-install-and-run-wordpress-with-mariadb/

 mysql -u root -p
 MariaDB [(none)]> use wordpress_db;
 
 MariaDB [wordpress_db]> explain wp_options;
 +--------------+---------------------+------+-----+---------+----------------+
 | Field        | Type                | Null | Key | Default | Extra          |
 +--------------+---------------------+------+-----+---------+----------------+
 | option_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
 | option_name  | varchar(64)         | NO   | UNI |         |                |
 | option_value | longtext            | NO   |     | NULL    |                |
 | autoload     | varchar(20)         | NO   |     | yes     |                |
 +--------------+---------------------+------+-----+---------+----------------+
 4 rows in set (0.01 sec)
  
 MariaDB [wordpress-db]> SELECT * FROM wp_options WHERE option_name = 'home';
 +-----------+-------------+------------------------------+----------+
 | option_id | option_name | option_value                 | autoload |
 +-----------+-------------+------------------------------+----------+
 |         2 | home        | https://website.com | yes      |
 +-----------+-------------+------------------------------+----------+
 1 row in set (0.00 sec) 
 
 MariaDB [wordpress-db]> SELECT * FROM wp_options WHERE option_name = 'siteurl';
 +-----------+-------------+------------------------------+----------+
 | option_id | option_name | option_value                 | autoload |
 +-----------+-------------+------------------------------+----------+
 |         1 | siteurl     | https://website.com | yes      |
 +-----------+-------------+------------------------------+----------+


====How to change wp_options 'siteurl' and 'home'====
----

 MariaDB [(none)]> use wordpress-db   
   
 MariaDB [(none)]> SELECT * FROM wp_options WHERE option_name = 'home';  
 MariaDB [(none)]> UPDATE wp_options SET option_value="https://soundchiropractic.co" WHERE option_name = "home"; 
 
 MariaDB [(none)]> SELECT * FROM wp_options WHERE option_name = 'siteurl'; 
 MariaDB [(none)]> UPDATE wp_options SET option_value="https://soundchiropractic.co"  WHERE option_name = "siteurl;  
 


===== What to do if you want to move MySQL database from secondary volume to the root volume =====
----

What that engineer did was tell the customer to:
# Check <b>/etc/my.cnf</b>
# change 'datadir=/var/lib/mysql'  —> datadir= /rescue/var/lib/mysql
# Restart mysql to see if it works and it did. So now the customer just have to move all their files from /rescue/var/lib/mysql to /var/lib/mysql, and revert the change back to datadir=/var/lib/mysq


Robert's alternative way:
 # stop the mysql service
 $ systemctl stop mysql
 
 $ mount -o bind /var/lib/mysql /mnt/var/lib/mysql
 $ mount -o bind /etc/mysql /mnt/etc/mysql
  
 # then start mysql service
 $ systemctl start mysql
 
 # connect to mysql DB
 $ mysql -u root -p
 
 # then show databases
 SHOW DATABASES;


====[[WordPress|<b>WordPress</b>]] site gets "Error establishing database connection"====
----

Most likely due to out of memory, mysql is down, or corrupted table (from my experience). Check the following logs:
 $ grep -i "memory" /var/log/messages
 $ grep -i "memory" /var/log/mariadb/mariadb.log
 
 # Bitnami MySQL logs:
 /opt/bitnami/mysql/data/mysqld.log


====[[WordPress|<b>WordPress</b>]] database error Table … is marked as crashed and should be repaired====
----

https://chrisjean.com/fix-wordpress-database-table-marked-crashed/

<b>Preferred method (manual)</b>:
 [user@server ~/public_html]$ mysql -u user -p
 Enter password:
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 1120449
 Server version: 5.1.48 MySQL Community Server (GPL)
 
 Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
 This software comes with ABSOLUTELY NO WARRANTY. This is free software,
 and you are welcome to modify and redistribute it under the GPL v2 license
 
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
 mysql> connect database
 Connection id:    1120477
 Current database: database 
 
 mysql> select * from prefix_posts limit 1;
 ERROR 2006 (HY000): MySQL server has gone away
 No connection. Trying to reconnect...
 Connection id:    1120568
 Current database: database 
 
 ERROR 145 (HY000): Table './database/prefix_posts' is marked as crashed and should be repaired
 mysql> repair table prefix_posts;
 +-----------------------+--------+----------+----------+
 | Table                 | Op     | Msg_type | Msg_text |
 +-----------------------+--------+----------+----------+
 | database.prefix_posts | repair | status   | OK       |
 +-----------------------+--------+----------+----------+
 1 row in set (3.56 sec) 
 
 mysql> select * from prefix_posts limit 1;
 +----+-------------+---------------------+---------------------+--------------+------------+---------------+--------------+-------------+----------------+-------------+---------------+-----------+---------+--------+---------------------+---------------------+-----------------------+-------------+------------------------------------------------------------+------------+------------+----------------+---------------+
 | ID | post_author | post_date           | post_date_gmt       | post_content | post_title | post_category | post_excerpt | post_status | comment_status | ping_status | post_password | post_name | to_ping | pinged | post_modified       | post_modified_gmt   | post_content_filtered | post_parent | guid                                                       | menu_order | post_type  | post_mime_type | comment_count |
 +----+-------------+---------------------+---------------------+--------------+------------+---------------+--------------+-------------+----------------+-------------+---------------+-----------+---------+--------+---------------------+---------------------+-----------------------+-------------+------------------------------------------------------------+------------+------------+----------------+---------------+
 | 12 |           8 | 2008-05-20 10:09:49 | 2008-05-20 15:09:49 |              | 308image1  |             0 |              | inherit     | open           | open        |               | 308image1 |         |        | 2008-05-20 10:09:49 | 2008-05-20 15:09:49 |                       |           0 | http://gaarai.com/wp-content/uploads/2008/05/308image1.gif |          0 | attachment | image/gif      |             0 |
 +----+-------------+---------------------+---------------------+--------------+------------+---------------+--------------+-------------+----------------+-------------+---------------+-----------+---------+--------+---------------------+---------------------+-----------------------+-------------+------------------------------------------------------------+------------+------------+----------------+---------------+
 1 row in set (0.00 sec)
 
 mysql> exit
 Bye
 [user@server ~/public_html]$

----

<b>Repairing Tables with phpMyAdmin</b>:


For you, you might prefer to use phpMyAdmin. Fortunately, repairing a table with phpMyAdmin is easy.
# Log in to your phpMyAdmin or connect to it via your cPanel back-end.
# Select the database with the crashed table.
# Put a checkmark next to each crashed table.
# Select “Repair table” from the “With selected:” drop down at the bottom of the list.
# Let phpMyAdmin do its thing.