Additions:
slow queries
%%(text; my.cnf)
log-slow-queries=/var/log/mysql-slow.log
Additions:
Additions:
Additions:
$link = mysql_connect('localhost:/tmp/mysql.sock', 'mysql_user', 'mysql_password');
Deletions:
localhost:/tmp/mysql.sock
Additions:
php use socket instead of tcp
Put this in host parameter
localhost:/tmp/mysql.sock
Additions:
--key_buffer_size=128M --sort_buffer_size=128M \
--read_buffer_size=1M --write_buffer_size=1M \
/path/to/datadir/*/*.MYI
Deletions:
--key_buffer_size=128M --sort_buffer_size=128M \
--read_buffer_size=1M --write_buffer_size=1M \
/path/to/datadir/*/*.MYI
Additions:
Additions:
Show databases on remote host
Show processes on mysql
Show query statistics
Display table statistics
Export to CSV
Explain mysql error code
Deletions:
# Show databases on remote host
# Show processes on mysql
# Show query statistics
# Display table statistics
# Export to CSV
# Explain mysql error code
Additions:
Inline sql
mysql -uroot -pxxx -se "show master status\G"
Additions:
Or better yet, run mysql_check while your database is running
# mysql_check --all-databases
# mysql_check --databases db1 db2
Additions:
If you need to run diagnosis when the database is running, use check table instead:
Additions:
myisamchk can be ran only when the database are shutdown. Otherwise, there will be an error similar to this one:
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
Additions:
Checking mysql tables
sh> myisamchk ./*/*.MYI
mysql> check table db.table_name;
Additions:
Additions:
# Explain mysql error code
perror nnn
$ perror 139
MySQL error code 139: Too big row
Additions:
# Export to CSV
select * from TABLE into outfile '/tmp/domains.csv' fields terminated by ',' optionally enclosed by '"';
Additions:
Additions:
Additions:
Sub Topics
Good articles
MySQL slave lag behind∞
MySQL useful commands
# 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%';
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
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
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