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>

-h clondb1 -u root -p > <tablename>.sql 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 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; 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