Note: You are currently viewing documentation for Moodle 3.6. Up-to-date documentation for the latest stable version of Moodle is likely available here: MySQL.

MySQL: Difference between revisions

From MoodleDocs
No edit summary
(Added steps to configure utf8mb4/Barracuda, previously CREATE DATABASE statement said utf8mb4 but Moodle makes Barracuda mandatory with this)
(16 intermediate revisions by 12 users not shown)
Line 4: Line 4:
== Installing MySQL ==
== Installing MySQL ==


* If you are running Linux your preference should be to install using your distributions package manager. This ensures you will get any available updates.  
* If you are running Linux your preference should be to install using your distribution's package manager. This ensures you will get any available updates.  However, you can also use apt-get or yum depending on the distribution that you are running.
* There are installers available for most popular operating systems at http://www.mysql.com/downloads/mysql/.
* There are installers available for most popular operating systems at http://www.mysql.com/downloads/mysql/.
* It is possible and reasonably straightforward to build mysql from source but it is not recommended (the pre-built binaries are supposedly better optimised).
* It is possible and reasonably straightforward to build mysql from source but it is not recommended (the pre-built binaries are supposedly better optimised).
* Make sure you set a password for the 'root' user (see http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html).
* Make sure you set a password for the 'root' user (see http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html).
* Consider installing and configuring my.cnf (the MySQL settings file) to suit your needs. The default configuration is usually very conservative in respect of memory usage versus performance.
* Consider installing and configuring my.cnf (the MySQL settings file) to suit your needs. The default configuration is usually very conservative in respect of memory usage versus performance. Increase the 'max_allowed_packet' setting to at least 4 megabytes.
* If you are going to use Master/Slave replication, you must add binlog_format = 'ROW' into your my.cnf within [mysqld]. Otherwise, Moodle will not be able to write to the database.
 
=== Configure full UTF-8 support ===
 
It's recommended that you have full UTF-8 support configured in MySQL. If this is not done some character sets, notably emojis, cannot be used. It is possible to do this after your site is installed but it is much easier before installation.
 
First check if this is already configured by running the following statement, e.g. at the '''mysql>''' prompt or in phpMyAdmin:
<pre>SHOW GLOBAL VARIABLES WHERE variable_name IN ('innodb_file_format', 'innodb_large_prefix', 'innodb_file_per_table');</pre>
{| class="nicetable"
|-
! Variable_name
! Value
|-
| innodb_file_format
| Barracuda
|-
| innodb_file_per_table
| ON
|-
| innodb_large_prefix
| ON
|}
 
