MySQL

Links

 * MySQL Website
 * MySQL Documentation
 * MySQL 5.1 Reference Manual

Download
Download the program from http://dev.mysql.com/downloads/

It’s better to download the version 4.1.10 or higher.

There you can find some JDBC and ODBC drivers, graphic clients, installation wizard tools, documentation, tutorials and other things.

Installation

 * You can use the windows version that comes with an installer or you can download the version without installer.


 * The version with installer is user friendly and the user has to answer some questions about the environment. The user has just to follow the instructions on the screen.

Installing MySQL manually
To install MySQL manually, do the following:


 * If you are upgrading from a previous version please refer the internet and reference manual to Section 2.3.15, “Upgrading MySQL on Windows”, before beginning the upgrade process.
 * If you are using a Windows NT-based operating system such as Windows NT, Windows 2000, Windows XP, or Windows Server 2003, make sure that you are logged in as a user with administrator privileges.
 * Choose an installation location. Traditionally the MySQL server is installed at C:\mysql, and the new MySQL Installation Wizard installs MySQL to C:\Program Files\MySQL. If you do not install MySQL in C:\mysql, you must specify the path to the install directory during startup or in an option file. See Section 2.3.8, “Creating an Option File”.
 * Extract the install archive to the chosen installation location using your preferred Zip archive tool. Some tools may extract the archive to a folder within your chosen installation location. If this occurs you can move the contents of the subfolder into the chosen installation location.

Creating an option file

 * When the MySQL server starts on Windows, it looks for options in two files: the my.ini file in the Windows directory, and the C:\my.cnf file.


 * MySQL looks for options first in the my.ini file, and then in the my.cnf file. However, to avoid confusion, it is best if you use only one file. If your PC uses a boot loader where the C: drive is not the boot drive, your only option is to use the my.ini file. Whichever option file you use, it must be a plain text file.


 * You can also make use of the example option files included with your MySQL distribution. Look in your installation directory for files such as my-small.cnf, my-medium.cnf, my-large.cnf, and so on, which you can rename and copy to the appropriate location for use as a base configuration file.


 * An option file can be created and modified with any text editor, such as the Notepad program. For example, if MySQL is installed in E:\mysql and the data directory is E:\mydata\data, you can create the option file and set up a [mysqld] section to specify values for the basedir and datadir parameters:

[mysqld] basedir=E:/mysql datadir=E:/mydata/data
 * 1) set basedir to your installation path
 * 1) set datadir to the location of your data directory

Selecting a MySQL Server type
Up through the early releases of MySQL 4.1, the servers included in Windows distributions are named like the table bellow. We have found that the server with the most generic name (mysqld) is the one that many users are likely to choose by default. However, that is also the server that results in the highest memory and CPU use due to the inclusion of full debugging support. The server named mysqld-opt is a better general-use server choice to make instead if you do not need debugging support and do not want the maximal feature set offered by the -max servers or named pipe support offered by the -nt servers. To make it less likely that the debugging server would be chosen inadvertently, some name changes were made from MySQL 4.1.2 to 4.1.4: mysqld has been renamed to mysqld-debug and mysqld-opt has been renamed to mysqld. Thus, the server that includes debugging support indicates that in its name, and the server named mysqld is an efficient default choice. The other servers still have their same names. The resulting servers are named like this:

Binary / Description


 * mysqld-debug
 * Compiled with full debugging and automatic memory allocation checking, and InnoDB and BDB tables.


 * mysqld
 * Optimized binary with InnoDB support.


 * mysqld-nt
 * Optimized binary for Windows NT, 2000, and XP with support for named pipes.


 * mysqld-max
 * Optimized binary with support for InnoDB and BDB tables.


 * mysqld-max-nt
 * Like mysqld-max, but compiled with support for named pipes.

The name changes were not both instituted at the same time. If you have MySQL 4.1.2 or 4.1.3, it might be that you have a server named mysqld-debug but not one named mysqld. In this case, you should have a server mysqld-opt, which you should choose as your default server unless you need maximal features, named pipes, or debugging support.

MySQL as Window Service
To install MySQL as window service use the following commands:

shell> mysqld –-install shell> mysqld –-remove

To start the service type: c:\>net start mysql To stop the service type: c:\>net stop mysql

Starting the Server for the First Time
To start the server, enter this command: C:\> C:\mysql\bin\mysqld --console

