MySQL

Setting up the program on FEDORA FC5

yum install mysql (bunch of these)

initialize the DB:
su - mysql           	# don't do it as ROOT!!  start it at /var/lib/mysql
mysql_install_db

Logs:
/var/log/myusql.log


Start DB:
su - mysql
mysqld_safe &		# need to start it in dir having mysql/host.frm

Shutdown DB:
mysqladmin -u root shutdown
or
mysqladmin -u root shutdown -p	# tell mysql to prompt for pw if it isn't empty


Verify status:
mysqladmin version 
mysqladmin variables
ps -ef , running processes: mysqld_safe, mysqld 



You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
cd /usr/share/.../sql-bench ; perl run-all-tests
# didn't work...



Secure it:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h HOSTNAME password 'new-password'


# checking for installed DB:

mysqlshow 
mysqlshow 
--> special priv needed to see all DB, root has full access.


# add new user : http://dev.mysql.com/doc/refman/5.0/en/adding-users.html

### mysql -u root -p -e create user tinh identified by 'Passw0rD'  ## not needed

mysql -u root -p
> grant all privileges on *.* to 'tin-adm'@'localhost' identified by 'PassworD' with grant option
> grant all privileges on *.* to 'tin-adm'@'%'         identified by 'PassworD' with grant option

# tin-adm has full dba access, localhost for local connection, % for all remote host.


# not sure about these...        password is needed for remote connection.
> GRANT USAGE ON *.* to 'tin'@'%';
> GRANT RELOAD,PROCESS ON *.* to 'tin'@'%'             identified by 'PassworD' with grant option;

> GRANT SELECT ON license.* TO 'tin'@'%'               identified by 'PassworD';

SQL queries using mysql

mysql -h hostname -u username -p  -e "SQL Query"
	-h = use given host, localhost by default
	-u = use given username, current user by default
	-p = prompt for password, if it is set
	-e = execute SQL Query, if not issued, give mysql> prompt
	     and one can enter many queries in interactive mode. 


mysql -e "SELECT * FROM t1" test
                 1       2   3
	1 = attributes, not case senstive (same as ANSI std)	
	2 = table names, it *IS* CaSe sensitive!
	3 = DB NAME, case sensitive! 
	

Note on Ineteractive mode when at the mysql> prompt:
- DB Name specified in [3] is done by first issuing 
	"use     "	or
	"connect "	
- All commands ends in ;  


mysql -e "SELECT Host,Db,User FROM db" mysql	
                 [------1----]       2    3
	# list all mysql users ?   3, mysql, is a system DB


mysql -e "SELECT VERSION(), CURRENT_DATE"
	# find current version, and date.  
	# These keyword "queries" does not hit any DB, and it is good to 
	# find out if at least mysql deamon is running.


mysql            -e "SHOW DATABASES"
mysql -u root -p -e "SHOW DATABASES"
	# equiv to cmd mysqlshow 
	# again, mysql DB (and some others) are only visible to priv user, eg root


mysql -u root -p -e "SHOW TABLES" mysql
	# equiv to "mysqlshow mysql" ran by root


mysql -e "describe license_usage" license
	# Describe a table:
	# list all fields in the license_usage TABLE in the license DB

MySQL Backup, Migration

File Formats

DB Dump

http://dev.mysql.com/doc/refman/5.0/en/upgrading-to-arch.html

Migrate to other machine:

create a new db on the new machine "other_hostname"
then do an optimized dump and send it thru a pipe and remote import the data.
Some indexes, foreign key issues may arise... check before proceeding.

shell> mysqladmin -h 'other_hostname' create db_name
shell> mysqldump --opt db_name | mysql -h 'other_hostname' db_name

[Doc URL: http://www.cs.fiu.edu/~tho01/psg/]
(cc) Tin Ho. See main page for copyright info.

---