If the three settings you see match the above list then no further configuration changes are needed and you can skip to [[#Creating_Moodle_database| Creating Moodle database]].
 
If your settings do not match this list then you will have to edit your MySQL configuration file. On Linux this may be the file '''/etc/my.cnf''' or '''/etc/mysql/my.cnf''', on Microsoft Windows it may be '''my.ini'''.
 
* Note: You should back up your configuration file before changing it.
* Note: You should back up your databases before making this change.
* Note: Other systems using databases on this server may be impacted by this change.
 
Add the following settings to the configuration file:
<pre>[client]
default-character-set = utf8mb4
 
[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix
 
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake
 
[mysql]
default-character-set = utf8mb4</pre>
 
Restart MySQL to apply these settings.
 
If you have any difficulty applying these settings, see [[MySQL_full_unicode_support]] for further information.
 
If for some reason you cannot change to the recommended settings as described here you can continue to install Moodle, however you must select '''utf8''' and '''utf8_unicode_ci''' for the default character set and collation respectively.


== Creating Moodle database ==
== Creating Moodle database ==
Line 15: Line 67:


The instructions assume that the web server and MySQL server are on the same machine. In this case the 'dbhost' is 'localhost'. If they are on different machines substitute the name of the web server for 'localhost' in the following instructions and the 'dbhost' setting will be the name of the database server.  
The instructions assume that the web server and MySQL server are on the same machine. In this case the 'dbhost' is 'localhost'. If they are on different machines substitute the name of the web server for 'localhost' in the following instructions and the 'dbhost' setting will be the name of the database server.  
Databases have a "Character set" and a "Collation". For Moodle, we recommend the Character Set be set to '''utf8mb4''' and the Collation '''utf8mb4_unicode_ci'''. You may get the option to set these values when you create the database. If you are not given a choice, the default options are probably good. An install on an old server may have the wrong settings.


=== Command line ===  
=== Command line ===  
Line 24: Line 77:
</pre>
</pre>
(Enter the password you previously set - or been given - for the MySQL 'root' user). After some pre-amble this should take you to the ''mysql>'' prompt.
(Enter the password you previously set - or been given - for the MySQL 'root' user). After some pre-amble this should take you to the ''mysql>'' prompt.
* Create a new database (called 'moodle' - substitute your own name if required). We recommend you use '''utf8_unicode_ci''' for collation.
* Create a new database (called 'moodle' - substitute your own name if required).
If you have successfully configured the recommended full UTF-8 support as described above run:
<pre>
<pre>
mysql> CREATE DATABASE moodle DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
mysql> CREATE DATABASE moodle DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
</pre>
</pre>
If you do not have the recommended full UTF-8 support run:
<pre>mysql> CREATE DATABASE moodle DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;</pre>
* Add a user/password with the minimum needed permissions:
* Add a user/password with the minimum needed permissions:
<pre>
<pre>
Line 33: Line 89:
</pre>
</pre>
...which creates a user called 'moodleuser' with a password 'yourpassword'. Make sure you invent a strong password and resist the temptation to 'GRANT ALL'.
...which creates a user called 'moodleuser' with a password 'yourpassword'. Make sure you invent a strong password and resist the temptation to 'GRANT ALL'.
* Exit from mysql:
<pre>
mysql> quit
</pre>


=== phpMyAdmin ===
=== phpMyAdmin ===


[http://www.phpmyadmin.net/ phpMyAdmin] is a web based administration tool for MySQL. If this is available you can use it to create a new database. Make sure that you select 'UTF8' as the default character set.
[http://www.phpmyadmin.net/ phpMyAdmin] is a web based administration tool for MySQL. If this is available you can use it to create a new database. If you have successfully configured the recommended full UTF-8 support as described above select collation '''utf8mb4_unicode_ci'''. If you do not have the recommended full UTF-8 support select collation '''utf8_unicode_ci'''.
 
==Which database belongs to which Moodle==
If you have installed several Moodle installations on the same server, there will be several databases in your MySQL server. The names might be quite poor reflections of the content like  _mdl1 _mdl2 _mdl3 . So how do I see which database goes with which Moodle installation? You can go in with phpMyAdmin and in the various databases check for the table "mdl_course". There you will easily see the name of that Moodle Installation. In table mdl_config you can see the Moodle version. The main URL for the site is not in the database except where there are absolute links.


== See also ==
== See also ==


* [[MariaDB]]
* [[MySQL full unicode support]]
* [http://www.mysql.com/ The MySQL homepage]
* [http://www.mysql.com/ The MySQL homepage]
* [http://moodle.org/mod/forum/view.php?id=45 Using Moodle databases forum]
* [http://en.wikipedia.org/wiki/MySQL Wikipedia article about ''MySQL'']
* [http://en.wikipedia.org/wiki/MySQL Wikipedia article about ''MySQL'']
* [http://forums.mysql.com/read.php?24,92131,92131 List of articles on MySQL performance tuning]
* [http://forums.mysql.com/read.php?24,92131,92131 List of articles on MySQL performance tuning]
Line 49: Line 113:
[[ja:MySQL]]
[[ja:MySQL]]
[[de:MySQL]]
[[de:MySQL]]
[[es:MySQL]]

Revision as of 16:11, 26 January 2018

MySQL is one of the supported databases that underpins a Moodle installation.

Installing MySQL

  • If you are running Linux your preference should be to install using your distribution's package manager. This ensures you will get any available updates. However, you can also use apt-get or yum depending on the distribution that you are running.
  • There are installers available for most popular operating systems at http://www.mysql.com/downloads/mysql/.
  • It is possible and reasonably straightforward to build mysql from source but it is not recommended (the pre-built binaries are supposedly better optimised).
  • Make sure you set a password for the 'root' user (see http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html).
  • Consider installing and configuring my.cnf (the MySQL settings file) to suit your needs. The default configuration is usually very conservative in respect of memory usage versus performance. Increase the 'max_allowed_packet' setting to at least 4 megabytes.
  • If you are going to use Master/Slave replication, you must add binlog_format = 'ROW' into your my.cnf within [mysqld]. Otherwise, Moodle will not be able to write to the database.

Configure full UTF-8 support

It's recommended that you have full UTF-8 support configured in MySQL. If this is not done some character sets, notably emojis, cannot be used. It is possible to do this after your site is installed but it is much easier before installation.

First check if this is already configured by running the following statement, e.g. at the mysql> prompt or in phpMyAdmin:

SHOW GLOBAL VARIABLES WHERE variable_name IN ('innodb_file_format', 'innodb_large_prefix', 'innodb_file_per_table');
Variable_name Value
innodb_file_format Barracuda
innodb_file_per_table ON
innodb_large_prefix ON

If the three settings you see match the above list then no further configuration changes are needed and you can skip to Creating Moodle database.

If your settings do not match this list then you will have to edit your MySQL configuration file. On Linux this may be the file /etc/my.cnf or /etc/mysql/my.cnf, on Microsoft Windows it may be my.ini.

  • Note: You should back up your configuration file before changing it.
  • Note: You should back up your databases before making this change.
  • Note: Other systems using databases on this server may be impacted by this change.

Add the following settings to the configuration file:

[client]
default-character-set = utf8mb4

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake

[mysql]
default-character-set = utf8mb4

Restart MySQL to apply these settings.

If you have any difficulty applying these settings, see MySQL_full_unicode_support for further information.

If for some reason you cannot change to the recommended settings as described here you can continue to install Moodle, however you must select utf8 and utf8_unicode_ci for the default character set and collation respectively.

Creating Moodle database

These are the steps to create an empty Moodle database. Substitute your own database name, user name and password as appropriate.

The instructions assume that the web server and MySQL server are on the same machine. In this case the 'dbhost' is 'localhost'. If they are on different machines substitute the name of the web server for 'localhost' in the following instructions and the 'dbhost' setting will be the name of the database server. Databases have a "Character set" and a "Collation". For Moodle, we recommend the Character Set be set to utf8mb4 and the Collation utf8mb4_unicode_ci. You may get the option to set these values when you create the database. If you are not given a choice, the default options are probably good. An install on an old server may have the wrong settings.

Command line

  • To create a database using the 'mysql' command line client, first log into MySQL
$ mysql -u root -p
Enter password: 

(Enter the password you previously set - or been given - for the MySQL 'root' user). After some pre-amble this should take you to the mysql> prompt.

  • Create a new database (called 'moodle' - substitute your own name if required).

If you have successfully configured the recommended full UTF-8 support as described above run:

mysql> CREATE DATABASE moodle DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

If you do not have the recommended full UTF-8 support run:

mysql> CREATE DATABASE moodle DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  • Add a user/password with the minimum needed permissions:
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,CREATE TEMPORARY TABLES,DROP,INDEX,ALTER ON moodle.* TO moodleuser@localhost IDENTIFIED BY 'yourpassword';

...which creates a user called 'moodleuser' with a password 'yourpassword'. Make sure you invent a strong password and resist the temptation to 'GRANT ALL'.

  • Exit from mysql:
mysql> quit

phpMyAdmin

phpMyAdmin is a web based administration tool for MySQL. If this is available you can use it to create a new database. If you have successfully configured the recommended full UTF-8 support as described above select collation utf8mb4_unicode_ci. If you do not have the recommended full UTF-8 support select collation utf8_unicode_ci.

Which database belongs to which Moodle

If you have installed several Moodle installations on the same server, there will be several databases in your MySQL server. The names might be quite poor reflections of the content like _mdl1 _mdl2 _mdl3 . So how do I see which database goes with which Moodle installation? You can go in with phpMyAdmin and in the various databases check for the table "mdl_course". There you will easily see the name of that Moodle Installation. In table mdl_config you can see the Moodle version. The main URL for the site is not in the database except where there are absolute links.

See also