The server continues to write to the console any further diagnostic output it produces. You can open a new console window in which to run client programs.If mysqld is slow to respond to TCP/IP connections from client programs on Windows 9x/Me, there is probably a problem with your DNS. In this case, start mysqld with the --skip-name-resolve option and use only localhost and IP numbers in the Host column of the MySQL grant tables. You can force a MySQL client to use a named pipe connection rather than TCP/IP by specifying the --pipe option or by specifying. (period) as the host name. Use the --socket option to specify the name of the pipe. As of MySQL 4.1, you can use the --protocol=PIPE option instead.

Under Linux you should start MySQL using the mysql account. If you are using the root account you must use the following command:

$ mysqld_safe &

Stopping the server
Shutting down: mysqladmin shutdown mysqladmin shutdown -u root -p

If you omit the --console option, the server writes diagnostic output to the error log in the data directory (C:\mysql\data by default). The error log is the file with the .err extension.

Note: The accounts that are listed in the MySQL grant tables initially have no passwords. After starting the server, you should set up passwords for them using the instructions in Section 2.9, “Post-Installation Setup and Testing”.

Testing The MySQL Installation
You can test whether the MySQL server is working by executing any of the following commands:

C:\> C:\mysql\bin\mysqlshow C:\> C:\mysql\bin\mysqlshow -u root mysql C:\> C:\mysql\bin\mysqladmin version status proc C:\> C:\mysql\bin\mysql test

If mysqld is slow to respond to TCP/IP connections from client programs on Windows 9x/Me, there is probably a problem with your DNS. In this case, start mysqld with the --skip-name-resolve option and use only localhost and IP numbers in the Host column of the MySQL grant tables. You can force a MySQL client to use a named pipe connection rather than TCP/IP by specifying the --pipe option or by specifying. (period) as the host name. Use the --socket option to specify the name of the pipe. As of MySQL 4.1, you can use the --protocol=PIPE option instead.

Stopping the server
Shutting down the MySQL server:

$ mysqladmin shutdown $ mysqladmin shutdown -u root -p

Securing the Initial MySQL Accounts
Extracted from “2.9. Post-Installation Setup and Testing”

The grant tables define the initial MySQL user accounts and their access privileges. These accounts are set up as follows:


 * Accounts are created with the username root. These are superuser accounts that can do anything. The initial root account passwords are empty, so anyone can connect to the MySQL server as root without a password and be granted all privileges.


 * On Windows, prior to MySQL 4.1.10, two root accounts are created; one of these is for connecting from the local host and the other allows connections from any host. Beginning with MySQL 4.1.10, the Windows installer creates only one root account, which can connect from the local machine only. The Windows installer will optionally create an account allowing for connections from any host only if the user selects the Enable root access from remote machines option during installation.


 * Two anonymous-user accounts are created, each with an empty username. The anonymous accounts have no passwords, so anyone can use them to connect to the MySQL server.

To assign passwords to the anonymous accounts, you can use either SET PASSWORD or UPDATE. In both cases, be sure to encrypt the password using the PASSWORD function. To use SET PASSWORD on Windows, do this: shell> mysql -u root mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd'); mysql> SET PASSWORD FOR ''@'%' = PASSWORD('newpwd');

To use SET PASSWORD on Unix, do this: shell> mysql -u root mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd'); mysql> SET PASSWORD FOR ''@'host_name' = PASSWORD('newpwd');

File Definitions
File            Purpose tbl_name.frm 	Definition (format) file tbl_name.MYD 	Data file tbl_name.MYI 	Index file

Data Types
Source : MySQL Data Types and Storage Requirements

Storage Requirements for Numeric Types

Storage Requirements for Date and Time Types

Storage Requirements for String Types


 * M represents the declared column length in characters for nonbinary string types and bytes for binary string types.
 * L represents the actual length in bytes of a given string value.

length of VARCHAR and VARBINARY depends on the entire length of the table(in bytes). The effective length is equal to 65535 - length of table. Normally, you have to decrease the length of your VARCHAR or VARBINARY field. If you want a table that has a text field whose length is always <= 65535, then use the data type TEXT because the max text length L < 28 = 65536.

