MySQL

MySQL

 * "MySQL, the most popular Open Source SQL database management system, is developed, distributed, and supported by MySQL AB. MySQL AB is a commercial company, founded by the MySQL developers. It is a second generation Open Source company that unites Open Source values and methodology with a successful business model."

Pronunciation

 * "The official way to pronounce “MySQL” is “My Ess Que Ell” (not “my sequel”), but we don't mind if you pronounce it as “my sequel” or in some other localized way."

SunSQL
MySQL is now owned by Sun Microsystems.

"we're (Sun Microsystems) acquiring MySQL AB"

MySQL CLI Commands
Connect to MySQL mysql -h -u -p

An introduction to MySQL permissions:

The table mysql.user has all db users. USE mysql; DESC user;

Show host and user pairs: SELECT host, user FROM user; SELECT host, uuser, select_priv, insert_priv FROM user;

If the user table allows access, but disallows permission for a particular operation, the next table to worry about is the db table. DESC db; SELECT host, db, user, select_priv, insert_priv FROM db;

The order of precedence of the tables is as follows:
 * 1) user
 * 2) db/host
 * 3) tables_priv
 * 4) columns_priv

Clear All Users
Tables of concern: mysql.db mysql.user

Clear all: DELETE FROM mysql.db; DELETE FROM mysql.user;

Set default root access: GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES;

Export via Select
Mysql: Select:
 * "The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file."

The file is written to the mysql data directory, so you have to have write access to this folder.

Export via CLI
The better solution:
 * "If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as mysql -e "SELECT ..." > file_name to generate the file on the client host."

Summary: mysql -u -p  -e  mysql -u root -pMyPassword cca -t -e "SELECT * FROM TABLE1;"

Export in tab separated format (default format): -B --batch

Export in table format: -t --table

Export in HTML format: -H --html

Export in XML format: -X --xml

Example of multi-line mysql -u kenneth -p cca <<EOL | mail kenneth.burgener@contractpal.com -s "daily.report.$(date +%Y.%m.%d)" SELECT col1, col2 FROM mytable EOL

Common Server Settings
Default Red Hat MySQL settings: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1
 * 1) Default to using old password format for compatibility with mysql 3.x
 * 2) clients (those using the mysqlclient10 compatibility package).

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

[mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

Performance changes: [mysqld] ... max_connections = 500 max_allowed_packet = 100M

Query Cache
Enable the query cache in MySQL to improve performance - http://www.cyberciti.biz/tips/enable-the-query-cache-in-mysql-to-improve-performance.html

mysql> SHOW VARIABLES LIKE 'query_cache_size'; mysql> SHOW VARIABLES LIKE 'query_cache_type'; mysql> SHOW VARIABLES LIKE 'query_cache_limit';

/etc/my.cnf: [mysqld] query_cache_size = 16M query_cache_type = 1 query_cache_limit = 1M
 * 1) query_cache_size = 16777216
 * 2) query_cache_limit = 1048576

Packet Size
/etc/my.cnf: [mysqld] max_allowed_packet = 100M

Connections
/etc/my.cnf: [mysqld] max_connections = 500

Storage Engine
Set the default storage engine to InnoDB:

/etc/my.cnf: [mysqld] default-storage-engine = INNODB

To verify storage engine: SHOW VARIABLES LIKE 'storage_engine';

default character set
default character set to be UTF-8:

/etc/my.cnf: [mysqld] ... default-collation = utf8_bin character-set-server = utf8 collation-server = utf8_bin default-character-set = utf8

Use the status command to verify database character encoding information: mysql> use fisheye; mysql> status;

Backup User Permissions
GRANT LOCK TABLES, SELECT ON mydatabase.* TO 'backup'@'backuphost' IDENTIFIED BY 'somecoolpassword';

GRANT LOCK TABLES, SELECT ON *.* TO 'backup'@'%' IDENTIFIED BY 'b@ckup';

MySQL: Backup User Privileges

Q: What privileges must I grant to a MySQL user to allow them to run mysqldump?

