MySQL - Revision history

Leonstr: /* Configure full UTF-8 support */ Rewrite as MariaDB deprecates innodb_file_format/innodb_large_prefix, setting these generates a warning

2020-08-28T15:09:56Z

Configure full UTF-8 support: Rewrite as MariaDB deprecates innodb_file_format/innodb_large_prefix, setting these generates a warning

← Older revision		Revision as of 15:09, 28 August 2020
Line 13:		Line 13:
	=== Configure full UTF-8 support ===		=== 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.		It's recommended that full UTF-8 support is configured in MySQL. If this is not done some character sets – notably emojis – cannot be used. It is possible to do this after installing your site but is much easier and quicker before installation.

	~~First check~~ if this is already configured by running the following statement, e.g. at the '''mysql>''' prompt or in phpMyAdmin:		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>		<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~~]].		[[File:phpMyAdmin1.png\|alt=innodb_file_format=Barracuda;innodb_file_per_table=ON;innodb_large_prefix=ON]] or [[File:phpMyAdmin2.png\|alt=innodb_file_per_table=ON]]

	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'''~~.		If the settings you see match either list above then no changes are needed and you can skip to [[#Creating_Moodle_database\| Creating Moodle database]].

	* Note: You should back up your configuration file ~~before changing it~~.		If your settings do not match either list you will have to edit the MySQL configuration file. On Linux this may be '''/etc/my.cnf''', '''/etc/mysql/my.cnf''', or '''/etc/my.cnf.d/mariadb-server.cnf'''; on Microsoft Windows it may be '''my.ini'''.
	* 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:		* Note: Back up the configuration file before changing it.
			* Note: Back up all databases before making this change.
			* Note: Other systems with databases on this server may be impacted by this change.

			Add the following settings to the configuration file, skip '''innodb_file_format''' and '''innodb_large_prefix''' if these were blank in the table above:
	<pre>[client]		<pre>[client]
	default-character-set = utf8mb4		default-character-set = utf8mb4

	[mysqld]		[mysqld]
	innodb_file_format = Barracuda		innodb_file_format = Barracuda # Do not set if blank
	innodb_file_per_table = 1		innodb_file_per_table = 1
	innodb_large_prefix = 1		innodb_large_prefix = 1 # Do not set if blank

	character-set-server = utf8mb4		character-set-server = utf8mb4
Line 56:		Line 44:
	default-character-set = utf8mb4</pre>		default-character-set = utf8mb4</pre>

	Restart MySQL to apply these settings.		Restart the MySQL server process to apply these settings (e.g. MariaDB on Linux: '''systemctl restart mariadb''').

	If you have any difficulty applying these settings, see [[MySQL_full_unicode_support]] for further information.		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.		If for some reason you cannot change to the settings described here you can continue to install Moodle but you must select '''utf8''' and '''utf8_unicode_ci''' for the default character set and collation respectively.

	== Creating Moodle database ==		== Creating Moodle database ==

Bostelm: /* Command line */ remove "identified by"

2020-07-29T10:26:06Z

Command line: remove "identified by"

← Older revision		Revision as of 10:26, 29 July 2020
Line 87:		Line 87:
	<pre>		<pre>
	mysql> CREATE USER moodleuser@localhost IDENTIFIED BY 'yourpassword';		mysql> CREATE USER moodleuser@localhost IDENTIFIED BY 'yourpassword';
	mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,CREATE TEMPORARY TABLES,DROP,INDEX,ALTER ON moodle.* TO moodleuser@localhost ~~IDENTIFIED BY 'yourpassword'~~;		mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,CREATE TEMPORARY TABLES,DROP,INDEX,ALTER ON moodle.* TO moodleuser@localhost;
	mysql> FLUSH PRIVILEGES;		mysql> FLUSH PRIVILEGES;
	</pre>		</pre>

Bostelm: /* Command line */ User first needs to be created, then rights granted, at least in recent versions of mysql

2020-07-29T10:25:22Z

Command line: User first needs to be created, then rights granted, at least in recent versions of mysql

← Older revision		Revision as of 10:25, 29 July 2020
Line 86:		Line 86:
	* Add a user/password with the minimum needed permissions:		* Add a user/password with the minimum needed permissions:
	<pre>		<pre>
			mysql> CREATE USER moodleuser@localhost IDENTIFIED BY 'yourpassword';
	mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,CREATE TEMPORARY TABLES,DROP,INDEX,ALTER ON moodle.* TO moodleuser@localhost IDENTIFIED BY 'yourpassword';		mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,CREATE TEMPORARY TABLES,DROP,INDEX,ALTER ON moodle.* TO moodleuser@localhost IDENTIFIED BY 'yourpassword';
			mysql> FLUSH PRIVILEGES;
	</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'.

Hig: /* Configure full UTF-8 support */

2019-07-28T03:48:14Z

Configure full UTF-8 support

← Older revision		Revision as of 03:48, 28 July 2019
Line 47:		Line 47:
	innodb_file_format = Barracuda		innodb_file_format = Barracuda
	innodb_file_per_table = 1		innodb_file_per_table = 1
	innodb_large_prefix		innodb_large_prefix = 1

	character-set-server = utf8mb4		character-set-server = utf8mb4

Leonstr: Added steps to configure utf8mb4/Barracuda, previously CREATE DATABASE statement said utf8mb4 but Moodle makes Barracuda mandatory with this

2018-01-26T16:11:11Z

Added steps to configure utf8mb4/Barracuda, previously CREATE DATABASE statement said utf8mb4 but Moodle makes Barracuda mandatory with this

@@ Line 4: / Line 4: @@
 == 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.  However, you can also use apt-get or yum depending on the distribution that you are running.
+* 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. Also make sure that your collation and character set is using full UTF-8 support. Please view [[MySQL_full_unicode_support#Steps_to_upgrade]].
+* 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.
 == Creating Moodle database ==
@@ Line 26: / Line 77: @@
 </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.
-* Create a new database (called 'moodle' - substitute your own name if required).  We recommend you use '''utf8mb4_unicode_ci''' for Collation.
+* Create a new database (called 'moodle' - substitute your own name if required).
 <pre>
 mysql> CREATE DATABASE moodle DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 </pre>
 * Add a user/password with the minimum needed permissions:
 <pre>
@@ Line 36: / Line 90: @@
 ...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>
 </pre>
 === 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==

Demsley: /* Command line */

2017-10-16T10:00:10Z

Command line

← Older revision		Revision as of 10:00, 16 October 2017
Line 35:		Line 35:
	</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 ===

DanBennett: /* Creating Moodle database */ Fixing conflicting information of database collations

2017-05-16T09:31:06Z

Creating Moodle database: Fixing conflicting information of database collations

← Older revision		Revision as of 09:31, 16 May 2017
Line 16:		Line 16:

	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 the Character set ~~should be~~ '''~~utf8~~''' and the Collation '''~~utf8_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.		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 ===