Storage Engines
MySQL supports several storage engines that act as handlers for different table types. MySQL storage engines include both those that handle transaction-safe tables and those that handle non-transaction-safe tables:


 * MyISAM manages non-transactional tables. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. MyISAM is supported in all MySQL configurations, and is the default storage engine unless you have configured MySQL to use a different one by default.


 * The MEMORY storage engine provides in-memory tables. The MERGE storage engine allows a collection of identical MyISAM tables to be handled as a single table. Like MyISAM, the MEMORY and MERGE storage engines handle non-transactional tables, and both are also included in MySQL by default.

Note: The MEMORY storage engine formerly was known as the HEAP engine.


 * The InnoDB and BDB storage engines provide transaction-safe tables. BDB is included in MySQL-Max binary distributions on those operating systems that support it. InnoDB is also included by default in all MySQL 5.1 binary distributions. In source distributions, you can enable or disable either engine by configuring MySQL as you like.


 * The EXAMPLE storage engine is a “stub” engine that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them. The purpose of this engine is to serve as an example in the MySQL source code that illustrates how to begin writing new storage engines. As such, it is primarily of interest to developers.


 * NDB Cluster is the storage engine used by MySQL Cluster to implement tables that are partitioned over many computers. It is available in MySQL-Max 5.1 binary distributions. This storage engine is currently supported on Linux, Solaris, and Mac OS X only. We intend to add support for this engine on other platforms, including Windows, in future MySQL releases.


 * The ARCHIVE storage engine is used for storing large amounts of data without indexes with a very small footprint.


 * The CSV storage engine stores data in text files using comma-separated values format.


 * The BLACKHOLE storage engine accepts but does not store data and retrievals always return an empty set.


 * The FEDERATED storage engine stores data in a remote database. Currently, it works with MySQL only, using the MySQL C Client API. In future releases, we intend to enable it to connect to other data sources using other drivers or client connection methods.

The default storage engine is normally the MyISAM for MySQL versions 4.1 or lower and InnoDB for MySQL 5.0 or higher, but you can change it by using the --default-storage-engine or --default-table-type server startup option, or by setting the default-storage-engine or default-table-type option in the configuration file.

In the version 5.0 or higher the InnoDB storage engine is enabled by default. If you don't want to use InnoDB tables, you can add the skip-innodb option to your MySQL option file, i.e..

Backup
If you need more information consult http://dev.mysql.com/doc/refman/5.1/en/backup.html

A technique for backing up a database is to use the mysqldump program or the mysqlhotcopy script.


 * Create a full backup of your database:

$ mysqldump --tab=/path/to/some/dir --opt db_name

$ mysqlhotcopy db_name /path/to/some/dir

You can also create a binary backup simply by copying all table files (*.frm, *.MYD, and *.MYI files), as long as the server isn't updating anything. The mysqlhotcopy script uses this method. (But note that these methods do not work if your database contains InnoDB tables. InnoDB does not store table contents in database directories, and mysqlhotcopy works only for MyISAM tables.)

Stop mysqld if it is running, then start it with the --log-bin[=file_name] option. See Section 5.12.4, “The Binary Log”. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you executed mysqldump.

Example

$ mysqlhotcopy wikidb /opt/ftp/backup

Backuping InnoDB tables
To backup InnoDB tables use the mysqldump — A Database Backup Program. If you are doing a backup on the server, and your tables all are MyISAM tables, consider using the mysqlhotcopy instead

Syntax

$ mysqldump [options] db_name [tables] $ mysqldump [options] --databases db_name1 [db_name2 db_name3...] $ mysqldump [options] --all-databases

If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are dumped.

The most common use of mysqldump is probably for making a backup of an entire database:

$ mysqldump --opt db_name > backup-file.sql

If you want to compress your backups directly even without hitting your HD. You can try this command:

$ mysqldump --opt -u user --password="password"  | bzip2 -c > database.sql.bz2

mysqldump supports the many options. The --opt option is enabled by default. To disable the options that it enables, use --skip-opt.

This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly. See bellow what they mean:


 * --add-drop-table
 * Add a DROP TABLE statement before each CREATE TABLE statement.


 * --add-locks
 * Surround each table dump with LOCK TABLES and UNLOCK TABLES statements. This results in faster inserts when the dump file is reloaded. See Section 7.2.16, “Speed of INSERT Statements”.


 * --create-options
 * Include all MySQL-specific table options in the CREATE TABLE statements.


 * --disable-keys, -K
 * For each table, surround the INSERT statements with /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; statements. This makes loading the dump file faster because the indexes are created after all rows are inserted. This option is effective for MyISAM tables only.


 * --extended-insert, -e
 * Use multiple-row INSERT syntax that include several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.


 * --lock-tables, -l
 * Lock all tables before starting the dump. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB, --single-transaction is a much better option, because it does not need to lock the tables at all.


 * --quick, -q
 * This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.


 * --set-charset
 * Add SET NAMES default_character_set to the output. This option is enabled by default. To suppress the SET NAMES statement, use --skip-set-charset.

