MySQL Usage

---++Dump/Restore Login as root

mysql -u root -p

Dump one database or several databases

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

Restore one database

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

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

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

Copy database from one server to another

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

Dump all databases

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

There is a script '/database/dumpdb' (from Bryan Hess) which is running from cron job, it is dumping all databases from clondb1 to /usr/local/clas/mysql_dump directory on fileserver. See usage inside the script. Creating new table: for 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', for 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 !

---++Autorization Login as root

mysql -u root -p

For example, to autorize 'clasrun' access from clon10.jlab.org to 'clas_online' database do following:

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

Other example:

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

To revoke access, do following:

revoke all on clas_online.* from clasrun@clon10.jlab.org;

---++User access 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

-- Main.SergeyBoyarinov - 19 Jun 2006