MySQL - Commads

Connection string Format - "server=127.0.0.1;uid=root;pwd=12345;database=test"


mysql> show databases;


mysql> show databases;
+--------------+
| Database |
+--------------+
| mysql |
| test |
+--------------+
2 rows in set (0.00 sec)

mysql> drop database test;

mysql> create database test;


mysql> SELECT database(); -- Return the name of the current database.

There are two options for starting and stopping the the MySQL Database Server:

• Option 1
• % sh mysql.server start

% sh mysql.server stop

• Option 2
• % cd /usr/local/mysql; bin/safe_mysqld --user=mysql &

% mysqladmin -u root shutdown

on Linux -
$ mysqladmin -u root -ppassword shutdown - PREFERRED
OR $ /etc/rc.d/init.d/mysqld stop
OR $ service mysqld stop


Adding New Users to MySQL
You can add new users to MySQL in two different ways: by using the GRANT statement or my manipulating the MySQL grant tables directly. The preferred method is to use the GRANT statement because they are more concise and less error-prone.
The following examples show how to use the mysql client to set up new users. These examples assume that privileges are set up according to the defaults provided in the previous MySQL DBA Tip, "Setting Up the Initial MySQL Privileges". This means that to make changes, you must be on the same machine where mysqld is running, you must connect as the MySQL root user, and the root user must have the insert privilege for the mysql database and the reload administrative privilege. Also, if you have changed the root user password, you must specify it for the following mysql commands:
You can add new users by issuing GRANT statements:
% mysql -u root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO oracle@localhost
-> IDENTIFIED BY 'manager' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO oracle@"%"
-> IDENTIFIED BY 'manager' WITH GRANT OPTION;

mysql> GRANT RELOAD, PROCESS ON *.* TO admin@localhost;

mysql> GRANT USAGE ON *.* TO dummy@localhost;
The GRANT statements (above) create and set up three new users:

• oracle

A full superuser who can connect to the server from anywhere, but who must use a password 'manager' to do so. Note that we must issue GRANT statements for both oracle@localhost and oracle@"%". If we don't add the entry with localhost, the anonymous user entry for localhost that is create bymysql_install_db will take precedence when we connect from the local host because it has a more specific Host field value and thus comes earlier in theuser table sort order.

• admin

A user who can connect from localhost without a password and who is granted the reload and process administrative privileges. This allows the user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush_* commands, as well as mysqladmin processlist. No database-related privileges are granted. (They can be granted later by issuing additional GRANT statements.)

• dummy

A user who can connect without a password, but only from the local host. The global privileges are all set to 'N' - the usage privilege type allows you to create a user with no privileges. It is assumed that you will grant database-specific privileges later
You can also add the same user access information directly by issuing INSERT statements and then telling the server to reload the grant tables:
$ mysql -u root mysql

