Mysql - Revision history

PhilippeTeuwen: Reverted edits by Etegohy (Talk) to last revision by PhilippeTeuwen

2010-11-24T20:33:42Z

Reverted edits by Etegohy (Talk) to last revision by PhilippeTeuwen

Can't load revision 6816

at 00:27, 24 November 2010

2010-11-24T00:27:58Z

Can't load revision 6658

PhilippeTeuwen: /* Export to CSV */

2010-05-19T09:10:13Z

Export to CSV

← Older revision		Revision as of 09:10, 19 May 2010
Line 158:		Line 158:
	===Export to CSV===		===Export to CSV===
	mysql> select * from mytable where ...		mysql> select * from mytable where ...
	into outfile '/tmp/~~test3~~.csv' fields terminated by ';' enclosed by '"' lines terminated by '\n';		into outfile '/tmp/test.csv' fields terminated by ';' enclosed by '"' lines terminated by '\n';

	Then e.g. importing it into oocalc, choose UTF8, separator=semicolon, "		Then e.g. importing it into oocalc, choose UTF8, separator=semicolon, "

PhilippeTeuwen: /* Lost MySQL root password? */

2010-05-19T09:09:57Z

Lost MySQL root password?

← Older revision		Revision as of 09:09, 19 May 2010
Line 153:		Line 153:
	* http://www.idevelopernetwork.com/manual/Page0232.htm		* http://www.idevelopernetwork.com/manual/Page0232.htm
	* http://www.mysql.com/doc/en/Access_denied.html		* http://www.mysql.com/doc/en/Access_denied.html

			==Misc==

			===Export to CSV===
			mysql> select * from mytable where ...
			into outfile '/tmp/test3.csv' fields terminated by ';' enclosed by '"' lines terminated by '\n';

			Then e.g. importing it into oocalc, choose UTF8, separator=semicolon, "

PhilippeTeuwen: /* Basic commands */

2008-04-15T15:37:02Z

Basic commands

← Older revision		Revision as of 15:37, 15 April 2008
Line 12:		Line 12:
	Or shorter:		Or shorter:
	mysqladmin password sql_long_pwd		mysqladmin password sql_long_pwd

			Note that by default there are 2 admins, be sure to assign a password to both
			mysql> SET PASSWORD=PASSWORD('sql_long_pwd');
			mysql> SET PASSWORD FOR root@<hostname>=PASSWORD('sql_long_pwd');
			and to not leave the password in clear in you history, that would be a pity...
			grep -i pass ~/.mysql_history

	Now we have to give the pwd each time we manipulate the db:		Now we have to give the pwd each time we manipulate the db:

PhilippeTeuwen at 00:13, 26 February 2008

2008-02-26T00:13:31Z