A: LOCK TABLES, SELECT seems to do the trick.

Example: GRANT LOCK TABLES, SELECT ON mydatabase.* TO 'backup'@'backuphost' IDENTIFIED BY 'somecoolpassword';

Principle of least-privilege: don’t entrust your backup host with the power to hurt the database if you don’t have to. SELECT allows the user to read data, and LOCK TABLES allows the user to lock the tables while running a “snapshot”. . . and of course, narrow the privileges to a specific user-host-password tuple.

Reference: dannyman.toldme.com : MySQL: Backup User Privileges - http://dannyman.toldme.com/2006/08/22/mysql-backup-grant-privileges/

Nightly Backup
How to Schedule MySQL Backups in Windows

Example
Nightly MySQL Backup

BACKUP_DIR= BMYSQL_HOST= BMYSQL_DBNAME= BMYSQL_USER= BMYSQL_PWD= if [ ! -d $BACKUP_DIR ]; then mkdir -p $BACKUP_DIR fi for j in 6 5 4 3 2 1 0; do     for i in $BACKUP_DIR/$BMYSQL_DBNAME.gz.$j; do         if [ -e $i ]; then mv $i ${i/.$j/}.$(( $j + 1 )); fi    done done mysqldump --host=$BMYSQL_HOST --user=$BMYSQL_USER --pass=$BMYSQL_PWD $BMYSQL_DBNAME | gzip > $BACKUP_DIR/$BMYSQL_DBNAME.gz.0
 * 1) !/bin/sh
 * 1) Define your variables here:
 * 1)  Make sure output directory exists.
 * 1)  Rotate backups

crontab -e

# |     hour (0-23), # |     |       day of the month (1-31), # |     |       |       month of the year (1-12), # |     |       |       |       day of the week (0-6 with 0=Sunday). # |     |       |       |       |       commands 15      1       *       *       *       /home/path/to/your/script
 * 1) minute (0-59),

crontab -l

mysqldump
mysqldump

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

mysqldump mysql -u USER -p DBNAME > dump.sql

To import: mysql -u USER -p DBNAME < dump.sql

Error on import: ERROR 1227 (42000) at line 2068: Access denied; you need the SUPER privilege for this operation /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`cca`@`localhost` SQL SECURITY DEFINER */ /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`cca`@`qa.contractpal.com` SQL SECURITY DEFINER */ http://dev.mysql.com/doc/refman/5.0/en/create-view.html "The default DEFINER value is the user who executes the CREATE VIEW statement. (This is the same as DEFINER = CURRENT_USER.) If a user value is given, it should be a MySQL account in 'user_name'@'host_name' format (the same format used in the GRANT  statement). The user_name and host_name values both are required." Changing `localhost` or `qa.contractpal.com` to `%` appears to pass ok. Changing "`cca`@`localhost`" to just "CURRENT_USER" also works.

To fix: cat cca.sql | sed "s/DEFINER=\`cca\`@\`qa.contractpal.com\` SQL SECURITY DEFINER/DEFINER=CURRENT_USER SQL SECURITY DEFINER/g" > cca2.sql cat cca2.sql | sed "s/DEFINER=\`cca\`@\`localhost\` SQL SECURITY DEFINER/DEFINER=CURRENT_USER SQL SECURITY DEFINER/g" > cca3.sql

Binary Logs
The Binary Log

"You can delete all binary log files with the RESET MASTER statement, or a subset of them with PURGE MASTER LOGS. See Section 12.5.5.5, “RESET Syntax”, and Section 12.6.1.1, “PURGE MASTER LOGS Syntax”."


 * PURGE MASTER LOGS Syntax
 * RESET Syntax

Database Recovery using Binary Logs

 * Restoring lost data from the Binary Update Log

Error Log

 * The Error Log
 * The MySQL log files

Reindexing Tables
MySQL reindex...

Help mysql make better choices about index usage: ANALYZE TABLE tablename;

Use to defragment a table that had a large delete: OPTIMIZE TABLE tablename;

