Database 101

2010-05-18T14:41:09Z

JamilaCloud:

== MySQL installation ==
As with any package to be installed: use aptitude, update your APT, then make sure all your (other) software is up-to-date. Then find package ''mysql-server'' - under Debian 5.0 "Lenny" it'll be version 5.0.51a-something. Installing it will also automatically install some dependent packages, like mysql-common, mysql-client etcetera. So after downloading some 40MiB of files, MySQL is installed. The installation script under Debian Lenny asks you for a password for the MySQL root user. Think up a strong password for the MySQL root user; something like ''waYacUbaT2uW''. DON'T use this example password, [http://www.pctools.com/guides/password/ generate your own one!]. DO NOT use the password of the Linux ''root'' user. [http://www.writers.ph writing jobs]

Should you consult [http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html the official MySQL documentation], you might see mention of the ''mysql_install_db'' script - do not worry about that, since Debian has run it for you when it was setting up the database.

The standard user accounts that have been created by the installation script are:
* 'root'@'localhost'
* 'root'@'<hostname>'
* 'root'@'127.0.0.1'
All three root accounts are secured with the same password, the one you specified when installing the package ''mysql-server''.
----
'''NOTE for Debian 4.0 "Etch"'''
When you install MySQL under Etch, there are two root accounts with empty passwords, and two anonymous accounts. NOT safe! You'll want to rectify this in order to secure your MySQL server. Please take the following steps. Start the MySQL client from the command line:
mysql -u root
Then, from the mysql client prompt, set the password for the MySQL root users. They can be treated as two different accounts, but we don't recommend that. We thus give both accounts the same password:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('waYacUbaT2uW');
mysql> SET PASSWORD FOR 'root'@'<hostname>' = PASSWORD('waYacUbaT2uW');
Furthermore, there are two anonymous accounts created. These also have no password, and get full permission on the ''test'' database, as well as on any database who's name begins with ''test_''. These accounts can be fitted with a password (like we did for root - just use two quotes with nothing in between them, instead of two quotes with ''root'' in between), or they can be removed - which we recommend. The (single) command for this is:
<pre>mysql> DROP USER '';</pre>
'''End of NOTE'''
----

To verify the installation you might want to take some of the following steps:
* run ''mysqlshow -u root -p'', and see if that command outputs at least two databases (''information_schema'' and ''mysql'');
* check out the settings of your MySQL server by running ''mysqladmin -u root -p variables'' (piping the output to ''less'' or a file, if necessary);
* have a peek in ''/var/lib/mysql''; the database data files should be there;
* check ''/var/log'' for two logfiles ''mysql.err'' and ''mysql.log''.

==Moving the MySQL databases==
By default the location of your Debian MySQL databases is ''/var/lib/mysql/<database>''. However, sometimes you wish your databases to be in a different location. E.g. ''/data/mysql'', where ''/data'' is a mounted dedicated RAID array. Or perhaps even ''/data/mysql'' is its own array.

Whatever the reason, to move ''all'' MySQL databases from ''/var/lib/mysql'' to another location, you can follow these steps:
* Create the new directory, e.g. ''/data/mysql''
* Make this directory owned by user/group ''mysql''
cd /data
mkdir mysql
chown mysql:mysql mysql
* Shut down the database server with one of the following commands:
mysqladmin -u root -p shutdown
invoke-rc.d mysql stop
* Make a backup copy of ''/etc/mysql/my.cnf'', then edit this file. Find the section ''[mysqld]'' and change the line ''datadir = /var/lib/mysql'' to
datadir = /data/mysql
* As root, move (or better yet: copy) ''all'' of the content of ''/var/lib/mysql'' over to ''/data/mysql''. Make sure you don't accidentally change the ownership or permissions of the files and folders in the ''/var/lib/mysql'' folder. We expect each and every file and folder to be owned by ''mysql:mysql''. Copy command would be (as root):
cp -p -r /var/lib/mysql/* /data/mysql
* Check and doublecheck your ''my.cnf'' settings and your database file owners, attributes and size. After the next step, there ''may'' be no way back!
* Start up your database server:
invoke-rc.d mysql start
* Check the working of your MySQL server:
** issue the following command to get status information (to see if MySQL is running after your start command):
invoke-rc.d mysql status
** look under ''/var/log'' for the default MySQL log files ''mysql.log'' and ''mysql.err''
** check your SysLog (standard: ''/var/log/syslog'') for MySQL error messages, since the default Debian configuration is to log MySQL errors there, rather than in the previously mentioned MySQL logfiles.

== MySQL commands ==
The following are examples of the most common MySQL commands
<pre><nowiki>
SHOW DATABASES;
USE <database>;
SHOW TABLES;
DESCRIBE <table>;
SELECT <col1>,<col2>...<col_i> FROM <table> WHERE <column> = '<value>';
INSERT INTO <table> (<col1>,<col2>...<col_i>) VALUES (<val1>,<val2>...<val_i>);
UPDATE <table> set <col1> = '<value1>' WHERE <column> = '<value>';
DELETE FROM <table> WHERE <column> = '<value>';
</nowiki></pre>
Ofcourse there are more useful commands, see the MySQL website.

SaruWiki - User contributions [en]

Database 101