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
(copying recent changes from 21 wiki)
(copying recent changes from 21 wiki)
Line 1: Line 1:
{{Installing Moodle}}
{{Installing Moodle}}
MySQL is one of the supported databases that underpins a Moodle installation.  
MySQL is one of the supported databases that underpins a Moodle installation.  
MySQL describes itself as "the most popular Open Source SQL database management system, is developed, distributed, and supported by MySQL AB. MySQL AB is a commercial company, founded by the MySQL developers. It is a second generation Open Source company that unites Open Source values and methodology with a successful business model."
MySQL comes with an array of [http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html storage engines]. The popular ones being MyISAM and InnoDB. Since MySQL 5.5.5, MyISAM was dropped as default and InnoDB was made the [http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html default storage engine] of choice. InnoDB is more well supportable than MyISAM due to known issues with MyISAM.  MyISAM and non-ACID master-master setups can have issues which sometimes prove difficult to support.


== Installing MySQL ==
== Installing MySQL ==
Line 11: Line 7:
* 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.  
* 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.


== Creating Moodle database ==
== Creating Moodle database ==
Line 46: Line 43:
* [http://moodle.org/mod/forum/view.php?id=45 Using Moodle databases forum]
* [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]


[[Category:SQL databases]]
[[Category:SQL databases]]

Revision as of 10:21, 20 December 2011

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 distributions package manager. This ensures you will get any available updates.
  • 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.

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.

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)
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'.

phpMyAdmin

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.

See also