MySQL Usage - Revision history

Boiarino at 14:56, 6 June 2017

2017-06-06T14:56:01Z

← Older revision		Revision as of 10:56, 6 June 2017
Line 14:		Line 14:

	mysqldump database table -h clondb1 -u root -p > tablename.sql		mysqldump database table -h clondb1 -u root -p > tablename.sql

			without data (structure only):

			mysqldump -h clondb1 -u root -p --no-data <dbname> > tablename.sql

	To restore one database:		To restore one database:

2015-11-23T16:15:25Z

← Older revision		Revision as of 12:15, 23 November 2015
Line 47:		Line 47:

	to repair them.		to repair them.

			NOTE: sometimes '--lock-tables=false' have to be used if error 1016 (error 24) occur.

	To dump binary log file contents (example):		To dump binary log file contents (example):

2015-11-23T16:13:15Z

← Older revision		Revision as of 12:13, 23 November 2015
Line 18:		Line 18:

	mysql -u root -p db_name < /usr/local/clas/mysql_dump/backup-file.sql		mysql -u root -p db_name < /usr/local/clas/mysql_dump/backup-file.sql

			NOTE: to restore database on another server you may need to create database first:

			mysql> create database <database>;

	To restore all databases ('USE <db_name>' statement must be in the file):		To restore all databases ('USE <db_name>' statement must be in the file):

2015-11-23T15:27:42Z

@@ Line 33: / Line 33: @@
 NOTE: there is a script '/database/dumpdb' (obtained from Bryan Hess and adjusted) which is running from cron job,
 it is dumping all databases from clondb1 to /mysql/clondb1_dump/ directory on fileserver. See usage inside the script.
 To dump binary log file contents (example):

2015-11-20T19:20:24Z

← Older revision		Revision as of 15:20, 20 November 2015
Line 73:		Line 73:
	mysql> grant all on clas_online.* to clasrun@clon10.jlab.org identified by 'your_mysql_user_password';		mysql> grant all on clas_online.* to clasrun@clon10.jlab.org identified by 'your_mysql_user_password';
	mysql> flush privileges;		mysql> flush privileges;

			Inventory database users (passwords in 'environment.php'):

			grant execute,select on JInventory.* to JInventoryRO@clonwiki.jlab.org identified by '********';
			grant execute,select,INSERT,UPDATE on JInventory.* to JInventoryRW@clonwiki.jlab.org identified by '********';
			grant execute,select,INSERT,UPDATE,delete on JInventory.* to JInventoryRWD@clonwiki.jlab.org identified by '********';
			flush privileges;

	To grant all privileges, including 'grant' privilege, to 'root' do following:		To grant all privileges, including 'grant' privilege, to 'root' do following:

2013-10-03T18:54:55Z

← Older revision		Revision as of 14:54, 3 October 2013
Line 13:		Line 13:
	To dump one table:		To dump one table:

	mysqldump <database~~> <~~table> -h clondb1 -u root -p > <tablename>.sql		mysqldump database table -h clondb1 -u root -p > tablename.sql

	To restore one database:		To restore one database:

2013-10-03T18:54:33Z

← Older revision		Revision as of 14:54, 3 October 2013
Line 10:		Line 10:

	(always do it as a 'root' so you have sufficient privileges, otherwise it may dump not everything and you will never know !).		(always do it as a 'root' so you have sufficient privileges, otherwise it may dump not everything and you will never know !).

			To dump one table:

			mysqldump <database> <table> -h clondb1 -u root -p > <tablename>.sql

	To restore one database:		To restore one database:

2009-06-15T14:17:09Z

← Older revision		Revision as of 10:17, 15 June 2009
Line 87:		Line 87:
	mysql> describe clonalarm;		mysql> describe clonalarm;
	mysql> select * from clonalarm;		mysql> select * from clonalarm;
			mysql> quit

			For example if 'clasrun' want to modify 'clasprod' database on 'clondb1' from remote host and change field 'configuration' in table 'run_log_begin', it should use following commands:

			mysql -h clondb1 -u clasrun
			mysql> use clasprod;
			mysql> UPDATE run_log_begin SET configuration''PROD' WHERE run=xxxxx;
	mysql> quit		mysql> quit

2008-12-17T20:42:27Z

← Older revision		Revision as of 16:42, 17 December 2008
Line 29:		Line 29:
	NOTE: there is a script '/database/dumpdb' (obtained from Bryan Hess and adjusted) which is running from cron job,		NOTE: there is a script '/database/dumpdb' (obtained from Bryan Hess and adjusted) which is running from cron job,
	it is dumping all databases from clondb1 to /mysql/clondb1_dump/ directory on fileserver. See usage inside the script.		it is dumping all databases from clondb1 to /mysql/clondb1_dump/ directory on fileserver. See usage inside the script.

			To dump binary log file contents (example):

			mysqlbinlog clondb1-bin.000360

	To create new database (example):		To create new database (example):

2007-01-25T03:49:36Z

← Older revision		Revision as of 23:49, 24 January 2007
Line 64:		Line 64:

	mysql> grant all on clas_online.* to clasrun@clon10.jlab.org identified by 'your_mysql_user_password';		mysql> grant all on clas_online.* to clasrun@clon10.jlab.org identified by 'your_mysql_user_password';
			mysql> flush privileges;

			To grant all privileges, including 'grant' privilege, to 'root' do following:

			mysql> grant all on . to root@clonweb.jlab.org identified by '********' with grant option;
	mysql> flush privileges;		mysql> flush privileges;