Category: Moodle Notes and Tips

Folks at Percona are known for their MySQL DB expertise … and sharing.

https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/

https://www.percona.com/sites/default/files/innodb_performance_optimization_final.pdf

 

If you leave slow query logging running you are eating up even more
resources.

One must at least define the log files in my.cnf

general_log_file=/var/log/mysql/mysql-general.log
slow_query_log_file=/var/log/mysql/mysql-slow.log

Just don’t turn on slow logging in my.cnf.   Can turn it on and off with  two scripts … one to turn on slow logging = slowlogon
and one to turn it off … slowlogoff.   Make them executable by root user only.  Both in /usr/local/bin/ means you can launch them from any location on server.   Replace root and pass below with your superuser credentials.

slowlogon
# to turn on:
mysql -e ‘set global log = 1′ -u root -p’pass’
mysql -e ‘set global log_slow_queries = 1′ -u root -p’pass’
mysql -e ‘set global slow_query_log = 1′ -u root -p’pass’
multitail -i /var/log/mysql/mysql-general.log -i /var/log/mysql/mysql-slow.log;

The multitail line will show in realtime.

slowlogoff
# to turn off
set global slow_query_log = 0
set global log_slow_queries = 0
set global log = 0;

Command to look at the slow log by itself:

mysqldumpslow mysql-slow.log

For a quickie, one could:

fgrep ‘quiz’ /var/log/mysql/mysql-slow.log

or for assignments

fgrep ‘assign’ /var/log/mysql/mysql-slow.log

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

 

MySQLTuner is a perl script so Perl needs to be present on your system.
To find out if you have perl:
which perl
should respond with a path to perl, like:
/usr/bin/perl

We want to install MySQLTuner so we can execute it from any location.

As root user, check your environment.
env |grep PATH [ENTER]

That should displah something like:

PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

cd /usr/local/bin/
wget http://mysqltuner.com/mysqltuner.pl

NOTE: one of the reasons to acquire mysqltuner.pl from the URL above, is
simple … one gets the lastest version – which is, at time of these posting,
is 1.7.13.

After the mysqltuner.pl file has been downloaded …
chmod +x mysqltuner.pl

That makes it executable.

We want to shorten the use of mysqltuner.pl by creating a small bash
shell script called ‘tuner’ to run mysqltuner.pl.

nano tuner
mysqltuner.pl -user=[SUPERUSER] -pass='[SUPERUSERPASSWORD’ –nocolor

where [SUPERUSER] is your superuser for MySQL … like ‘root’.
It is the user that has all privileges.

and were [SUPERUSERPASSWORD] is the password for the [SUPERUSER].

Save ‘tuner’ … [CTRL][x]

Make ‘tuner’ executable:
chmod u+x tuner

Note: the tuner script and mysqltuner.pl are now executable by the root user ONLY.
To run either one must be the root user.

Now you can run tuner as soon as you login to the system.

Type: tuner [ENTER]

Some things to look at for Moodle:

[!!] /var/log/mysqld.log contains 11 warning(s).
[!!] /var/log/mysqld.log contains 5 error(s)

In ——– Performance Metrics

The server from which this was run is an 8Gig memory machine that’s
an all in one … apache, moodle code, and MySQL on same server.
The goal is to get MySQL to use as much memory as possible, while
leaving enough memory for other server processes.

[–] Physical Memory : 7.6G
[–] Max MySQL memory : 338.9M
[–] Other process memory: 604.2M

[OK] Maximum reached memory usage: 176.9M (2.26% of installed RAM)
[OK] Maximum possible memory usage: 338.9M (4.34% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/3M)
[OK] Highest usage of available connections: 4% (7/151)
Note: the above is what Moodle docs refers to as ‘concurrent’.

