MySQL Usage

To login as root:

 ssh root@clondb1
 mysql -u root -p

To dump one database or several databases:

 ??mysqldump --opt db_name > /usr/local/clas/mysql_dump/backup-file.sql
 mysqldump --databases db_name1 [db_name2 ...] -u root -p > /usr/local/clas/mysql_dump/my_databases.sql

(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

without data (structure only):

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

To restore one database:

 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):

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

To copy database from one server to another:

 mysqldump --opt db_name | mysql --host=remote_host -C db_name

To dump all databases:

 mysqldump --all-databases > /usr/local/clas/mysql_dump/all_databases.sql

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.

If some tables are corrupter, 'mysqldump' will not work. To fix tables following command can be used:

mysqlcheck <database> -h <hostname> -u root -p

to just check tables in <database>, and

mysqlcheck --repair <database> -h <hostname> -u root -p

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):

 mysqlbinlog clondb1-bin.000360

To create new database (example):

 mysql> create database clasdb;

To delete database (example):

 mysql> drop database clasdb;

To creating new table (example):

 create table clonalarm(
      alarm_id integer not null,
      alarm_status integer default NULL,
      alarm_time date default NULL,
      system char(20) default NULL,
      alarm_text varchar(120) default NULL,
      help_id integer default NULL
 )\g

To import data from ASCII file into existing table use 'mysqlimport' (example):

 mysqlimport -u root -p clasprod ./clonalarm.txt

where 'clasprod' is database name and 'clonalarm.txt' is file in /database directory. File name must coinside with table name !

To autorize 'clasrun' access from clon10.jlab.org to 'clas_online' database do following:

 mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,LOCK TABLES ON clas_online.* TO clasrun@clon10.jlab.org;
 mysql> flush privileges;

Other example:

 mysql> grant all on clas_online.* to clasrun@clon10.jlab.org identified by 'your_mysql_user_password';
 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:

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

To revoke access, do following:

 revoke all on clas_online.* from clasrun@clon10.jlab.org;
 mysql> flush privileges;

For example if 'clasrun' want to assess 'test' database on 'clondb1' from remote host, it should use following commands:

 mysql -h clondb1 -u clasrun
 mysql> use clasprod;
 mysql> show tables;
 mysql> describe 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 configurationPROD' WHERE run=xxxxx;
 mysql> quit