MySQL

MySQL 101

• MySQL users are different set of user than Unix or Windows OS user. MySQL maintain its own list of user internally (in the mysql table?)
• Default "root" user has no password, should secure it.
• Default access for anonymous user (ie, '' blank) has some basic priv. it is not that the unix user "mysql" is treated especially. commands that don't specify --user assume the anonymous user. command that don't specify --password assume blank password.
• Most, if not all, commands should be run as normal user. Hardly anything need to be run as root. Unix "root" user is not treated as priviledged when running mysql command. --user root is what does log the user with dba priviledges (and other user can be granted the same).

Reset root user password in case it is lost

1. stop mysqld, may need to login as root and do kill on the mysqld process.
2. login as user that own the mysql db (usually mysql), start
   mysqld --skip-grant-tables
3. mysql -u root
   UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';
   FLUSH PRIVILEGES;
4. At this point, the root user password is reset to "password" .
5. Restart mysqld without the --skip-grant-tables option above.

Setting up the program on FEDORA FC5

Install the packages

(sys admin work, require root priv)
yum install mysql (bunch of these)
	mysql-server... is server portion
	mysql-5... is client package, client is also needed in server to run commands.

Initialize the DB

su - mysql           	# don't do it as ROOT!!  
cd /var/lib/mysql	# DB tables are saved to this dir. 
mysql_install_db

Logs:
/var/log/myusql.log

Start the DB

su - mysql
mysqld_safe &		# need to start it in dir having mysql/host.frm   (no longer needed in mysql 5 ??)

			# after initial startup, maybe subsequent startup should just run mysqld &  ??

Shutdown DB

mysqladmin -u root shutdown
or
mysqladmin -u root shutdown -p	# tell mysql to prompt for pw if it isn't specified on the command line
(the unix user running the commands above don't matter too much, as -u root says to use the mysql root user to carry them out)

Verify status

mysqladmin version 
mysqladmin variables
ps -ef should show running processes like: mysqld_safe, mysqld 

Secure MySQL Installation

Benchmark MySQL

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 for me :(

Checking for installed DB

mysqlshow 
mysqlshow DBNAME
NOTE: special priv needed to see all DB, mysql root user has full access.
mysqlshow  -u root -p 

Add new user

### 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.
# if only specify username eg 'tin-adm', the @'%' will be assumed by default.
# any user that does not exist during grant will be created.




# 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     DBNAME"	or
	"connect DBName"	
- DB Name can be explicityly prefix the table name:
	"SELECT * FROM DBName.testTable01"
- All commands ends in ;  

Examples

mysql -e "SELECT Host,Db,User FROM db" mysql	
                 [------1----]       2    3
	# list all mysql users ?   3, mysql, is a system DB
	
	sudo mysql -u root
	> use mysql;				# mysql db specified in [3] above
	> SELECT Host,Db,User FROM db;
	>
	> DROP user 'tin';			# remove 'tin'@'%'

			


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
	# This eg is for the php licwatch program that monitor FlexLM license usage.


mysql -e "show columns from mydb.test01"
	# show the columns in table test01 in the MYDB database
	# interestingly, interactive mysql run may require special write to /tmp
	# which doesn't work if /tmp is mounted from a samba share (prob cuz "mysql" user can't write to it).
	# such problem won't appear in a one-liner unix shell command!

mysql -e 'show status like "qcache%"'
	# show Qcache performance stats

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

Ref

Tools

[Doc URL: http://tin6150.github.io/psg/psg/mysql.html]
(cc) Tin Ho. See main page for copyright info.

---