Difference between revisions of "MySQL"

From "A B C"
Jump to navigation Jump to search
m
m
 
(One intermediate revision by the same user not shown)
Line 5: Line 5:
  
  
−
{{dev}}
+
{{fix}}
  
  
Line 35: Line 35:
 
 
 
 
 
==Installation==
 
==Installation==
 +
===Download===
 +
:1. navigate to the [http://dev.mysql.com/downloads MySQL download page]. Find the link to the appropriate download package of the MySQL community server for your system. (The recommended current release is 5.1 as of this writing). Follow the download instructions.
 +
 +
===Install===
 +
Installers for MySQL exist for most Unix like systems. Excellent step-by-step instalation instructions can be found on the MySQL developers site:
 +
* [http://dev.mysql.com/doc/mysql/en/Mac_OS_X_installation.html Mac OS X]
 +
* [http://dev.mysql.com/doc/refman/5.0/en/linux-rpm.html Linux (via RPM)]
 +
* [http://dev.mysql.com/doc/refman/5.0/en/installing-binary.html Other Unix like systems]
 +
* [http://dev.mysql.com/doc/refman/5.0/en/installing-source.html Compiling from source]
 +
 +
Note that MySQL does its basic work in a client-server model, this means a MySQL server will be running on your machine in the background, brokering all requests from application programs (clients) to store and retrieve items from the database. To work seamlessly, the MySQL server should therefore be started when the system boots up. On my system, a "MySQL startup item " needs to be installed saparately in order for this to work automatically. (Of course, one could also start the MySQL server manually before using it.)
 +
 +
===Post-Installation Setup and Testing===
 +
 +
Navigate to the [http://dev.mysql.com/doc/refman/5.0/en/post-installation.html Post-Installation Setup and Testing] section of the MySQL reference manual. Depending on the way you have installed MySQL, the '''grant tables''' that hold information about MySQL users and privileges will have been installed (most likely) by the <tt>mysql_install_db</tt> program, or you need to run this program yourself. Read and follow  the instructions on the reference page.
 +
 +
Regarding point '''3.''': the MySQL server "<tt>mysqld</tt>" can be started and run by any user. However, you should avoid running the server as the Unix root user for security reasons. If you have installed the option to autostart MySQL upon startup, all should be fine. Otherwise, manually starting the server can be accomplished by something like
 +
sudo /usr/local/mysql/bin/mysqld_safe --user=mysql &
 +
 +
At that point, you will also notice in case the user <tt>mysql</tt> does not have the appropriate privileges.
 +
 +
If the database server is setup to startup automatically, it is a good idea to reboot your system now, so that for further tests the system is in the same state that it will be in in the future. You can then verify that the server is actually running (and see the flags with which it was invoked) by typing:
 +
 +
ps -xa | grep mysql
 +
 +
on my system this produces the following output:
 +
 +
  204  ??  S      0:00.03 /bin/sh ./bin/mysqld_safe
 +
                              --datadir=/usr/local/mysql/data
 +
                              --pid-file=/usr/local/mysql/data/hugin.local.pid
 +
  230  ??  S      0:06.58 /usr/local/mysql/bin/mysqld
 +
                              --basedir=/usr/local/mysql
 +
                              --datadir=/usr/local/mysql/data
 +
                              --user=mysql
 +
                              --pid-file=/usr/local/mysql/data/hugin.local.pid
 +
 +
... where the first line (Process ID 204) is a command script that has started the MySQL daemon <tt>mysqld</tt> and the second line (PID 230) is the daemon (the MySQL server) itself, running in the background and waiting for requests.
 +
 +
Typing ...
 +
/usr/local/mysql/bin/mysqladmin version
 +
... should print the version of your installed database.
 +
 +
===Access privileges===
 +
(These notes are written from a Mac OS X perspective but should apply equally to other Unixes.)
 +
 +
After installation, all files in the <tt>/usr/local/mysql</tt> directory are owned by <tt>root</tt> and thus cannot be used by the user <tt>mysql</tt> (the user ID of the <tt>mysqld</tt> daemon.) We thus change the group ownership of all these files to the group <tt>mysql</tt> by typing:
 +
sudo chgrp -R mysql /usr/local/mysql
 +
 +
As a first action after installing, we should install a password for the database administrator, master and granter of all power: the root user. Note that this is not the same thing (nor does it need to be the same password) as the system's root user. Enter this using the <tt>mysqladmin</tt> tool, by typing
 +
 +
/usr/local/mysql/bin/mysqladmin -u root password ''ArarC0p_y''
 +
 +
(or whatever a good password may be).
 +
 +
However, this is not enough - in fact this probably has only secured access from <tt>localhost</tt> thus leaving a gaping security hole: please refer to the section on granting and revoking access privileges and the grant-tables in the [[Database introduction]] page how to read the tables and make sure everything is secured as it should be.
 +
 +
===;Notes===
 +
 +
Summary of MySQL commands: http://www.pantz.org/software/mysql/mysqlcommands.html
  
  
 
&nbsp;
 
&nbsp;
 +
 
==MariaDB==
 
==MariaDB==
  
Line 63: Line 123:
 
&nbsp;
 
&nbsp;
 
[[Category:Applied_Bioinformatics]]
 
[[Category:Applied_Bioinformatics]]
 +
[[Category:MySQL]]
 
</div>
 
</div>

Latest revision as of 14:32, 16 September 2012

MySQL


The contents of this page has recently been imported from an older version of this Wiki. This page may contain outdated information, information that is irrelevant for this Wiki, information that needs to be differently structured, outdated syntax, and/or broken links. Use with caution!


Summary ...


Related Pages


 

Introductory reading



 

Introduction

 

Installation

Download

1. navigate to the MySQL download page. Find the link to the appropriate download package of the MySQL community server for your system. (The recommended current release is 5.1 as of this writing). Follow the download instructions.

Install

Installers for MySQL exist for most Unix like systems. Excellent step-by-step instalation instructions can be found on the MySQL developers site:

Note that MySQL does its basic work in a client-server model, this means a MySQL server will be running on your machine in the background, brokering all requests from application programs (clients) to store and retrieve items from the database. To work seamlessly, the MySQL server should therefore be started when the system boots up. On my system, a "MySQL startup item " needs to be installed saparately in order for this to work automatically. (Of course, one could also start the MySQL server manually before using it.)

Post-Installation Setup and Testing

Navigate to the Post-Installation Setup and Testing section of the MySQL reference manual. Depending on the way you have installed MySQL, the grant tables that hold information about MySQL users and privileges will have been installed (most likely) by the mysql_install_db program, or you need to run this program yourself. Read and follow the instructions on the reference page.

Regarding point 3.: the MySQL server "mysqld" can be started and run by any user. However, you should avoid running the server as the Unix root user for security reasons. If you have installed the option to autostart MySQL upon startup, all should be fine. Otherwise, manually starting the server can be accomplished by something like 

sudo /usr/local/mysql/bin/mysqld_safe --user=mysql &

At that point, you will also notice in case the user mysql does not have the appropriate privileges.

If the database server is setup to startup automatically, it is a good idea to reboot your system now, so that for further tests the system is in the same state that it will be in in the future. You can then verify that the server is actually running (and see the flags with which it was invoked) by typing: 

ps -xa | grep mysql

on my system this produces the following output: 

  204  ??  S      0:00.03 /bin/sh ./bin/mysqld_safe 
                             --datadir=/usr/local/mysql/data 
                             --pid-file=/usr/local/mysql/data/hugin.local.pid
  230  ??  S      0:06.58 /usr/local/mysql/bin/mysqld
                             --basedir=/usr/local/mysql 
                             --datadir=/usr/local/mysql/data 
                             --user=mysql 
                             --pid-file=/usr/local/mysql/data/hugin.local.pid

... where the first line (Process ID 204) is a command script that has started the MySQL daemon mysqld and the second line (PID 230) is the daemon (the MySQL server) itself, running in the background and waiting for requests.

Typing ... 

/usr/local/mysql/bin/mysqladmin version

... should print the version of your installed database.

Access privileges

(These notes are written from a Mac OS X perspective but should apply equally to other Unixes.)

After installation, all files in the /usr/local/mysql directory are owned by root and thus cannot be used by the user mysql (the user ID of the mysqld daemon.) We thus change the group ownership of all these files to the group mysql by typing: 

sudo chgrp -R mysql /usr/local/mysql

As a first action after installing, we should install a password for the database administrator, master and granter of all power: the root user. Note that this is not the same thing (nor does it need to be the same password) as the system's root user. Enter this using the mysqladmin tool, by typing 

/usr/local/mysql/bin/mysqladmin -u root password ArarC0p_y

(or whatever a good password may be).

However, this is not enough - in fact this probably has only secured access from localhost thus leaving a gaping security hole: please refer to the section on granting and revoking access privileges and the grant-tables in the Database introduction page how to read the tables and make sure everything is secured as it should be.

;Notes

Summary of MySQL commands: http://www.pantz.org/software/mysql/mysqlcommands.html


 

MariaDB

   

Further reading and resources

 

Retrieved from "http://steipe.biochemistry.utoronto.ca/abc/index.php?title=MySQL&oldid=5489"