Abgreeve: /* Installing MySQL - minor UTF-8 update */

2017-05-05T02:19:52Z

Installing MySQL - minor UTF-8 update

← Older revision		Revision as of 02:19, 5 May 2017
Line 8:		Line 8:
	* 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. Increase the 'max_allowed_packet' setting to at least 4 megabytes. Also make sure that your collation is using full UTF-8 support. Please view [[MySQL_full_unicode_support#Steps_to_upgrade]].		* 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. Also make sure that your collation and character set is using full UTF-8 support. Please view [[MySQL_full_unicode_support#Steps_to_upgrade]].
	* 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.		* 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.

Abgreeve: utf8mb4 update

2017-05-05T02:07:52Z

utf8mb4 update

← Older revision		Revision as of 02:07, 5 May 2017
Line 8:		Line 8:
	* 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. Increase the 'max_allowed_packet' setting to at least 4 megabytes.		* 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. Also make sure that your collation is using full UTF-8 support. Please view [[MySQL_full_unicode_support#Steps_to_upgrade]].
	* 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.		* 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.

Line 26:		Line 26:
	</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). We recommend you use '''utf8mb4_unicode_ci''' for Collation.
	<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>
	* Add a user/password with the minimum needed permissions:		* Add a user/password with the minimum needed permissions:

Johannes.albert: /* See also */

2017-03-18T17:58:02Z

← Older revision		Revision as of 17:58, 18 March 2017
Line 46:		Line 46:

	* [[MariaDB]]		* [[MariaDB]]
	* [[~~MySQL_full_unicode_support~~]]		* [[MySQL full unicode support]]
	* [http://www.mysql.com/ The MySQL homepage]		* [http://www.mysql.com/ The MySQL homepage]
	* [http://en.wikipedia.org/wiki/MySQL Wikipedia article about ''MySQL'']		* [http://en.wikipedia.org/wiki/MySQL Wikipedia article about ''MySQL'']