{{parent page="Database"}}

===MySQL===

** Sub Topics **
~== [[MysqlInstall Installation]] ==
~== [[MysqlIndex Indexes]] ==
~== [[MysqlTuning Tuning]] ==
~== [[MysqlBackup Backup/Import/Export]]==
~== [[MysqlCSVExport Export to CSV]]==
~== [[MysqlSSL SSL]]==
~== [[Mysql3to4 Mysql3 to Mysql4 upgrade]]==
~==[[MysqlPasswordReset Resetting root password]]==
~==[[MysqlReplication Replication]]==
~==[[MysqlBinlog Binlog]]==
~==[[MysqlCorruption Corrupted datafile]]==
~==[[MysqlJDBCFailover Fail-over JDBC setting]]==
~==[[MysqlHA MySQL and HA]]==
~==[[Mysql4GLimit MySQL 4G Limit?]]==
~==[[MySQLDML DML]]==
~==[[MySQLDDL DDL]]==
~==[[MySQLLocks Locks]]==
~==[[MysqlProxy MysqlProxy]]==
~==[[MysqlDBSize Check DB size]]==
~==[[MysqlPartitions Partitions]]==
~==[[MysqlBenchmark Benchmarking]]==
~==[[MysqlCharacterset CharacterSet]]==
~==[[MysqlProcedures Procedures or Functions]]==
-----

** Good articles **
[[http://mysqldatabaseadministration.blogspot.com/2006/06/investigating-reasons-why-slaves-get.html MySQL slave lag behind]]
[[http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html Encrypt MySQL connection with SSL]]
[[http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html MySQL multi master hack]]

===sum of size of all tables===
%%
mysql> select sum(DATA_LENGTH)/1024/1024/1024 from INFORMATION_SCHEMA.TABLES;
%%

===slow queries===
%%(text; my.cnf)
log-slow-queries=/var/log/mysql-slow.log
%%

===php use socket instead of tcp===
Put this in host parameter

%%
$link = mysql_connect('localhost:/tmp/mysql.sock', 'mysql_user', 'mysql_password');
%%

=== MySQL useful commands ===
**Inline sql**
%%
mysql -uroot -pxxx -se "show master status\G"
%%

**Show databases on remote host**
%%
mysqlshow -h 1.2.3.4 -u root -p
%%

**Show processes on mysql**
%%
mysqladmin -u root proc
or
mysql> SHOW PROCESSLIST;
mysql> SHOW PROCESSLIST \G
mysql> kill 123
%%

**Show query statistics**
%%
mysql> explain select ~ from table \G

# Display running variables
mysql> SHOW VARIABLES;
mysql> SHOW STATUS;
%%

**Display table statistics**
%%
mysql> show table status;
mysql> show table status like 'wikka%';
%%

**Export to CSV**
%%
select * from TABLE into outfile '/tmp/domains.csv' fields terminated by ',' optionally enclosed by '"';
%%

**Explain mysql error code**
%%
perror nnn

$ perror 139
MySQL error code 139: Too big row
%%

=== Checking mysql tables===
myisamchk can be ran only when the database are shutdown. Otherwise, there will be an error similar to this one:
%%
sh> myisamchk ./*/*.MYI

Checking MyISAM file: ./roundcubemail/identities.MYI
Data records: 1 Deleted blocks: 0
myisamchk: warning: 1 client is using or hasn't closed the table properly
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check record links
MyISAM-table './roundcubemail/identities.MYI' is usable but should be fixed
%%

If you need to run diagnosis when the database is running, use check table instead:
%%
mysql> check table db.table_name;
%%

Or better yet, run mysql_check while your database is running
%%
# mysql_check --all-databases
# mysql_check --databases db1 db2
%%

=== Upgrading mysql ===
Run mysql_upgrade to check your tables
To move a database to another host is
%%
shell> mysqladmin -h 'other_hostname' create db_name
shell> mysqldump --opt db_name | mysql -h 'other_hostname' db_name
%%

To copy a database from a remote host
%%
shell> mysqladmin create db_name
shell> mysqldump -h 'other_hostname' --opt --compress db_name | mysql db_name
%%

To copy a database in two phases, dump the database and import it to the new host

%%
# On source db
shell> mysqldump --quick db_name | gzip > db_name.gz
# On target db
shell> mysqladmin create db_name
shell> gunzip < db_name.gz | mysql db_name
%%

=== Data checking ===
%%
myisamchk table_file
# Check all tables
myisamchk --silent --fast /path/to/datadir/~/~.MYI
# Recover datafile
myisamchk -i -r [-f] [-o] /path/to/datadir/*.MYI
# -o: safe recover, much slower but is able to fix more
# -f: force
OR
myisamchk --silent --force --fast --update-state \
--key_buffer_size=128M --sort_buffer_size=128M \
--read_buffer_size=1M --write_buffer_size=1M \
/path/to/datadir/*/*.MYI
%%

To perform checking while database is in use, use check table command
%%(sql)
CHECK TABLE test_table FAST QUICK;
# fast option: Check only tables that have not been closed properly.
# quick: Do not scan the rows to check for incorrect links.
%%

=== max_connection_errors ===
When mysql detects more than 10 connection failures, such as bad password or bad tcp connection from the same host, the host will be blocked. It will throw an error suggesting a "mysqladmin flush-hosts". This could be an indication of a brute force password attack. Such limit can be overridden by supplying the following options to start up
%%
--max_connection_errors=30
%%
But of course, you would most likely want to see if someone was trying to get to your database. To do so, enable connection logging by

%%
--log-warnings
%%

Mysql can also be started with the -l=[filename] option to log all general queries. This file will contain what mysql is doing. Log can be rotated by

%%
mv host_name.log host_name-old.log
mysqladmin flush-logs
%%