MySQL / MariaDB

From Dikapedia
Jump to: navigation, search


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.
  • 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: /etc/my.cnf

The default port for MySQL is 3306.

Database files: /var/lib/mysql

  • Usually /var/lib/mysql (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

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

Can also use PhpMyAdmin to backup and restore if they are using that.
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?
https://stackoverflow.com/questions/50690076/phpmyadmin-error-incorrect-format-parameter
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?

Sorry! This site is experiencing technical difficulties.
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 'Performance Schema' [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 '/etc/my.cnf'. (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:
[1] Starting MySQL On Low Memory Virtual Machines
[2] Chapter 26 MySQL Performance Schema
[3] 22.3 Performance Schema Startup Configuration
[4] 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: 'Access denied for user 'root'@'localhost'.

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

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:

  1. Check /etc/my.cnf
  2. change 'datadir=/var/lib/mysql' —> datadir= /rescue/var/lib/mysql
  3. 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 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 database error Table … is marked as crashed and should be repaired

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

Preferred method (manual): 

[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]$

Repairing Tables with phpMyAdmin:


For you, you might prefer to use phpMyAdmin. Fortunately, repairing a table with phpMyAdmin is easy.

  1. Log in to your phpMyAdmin or connect to it via your cPanel back-end.
  2. Select the database with the crashed table.
  3. Put a checkmark next to each crashed table.
  4. Select “Repair table” from the “With selected:” drop down at the bottom of the list.
  5. Let phpMyAdmin do its thing.
Retrieved from "https://dikapedia.com/wiki/index.php?title=MySQL_/_MariaDB&oldid=2017"