Database 101 - Revision history

Saruman!: /* MySQL commands */ userdrop

2013-06-03T03:54:11Z

MySQL commands: userdrop

← Older revision		Revision as of 03:54, 3 June 2013
Line 59:		Line 59:
	GRANT <right>, <right2> ON <database>.* TO '<user>'@'<host>';		GRANT <right>, <right2> ON <database>.* TO '<user>'@'<host>';
	REVOKE <right>, <right2> ON <database>.* FROM '<user>'@'<host>';		REVOKE <right>, <right2> ON <database>.* FROM '<user>'@'<host>';
			DROP USER '<user>'@'<host>';
	SHOW DATABASES;		SHOW DATABASES;
	USE <database>;		USE <database>;

Saruman!: /* MySQL commands */ user rights mgmt

2012-02-29T07:28:08Z

MySQL commands: user rights mgmt

← Older revision		Revision as of 07:28, 29 February 2012
Line 55:		Line 55:
	The following are examples of the most common MySQL commands		The following are examples of the most common MySQL commands
	<pre><nowiki>		<pre><nowiki>
			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;		SHOW DATABASES;
	USE <database>;		USE <database>;

Saruman!: /* MySQL commands */ added commas

2010-09-07T11:21:46Z

MySQL commands: added commas

← Older revision		Revision as of 11:21, 7 September 2010
Line 60:		Line 60:
	DESCRIBE <table>;		DESCRIBE <table>;
	SELECT <col1>,<col2>...<col_i> FROM <table> WHERE <column> = '<value>';		SELECT <col1>,<col2>...<col_i> FROM <table> WHERE <column> = '<value>';
	INSERT INTO <table> (<col1>,<col2>...<col_i>) VALUES (<val1>,<val2>...<val_i>);		INSERT INTO <table> (<col1>,<col2>...<col_i>) VALUES ('<val1>','<val2>'...'<val_i>');
	UPDATE <table> set <col1> = '<value1>' WHERE <column> = '<value>';		UPDATE <table> set <col1> = '<value1>' WHERE <column> = '<value>';
	DELETE FROM <table> WHERE <column> = '<value>';		DELETE FROM <table> WHERE <column> = '<value>';
	</nowiki></pre>		</nowiki></pre>
	Ofcourse there are more useful commands, see the MySQL website.		Ofcourse there are more useful commands, see the MySQL website.

Saruman!: Reverted edits by JamilaCloud (Talk) to last revision by Saruman!

2010-05-18T16:00:09Z

Reverted edits by JamilaCloud (Talk) to last revision by Saruman!

← Older revision		Revision as of 16:00, 18 May 2010
Line 1:		Line 1:
	== MySQL installation ==		== 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]~~		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.

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

JamilaCloud at 14:41, 18 May 2010

2010-05-18T14:41:09Z

← Older revision		Revision as of 14:41, 18 May 2010
Line 1:		Line 1:
	== MySQL installation ==		== 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.		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.		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.

Saruman!: /* MySQL commands */

2009-10-23T09:34:18Z

MySQL commands

← Older revision		Revision as of 09:34, 23 October 2009
Line 64:		Line 64:
	DELETE FROM <table> WHERE <column> = '<value>';		DELETE FROM <table> WHERE <column> = '<value>';
	</nowiki></pre>		</nowiki></pre>
			Ofcourse there are more useful commands, see the MySQL website.

Saruman!: /* MYsql commands */ expanded

2009-10-16T10:46:30Z

MYsql commands: expanded

← Older revision		Revision as of 10:46, 16 October 2009
Line 52:		Line 52:
	** 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.		** 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 ==		== MySQL commands ==
			The following are examples of the most common MySQL commands
	~~DELETE~~ FROM "table ~~name"~~ WHERE ~~"colomn name"~~='~~VALUE~~'		<pre><nowiki>
	UPDATE "table ~~name"~~ set ~~"colomn name"~~ = '~~VALUE~~' ~~where "colomn name"~~ = '~~VALUE~~';		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>

Insomnia: /* Moving the MySQL databases */

2009-08-29T08:14:38Z

Moving the MySQL databases

← Older revision		Revision as of 08:14, 29 August 2009
Line 51:		Line 51:
	** look under ''/var/log'' for the default MySQL log files ''mysql.log'' and ''mysql.err''		** 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.		** 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 ==

			DELETE FROM "table name" WHERE "colomn name"='VALUE'
			UPDATE "table name" set "colomn name" = 'VALUE' where "colomn name" = 'VALUE';

Saruman!: distinguished between etch and lenny

2009-02-01T10:52:53Z

distinguished between etch and lenny

← Older revision		Revision as of 10:52, 1 February 2009
Line 1:		Line 1:
	== MySQL installation ==		== 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 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.		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.

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

	~~To now configure~~ the ~~MySQL server, please take the following steps~~:		The standard user accounts that have been created by the installation script are:
	~~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!]~~.		* 'root'@'localhost'
	Start the MySQL client from the command line:		* '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		mysql -u root
	Then, from the mysql client prompt, set the password for the MySQL root user. At installation time, two accounts "root" were created, each with an empty password - NOT safe! One account is used to connect to the MySQL server from localhost, one to connect from the IP address of the machine. They can be treated as two different accounts, but we don't recommend that. We thus give both accounts the same password:		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'@'localhost' = PASSWORD('waYacUbaT2uW');
	mysql> SET PASSWORD FOR 'root'@'<hostname>' = 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 ~~inbetween~~ them, instead of two quotes with ''root'' ~~inbetween~~), or they can be removed - which we recommend. The (single) command for this is:		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>		<pre>mysql> DROP USER '';</pre>
			'''End of NOTE'''
			----

	To verify the installation you might want to take some of the following steps:		To verify the installation you might want to take some of the following steps:

Saruman!: Added database move

2009-01-27T17:05:12Z

Added database move

← Older revision		Revision as of 17:05, 27 January 2009
Line 18:		Line 18:
	* have a peek in ''/var/lib/mysql''; the database data files should be there;		* 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''.		* 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.