Restoring InnoDB tables
You can read the dump file back into the server like this:

$ mysql db_name < backup-file.sql

You can find bellow the best practices of somebody that contributed for improving the Backup and Restore Strategy of Mysql databases.

1. mysqldump --opt --user=username --password database > dumbfile.sql

2. Edit the dump file and put these lines at the beginning:

SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0;

3. Put these lines at the end:

SET FOREIGN_KEY_CHECKS = 1; COMMIT; SET AUTOCOMMIT = 1;

4. mysql --user=username --password database < dumpfile.sql

Using scripts to backup
Joao Araujo wrote some simple scripts to backup MySQL databases into a backup path. Follow the instructions bellow:


 * Create the scripts and.
 * Before saving substitute the  to the real root password of your MySQL database.
 * Change the file privileges:

$ chmod ug+x mysql-backup-myisam

Syntax

$ mysql-backup-myisam  $ mysql-backup-innodb 

File: 
 * 1) !/bin/bash


 * 1) Date : 20.07.2006
 * 2) Autor: Joao Araujo
 * 3) Description: This script makes backup of the MySQL databases.
 * 4)              Only MyISAM tables are supported.
 * 5)              For InnoDB tables use the script mysql-backup-innodb.
 * 6) Syntax : mysql-backup-myisam
 * 1)              Only MyISAM tables are supported.
 * 2)              For InnoDB tables use the script mysql-backup-innodb.
 * 3) Syntax : mysql-backup-myisam
 * 1) Syntax : mysql-backup-myisam

PATH=$PATH:/usr/sbin
 * 1) SET THE PATH

DBNAME=$1 BACKUPPATH=$2 MYSQLUSER=root MYSQLPASS= VARDATE=`date +%Y%m%d` TARFILE=bkp-$DBNAME-$VARDATE.tgz

mysqlhotcopy $DBNAME $BACKUPPATH -u $MYSQLUSER -p $MYSQLPASS
 * 1) MAKE THE BACKUP

cd $BACKUPPATH
 * 1) CHANGE THE DIRECTORY

rm bkp-$DBNAME*.tgz
 * 1) REMOVE OLD BACKUPS

tar czf $TARFILE $DBNAME
 * 1) COMPRESS THE DIRECTORY

rm -R $DBNAME
 * 1) DELETE THE DIRECTORY

File: 
 * 1) !/bin/bash


 * 1) Date : 17.08.2006
 * 2) Autor: Joao Araujo
 * 3) Description: This script makes backup of the MySQL databases.
 * 4)              Only InnoDB tables are supported.
 * 5)              For MyISAM tables use the script mysql-backup-myisam.
 * 6) Syntax : mysql-backup-innodb
 * 1)              Only InnoDB tables are supported.
 * 2)              For MyISAM tables use the script mysql-backup-myisam.
 * 3) Syntax : mysql-backup-innodb
 * 1) Syntax : mysql-backup-innodb

PATH=$PATH:/usr/sbin
 * 1) SET THE PATH

DBNAME=$1 BACKUPPATH=$2 MYSQLUSER=root MYSQLPASS= VARDATE=`date +%Y%m%d` BZ2FILE=$BACKUPPATH/bkp-$DBNAME-$VARDATE.sql.bz2

rm $BACKUPPATH/bkp-$DBNAME*.bz2
 * 1) REMOVE OLD BACKUPS

mysqldump --opt -u $MYSQLUSER --password=$MYSQLPASS $DBNAME | bzip2 -c > $BZ2FILE
 * 1) MAKE THE BACKUP

To backup many databases at once create this script and copy it to a secure place:

File: 
 * 1) !/bin/bash


 * 1) Date : 20.07.2006
 * 2) Autor: Joao Araujo
 * 3) Description: This script makes backup of many MySQL databases.
 * 1) Autor: Joao Araujo
 * 2) Description: This script makes backup of many MySQL databases.
 * 1) Description: This script makes backup of many MySQL databases.