[OK] Aborted connections: 0.02% (4/17179

The ‘4’ above were restarts of server or restart of MySQL daemon after changing
configuration file.

——– InnoDB Metrics
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/34.6M

The one above is key to getting MySQL databases into memory.
The first number (buffer pool) is what MySQL has to use.
The second number (data size) are your database(s).

You will see this mentioned in Recommendations IF the pool is smaller
than data.

At the bottom of the outpot, are MySQLTuner Recommendations.

First, resorting to editing the DB directly is never the first option – have learned that the hard way. http to https has a utility just for that specific purpose.

If using Moodle version 3.5.x, http://site/admin/tool/httpsreplace/
IF using any other version (lower <- 3.5) then the tool is
http://site/admin/tool/replace/

If the older one, search for http://site/ – including the trailing slash.
Replace with https://site/

If your site has moodle in a directory … like http://site/moodle/ … then include the directory.

Why use the tool … multiple tables – images for themes, for example, could be in  mdl_config_plugins. Images inside courses used as icons for sections are uploaded files and reside in moodledata/filedir/ (those display because code builds from metadata contained in tables for generating course content).

One could, however, use an older method … do a mysql dump of the DB.
Open the sql dump in a text editor that has search and replace … on Linux, nano editor comes to mind.

nano nameofsqldump.sql

In nano [ctrl][w] (where) and in the dialog box for what to locate, use http://site/
Note what table that URL found in. Do another find [ctrl][w] leave the default … which is what you used the first time, and search again.

How many did you find that way?

Now to replace:
In nano, [ctrl][w], then [ctrl][r] first nano will ask for what to search for. After entering http://site/ and pressing enter, it will prompt for what to replace with:
https://site/ Pressing ENTER will begin the routine and take you to the first instanace it finds and ask if you want to replace that one. If you choose to do so, nano then goes the next instance and ask, etc..

OR if you are satisfied it’s finding what it should, look in the menu at the bottom of nano … there is an option to do them all.

When you choose that option, it might take some time. This to say be patient and wait for it to finish. If the sql file is large, they usually are, it will take some time and nano might appear to you as locked up/not doing anything … it is … just give it time.
Do NOT cancel.

Once finished, save the file. [cntrl][o] to write out the file.
Exit nano. [ctrl][x]

From the mysql client create another database for moodle … not the same one that was being used. Exit the client.

Then use command line mysql again only this time tell it to import the editedsql file into the new db you created for Moodle.

mysql -u [superuser[ -p'[superuserpass]’ [newmoodledb] < editedsql.sql

You replace [] items above.

Yet another … using sed.

sed -e “s/https:\/\/sos.sosoftexas.org\/moodle34\//http:\/\/sos.sosoftexas.org\/moodle34\//g” sedtest.sql > editedsedtest.sql

**NOTE**: the \’s around the URL’s ‘/’ are absolutely needed. In linux a ‘\’ command line environments,
like bash, is an escape character – meaning treat the next character as a literal.

Then import newmysqldump.sql into the new database created for the moodle.

Then, last step, edit config.php. file in Moodle code and point to the new DB.

There is also in 3.5.x a command line utility to do the same.
cd /path/to/moodle/code/admin/tool/httpsreplace/cli/
Issuing: php url_replace.php (the script found in that directory) will present you
with help – which says:

Examines DB for non-https src or data links, and lists broken links or replaces all links.
Options:
-h, --help Print out this help
-l, --list List of http (not https) urls on a site in the DB that would become broken.
-r, --replace List of http (not https) urls on a site in the DB that would become broken.
--confirm Replaces http urls with https across a site's content.
Example:
$ sudo -u www-data /usr/bin/php admin/tool/httpsreplace/cli/url_replace.php --list

 

Install mysqltuner.pl

https://github.com/major/MySQLTuner-perl

Has important info and stats:

Info:
[OK] Maximum reached memory usage: 6.4G (40.77% of installed RAM)
[OK] Maximum possible memory usage: 7.4G (47.70% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available

stats
[OK] Slow queries: 0% (1/134M)
[OK] Highest usage of available connections: 10% (49/451)
[OK] Aborted connections: 0.00% (1/3223797)

Report generated by MySQLTuner has a section on InnoDB Metrics and
in that section some important information for tuning.
Examples:

[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size – buffer pool needs to be larger than data size.

[OK] InnoDB buffer pool instances: – needs to be 1 per Gig of buffer pool.

[OK] InnoDB Write Log efficiency:

If you have slow logging defined and turned on, you are making your DB server even slower. While you can and should define the slow log file, you don’t have to set slow logging on in the my.cnf. Rather, turn on slow query logging when you want to get a sampling, then turn it off.

In my.cnf, define the log files:

general_log_file=/var/log/mysql/mysql-general.log
slow_query_log_file=/var/log/mysql/mysql-slow.log

The above log files must belong to mysql user/group.

-rw-r–r–. 1 mysql mysql 416025 May 8 2015 mysql-general.log
-rw-r–r–. 1 mysql mysql 380 May 8 2015 mysql-slow.log

I use 2 bash shell scripts …
One to turn on MySQL slow query logs
The second to turn OFF MySQL slow query logs

Also use a thing called ‘multitail’ which allows watching logs in realtime.
https://www.vanheusden.com/multitail/

Watch MySQL Logs in realtime using multitail
(you replace []’s below)

# to turn on:
mysql -e 'set global log = 1' -u [SUPERUSER] -p'[PASSWORD]'
mysql -e 'set global log_slow_queries = 1' -u [SUPERUSER] -p'[PASSWORD]'
mysql -e 'set global slow_query_log = 1' -u [SUPERUSER] -p'[PASSWORD]'
multitail -i /var/log/mysql/mysql-general.log -i /var/log/mysql/mysql-slow.log;

The mysql-general.log file and the mysql-slow.log already exist and
have to be defined in my.cnf as well as belong to mysql user.

# to turn off
set global slow_query_log = 0
set global log_slow_queries = 0
set global log = 0;

 

checkaddons bash shell script that uses moosh to check your sites addons for updates.

Script:

#!/bin/bash
#
echo ‘Add-on listing: ‘;
cat ./addons.txt;
echo ‘———————‘;
for i in `cat ./addons.txt`
do
echo “Addon in que: $i”;
moosh -n plugin-list |grep $i
done

The addons.txt file for script above:

atto_morefontcolors
auth_saml2

The names can be seen in the Moodle Admin Interface when checking plugins.  The ‘grayed out’ item just below the humanly understandable title of the plugin.  See below:Example of output using the checkaddons and addons.txt file above.
Shows the addons.txt file … thus the plugins grepped that the moosh command
to show all plugins executes.

Add-on listing:
atto_morefontcolors
auth_saml2

———————
Addon in que: atto_morefontcolors
atto_morefontcolors,2.7,2.8,2.9,3.0,3.1,3.2,3.3,3.4,3.5,https://moodle.org/plugins/download.php/17254/atto_morefontcolors_moodle35_2018050202.zip

Addon in que: auth_saml2
auth_saml2,2.7,2.8,2.9,3.0,3.1,3.2,3.3,3.4,3.5,https://moodle.org/plugins/download.php/17556/auth_saml2_moodle35_2018071100.zip
auth_saml2sso,3.0,3.1,3.2,3.3,3.4,3.5,https://moodle.org/plugins/download.php/16753/auth_saml2sso_moodle35_2018051500.zip

Note: one can see that the auth_saml2 and moosh pickups up 3 auth_saml plugins.

 

For some, this might be ho-hum … been there done that … etc.
but thought I’d share …

Recently read a page in which a security researcher investigated thousands
of web sites for the presence of .git and security of git.
Was then reminded to check sites I admin.

https://docs.moodle.org/35/en/Git_for_Administrators
tells one how to use, but there is no advice on security.

If one uses git to install/update/maintain your moodle, check:
https://site/.git/

IF you can see the files/subdirectories in .git,
read the following:

https://en.internetwache.org/dont-publicly-expose-git-or-how-we-downloaded-your-websites-sourcecode-an-analysis-of-alexas-1m-28-07-2015/

Command line way of locking down .git.

[root@moodle]# cd /var/www/html/moodle
[root@moodle moodle]# ls -ld .git
drwxr-xr-x. 8 apache apache 4096 Jul 16 08:42 .git

Oops … .git directory accessible to anyone using browser.
Fix it:

Change ownerships … owner/group … of .git directory

[root@moodle moodle]# chown root:root .git -R
[root@moodle moodle]# ls -ld .git
drwxr-xr-x. 8 root root 4096 Jul 16 08:42 .git

Change permissions on .git directory
g=group
o=all others
r=read
x=execute

[root@moodle moodle]# chmod go-rx .git -R

Aove means change the ‘group/others’ permissions taking away (the minus) read and execute

Checking:

[root@moodle moodle]# ls -ld .git
drwx——. 8 root root 4096 Jul 16 08:42 .git

End result … only the root user and group can rwx in .git directory.

In apache config:

For 2.4:
<DirectoryMatch “^/.*/\.git/”>
Require all denied
</DirectoryMatch>

 

You are expected to change variables/details for versions below.   This script will not create the backup directory.  Operator must create those directories manually.

Code is in /var/www/html/

nano up

Paste the following and adjust the variables

# 3.5.1+ (Build: 20180810)
tar -cvf /home/backup/m35/moodle-code-351+-$(date +%Y%m%d%-H%M%S).tar ./;
tar -cvf /home/backup/m35/moodle-data-min-351+-$(date +%Y%m%d%-H%M%S).tar /var/moodle35data/filedir;
mysqldump -u root -p moodle35 > /home/backup/m35/moodle351+-db-$(date +%Y%m%d%-H%M%S).sql;
ls -l /home/backup/m35/;
echo ‘Paused …’;
read $keypress;
git branch -a
php admin/cli/cron.php;
php admin/cli/maintenance.php –enable;
git pull;
php admin/cli/upgrade.php –non-interactive;
php admin/cli/maintenance.php –disable;
php admin/cli/purge_caches.php;
chown apache:apache * -R
fgrep ‘$release’ version.php

To execute:

cd /var/www/html/

source up [ENTER]

Protect up from browsers:

chmod go-rw up

chown root:root up

ls -l up

would look like:

-rwxr—–. 1 root root 803 Aug 15 18:12 up

Now login to the Moodle and go to Site Admin Menu -> Notifications (version is now?) – check updates.   Get updates to plugins.

The first line of this script was aquired via:

fgrep ‘$release’ version.php

while in the code directory.   Added to the top of the script as a reminder.

Posting assumes you can install git via your package manager (yum/apt-get/whatever).  Many distros now included it when distro installed.

Dunno if you have it?   which git [ENTER]

IF the above doesn’t respond with the path to git (normally /usr/bin/git) then your server doesn’t have it installed.

Situation:  have an existing moodle but installed via ftp/wget or some other method of acquiring code.   Code resides in /var/www/html/ and the version of your moodle is 1.9.x

Using Git: “side load”

cd /var/www/

git clone git://git.moodle.org/moodle.git htmlgit

cd htmlgit

git branch –track MOODLE_19_STABLE origin/MOODLE_19_STABLE

git checkout MOODLE_19_STABLE

To see if you are tracking/checked out the 1.9:

git branch -a

The * should be next to the 1.9 branch.

chown apache:apache * -R

We are still in htmlgit.  Now the side load.

cp -rp .git .gitignore .gitattributes ../html/

The above copies (cp) recursively (-r) and preserves (-p) .git (the hidden .git directory and the hidden .gitignore and .gitattributes files up on level (out of htmlgit) and into html … where the moodle code resides.

Now we check the active code directory …

cd ../html/

ls -ld .git (is .git there?)

Now we check which branch it’s tracking.

git branch -a

Should show us:

* MOODLE_19_STABLE
master

with other versions of Moodle under ‘master’.

To make sure git understands issue the following while in /var/www/html/

git reset –hard

The above should result in something like:

Checking out files: 100% (18560/18560), done.
HEAD is now at [lettersnumbers] release 1.9.20+

You are now ready to update the 1.9.x code to the highest 1.9.x version available.

This is a one time thing … using git from this point forward will take place in the code directory of moodle with other git commands.

You can also remove the /var/www/htmlgit directory we used to side load as it is just taking up space now.

 

 

Teachers Plan!
Server Administrators Plan!

Upgrading a 1.9.x to 3.5.x *requires* planning.

Plan your work … work your plan!

If you can, work with a clone of current 1.9.x site and have plenty of
space to backup code, DB, and data directory at every step of the ‘march’.

Along the ‘march’ from 1.9.x to 3.5.x there will be *new* requirements for:
version of PHP
PHP extentions
version of MySQL
Database character set and database collation
Datebase file type

To ‘plan’ the ‘march’, login to your site.
Go to Server -> Admin -> Environment
Update the Component
In the pick list for Moodle version your current version of Moodle will be selected.
What you see below are the ‘Server Checks’.
Green (OK) is good.
Yellow (check) is no so good but not a show stopper, yet.
Red – must perform before attempting to upgrage your current version of Moodle to
the version selected in the drop down.

See:
https://docs.moodle.org/34/en/Environment

How to use the information …
On a sheet of paper, yep, going very low tech here ….

Hand *draw* a chart …

1st Column – Current version of Moodle or the version chosen in the pick list
2nd Column – PHP version currently running
3rd Column – PHP extensions – note only the ones the Environment check says you need
4th Column – Current Version of Data Base
5th Column – Version of Database needed
6th Column – Notes – this for *when* one has to do the PHP version, MySQL version, character set/collation, etc. changes.

Going from 1.9 to 3.5.x you will need *17* rows:
1st Column
1.9.x
2.0
2.1
2.2
2.3
2.4
2.5
2.6
2.7
2.8
2.9
3.0
3.1
3.2
3.3
3.4
3.5

Google Sheet 1

Got Google?   Get a copy.