MySQL

MySQL 101

Reset root user password in case it is lost

http://www.tech-faq.com/reset-mysql-password.shtml
  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
MySQL is not secured by default, should reset the mysql internal root user password (dba):
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h HOSTNAME password 'new-password'
The quotes around the password are to be included in the command line, it won't become part of the password.


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
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.
# 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

http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html

Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.

You can copy the .frm, .MYI, and .MYD files for MyISAM tables between different architectures that support the same floating-point format. (MySQL takes care of any byte-swapping issues.) See Section 14.1, “The MyISAM Storage Engine”.

--** The issue is, Different version of MySQL use different DB Back end. So, this maybe okay for backup and restore to same machine. But if restore to new machine, better have original DB version and config info! Safer to use dump...

Files location: /var/lib/mysql, each db is listed under its own subdir. mysql db presumably contain user db info, etc.

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

Too many writtings, so far, sticking to the reference manual from the source:
http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html

Tools

Toad for MySQL (it is free).






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


hoti1
bofh1