BACKUPPATH=/home/joao/backup/mysql
 * 1) SET THE BACKUP PATH

./mysql-backup-innodb wikidb $BACKUPPATH ./mysql-backup-innodb mysql $BACKUPPATH
 * 1) BACKUP THE MYSQL DATABASES

Scheduling the backup
It's better to schedule the backup using the command Crontab:

$ crontab -e 10    20      *      *       mon-fri      /root/scripts/mysqldbbackup > /home/cwk/backup/mysql/bkp.log
 * 1) Mins Hours  Days   Months  Day of the week

See the section Scheduling cron jobs to learn more about this command.

Using myisamchk for Crash Recovery
If you need more information go to http://dev.mysql.com/doc/refman/5.1/en/crash-recovery.html and http://dev.mysql.com/doc/refman/5.1/en/repair.html

ATTENTION


 * If you run mysqld with external locking disabled (which is the default as of MySQL 4.0), you cannot reliably use myisamchk to check a table when mysqld is using the same table. If you can be certain that no one will access the tables through mysqld while you run myisamchk, you only have to execute mysqladmin flush-tables before you start checking the tables. If you cannot guarantee this, you must stop mysqld while you check the tables. If you run myisamchk to check tables that mysqld is updating at the same time, you may get a warning that a table is corrupt even when it is not.
 * If the server is run with external locking enabled, you can use myisamchk to check tables at any time. In this case, if the server tries to update a table that myisamchk is using, the server will wait for myisamchk to finish before it continues.


 * If you use myisamchk to repair or optimize tables, you must always ensure that the mysqld server is not using the table (this also applies if external locking is disabled). If you don't stop mysqld, you should at least do a mysqladmin flush-tables before you run myisamchk. Your tables may become corrupted if the server and myisamchk access the tables simultaneously.

Stage 1: Checking your tables

 * Run myisamchk *.MYI or myisamchk -e *.MYI if you have more time. Use the -s (silent) option to suppress unnecessary information.


 * If the mysqld server is stopped, you should use the --update-state option to tell myisamchk to mark the table as checked.


 * You have to repair only those tables for which myisamchk announces an error. For such tables, proceed to Stage 2.

If you get unexpected errors when repairing (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

Stage 2: Easy safe repair

 * First, try myisamchk -r -q tbl_name (-r -q means “quick recovery mode”). This attempts to repair the index file without touching the data file. If the data file contains everything that it should and the delete links point at the correct locations within the data file, this should work, and the table is fixed. Start repairing the next table. Otherwise, use the following procedure:


 * 1) Make a backup of the data file before continuing.
 * 2) Use myisamchk -r tbl_name (-r means “recovery mode”). This removes incorrect rows and deleted rows from the data file and reconstructs the index file.
 * 3) If the preceding step fails, use myisamchk --safe-recover tbl_name. Safe recovery mode uses an old recovery method that handles a few cases that regular recovery mode does not (but is slower).

Note: If you want a repair operation to go much faster, you should set the values of the sort_buffer_size and key_buffer_size variables each to about 25% of your available memory when running myisamchk.

If you get unexpected errors when checking (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

Stage 3: Difficult repair
You should reach this stage only if the first 16KB block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it is necessary to create a new index file. Do so as follows:

1. Move the data file to a safe place.

2. Use the table description file to create new (empty) data and index files:

shell> mysql db_name mysql> SET AUTOCOMMIT=1; mysql> TRUNCATE TABLE tbl_name; mysql> quit

3. Copy the old data file back onto the newly created data file. (Do not just move the old file back onto the new file. You want to retain a copy in case something goes wrong.)


 * Go back to Stage 2. myisamchk -r -q should work. (This should not be an endless loop.)


 * You can also use the REPAIR TABLE tbl_name USE_FRM SQL statement, which performs the whole procedure automatically. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use REPAIR TABLE. See Section 13.5.2.6, “REPAIR TABLE Syntax”.

Stage 4: Very difficult repair
You should reach this stage only if the .frm description file has also crashed. That should never happen, because the description file is not changed after the table is created:


 * 1) Restore the description file from a backup and go back to Stage 3. You can also restore the index file and go back to Stage 2. In the latter case, you should start with myisamchk -r.
 * 2) If you do not have a backup but know exactly how the table was created, create a copy of the table in another database. Remove the new data file, and then move the .frm description and .MYI index files from the other database to your crashed database. This gives you new description and index files, but leaves the .MYD data file alone. Go back to Stage 2 and attempt to reconstruct the index file.

