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 restore one database:

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

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.

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;

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