mysql> INSERT INTO user VALUES('localhost', 'oracle', PASSWORD('manager'),
-> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

mysql> INSERT INTO user VALUES('%', 'oracle', PASSWORD('manager'),
-> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

mysql> INSERT INTO user SET Host='localhost', User='admin',
-> Reload_priv='Y', Process_priv='Y';

mysql> INSERT INTO user (Host, User, Password)
-> VALUES('localhost', 'dummy', '');

mysql> FLUSH PRIVILEGES;
Depending on you version of MySQL, you may have to use a different number of 'Y' values (versions prior to Version 3.22.11 had fewer privilege columns). For the admin user, the more readable extended INSERT syntax that is available starting with Version 3.22.11 is used.
Note that to set up a superuser, you need only create a user table entry with teh privilege fields set to 'Y'. No db or host table entries are necessary.
The privilege columns in the user table were not set explicitly in the last INSERT statement (for the dummy user), so those columns are assigned the default value of 'N'. This is the same thing that GRANT USAGE does.
The following example adds a user custom who can connect from hosts localhost and sundev5.comanage.net. He wants to access the bankaccount database only fromlocalhost , the expenses database only from sundev5.comanage.net, and the customer database from both hosts.
To set up this user's privileges using GRANT statements, run these commands:
% mysql -u root mysql

mysql> create database bankaccount;

mysql> create database expenses;

mysql> create database customer;

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
-> ON bankaccount.*
-> TO custom@localhost
-> IDENTIFIED BY 'manager';

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
-> ON expenses.*
-> TO custom@sundev5.comanage.net
-> IDENTIFIED BY 'manager';

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
-> ON customer.*
-> TO custom@'%'
-> IDENTIFIED BY 'manager';
We do this to grant statements for teh user 'custom' because we want to give the user access to MySQL both from the local machine with Unix sockets and from the remote machine sundev5.comanage.net over TCP/IP.
To set up the user's privileges by modifying the grant tables directly, run these commands:
$ mysql -u root mysql

mysql> create database bankaccount;

mysql> create database expenses;

mysql> create database customer;

mysql> INSERT INTO user (Host, User, Password)
-> VALUES('localhost', 'custom', PASSWORD('manager'));

mysql> INSERT INTO user (Host, User, Password)
-> VALUES('sundev5.comanage.net', 'custom', PASSWORD('manager'));

mysql> INSERT INTO db
-> (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
-> Create_priv, Drop_priv)
-> VALUES
-> ('localhost', 'bankaccount', 'custom', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

mysql> INSERT INTO db
-> (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
-> Create_priv, Drop_priv)
-> VALUES
-> ('sundev5.comanage.net', 'expenses', 'custom', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

mysql> INSERT INTO db
-> (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
-> Create_priv, Drop_priv)
-> VALUES
-> ('%', 'customer', 'custom', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

mysql> FLUSH PRIVILEGES;
The first two INSERT statements add user table entries that allow user custom to conect from the various hosts with the given password, but grant no permissions to him (all privileges are set to the default value of 'N'). The next two INSERT statements add db table entries that grant privileges to custom for the bankaccount, expenses, andcustomer databases, but only from the proper hosts. As usual, when the grant tables are modified directly, the server must be told to reload them (with FLUSH PRIVILEGES) so that the privilege changes take effect.
If you want to give a specific user access from any machine in a given domain, you can issue a GRANT statement like the following:
mysql> GRANT ...
-> ON *.*
-> TO myusername@"%.mydomainname.com"
-> IDENTIFIED BY 'mypassword';
To do the same thing by modifiying the grant tables directly, use the following:
mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername',
-> PASSWORD('mypassword'), ...

mysql> FLUSH PRIVILEGES;


What are the MySQL Privilege Tables?
The privilege tables are used by the MySQL Privilege System to authenticate a user connecting from a given host, and to associate that user with privileges on a database such as select, insert, update, and delete.
Additional functionality includes the ability to have an anonymous user and to grant privileges for MySQL-specific functions such as LOAD DATA INFILE and administrative operations.
The privilege tables are core to MySQL in order to perform the functions of it's privilege system. The privilege tables are created by running the script mysql_install_db and created in a special database called mysql.
The following is a breif overview of the privilege tables in the mysql database.
Show all databases created for this instance:
% mysqlshow
+-------------+
| Databases |
+-------------+
| bankaccount |
| customer |
| expenses |
| mysql |
| test |
+-------------+
Show all tables in the mysql database:
% mysqlshow -u root mysql
Database: mysql
+--------------+
| Tables |
+--------------+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+--------------+
Describe all Privilege Tables:
% desc user;
+-----------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI | | |
| User | char(16) binary | | PRI | | |
| Password | char(16) binary | | | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Reload_priv | enum('N','Y') | | | N | |
| Shutdown_priv | enum('N','Y') | | | N | |
| Process_priv | enum('N','Y') | | | N | |
| File_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
+-----------------+-----------------+------+-----+---------+-------+
% desc db;
+-----------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI | | |
| Db | char(64) binary | | PRI | | |
| User | char(16) binary | | PRI | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
+-----------------+-----------------+------+-----+---------+-------+
% desc host;
+-----------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI | | |
| Db | char(64) binary | | PRI | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
+-----------------+-----------------+------+-----+---------+-------+
% desc func;
+-------+------------------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+----------+-------+
| name | char(64) binary | | PRI | | |
| ret | tinyint(1) | | | 0 | |
| dl | char(128) | | | | |
| type | enum('function','aggregate') | | | function | |
+-------+------------------------------+------+-----+----------+-------+
% desc tables_priv;
+-------------+-----------------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------------------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI | | |
| Db | char(64) binary | | PRI | | |
| User | char(16) binary | | PRI | | |
| Table_name | char(60) binary | | PRI | | |
| Grantor | char(77) | | MUL | | |
| Timestamp | timestamp(14) | YES | | NULL | |
| Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') | | | | |
| Column_priv | set('Select','Insert','Update','References') | | | | |
+-------------+----------------------------------------------------------
% desc columns_priv;
+-------------+----------------------------------------------+------+----
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+---
| Host | char(60) binary | | PRI | | |
| Db | char(64) binary | | PRI | | |
| User | char(16) binary | | PRI | | |
| Table_name | char(64) binary | | PRI | | |
| Column_name | char(64) binary | | PRI | | |
| Timestamp | timestamp(14) | YES | | NULL | |
| Column_priv | set('Select','Insert','Update','References') | | | | |
+-------------+----------------------------------------------+------+----


To delete a table.
mysql> drop table [table name];
Show all data in a table.
mysql> SELECT * FROM [table name];
Returns the columns and column information pertaining to the designated table.
mysql> show columns from [table name];
Show certain selected rows with the value "whatever".
mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";
Show all records containing the name "Bob" AND the phone number '3444444'.
mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';
Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.
mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;
Show all records starting with the letters 'bob' AND the phone number '3444444'.
mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';
Show all records starting with the letters 'bob' AND the phone number '3444444' limit to records 1 through 5.
mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;
Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.
mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";
Show unique records.
mysql> SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted in an ascending (asc) or descending (desc).
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Return number of rows.
mysql> SELECT COUNT(*) FROM [table name];
Sum column.
mysql> SELECT SUM(*) FROM [table name];
Join tables on common columns.
mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.
# mysql -u root -p

mysql> use mysql;

mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));

mysql> flush privileges;
Change a users password from unix shell.
# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'
Change a users password from MySQL prompt. Login as root. Set the password. Update privs.
# mysql -u root -p

mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

mysql> flush privileges;
Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.
# /etc/init.d/mysql stop

# mysqld_safe --skip-grant-tables &

# mysql -u root

mysql> use mysql;

mysql> update user set password=PASSWORD("newrootpassword") where User='root';

mysql> flush privileges;

mysql> quit

# /etc/init.d/mysql stop

# /etc/init.d/mysql start
Set a root password if there is on root password.
# mysqladmin -u root password newpassword
Update a root password.
# mysqladmin -u root -p oldpassword newpassword
Allow the user "bob" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs.
# mysql -u root -p

mysql> use mysql;

mysql> grant usage on *.* to bob@localhost identified by 'passwd';

mysql> flush privileges;
Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.
# mysql -u root -p

mysql> use mysql;

mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');

mysql> flush privileges; 



or 



mysql> grant all privileges on databasename.* to username@localhost;

mysql> flush privileges;
To update info already in a table.
mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';
Delete a row(s) from a table.
mysql> DELETE from [table name] where [field name] = 'whatever';
Update database permissions/privilages.
mysql> flush privileges;
Delete a column.
mysql> alter table [table name] drop column [column name];
Add a new column to db.
mysql> alter table [table name] add column [new column name] varchar (20);
Change column name.
mysql> alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes.
mysql> alter table [table name] add unique ([column name]);
Make a column bigger.
mysql> alter table [table name] modify [column name] VARCHAR(3);
Delete unique from table.
mysql> alter table [table name] drop index [colmn name];
Load a CSV file into a table.
mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
Dump all databases for backup. Backup file is sql commands to recreate all db's.
# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
Dump one database for backup.
# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
Dump a table from a database.
# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
Restore database (or database table) from backup.
# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
Create Table Example 1.
mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
Create Table Example 2.
mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');
MYSQL Statements and clauses

MYSQL Statements and clauses	String Functions	Date and Time Functions	Mathematical and Aggregate Functions
ALTER DATABASE	AES_DECRYPT	ADDDATE	ABS
ALTER TABLE	AES_ENCRYPT	ADDTIME	ACOS
ALTER VIEW	ASCII	CONVERT_TZ	ASIN
ANALYZE TABLE	BIN	CURDATE	ATAN
BACKUP TABLE	BINARY	CURRENT_DATE	ATAN2
CACHE INDEX	BIT_LENGTH	CURRENT_TIME	AVG
CHANGE MASTER TO	CHAR	CURRENT_TIMESTAMP	BIT_AND
CHECK TABLE	CHAR_LENGTH	CURTIME	BIT_OR
CHECKSUM TABLE	CHARACTER_LENGTH	DATE	BIT_XOR
COMMIT	COMPRESS	DATE_ADD	CEIL
CREATE DATABASE	CONCAT	DATE_FORMAT	CEILING
CREATE INDEX	CONCAT_WS	DATE_SUB	COS
CREATE TABLE	CONV	DATEDIFF	COT
CREATE VIEW	DECODE	DAY	COUNT
DELETE	DES_DECRYPT	DAYNAME	CRC32
DESCRIBE	DES_ENCRYPT	DAYOFMONTH	DEGREES
DO	ELT	DAYOFWEEK	EXP
DROP DATABASE	ENCODE	DAYOFYEAR	FLOOR
DROP INDEX	ENCRYPT	EXTRACT	FORMAT
DROP TABLE	EXPORT_SET	FROM_DAYS	GREATEST
DROP USER	FIELD	FROM_UNIXTIME	GROUP_CONCAT
DROP VIEW	FIND_IN_SET	GET_FORMAT	LEAST
EXPLAIN	HEX	HOUR	LN
FLUSH	INET_ATON	LAST_DAY	LOG
GRANT	INET_NTOA	LOCALTIME	LOG2
HANDLER	INSERT	LOCALTIMESTAMP	LOG10
INSERT	INSTR	MAKEDATE	MAX
JOIN	LCASE	MAKETIME	MIN
KILL	LEFT	MICROSECOND	MOD
LOAD DATA FROM MASTER	LENGTH	MINUTE	PI
LOAD DATA INFILE	LOAD_FILE	MONTH	POW
LOAD INDEX INTO CACHE	LOCATE	MONTHNAME	POWER
LOAD TABLE...FROM MASTER	LOWER	NOW	RADIANS
LOCK TABLES	LPAD	PERIOD_ADD	RAND
OPTIMIZE TABLE	LTRIM	PERIOD_DIFF	ROUND
PURGE MASTER LOGS	MAKE_SET	QUARTER	SIGN
RENAME TABLE	MATCH AGAINST	SEC_TO_TIME	SIN
REPAIR TABLE	MD5	SECOND	SQRT
REPLACE	MID	STR_TO_DATE	STD
RESET	OCT	SUBDATE	STDDEV
RESET MASTER	OCTET_LENGTH	SUBTIME	SUM
RESET SLAVE	OLD_PASSWORD	SYSDATE	TAN
RESTORE TABLE	ORD	TIME	TRUNCATE
REVOKE	PASSWORD	TIMEDIFF	VARIANCE
ROLLBACK	POSITION	TIMESTAMP
ROLLBACK TO SAVEPOINT	QUOTE	TIMESTAMPDIFF
SAVEPOINT	REPEAT	TIMESTAMPADD
SELECT	REPLACE	TIME_FORMAT
SET	REVERSE	TIME_TO_SEC
SET PASSWORD	RIGHT	TO_DAYS
SET SQL_LOG_BIN	RPAD	UNIX_TIMESTAMP
SET TRANSACTION	RTRIM	UTC_DATE
SHOW BINLOG EVENTS	SHA	UTC_TIME
SHOW CHARACTER SET	SHA1	UTC_TIMESTAMP
SHOW COLLATION	SOUNDEX	WEEK
SHOW COLUMNS	SPACE	WEEKDAY
SHOW CREATE DATABASE	STRCMP	WEEKOFYEAR
SHOW CREATE TABLE	SUBSTRING	YEAR
SHOW CREATE VIEW	SUBSTRING_INDEX	YEARWEEK
SHOW DATABASES	TRIM
SHOW ENGINES	UCASE
SHOW ERRORS	UNCOMPRESS
SHOW GRANTS	UNCOMPRESSED_LENGTH
SHOW INDEX	UNHEX
SHOW INNODB STATUS	UPPER
SHOW LOGS
SHOW MASTER LOGS
SHOW MASTER STATUS
SHOW PRIVILEGES
SHOW PROCESSLIST
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW VARIABLES
SHOW WARNINGS
START SLAVE
START TRANSACTION
STOP SLAVE
TRUNCATE TABLE
UNION
UNLOCK TABLES
USE
Flow Control Functions	Perl API - using functions and methods built into the Perl DBI with MySQL	PHP API - using functions built into PHP with MySQL
CASE	available_drivers	mysql_affected_rows
IF	begin_work	mysql_change_user
IFNULL	bind_col	mysql_client_encoding
NULLIF	bind_columns	mysql_close
Command-Line Utilities	bind_param	mysql_connect
comp_err	bind_param_array	mysql_create_db
isamchk	bind_param_inout	mysql_data_seek
make_binary_distribution	can	mysql_db_name
msql2mysql	clone	mysql_db_query
my_print_defaults	column_info	mysql_drop_db
myisamchk	commit	mysql_errno
myisamlog	connect	mysql_error
myisampack	connect_cached	mysql_escape_string
mysqlaccess	data_sources	mysql_fetch_array
mysqladmin	disconnect	mysql_fetch_assoc
mysqlbinlog	do	mysql_fetch_field
mysqlbug	dump_results	mysql_fetch_lengths
mysqlcheck	err	mysql_fetch_object
mysqldump	errstr	mysql_fetch_row
mysqldumpslow	execute	mysql_field_flags
mysqlhotcopy	execute_array	mysql_field_len
mysqlimport	execute_for_fetch	mysql_field_name
mysqlshow	fetch	mysql_field_seek
perror	fetchall_arrayref	mysql_field_table
	fetchall_hashref	mysql_field_type
	fetchrow_array	mysql_free_result
	fetchrow_arrayref	mysql_get_client_info
	fetchrow_hashref	mysql_get_host_info
	finish	mysql_get_proto_info
	foreign_key_info	mysql_get_server_info
	func	mysql_info
	get_info	mysql_insert_id
	installed_versions	mysql_list_dbs
		mysql_list_fields
	last_insert_id
		mysql_list_processes
	looks_like_number
		mysql_list_tables
	neat
		mysql_num_fields
	neat_list
		mysql_num_rows
	parse_dsn
		mysql_pconnect
	parse_trace_flag
		mysql_ping
	parse_trace_flags
		mysql_query
	ping
		mysql_real_escape_string
	prepare
		mysql_result
	prepare_cached
		mysql_select_db
	primary_key
		mysql_stat
	primary_key_info
		mysql_tablename
	quote
		mysql_thread_id
	quote_identifier
		mysql_unbuffered_query
	rollback
	rows
	selectall_arrayref
	selectall_hashref
	selectcol_arrayref
	selectrow_array
	selectrow_arrayref
	selectrow_hashref
	set_err
	state
	table_info
	table_info_all
	tables
	trace
	trace_msg
	type_info
	type_info_all
	Attributes for Handles

Red Hat / Fedora Core RPM Packages:

mysql-VERSION.i386.rpm (Required)
mysql-server-VERSION.i386.rpm (Required)
mysqlclient9-VERSION.i386.rpm (Shared object libraries)
mysql-devel-VERSION.i386.rpm (C include files and libraries for developers)
php-mysql-VERSION.i386.rpm (For accessing MySQL database from php)
Install: rpm -ivh mysql-VERSION.i386.rpm mysql-server-VERSION.i386.rpm mysqlclient9-VERSION.i386.rpm
Check if installed: rpm -q mysql mysql-server mysqlclient9
The examples on this page used mySQL 3.23.58 which is used in Red Hat 7, 8, 9 and Fedora Core 1, 2, 3.
Also see YoLinux.com systems administration - using RPM to set GPG signatures and install RPM packages.
Installing MySQL.com RPM packages: If instaling newer versions of MySQL from RPM packages obtained from MySQL.com, you must first import and register their public GPG key:

Download public key named build@mysql.com from http://www.keyserver.net/ with one of two methods:
wget --output-document=pubkey_mysql.asc http://keyserver.veridis.com:11371/export?id=-8326718950139043339
(Saves key 0x5072E1F5 as file pubkey_mysql.asc)
gpg --keyserver keyserver.veridis.com --recv-key 5072e1f5
gpg --export -a 5072e1f5 > pubkey_mysql.asc
Import key: rpm --import pubkey_mysql.asc
[Potential Pitfall]: Your system should have a host name other than the default "localhost". Give your systems a host name if you get the following installation error:
ERROR: 1062 Duplicate entry 'localhost-root' for key 1
ERROR: 1062 Duplicate entry 'localhost-' for key 1
Use the command hostname to give your system a hostname and also set in the configuration file /etc/sysconfig/network
Ubuntu / Debian package installation:

apt-get install mysql-client
apt-get install mysql-server
Start the database:

Start the database: /etc/rc.d/init.d/mysqld start
(The script will run mysql_install_db to create a default database in /var/lib/mysql/mysql/ if the mysql init script has never been run before. The install script will not be run again as long as the default database directory exists.)
The database executes as user mysqld and group mysqld.
Notes:

One may manually initialize the database with the command: /usr/bin/mysql_install_db
Creates system tables in /var/lib/mysql/mysql/
Only execute the first time MySQL is installed.
Databases located in: /var/lib/mysql/
Default config file installed by RPM: /etc/my.cnf
(Ubuntu: /etc/mysql/my.cnf)
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Post installation:

Admin user id: root
Default password: blank
The first task is to assign a password:

[prompt]$ mysqladmin -u root password 'new-password'

Note: the following SQL commands will also work:
mysql> USE mysql;
mysql> UPDATE user SET Password=PASSWORD('new-password') WHERE user='root';
mysql> FLUSH PRIVILEGES;

Create a database: (Creates directory /var/lib/mysql/bedrock)
[prompt]$ mysqladmin -h localhost -u root -ppassword create bedrock

(or use SQL command: CREATE DATABASE bedrock;)
Show all mysql databases: mysqlshow -u root -ppassword
Add tables, data, etc:
Connect to database and issue the following SQL commands:
[prompt]$ mysql -h localhost -u root -ppassword
...
mysql> show databases; - List all databases in MySQL.
+----------+
| Database |
+----------+
| bedrock |
| mysql |
| test |
+----------+
mysql> use bedrock; - Define database to connect to. Refers to directory path: /var/lib/mysql/bedrock
mysql> create table employee (Name char(20),Dept char(20),jobTitle char(20));
mysql> DESCRIBE employee; - View the table just created. Same as "show columns from employee;"
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| Name | char(20) | YES | | NULL | |
| Dept | char(20) | YES | | NULL | |
| jobTitle | char(20) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.03 sec)

mysql> show tables;
+-------------------+
| Tables_in_bedrock |
+-------------------+
| employee |
+-------------------+

mysql> INSERT INTO employee VALUES ('Fred Flinstone','Quarry Worker','Rock Digger');
mysql> INSERT INTO employee VALUES ('Wilma Flinstone','Finance','Analyst');
mysql> INSERT into employee values ('Barney Rubble','Sales','Neighbor');
mysql> INSERT INTO employee VALUES ('Betty Rubble','IT','Neighbor');

Note: Data type used was CHAR. Other data types include:
CHAR(M) : Fixed length string. Always stores M characters whether it is holding 2 or 20 characters. Where M can range 1 to 255 characters.
VARCHAR(M) : Variable length. Stores only the string. If M is defined to be 200 but the string is 20 characters long, only 20 characters are stored. Slower than CHAR.
INT : Ranging from -2147483648 to 2147483647 or unsigned 0 to 4294967295
FLOAT(M,N) : FLOAT(4,2) - Four digits total of which 2 are after the decimal. i.e. 12.34 Values are rounded to fit format if they are too large.
DATE, TEXT, BLOB, SET, ENUM
Add a user. Use the MySQL SQL console to enter SQL commands. The command mysql with the correct login/password will connect you to the database. The admin tables are stored in the database "mysql".
[prompt]$ mysql -h localhost -u root -ppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.41

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE mysql;
mysql> SHOW TABLES;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+-----------------+
mysql> INSERT INTO user (Host, User, Password, Select_priv) VALUES ('', 'Dude1', password('supersecret'), 'Y');
mysql> FLUSH PRIVILEGES; - Required each time one makes a change to the GRANT table
mysql> GRANT ALL PRIVILEGES ON bedrock.* TO Dude1;
mysql> FLUSH PRIVILEGES; - Required each time one makes a change to the GRANT table
mysql> quit


Note:
There is NO space between the -p and the password! You can omit the password and you will be prompted for it.
The SQL flush command is equivalent to issuing the command:
[prompt]$ mysqladmin reload

Test the database:
mysql> SELECT * from employee;
+-----------------+---------------+-------------+
| Name | Dept | jobTitle |
+-----------------+---------------+-------------+
| Fred Flinstone | Quarry Worker | Rock Digger |
| Wilma Flinstone | Finance | Analyst |
| Barney Rubble | Sales | Neighbor |
| Betty Rubble | IT | Neighbor |
+-----------------+---------------+-------------+
1 row in set (0.00 sec)

mysql> SELECT name FROM employee WHERE dept='Sales';
+---------------+
| name |
+---------------+
| Barney Rubble |
+---------------+
1 row in set (0.00 sec)

Quit from the SQL shell:
[prompt]$ quit

Shutting down the database:
[prompt]$ mysqladmin -u root -ppassword shutdown - PREFERRED
OR
[prompt]$ /etc/rc.d/init.d/mysqld stop
OR
[prompt]$ service mysqld stop

Documentation in /usr/share/doc/mysql-3.23.41/ (local file)

Security:
Security and database access is controlled by the GRANT tables. Access to connect to the database and access to process the transaction (table and column access, etc.) are both required. Privileges are searched in the following order:
user table
db and host table
tables_priv
columns_priv
Use the user table to grant connection privileges to database by a user (host, user name and password). Grant database and table access for transaction access. i.e. grant "SELECT", "UPDATE", "CREATE", "DELETE", "ALTER" etc. permission for database, table, field (columns) or database server access.

Access can be granted by network permissions: GRANT ALL PRIVILEGES on bedrock.* to david@'192.168.10.0/255.255.255.0';
This grants access from nodes 192.168.10.0 - 192.168.10.255. Or the network definitions can reference resolvable names: '%.domain.com'. The host definition of '%' or '' (null) refers to any host. (..according to the documentation. My experience is that in the mysql.user table use only '%' for "Host" to refer to any host.)

mysql> GRANT ALL PRIVILEGES on bedrock.* to david@'%';
mysql> FLUSH PRIVILEGES;

or (more promiscuous)
mysql> GRANT ALL PRIVILEGES on *.* to david@'%' identified by 'david';
mysql> FLUSH PRIVILEGES;

Show privileges: SHOW GRANTS FOR Dude2@'%';

Network security: Use firewall rules (ipchains or iptables) to block internet access to port 3306. (default port used by MySQL)

Note: I have found that when adding access from "anywhere" ('%'), the MySQL database table 'user' requires two entries, 'localhost' and '%'. Also, it is typically safer to allow more privileges to those with 'localhost' access than to users from '%' ("anywhere").

Passwords and connecting to the databse:

Connect: [prompt]$ mysql -h host_name -u user_name -ppassword
Using default blank password: [prompt]$ mysql -h localhost -u root -p
If a password is required, you will be prompted. Note, blank passwords are a security hole which has already lead to one mySQL internet worm. Change any default blank passwords.
Delete null/blank users: DELETE FROM user WHERE User = '';
Beware of open access permissions from hosts '%': SELECT * FROM db WHERE Host = '%';
Change a password:
[prompt]$ mysqladmin -u root -p password new-password


You will be prompted to enter the old root password to complete this command.
or:
[prompt]$ mysqladmin -u root -pold-password password new-password


or:
mysql> SET PASSWORD FOR root@'localhost' = PASSWORD('supersecret');
mysql> FLUSH PRIVILEGES;

As an added security precaution it is wise to delete any user id not used. i.e. any defaults generated for demonstration purposes.
Note that the default port used by MySQL is 3306. This can be protected with firewall rules. See the YoLinux IpTables tutorial.
Debian/Ubuntu upgrades: Note that the Debian/Ubuntu distribution will have an additional file /etc/mysql/debian.conf. This file holds a password for the user "debian-sys-maint" which is used by the install tool dpkg to perform database upgrades. This can also be used in emergencies if you forget the root password. It is also a security hole if the file is available to others.

[Potential Pitfall]: It is very easy to make mistakes which get entered into important tables. If you enter the command twice you may have one incorrect and one correct entry. Look at the table data after a mistake to see what happened in case it needs to be fixed.
Example:

mysql> USE mysql;
mysql> SELECT User,Password,Host from user;
+-------+------------------+------------+
| User | Password | Host |
+-------+------------------+------------+
| root | 99a1544eb571ad63 | localhost |
| | | localhost |
| Dude1 | 81a10dba5f6f2144 | |
| Dude1 | | |
| Dude2 | 92b10dba6f7f3155 | % |
+-------+------------------+------------+
5 rows in set (0.00 sec)
mysql> DELETE FROM user WHERE User='' AND Host='localhost';
mysql> DELETE FROM user WHERE User='Dude1' AND Password='';
mysql> FLUSH PRIVILEGES;
mysql> QUIT
User entries may also be found in the table mysql.db.
mysql> DELETE FROM db WHERE User='Dude3' AND Host='localhost';
[Potential Pitfall]: Any changes (UPDATE) to the user table will require a "FLUSH PRIVILEGES" before the changes will be effective.

mysql> UPDATE user SET Host='%' WHERE User='Dude2';
mysql> FLUSH PRIVILEGES;
This will allow a connection with mysql client from any host:
[prompt]$ mysql -u Dude2 -ppassword -h node.your-domain.com
MySQL root password recovery:

As Linux system root user stop the database process: /etc/init.d/mysql stop
(or: service mysql stop)
Start MySQL in safe mode and skip the use of the "grant tables": /usr/bin/mysqld_safe --user=mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --datadir=/var/lib/mysql --skip-grant-tables --skip-networking &
Reset the MySQL root password: mysqladmin -u root flush-privileges password newpassword
Stop MySQL running in safe mode: kill `cat /var/run/mysqld/mysqld.pid`
Start MySQL: /etc/init.d/mysql start
The new MySQL root password can now be used: mysql -u root -p
Respond withthe password: newpassword
Disabling networking:

If your configuration is a web server interacting with a mySQL database running on the same "localhost" then one may turn off network access to tighten security. Edit shell script:
/usr/bin/safe_mysqld (Fedora Core 3)
/usr/bin/mysqld_safe (Red Hat Enterprise Linux 4)
..
...
if test -z "$args"
then
$NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION \
--datadir=$DATADIR $USER_OPTION --pid-file=$pid_file \
--skip-networking --skip-locking >> $err_log 2>&1
else
eval "$NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION \
--datadir=$DATADIR $USER_OPTION --pid-file=$pid_file \
--skip-networking --skip-locking $args >> $err_log 2>&1"
fi
...
..
Add the flag "--skip-networking" marked in bold.
Mysql 5.0: Networking is disabled by default on the default Ubuntu installation. To enable remote database access, comment out (or remove) the following line with a "#" in the file: /etc/mysql/my.cnf
...
...

bind-address = 127.0.0.1

...
...

Restart the database after making changes.
MySQL Admin Commands:
Statistics: [prompt]$ mysqladmin version
List database environment: [prompt]$ mysqladmin variables
Show if database is running: [prompt]$ mysqladmin ping
Show databases available:
[prompt]$ mysqlshow

+-----------+
| Databases |
+-----------+
| bedrock |
| mysql |
| test |
+-----------+
OR
mysql> SHOW DATABASES;
Delete database: mysql> drop database bedrock;
Show list of active threads in server:
[prompt]$ mysqladmin -h localhost -u root -p processlist

+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 15 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
Delete a database: [prompt]$ mysqladmin drop database-name
Execute SQL from Linux command line interface:
[prompt]$ mysql -h localhost -u root -p -e "select host,db,user from db" mysql
Execute SQL command file from Linux command line interface:
[prompt]$ mysql -h localhost -u root -p database-name < text-file-with-sql-statements.sql

Loadtest (benchmark) the system:
[prompt]$ cd sql-bench
[prompt]$ run-all-tests
or
[prompt]$ mysql -vvf test < ./tests/auto_increment.tst
Sample SQL:
SQL requests are either administrative or data-related. The following are sample SQL segments and are not necessarily pertinent to the previous example:

mysql> CREATE DATABASE bedrock;
mysql> USE bedrock;
mysql> SHOW tables;
mysql> SHOW DATABASES;
mysql> LOAD DATA LOCAL INFILE "data.txt" INTO TABLE bedrock;
mysql> SELECT DISTINCT dept FROM bedrock;
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird";
mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f";
mysql> SELECT * FROM pet WHERE birth >= "1998-1-1";
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
mysql> SELECT * FROM pet WHERE name LIKE "b%";
mysql> SELECT * FROM pet WHERE name REGEXP "^b";
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
mysql> SELECT MAX(article) AS article FROM shop;
mysql> DROP TABLE tmp;
mysql> CREATE TABLE retired_employee (
Name char(20) DEFAULT '' NOT NULL,
Dept char(10) DEFAULT '' NOT NULL,
JobTitle char(20),
UNIQUE name_dept (Name,Dept)
);
mysql> CREATE UNIQUE index name_dept on employee (name,dept); - avoids duplicate keys
mysql> INSERT INTO employee VALUES ("Jane Smith","Sales","Customer Rep");
mysql> INSERT INTO employee VALUES ('Jane Smith','Sales','Account Manager');
mysql> INSERT INTO employee VALUES ('Jane Smith','Engineerin','Manager');
mysql> UPDATE employee SET dept='HR' WHERE name='Jane Smith';

Use "auto_increment" integer column:
mysql> ALTER TABLE employee ADD EmpId INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

mysql> SHOW INDEX FROM employee;
mysql> ALTER TABLE employee DROP INDEX name_dept; - get rid of
mysql> SELECT VERSION();
mysql> SELECT NOW();
mysql> SELECT USER();
mysql> SELECT * FROM employee WHERE name LIKE "%Sm%";
mysql> SELECT * FROM employee WHERE name REGEXP "^Ja";
mysql>

See section 3 of MySQL manual for more examples.
Tip: Execute a shell command from the MySQL client interface, use either option:
system ls -l
OR
\! ls -l
Example: execute the "ls" command to list files from the MySQL client.
Loading Data into the MySQL database:
Loading a SQL file into MySQL:

Import SQL file from MySQL client command line:
mysql> source file.sql
OR
mysql> \. file.sql
The SQL file may have schema generation statements like CREATE TABLE ... or data load statements like INSERT INTO ... . The statements in the SQL file will be executed as if they were being specified at the MySQL client command line interface.
One may import data into the MySQL database from SQL files or "load" data from CSV or tab delimited files using the LOAD command:

Loading CSV or tab delimeted files into MySQL:

"LOAD DATA LOCAL INFILE" vs "LOAD DATA INFILE": The term "LOCAL" pertains to whether the file is local to the MySQL client. Without the keyword "LOCAL", the datafile must reside on the same computer as the database server. The location of the client in this case would be irrelevant. The "LOAD DATA INFILE" has many file permission pitfalls and is thus trickey. In fact I have never been sucessful using this method with a user directory.
Load a tab delimited file into the database:

Command: LOAD DATA LOCAL INFILE 'file.dat' INTO TABLE employer;

Input tab delimited file: file.dat

Fred Flinstone Quarry Worker Rock Digger
Wilma Flinstone Finance Analyst
Barney Rubble Sales Neighbor
Betty Rubble IT Neighbor

Note:
The number of tab delimeted fields MUST match the number and order of fields in the database.
Load a comma delimited file (CSV) into the database:

Command: LOAD DATA LOCAL INFILE "/tmp/TableData.csv" INTO TABLE employer FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """" LINES TERMINATED BY "\r\n" (Name, Dept, jobTitle);
Note:
MS/Windows generated files will have lines terminated by "\r\n".
Linux/Unix generated files will have lines terminated by "\n".
File locations on database server must be absolute path names, relative path or relative to the mysqld process owner's home directory (typically /var/lib/mysql/). File locations on the client may be fully qualified or relative to the current mysql client directory.
Fully qualified: /tmp/TableData.csv
Relative to current mysql client directory: ./TableData.csv
(Verify current directory: mysql> \! pwd)
Database process owner home directory: TableData.csv
(Actual: /var/lib/mysql/TableData.csv)
Text strings often are encapsulated by quotes so that the strings may contain a comma without representing a new field.
[Potential Pitfalls]:

ERROR 13 (HY000): Can't get stat of '/tmp/TableData.csv' (Errcode: 13)
The fils is local and you have not specified the "LOCAL" directive.
ERROR 29 (HY000): File '/var/lib/mysql/test/TableData.csv' not found (Errcode: 2)
Error from command LOAD DATA INFILE 'TableData.csv' INTO ... where the file is assumed to be read from the /database-process-home-directory/mysql-database-name/TableData.csv
(Note: Database name "test" is being used.)
ERROR 1045 (28000): Access denied for user 'user1'@'%' (using password: YES)
OR
ERROR 2 (HY000): File '/tmp/TableData.csv' not found (Errcode: 2)
Error from command LOAD DATA INFILE '/tmp/TableData.csv' INTO .... This is a common pitfall, trying to load a file located on the database server remotely from a client. Getting the file permissions correct is difficult. Avoid this method. Use the LOAD DATA LOCAL INFILE instead of the LOAD DATA INFILE method (it is so much easier).
Also look at the mysqlimport command.

Dump/Backup/Transfer Database:
The mysqldump command will read the mySQL database and generate a SQL command text file. This allows data to be migrated to other versions of mySQL (i.e. upgrade from typical Red Hat (RH7.x to FC3) mySQL release 3.23.58 to a more advanced mySQL 4.1 or 5.0) or to other SQL databases. SQL command file generated can create tables, insert data, ....

Option Description
-A
--all-databases Dump all the databases.
-B
--databases Dump the specified databases.
-h
--host= Specify host to connect to.
-p
--password= Specify password. If you do not specify a password, then you will be queried.
-u
--user= Specify user. Defaults to current user logged in.
--opt Same as: --add-drop-table --add-locks --all --extended-insert --quick --lock-tables
--add-drop-table Add a "drop table" SQL statement before each "create" SQL statement.
--add-locks Add "lock" SQL statements around "insert" SQL statements.
-a
--all Include all mySQL specific SQL "create" options.
-e
--extended-insert Allows utilization of the new, much faster INSERT syntax. Database you are migrating to must support this notation.
-q
--quick Don’t buffer query, dump directly to stdout.
-l
--lock-tables Lock all tables for read.
-?
--help Display command line options.
Examples:

Dump database to a file:
Dump specified database:
mysqldump --opt database > db-dump-file.sql
Dump specified table in database:
mysqldump --opt database table-name > db-dump-file.sql
Dump multiple databases:
mysqldump --opt --databases database1 database2 database3 > db-dump-file.sql
Dump everything:
mysqldump --opt --all-databases > total-db-dump-file.sql
mysqldump -u user-id -h host-name --opt --all-databases > total-db-dump-file.sql
[Potential Pitfall]: If you experience the following error:
mysqldump: Got error: 1016: Can't open file: 'Database-Name' (errno: 145) when using LOCK TABLES
Fix with the following command: mysqlcheck -r -u root -p Database-Name
Import dumped file:
mysql database < db-dump-file.sql
Export from one database and import to another:
Transfer specifed database from one database to another:
mysqldump --opt database | mysql --host=host-name -C database
Man Page:

mysqldump
Upgrading to 4.1:

Upgrading mySQL to 4.1 from 3.23
Use the command: mysql_fix_privilege_tables --password=root-password
This allows you to use the new GRANT command.
Restore MySql Database:
Restore using dump generated by mysqldump above:

mysql -h host-name -u user-id -psupersecretpassword < total-db-dump-file.sql
mysql database-name -h host-name -u user-id -psupersecretpassword < db-dump-file.sql
System Notes:
[Potential Pitfall]: Ubuntu mysql 5.0 database migration - When migrating the mysql database by copying files from /var/lib/mysql/... and /etc/mysql/... from one system running Ubuntu 6.11 to 8.04, I got nebulous error message in /var/log/syslog. The root cause of the problem was apparmor. If turing off apparmor (/etc/init.d/apparmor stop) allows your database to start and function properly, then go fix your apparmor security rules in /etc/apparmor.d/usr.sbin.mysqld. Also note that you must use the newer script /etc/mysql/debian-start from release 8.04 after copying /etc/mysql/....

Note: Debian and Ubuntu distributions manage mysql package upgrades using a mysql user debian-sys-maint which has its information located in /etc/mysql/debian.cnf. If you ever forget your mysql root password, you can always get back into the mysql database using the user debian-sys-maint and its password held in /etc/mysql/debian.cnf.

Building MySql from source: (on Linux)
Prerequisites:

C compiler: 2.95.2 or later. (Check with the command: rpm -q gcc)
Compile and install: (as root)
Downloaded source from http://dev.mysql.com/downloads/mysql/4.1.html
Expand tar file: tar xzf mysql-4.1.16.tar.gz
cd mysql-4.1.16
./configure --prefix=/opt/mysql --sysconfdir=/opt/etc --localstatedir=/opt/var/mysql --with-unix-socket-path=/opt/tmp/mysql.sock
(Use the command ./configure --help to see all options.)
This should create an installation which will not clobber an existing RPM mySQL installation.
make
make install
Create mysql config file: cp support-files/my-medium.cnf /opt/var/my.cnf
Create user/group mysql
Test if user/group mysql already exists: groups mysql
Create group: groupadd mysql
Create user: useradd -g mysql -M -r -d /opt/lib/mysql -s /sbin/nologin -c "MySQL Server" mysql
chown -R mysql:mysql /opt/var/mysql
Configure:
Install default database: /opt/mysql/bin/mysql_install_db --user=mysql
Since this command is run as root, specify the --user option to operate command as user mysql.
Creates help database with SQL script: /opt/mysql/share/mysql/fill_help_tables.sql
Start mySQL database: /opt/mysql/bin/mysqld_safe --user=mysql &
/opt/mysql/bin/mysqladmin -u root password 'new-password'
/opt/mysql/bin/mysqladmin -u root -h yoserver2 password 'new-password'
See tutorial above for use and administration.
Check defaults: (Defaults from config file: /opt/var/my.cnf)
/opt/mysql/bin/my_print_defaults --config-file=my client mysql
--password=supersecret
--port=3306
--socket=/opt/tmp/mysql.sock
--no-auto-rehash

/opt/mysql/bin/my_print_defaults --config-file=my client mysql mysql_install_db
--datadir=/var/lib/mysql
--socket=/var/lib/mysql/mysql.sock
--password=supersecret
--port=3306
--socket=/opt/tmp/mysql.sock
--port=3306
--socket=/opt/tmp/mysql.sock
--skip-locking
--key_buffer=16M
--max_allowed_packet=1M
--table_cache=64
--sort_buffer_size=512K
--net_buffer_length=8K
--read_buffer_size=256K
--read_rnd_buffer_size=512K
--myisam_sort_buffer_size=8M
--log-bin
--server-id=1

Commands/Man pages:
isamchk - Check and repair of ISAM tables.
isamlog - Write info about whats in a nisam log file.
msql2mysql
my_print_defaults
myisamchk
myisamlog
myisampack
mysql - text-based client for mysqld, a SQL-based relational database daemon
mysql_config
mysql_convert_table_format
mysql_find_rows
mysql_fix_privilege_tables
mysql_install_db
mysql_setpermission
mysql_zap - a perl script used to kill processes
mysqlaccess - Create new users to mysql.
mysqlbinlog
mysqlbug
mysqlcheck
mysqld_multi - Used for managing several mysqld processes running in different UNIX sockets and TCP/IP ports.
mysqldump - text-based client for dumping or backing up mysql databases , tables and or data.
mysqldumpslow
mysqlhotcopy
mysqlimport
mysqlshow - Shows the structure of a mysql database (databases,tables and columns)
mysqltest
pack_isam
perror - used to display a description for a system error code, or an MyISAM/ISAM table handler error code.
replace - A utility program to replace changes strings in place in files or on the standard input.
resolve_stack_dump
resolveip
Server:
mysqladmin - A utility for performing administrative operations
safe_mysqld - The recommended way to start a mysqld daemon on Unix.