Connecting to and Disconnecting from the Server
Use the following commands: shell> mysql -b -u root -h localhost -p Enter password: ********

To exit type quit.

Showing database information
Example: SELECT VERSION, CURRENT_DATE, USER, NOW;

SHOW DATABASES;

SHOW TABLES;

Removing a database
Use the following commands:

DROP DATABASE 

Privileges and User Management

 * You can create users using the CREATE USER syntax or the  GRANT syntax.
 * You can revoke privileges using the REVOKE syntax.

In the following sections you will find a short explanation about these methods. More details are explained in the section Adding New User Accounts of the MySQL 5.0 Reference.

CREATE USER
Syntax: CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'] [, user [IDENTIFIED BY [PASSWORD] 'password']] ...

Each account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. The user and host parts of the account name correspond to the User and Host column values of the user table row for the account.

To specify the password in plain text, omit the PASSWORD keyword. To specify the password as the hashed value as returned by the PASSWORD function, include the PASSWORD keyword.

Example:

CREATE USER 'phamstein'@'localhost' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4'; CREATE USER 'lauracarvalho'@'localhost' IDENTIFIED BY 'mypass';

GRANT
The GRANT statement enables system administrators to create MySQL user accounts and to grant rights to from accounts.

Bellow you see the basic syntax. The complete syntax you find in the Grant syntax of the MySQL Reference.

Syntax: GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] {tbl_name | * | *.* | db_name.*} TO user [IDENTIFIED BY [PASSWORD] 'password'] [, user [IDENTIFIED BY [PASSWORD] 'password']] ...

Example:

GRANT ALL PRIVILEGES ON mydatabase.* TO 'phamstein'@'localhost' IDENTIFIED BY 'goodsecret'; GRANT ALL PRIVILEGES ON mydatabase.mytable.* TO 'lauracarvalho'@'localhost' IDENTIFIED BY 'mypass';

For the GRANT and REVOKE statements, priv_type can be specified as any of the following:
 * ALL [PRIVILEGES] 	:Sets all simple privileges except GRANT OPTION
 * ALTER 	:Enables use of ALTER TABLE
 * ALTER ROUTINE 	:Enables stored routines to be altered or dropped
 * CREATE 	:Enables use of CREATE TABLE
 * CREATE ROUTINE 	:Enables creation of stored routines
 * CREATE TEMPORARY TABLES 	:Enables use of CREATE TEMPORARY TABLE
 * CREATE USER 	:Enables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
 * CREATE VIEW 	:Enables use of CREATE VIEW
 * DELETE 	:Enables use of DELETE
 * DROP 	Enables :use of DROP TABLE
 * EXECUTE 	:Enables the user to run stored routines
 * FILE 	:Enables use of SELECT ... INTO OUTFILE and LOAD DATA INFILE
 * INDEX 	:Enables use of CREATE INDEX and DROP INDEX
 * INSERT 	:Enables use of INSERT
 * LOCK TABLES 	:Enables use of LOCK TABLES on tables for which you have the SELECT privilege
 * PROCESS 	:Enables use of SHOW FULL PROCESSLIST
 * REFERENCES 	:Not implemented
 * RELOAD 	:Enables use of FLUSH
 * REPLICATION CLIENT 	:Enables the user to ask where slave or master servers are
 * REPLICATION SLAVE 	:Needed for replication slaves (to read binary log events from the master)
 * SELECT 	:Enables use of SELECT
 * SHOW DATABASES 	:SHOW DATABASES shows all databases
 * SHOW VIEW 	:Enables use of SHOW CREATE VIEW
 * SHUTDOWN 	:Enables use of mysqladmin shutdown
 * SUPER 	:Enables use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the mysqladmin debug command; allows you to connect (once) even if max_connections is reached
 * UPDATE 	:Enables use of UPDATE
 * USAGE 	:Synonym for “no privileges”
 * GRANT OPTION 	:Enables privileges to be granted

REVOKE
The REVOKE statement is related and enables administrators to remove account privileges.

Syntax: REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] {tbl_name | * | *.* | db_name.*} FROM user [, user] ...

