Kenneth: /* MySQL CLI Commands */

2018-01-04T05:21:47Z

MySQL CLI Commands

New page

== 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." [http://dev.mysql.com/doc/refman/5.0/en/what-is-mysql.html]

== Subpage Table of Contents ==

{{Special:PrefixIndex/{{PAGENAME}}/}}

==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." [http://dev.mysql.com/doc/refman/5.0/en/what-is-mysql.html]

==SunSQL==
MySQL is now owned by Sun Microsystems.

"we're (Sun Microsystems) acquiring MySQL AB" [http://blogs.sun.com./jonathan/date/20080116]

==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);

<pre>
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
</pre>

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===

<pre>
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
</pre>

=== 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===
[http://dev.mysql.com/doc/refman/5.0/en/set-password.html 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==
[http://dev.mysql.com/doc/refman/5.0/en/grant.html 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>;

<table class="wikitable">
<caption>Privileges</caption>
<tr><th>Privilege</th><th>Meaning</th></tr>
<tr><td>ALL [PRIVILEGES]</td><td>Sets all simple privileges except GRANT OPTION</td></tr>
<tr><td>ALTER</td><td>Enables use of ALTER TABLE</td></tr>
<tr><td>ALTER ROUTINE</td><td>Enables stored routines to be altered or dropped</td></tr>
<tr><td>CREATE</td><td>Enables use of CREATE TABLE</td></tr>
<tr><td>CREATE ROUTINE</td><td>Enables creation of stored routines</td></tr>
<tr><td>CREATE TEMPORARY TABLES</td><td>Enables use of CREATE TEMPORARY TABLE</td></tr>
<tr><td>CREATE USER</td><td>Enables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.</td></tr>
<tr><td>CREATE VIEW</td><td>Enables use of CREATE VIEW</td></tr>
<tr><td>DELETE</td><td>Enables use of DELETE</td></tr>
<tr><td>DROP</td><td>Enables use of DROP TABLE</td></tr>
<tr><td>EXECUTE</td><td>Enables the user to run stored routines</td></tr>
<tr><td>FILE</td><td>Enables use of SELECT ... INTO OUTFILE and LOAD DATA INFILE</td></tr>
<tr><td>INDEX</td><td>Enables use of CREATE INDEX and DROP INDEX</td></tr>
<tr><td>INSERT</td><td>Enables use of INSERT</td></tr>
<tr><td>LOCK TABLES</td><td>Enables use of LOCK TABLES on tables for which you have the SELECT privilege</td></tr>
<tr><td>PROCESS</td><td>Enables the user to see all processes with SHOW PROCESSLIST</td></tr>
<tr><td>REFERENCES</td><td>Not implemented</td></tr>
<tr><td>RELOAD</td><td>Enables use of FLUSH</td></tr>
<tr><td>REPLICATION CLIENT</td><td>Enables the user to ask where slave or master servers are</td></tr>
<tr><td>REPLICATION SLAVE</td><td>Needed for replication slaves (to read binary log events from the master)</td></tr>
<tr><td>SELECT</td><td>Enables use of SELECT</td></tr>
<tr><td>SHOW DATABASES</td><td>SHOW DATABASES shows all databases</td></tr>
<tr><td>SHOW VIEW</td><td>Enables use of SHOW CREATE VIEW</td></tr>
<tr><td>SHUTDOWN</td><td>Enables use of mysqladmin shutdown</td></tr>
<tr><td>SUPER</td><td>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</td></tr>
<tr><td>UPDATE</td><td>Enables use of UPDATE</td></tr>
<tr><td>USAGE</td><td>Synonym for “no privileges”</td></tr>
<tr><td>GRANT OPTION</td><td>Enables privileges to be granted</td></tr>
</table>

[http://www.databasejournal.com/features/mysql/article.php/3311731 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===
[http://dev.mysql.com/doc/refman/5.0/en/select.html 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." [http://dev.mysql.com/doc/refman/5.0/en/select.html]

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:
<pre>
[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
</pre>

Performance changes:
<pre>
[mysqld]
...
max_connections = 500
max_allowed_packet = 100M
</pre>

== 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: [http://confluence.atlassian.com/display/DOC/Database+Setup+For+MySQL]

/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: [http://confluence.atlassian.com/display/DOC/Database+Setup+For+MySQL]

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

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 [http://www.dailytechnology.net/how_to_back_up_mysql_with_windows.php]

===Example===
Nightly MySQL Backup [http://www.andrejciho.com/linux/nightly-mysql-backup/]

#!/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==
[http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html 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==
[http://dev.mysql.com/doc/refman/5.0/en/binary-log.html 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”."

*[http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html PURGE MASTER LOGS Syntax]
*[http://dev.mysql.com/doc/refman/5.0/en/reset.html RESET Syntax]

==Database Recovery using Binary Logs==
*[http://www.databasejournal.com/features/mysql/article.php/3326351 Restoring lost data from the Binary Update Log]

==Error Log==
*[http://www.databasejournal.com/features/mysql/article.php/2112011 The Error Log]
*[http://www.science.uva.nl/ict/ossdocs/mysql/manual_Log_files.html The MySQL log files]

==Reindexing Tables==
[http://www.experts-exchange.com/Databases/Mysql/Q_20854227.html 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" [http://dev.mysql.com/doc/refman/5.0/en/charset-database.html]

"A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set." [http://dev.mysql.com/doc/refman/5.0/en/charset-general.html]

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." [http://dev.mysql.com/doc/refman/5.0/en/charset-database.html]

/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." [http://dev.mysql.com/doc/refman/5.0/en/charset-database.html]

To discover the character set and collation of a database: [http://dev.mysql.com/doc/refman/5.0/en/charset-database.html]
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: [http://dev.mysql.com/doc/refman/5.0/en/charset-database.html]
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:
*[http://dev.mysql.com/doc/refman/5.0/en/charset-database.html 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:
*[http://dev.mysql.com/doc/refman/5.0/en/alter-database.html MySQL :: MySQL 5.0 Reference Manual :: 12.1.1 ALTER DATABASE Syntax]

Another Possible option:
*[http://codex.wordpress.org/Converting_Database_Character_Sets 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: [http://ez.no/developer/forum/install_configuration/database_charset_conversion_downgrade_sql_to_non_utf_8 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 ==

[http://www.cyberciti.biz/faq/howto-clear-mysql-command-history/ Clear mysql command line history stored in ~/.mysql_history file]

$ > ~/.mysql_history

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

[http://www.cyberciti.biz/tips/howto-clear-screen-when-you-logout.html 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:" [http://kb.ucla.edu/articles/configuring-mediawiki-to-search-for-three-letter-words]

[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." [http://kb.ucla.edu/articles/configuring-mediawiki-to-search-for-three-letter-words]

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

== Show long running processess ==

Notice "Time" field when running: [http://www.electrictoolbox.com/show-running-queries-mysql/]
mysql> show processlist;

mysql> show full processlist\G

== Minimal Memory Instance ==

<pre>
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
</pre>

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 [http://www.tocker.ca/2014/03/10/configuring-mysql-to-use-minimal-memory.html])
<pre>
[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
</pre>

---

---

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 [http://bugs.mysql.com/bug.php?id=4541].

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

== keywords ==

[[Category:Database]]
[[Category:Web Development‎]]

MySQL - Revision history

Kenneth: /* MySQL CLI Commands */