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." [1]
Subpage Table of Contents
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." [2]
SunSQL
MySQL is now owned by Sun Microsystems.
"we're (Sun Microsystems) acquiring MySQL AB" [3]
MySQL CLI Commands
Connect to MySQL
mysql -h <host> -u <user> -p<password> <database> $ mysql -u root -p mysql>
Show all databases:
SHOW DATABASES;
Show database create info:
SHOW CREATE DATABASE [database_name];
Select database:
USE <database>
Show all tables in current database:
SHOW TABLES;
Describe table layout (Show all columns in a table):
SHOW COLUMNS FROM <table_name>; DESC <table_name>; # DESCRIBE <table_name>
Exit MySQL CLI:
exit
Show MySQL variables (ie. max_connections):
SHOW VARIABLES; SHOW VARIABLES LIKE 'max_connections';
Show a list of processes (rough estimation of the number of connections):
SHOW PROCESSLIST;
Grant privileges (it is important to do localhost also, or anonymous takes over):
grant all (privileges) on <db|*>.<table_name|*> to "<user>"@"<host|%>" identified by "<password>" (with Grant option);
Have server to reload grant table:
flush privileges;
Drop user
drop user <username>;
Revoke privileges
revoke all PRIVILEGES, GRANT OPTION FROM <user>,<user...>;
Set password
set password for '<user>'@'<host>' = password('<newpassword>');
Create Table
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
Data types - https://dev.mysql.com/doc/refman/5.5/en/data-types.html
Insert Into Table
Install MySQL
yum install mysql mysql-server
Installation Message
To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h otrs.lindonlabs.com password 'new-password' Alternatively you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com
Security
mysql -u root use mysql; truncate user; truncate db; grant all on *.* to root@localhost with grant option; flush privileges;
Config Check
'lint' for config check for mysql
[root@db opt]# /usr/libexec/mysqld --help 110729 23:08:58 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295 110729 23:08:58 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295 110729 23:08:58 [ERROR] /usr/libexec/mysqld: unknown variable 'storage_engine=InnoDb'
References:
- How to check MySQL Config files - MySQL Performance Blog - http://www.mysqlperformanceblog.com/2008/02/12/how-to-check-mysql-config-files/
Change Password
Set Root Password
# Set root password the first time /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h dev.y-t-c.com password 'new-password'
# Change root password mysqladmin -u root -p <oldpassword> <newpass>
Reset Forgot Root Password
# mysqld_safe --skip-grant-tables # mysql MYSQL> use mysql; MYSQL> flush privileges;
MYSQL> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
MYSQL> grant all on *.* to 'root'@'%' identified by 'test12' with grant option
MYSQL> flush privileges; MYSQL> exit;
Set User Password
# Change user password
mysqladmin -u <user> -p <oldpassword> <newpass>
mysql> set password = password("yournewpassword");
mysql> SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');
# is equivalent to:
mysql> UPDATE mysql.user SET Password=PASSWORD('newpass')
WHERE User='bob' AND Host='%.loc.gov';
mysql> FLUSH PRIVILEGES;
Privileges
GRANT <privileges> ON <table_or_db_name> TO '<user>'@'<host>' IDENTIFIED BY '<password>';
Create user:
CREATE USER <user>@<host> IDENTIFIED BY '<password>';
Global Level:
GRANT ALL ON *.* TO 'someuser'@'somehost'; GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';
Table Level:
GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost'; GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';
Column Level:
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';
Revoke Permission:
REVOKE SELECT ON *.* FROM <user>@<host>; DROP USER <user>@<host>;
List MySQL privileges:
SHOW PRIVILEGES;
Show privileges for user:
SHOW GRANTS FOR <user>@<host>;
| Privilege | Meaning |
|---|---|
| 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 the user to see all processes with SHOW 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 |
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:
- user
- db/host
- tables_priv
- 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 to file
Export via 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." [4]
Summary:
mysql -u <user> -p<password> <db> <format> -e <QUERY> 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 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [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
Performance Tuning
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 = 16777216 # query_cache_limit = 1048576 query_cache_size = 16M query_cache_type = 1 query_cache_limit = 1M
Packet Size
/etc/my.cnf:
[mysqld] max_allowed_packet = 100M
Connections
/etc/my.cnf:
[mysqld] max_connections = 500
Server Configuration
Storage Engine
Set the default storage engine to InnoDB: [5]
/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: [6]
/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 [7]
Example
Nightly MySQL Backup [8]
#!/bin/sh
#Define your variables here:
BACKUP_DIR=
BMYSQL_HOST=
BMYSQL_DBNAME=
BMYSQL_USER=
BMYSQL_PWD=
# Make sure output directory exists.
if [ ! -d $BACKUP_DIR ]; then
mkdir -p $BACKUP_DIR
fi
# Rotate backups
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
crontab -e
# minute (0-59), # | 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
crontab -l
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
"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”."
Database Recovery using Binary Logs
Error Log
Reindexing Tables
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" [9]
"A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set." [10]
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." [11]
/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." [12]
To discover the character set and collation of a database: [13]
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: [14]
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:
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:
Another Possible option:
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
stored procedure
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:" [15]
[mysqld] # Minimum word length to be indexed by the full text search index. # You might wish to decrease it if you need to search for shorter words. # Note that you need to rebuild your FULLTEXT index, after you have # modified this value. ft_min_word_len = 3
"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." [16]
Reindex table:
use [database]; ANALYZE TABLE searchindex; REPAIR TABLE searchindex; OPTIMIZE TABLE searchindex;
Show long running processess
Notice "Time" field when running: [17]
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 [18])
[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/
Issues
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 [19].
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: