MySQL Often Used Commands

1. Creating A Database

mysql>CREATE DATABASE CHARACTER SET = utf8;

2. Creating An Account

mysql>GRANT ALL ON .* TO 'usr'@'host';

Install MySql (5.0) on Fedora 9 Using A Normal Account

Step 1 - Download the binary distribution

The url: http://dev.mysql.com/downloads/

The file name should be mysql-5.0.67-linux-i686-glibc23.tar.gz

Suppose you downloaded the above file to the following location on your local machine.

$HOME/Download/mysql/mysql-5.0.67-linux-i686-glibc23.tar.gz

Step 2 - Decide the installation account and location

If the account is new, you should first create it using root account, like:

>#groupadd howard
>#useradd -g howard howard

Once you have the account, use the account to do the following steps (unless otherwise specified).

Next you need to figure out where to install the mysql 5.0. Usually this is somewhere under the user's home directory, like:

/home/howard/apps/mysql/5.0.67

Create the directory if it doesn't exist.

Step 3 - Unpack the binary distribution zip file

$cd /home/howard/apps/mysql
$tar zxvf /home/howard/Download/mysql/mysql-5.
mysql-5.0.67-linux-i686-glibc23.tar.gz

This will unpack the file to a folder under the /home/howard/apps/mysql, for convenience rename it to a short one:

>$mv mysql-5.0.67-linux-i686-glibc23 5.0.67

Now the unpacking is done. You can check the contents under the folder 5.0.67.

>$cd /home/howard/apps/mysql/5.0.67
>$ls

bin docs lib scripts tests
configure EXCEPTIONS-CLIENT man share
COPYING include mysql-test sql-bench
data INSTALL-BINARY README support-files

Step 4 - Running the initialization script

Important Note: Fedora 9 comes with a default mysql database, if you have installed it, please stop the mysqld daemon service first!! Also the PATH environment variable includes the default mysql path (/usr/bin/). Then you need to add the path /home/howard/apps/mysql/5.0.67/bin to PATH, make sure it comes before the /usr/bin.

Now that we have the binary unpacked, we need to initialize it - run the mysql_install_db script under /home/howard/apps/mysql/5.0.67/scripts directory.

Note: the mysql_install_db script has two important command line options regarding to the installation. One is basedir -- indicating your mysql installation base; the other is datadir -- indicating where the data files go. When using a different account (other than the default mysql one) and installing to a customized location, you have to use these two options to let the script know how to correctly find your new installation.

If you already have the default mysql db installed on your system, there will be a file /etc/my.cnf, which many mysql utilities (mysqld_safe for example) uses as default command line option file. To avoid confusing, explicitly give your options on the command line and use the "--no-defaults" option to tell the script not to try to look up any option files.

>$pwd
/home/howard/apps/mysql/5.0.67
>$./scripts/mysql_install_db --basedir=/home/howard/apps/mysql/5.0.67 --datadir=/home/howard/apps/mysql/5.0.67/data

Installing MySQL system tables...
080906 8:54:08 [Warning] One can only use the --user switch if running as root

OK
Filling help tables...
080906 8:54:08 [Warning] One can only use the --user switch if running as root

OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/home/howard/apps/mysql/5.0.67/bin/mysqladmin -u root password 'new-password'
/home/howard/apps/mysql/5.0.67/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:
/home/howard/apps/mysql/5.0.67/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd . ; /home/howard/apps/mysql/5.0.67/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com


Step 5 - Starting the server

Script: bin/mysqld_safe
options: --basedir --datadir --log-error
--pid-file

log file: under basedir, create log dir.

pid file: under basedir, create var/run/mysqld

bin/mysqld_safe --no-defaults --basedir=/home/howard/apps/mysql/5.0.67 --datadir=/home/howard/apps/mysql/5.0.67/data --log-error=/home/howard/apps/mysql/5.0.67/log/mysql.log --pid-file=/home/howard/apps/mysql/5.0.67/var/run/mysqld/mysqld.pid &

As indicated in the output of mysql_install_db script, after starting the mysqld first time, run the mysql_secure_installation script to setup proper root password and other constraints.

To Stop the server:

bin/mysqladmin --defaults-file=/home/howard/apps/mysql/5.0.67/my.cnf shutdown -u root -p shutdown

Once the server starts up, you can use mysql utility to connect to it, like,

$mysql --defaults-file=/home/howard/apps/mysql/5.0.67/my.cnf -u root -p

Sample my.cnf
[mysqld]
basedir=/home/howard/apps/mysql/5.0.67
datadir=/home/howard/apps/mysql/5.0.67/data
socket=/home/howard/apps/mysql/5.0.67/data/mysql.sock
character-set-server=utf8
default-storage-engine=innodb

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/home/howard/apps/mysql/5.0.67/log/mysql.log
pid-file=/home/howard/apps/mysql/5.0.67/var/run/mysqld/mysqld.pid

[mysqladmin]
socket=/home/howard/apps/mysql/5.0.67/data/mysql.sock

[mysql]
socket=/home/howard/apps/mysql/5.0.67/data/mysql.sock



Other Information


http://dev.mysql.com/doc/refman/5.0/en/installing-binary.html