Fixes corrupted tables/index: REPAIR TABLE tablename;

If all goes well you may need the occasional ANALYZE TABLE, but you should not need to rebuild the index itself on any regular basis.

"You don't need to reindex MySQL's tables; ... this is one of the major benefits of the long lasting MySQL Vs Postgress discussion where Postgress requires you to run vacuum every certain amount of time.

From the manual:

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the datafile.

In most setups you don't have to run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable length rows it's not likely that you need to do this more than once a month/week and only on certain tables."

Character Set
"Every database has a database character set and a database collation"

"A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set." 

List available character set options: SHOW CHARACTER SET; +--+-+-++ | Charset | Description                 | Default collation   | Maxlen | +--+-+-++ | latin1  | cp1252 West European        | latin1_swedish_ci   |      1 | | utf8    | UTF-8 Unicode               | utf8_general_ci     |      3 | +--+-+-++

latin1 is the mysql default character set.

List available collation options: SHOW COLLATION; SHOW COLLATION LIKE 'latin1%'; +--+--+-+-+--+-+ | Collation           | Charset  | Id  | Default | Compiled | Sortlen | +--+--+-+-+--+-+ | latin1_swedish_ci   | latin1   |   8 | Yes     | Yes      |       1 | | utf8_general_ci     | utf8     |  33 | Yes     | Yes      |       1 | +--+--+-+-+--+-+

How do I set the database character set?
Server settings (/etc/my.cnf): [mysqld] character-set-server=latin1 collation-server=latin1_swedish_ci

Create database and specify UTF8 character set: CREATE [DEFAULT] DATABASE db_name CHARACTER SET utf8; CREATE [DEFAULT] DATABASE db_name CHARSET utf8; CREATE [DEFAULT] DATABASE db_name CHARSET utf8 COLLATE utf8_general_ci;

The database character set and collation are used as default values if the table character set and collation are not specified in CREATE TABLE statements

How do I determine the database character set?
"All database options are stored in a text file named db.opt that can be found in the database directory." 

/var/lib/mysql/cca/db.opt: default-character-set=latin1 default-collation=latin1_swedish_ci

"The character set and collation for the default database can be determined from the values of the character_set_database and collation_database system variables. The server sets these variables whenever the default database changes. If there is no default database, the variables have the same value as the corresponding server-level system variables, character_set_server and collation_server." 

To discover the character set and collation of a database: USE your_database_of_interest; show variables like "character_set_database"; show variables like "collation_database";

To show the current character set use this query: SHOW CREATE DATABASE `DB_NAME`

To show a table's character set: SHOW CREATE TABLE [table_name];

To show databases character set: mysql> use mydb; mysql> status;

References:
 * MySQL :: MySQL 5.0 Reference Manual :: 9.1.3.2 Database Character Set and Collation