To revoke all privileges, use the following syntax, which drops all global, database-, table-, and column-level privileges for the named user or users:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

Privilege Levels
Privileges can be granted at several levels:


 * Global level
 * Global privileges apply to all databases on a given server. These privileges are stored in the mysql.user table. GRANT ALL ON *.* and REVOKE ALL ON *.* grant and revoke only global privileges.


 * Database level
 * Database privileges apply to all objects in a given database. These privileges are stored in the mysql.db and mysql.host tables. GRANT ALL ON db_name.* and REVOKE ALL ON db_name.* grant and revoke only database privileges.


 * Table level
 * Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table. GRANT ALL ON db_name.tbl_name and REVOKE ALL ON db_name.tbl_name grant and revoke only table privileges.

MySQL Tools

 * GUI-Tools 5.0

This is the MySQL GUI Tools Bundle for 5.0. It includes the following products.


 * MySQL Administrator 1.2
 * MySQL Query Browser 1.2
 * MySQL Migration Toolkit 1.1
 * MySQL Workbench 1.1

JDBC Client Example
Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql"          , "root"            , "password");

MySQL cannot start
If you installed MySQL from the MSI installer and MySQL cannot be started, try the solution Installing MySQL manually.

Some error messages in english:

A system error has occurred. System error 1067 has occurred. The process terminated unexpectedly. Some error messages in german (deutsch): MySQL wird gestartet. MySQL konnte nicht gestartet werden.

Ein Systemfehler ist aufgetreten.

Systemfehler 1067 aufgetreten.

Der Prozess wurde unerwartet beendet.

ERROR 1045: Access denied for user
There are a few possibilities; you:


 * 1) Verify if the file my.ini is in the windows directory or if the file C:\my.cfg exists. If no, try to copy, modify and rename one of configuration files that comes with MySQL. See the section  Creating an option file.
 * 2) Didn't tell mysql to prompt you for the password. Try starting it like this:

mysql -u username -p

Accessing a remote database
Use the option -h to connect to a remote host that has MySQL installed and is listening on the port 3306. Example: mysql -h dbserver -u myuser -p mydb

Do not forget to grant access for the database tables to a specific remote IP as below: GRANT ALL ON foodb.* TO myuser@'210.59.50.134' IDENTIFIED BY 'PASSWORD';

or to grant all tables to a specific user in any PC of the network: GRANT ALL ON foodb.* TO myuser@'*' IDENTIFIED BY 'PASSWORD';

Deadlock found when trying to get lock
You get easily a database deadlock in your application when certain DELETE and INSERT queries are executed in parallel/concurrently.

If you are getting a message such as "com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction", try firstly to investigate the problem:


 * 1) Investigate the file.
 * 2) Execute the command  to investigate the problem.

If you see something like this:

*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 304043 n bits 200 index `idx_timestamp` of table ...

it means the database locked the table or the row to reorder an index of the table. This issue is explained in the MySQL documentation.

Try one of the following solutions:


 * 1) Enable the innodb_locks_unsafe_for_binlog option. Set explicitly innodb_locks_unsafe_for_binlog=1 in the mysql configuration file and restart the server.
 * 2) Change the isolation level? To understand better the isolation levels click here.
 * 3) As last option, Delete the indices and the problem shall be solved.

Show last error
If you are using the engine INNODB execute the command below to see the last error.

SHOW ENGINE INNODB STATUS

Backup Without Header
If you want to backup only the SQL statements that contain many insert lines without any header or comment use the following command below:

$ mysqldump --compact --skip-opt --no-create-info --skip-comments -u myuser -p database_name tablename > tablename.sql


 * --compact :Produce less verbose output
 * --skip-opt :Turn off the options set by opt
 * --no-create-info :Do not write CREATE TABLE statements that re-create each dumped table
 * --skip-comments :Do not add comments to the dump file
 * -u :MySQL user name
 * -p :Password will be required

More details at http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Plugin InnoDB error
If you get the error below:

100521 14:42:36 [ERROR] Plugin 'InnoDB' init function returned error. 100521 14:42:36 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed 100521 14:42:36 [ERROR] Unknown/unsupported table type: INNODB 100521 14:42:36 [ERROR] Aborting 100521 14:42:36 [Note] mysqld: Shutdown complete

Try to move the and  files to other place. Just leave the file in the data directory.

Restart the server with --console option to see the errors.