Database 101

From SaruWiki
Revision as of 08:28, 29 February 2012 by Saruman! (talk | contribs) (→‎MySQL commands: user rights mgmt)
Jump to navigation Jump to search

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, generate your own one!. DO NOT use the password of the Linux root user.

Should you consult 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: 

mysql> DROP USER '';

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 

SELECT User,Host from mysql.user;
SHOW GRANTS FOR '<user>'@'<host>';
GRANT <right>, <right2> ON <database>.* TO '<user>'@'<host>';
REVOKE <right>, <right2> ON <database>.* FROM '<user>'@'<host>';
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>';

Ofcourse there are more useful commands, see the MySQL website.

Retrieved from "https://www.saruman.biz/saruwiki/index.php?title=Database_101&oldid=2711"