Converting database character set
Alter database defaults [ alter database cca charset = utf8 collate = utf8_general_ci;

Convert character set: mysql -u root -pMyPassword cca -B -e "SHOW TABLES" | awk '{print "mysql -u root -pMyPassword cca -e \"alter table " $1 " convert to character set utf8 collate utf8_general_ci;\""}' | sh

References:
 * MySQL :: MySQL 5.0 Reference Manual :: 12.1.1 ALTER DATABASE Syntax

Another Possible option:
 * Converting Database Character Sets « WordPress Codex

Dump Conversion
Dump the database and remember to use --skip-set-charset, or use the sed command after you have created the dump mysqldump -u username -p --skip-set-charset database > dump.sql

sed -e 's/ DEFAULT CHARSET=UTF-8//' dump.sql > dump_fix.sql

Convert to ISO-8859-1: iconv --from-code=UTF-8 --to-code=ISO-8859-1 dump.sql > new_dump.sql

Dump database back to new server: mysql -u username -p database < new_dump.sql

Source: Database charset conversion (downgrade sql to non utf-8) / Install & Configuration / Forum

MySQL Slave with Multiple Master Replication
My researching indicates MySQL does not support a slave having multiple masters. Replication was designed the other way around, for a master to have multiple slaves.

"Replication enables data from one MySQL database server (called the master) to be replicated to one or more MySQL database servers (slaves)." [1]

"One of our requirements is to have multiple master databases merge into a single slave database. The built-in MySQL replication engine does not support this kind of setup." [3]

Suggested Options:

1. One option I think we can consider is installing two copies of MySQL on the same box, and having each installation act as a separate slave.

2. A second option is we could chain the replication: SERVER A (transaction data master) -> SERVER B (transaction data slave, analytics data master) -> SERVER C (analytics data slave)

3. A third option would be have both transaction and analytics hosted on the master server, and have one or two other database servers act as replication servers.

4. Have a crazy stored procedure replication (not a very robust option) [3] Custom Replication - http://mysqlonrails.blogspot.com/2008/03/custom-replication.html

References:

[1] MySQL :: MySQL 5.0 Reference Manual :: 18 Replication http://dev.mysql.com/doc/refman/5.0/en/replication.html

[2] 18.1.2. Replication Startup Options and Variables - http://dev.mysql.com/doc/refman/5.0/en/replication-options.html

[3] Custom Replication - http://mysqlonrails.blogspot.com/2008/03/custom-replication.html

[4] MySQL Replication - http://dev.mysql.com/doc/refman/5.0/en/replication.html

MySQL History
Clear mysql command line history stored in ~/.mysql_history file

$ > ~/.mysql_history

$ rm $HOME/.mysql_history $ ln -s /dev/null $HOME/.mysql_history

Shell tip: Clear the command history and screen when you log out

ODBC
MySQL :: MySQL 5.1 Reference Manual :: 21.1.3.1 Installing Connector/ODBC from a Binary Distribution on Windows - http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-installation-binary-windows.html#connector-odbc-installation-binary-windows-installer

MySQL :: MySQL on Windows - Why, Where and How - http://www.mysql.com/why-mysql/white-papers/mysql_on_windows_wwh.php

MySQL :: MySQL 5.1 Reference Manual :: 21.1.5.2 Step-by-step Guide to Connecting to a MySQL Database through Connector/ODBC - http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-examples-walkthrough.html

Check Tables
MySQL :: MySQL 5.0 Reference Manual :: 4.5.3 mysqlcheck — A Table Maintenance Program - http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html

The mysqlcheck client performs table maintenance: It checks, repairs, optimizes, or analyzes tables.

Each table is locked and therefore unavailable to other sessions while it is being processed, although for check operations, the table is locked with a READ lock only.

mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user. It determines which statements to use for the operation you want to perform, and then sends the statements to the server to be executed.

mysqlcheck -A -e

mysqlcheck -c --all-databases

References:
 * Reindexing MySQL database : mysql, reindexing, database - http://www.experts-exchange.com/Database/MySQL/Q_21502908.html

Repair Tables
That will help mysql make better choices about index usage. ANALYZE TABLE tablename;

Use to defragment a table that had a large delete. OPTIMIZE TABLE tablename;

Fixes corrupted tables/index. REPAIR TABLE tablename;

References:
 * Reindexing MySQL database : mysql, reindexing, database - http://www.experts-exchange.com/Database/MySQL/Q_21502908.html

Search for Three Letter Words
"By default MediaWiki uses MySQL and the default FULLTEXT indexing uses built-in stop words and a minimum word length of four. A list of the stop words is available but are the usual thing (the, and, one_, etc.). You can set your own stop words by setting the ft_stopword_file system variable but the default stop words may be sufficient to your purposes. However, there are many three letter words which you may like to index (_SVN, RSS, FAQ, etc.). In order to do this you need to change the FULLTEXT indexing to a minimum word length of three. To do this edit the my.ini and add to the end of the [mysqld] section:"

[mysqld] ft_min_word_len = 3
 * 1) Minimum word length to be indexed by the full text search index.
 * 2) You might wish to decrease it if you need to search for shorter words.
 * 3) Note that you need to rebuild your FULLTEXT index, after you have
 * 4) modified this value.

