- 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
- stop mysqld, may need to login as root and do kill on the mysqld process.
- login as user that own the mysql db (usually mysql), start
- mysql -u root
UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';
- At this point, the root user password is reset to "password" .
- 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.
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 & ??
mysqladmin -u root shutdown
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)
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.
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
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  is done by first issuing
"use DBNAME" or
- DB Name can be explicityly prefix the table name:
"SELECT * FROM DBName.testTable01"
- 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
sudo mysql -u root
> use mysql; # mysql db specified in  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
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.
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
Too many writtings, so far, sticking to the reference manual from the source:
Toad for MySQL (it is free).