← Older revision		Revision as of 00:13, 26 February 2008
Line 113:		Line 113:

	And now everything seems to be in order.		And now everything seems to be in order.
			----
			== Lost MySQL root password? ==
			''Converted with [[MediaWiki#HTML-WikiConverter\|HTML::WikiConverter::MediaWiki]] from my old phpwiki site''
			----

			=== Using mysqladmin ===

			first kill (not kill -9) the mysql server
			kill `cat /mysql-data-directory/hostname.pid`

			Restart mysqld with the --skip-grant-tables option
			/path/to/safe_mysqld --skip-grant-tables

			Set a new password with the mysqladmin password command
			mysqladmin -u root password 'mynewpassword'

			either stop mysqld and restart it normally, or just flush privileges
			mysqladmin -h hostname flush-privileges

			now the new password should work.

			=== Using the mysql client: ===

			restart mysqld with the --skip-grant-tables Connect to the mysqld server with
			mysql -u root mysql

			Update root password like this
			mysql> UPDATE user SET Password=PASSWORD('mynewpassword') WHERE User='root';
			mysql> FLUSH PRIVILEGES;

			now the new password should work.

			* http://www.idevelopernetwork.com/manual/Page0232.htm
			* http://www.mysql.com/doc/en/Access_denied.html

192.168.6.2: /* Backups */

2007-10-11T12:19:16Z

Backups

@@ Line 45: / Line 45: @@
 ==Backups==
 cf http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html
 ==Recovery==
 After a violent reboot, I got the following error when accessing a table:

213.219.185.103: /* Backups */

2007-09-10T13:52:46Z

Backups

← Older revision		Revision as of 13:52, 10 September 2007
Line 45:		Line 45:
	==Backups==		==Backups==
	cf http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html		cf http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html
			==Recovery==
			After a violent reboot, I got the following error when accessing a table:

			[ERROR] /usr/sbin/mysqld: Table \'./syslog/logs20070909\' is marked as crashed and last (automatic?) repair failed

			What I did, following http://www.ooad.org/html-chapter/database-administration.html

			#/etc/init.d/mysql stop
			Stopping MySQL database server: mysqld.

			# myisamchk --update-state /var/lib/mysql/syslog/logs20070909.MYI
			Checking MyISAM file: logs20070909.MYI
			Data records: 12664 Deleted blocks: 0
			myisamchk: warning: Table is marked as crashed and last repair failed
			myisamchk: warning: 1 client is using or hasn't closed the table properly
			- check file-size
			- check record delete-chain
			- check key delete-chain
			- check index reference
			- check data record references index: 1
			myisamchk: error: Found 12678 keys of 12664
			- check record links
			myisamchk: error: Record-count is not ok; is 12678 Should be: 12664
			myisamchk: warning: Found 12678 parts Should be: 12664 parts
			MyISAM-table 'logs20070909.MYI' is corrupted
			Fix it using switch "-r" or "-o"

			# myisamchk --update-state -r /var/lib/mysql/syslog/logs20070909.MYI
			- recovering (with sort) MyISAM-table 'logs20070909.MYI'
			Data records: 12664
			- Fixing index 1
			- Fixing index 2
			- Fixing index 3
			- Fixing index 4
			- Fixing index 5
			- Fixing index 6
			Data records: 12678

			# /etc/init.d/mysql start
			Starting MySQL database server: mysqld ..
			Checking for corrupt, not cleanly closed and upgrade needing tables..

			Then I still found another warning in /var/log/syslog:

			Sep 10 15:41:12 sql /etc/mysql/debian-start[30715]: Checking for crashed MySQL tables.
			Sep 10 15:41:21 sql /etc/mysql/debian-start[30725]: WARNING: mysqlcheck has found corrupt tables
			Sep 10 15:41:21 sql /etc/mysql/debian-start[30725]: phpwiki_cartable.page
			Sep 10 15:41:21 sql /etc/mysql/debian-start[30725]: warning : 1 client is using or hasn't closed the table properly

			I did a simple check:
			#/etc/init.d/mysql stop
			# myisamchk --update-state /var/lib/mysql/phpwiki_cartable/*.MYI
			#/etc/init.d/mysql start

			And now everything seems to be in order.

PhilippeTeuwen: /* Installation of Mysql-dependant programs */

2007-04-26T20:31:20Z

Installation of Mysql-dependant programs

← Older revision		Revision as of 20:31, 26 April 2007
Line 41:		Line 41:
	* [[Webcalendar]]		* [[Webcalendar]]
	* [[Php-Syslog-ng]]		* [[Php-Syslog-ng]]
			* [[RSS2Jabber]]

	==Backups==		==Backups==
	cf http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html		cf http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html

PhilippeTeuwen: /* Installation of Mysql-dependant programs */

2007-02-20T18:21:10Z

Installation of Mysql-dependant programs

← Older revision		Revision as of 18:21, 20 February 2007
Line 41:		Line 41:
	* [[Webcalendar]]		* [[Webcalendar]]
	* [[Php-Syslog-ng]]		* [[Php-Syslog-ng]]
			==Backups==
			cf http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html