"Restart MySQL, and then reindex the relevant tables. The tables are ${mw_}searchindex where ${mw_} is the table prefix for the MediaWiki instance (there will be one searchindex table for each instance). To reindex the table, log into the server and start up MySQL Administrator. Log in as root and click Catalogs, then wikidb. Select the table and click the Maintenance button. Select Repair Tables and press Next. Check the Quick option under Repair Method and click Repair Tables. Repeat for each MediaWiki instance." 

Reindex table: use [database]; ANALYZE TABLE searchindex; REPAIR TABLE searchindex; OPTIMIZE TABLE searchindex;

Show long running processess
Notice "Time" field when running: mysql> show processlist;

mysql> show full processlist\G

Minimal Memory Instance
performance_schema     = 0

key_buffer             = 8M max_connections        = 30 # Limit connections query_cache_size       = 8M # try 4m if not enough query_cache_limit      = 512K thread_stack           = 128K

or another option...

cp /etc/my.cnf /etc/my.cnf.orig cp /usr/share/mysql/config.small.ini /etc/my.cnf

And give the instance a swap file:

SWAPFILE=/swapfile.swap dd if=/dev/zero of=$SWAPFILE bs=1M count=512 mkswap $SWAPFILE swapon $SWAPFILE

References:
 * Reducing memory consumption of mysql on ubuntu@aws micro instance - Stack Overflow - http://stackoverflow.com/questions/10676753/reducing-memory-consumption-of-mysql-on-ubuntuaws-micro-instance

---

Minimal: (based on ) [client] port           = 3306 socket         = /var/run/mysqld/mysqld.sock

[mysqld_safe] socket         = /var/run/mysqld/mysqld.sock nice           = 0

[mysqld] user           = mysql pid-file       = /var/run/mysqld/mysqld.pid socket         = /var/run/mysqld/mysqld.sock port           = 3306 basedir        = /usr datadir        = /var/lib/mysql tmpdir         = /tmp lc-messages-dir = /usr/share/mysql log_error      = /var/log/mysql/error.log bind-address   = 127.0.0.1 skip-external-locking

performance_schema     = 0

innodb_buffer_pool_size=5M innodb_log_buffer_size=256K query_cache_size=0 max_connections=10 key_buffer_size=8 thread_cache_size=0

thread_stack=131072 sort_buffer_size=32K read_buffer_size=8200 read_rnd_buffer_size=8200 max_heap_table_size=16K tmp_table_size=1K bulk_insert_buffer_size=0 join_buffer_size=128 net_buffer_length=1K

binlog_cache_size=4K binlog_stmt_cache_size=4K

---

---

TODO See:
 * http://wiki.vpslink.com/Low_memory_MySQL_/_Apache_configurations
 * http://www.narga.net/optimizing-apachephpmysql-low-memory-server/

Key too long
Error: ERROR 1071 (42000) at line 774: Specified key was too long; max key length is 1000 bytes

Line 744 shows the following: CREATE TABLE NXC_PAL_METER (   ...    ENTERPRISEID INT(11) NOT NULL DEFAULT 0,    ...    HOST VARCHAR(255),    WEB VARCHAR(128),    ...    INDEX (ENTERPRISEID,HOST,WEB),    ... );

11+180+128=394, which is less than 1000

After researching this issue it was determined that the cause is the UTF-8 charset. UTF-8 uses 3 bytes for 1 character and latin uses 1 character.

latin1 = 1 byte = 1 chararcter uft8 = 3 byte = 1 chararcter

394*3=1182

After reducing the host size from 255 to 180 the error went away. Martineau says that this code has been running on Palquad for months, and suggested letting the java code work it's magic. After researching why it worked on Palquad, I found that the NXC_PAL_METER table does not have the index. The Java code must have created the table, but failed upon building the index.

Reference:
 * [http://bugs.mysql.com/bug.php?id=4541