MySQL

From Omnia
(Redirected from Mysql)
Jump to navigation Jump to search

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:

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

SET PASSWORD Syntax 

# 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 Syntax 

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>;
Privileges
PrivilegeMeaning
ALL [PRIVILEGES]Sets all simple privileges except GRANT OPTION
ALTEREnables use of ALTER TABLE
ALTER ROUTINEEnables stored routines to be altered or dropped
CREATEEnables use of CREATE TABLE
CREATE ROUTINEEnables creation of stored routines
CREATE TEMPORARY TABLESEnables use of CREATE TEMPORARY TABLE
CREATE USEREnables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEWEnables use of CREATE VIEW
DELETEEnables use of DELETE
DROPEnables use of DROP TABLE
EXECUTEEnables the user to run stored routines
FILEEnables use of SELECT ... INTO OUTFILE and LOAD DATA INFILE
INDEXEnables use of CREATE INDEX and DROP INDEX
INSERTEnables use of INSERT
LOCK TABLESEnables use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESSEnables the user to see all processes with SHOW PROCESSLIST
REFERENCESNot implemented
RELOADEnables use of FLUSH
REPLICATION CLIENTEnables the user to ask where slave or master servers are
REPLICATION SLAVENeeded for replication slaves (to read binary log events from the master)
SELECTEnables use of SELECT
SHOW DATABASESSHOW DATABASES shows all databases
SHOW VIEWEnables use of SHOW CREATE VIEW
SHUTDOWNEnables use of mysqladmin shutdown
SUPEREnables 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
UPDATEEnables use of UPDATE
USAGESynonym for “no privileges”
GRANT OPTIONEnables 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:

  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 to file

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." [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

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”."

Database Recovery using Binary Logs


Error Log

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" [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:

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:

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:

---

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:

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:

keywords

Retrieved from "https://aznot.com/index.php?title=MySQL&oldid=4564"