MySQL
Sub Topics
Installation
Indexes
Tuning
Backup/Import/Export
Export to CSV
SSL
Mysql3 to Mysql4 upgrade
Resetting root password
Replication
Binlog
Corrupted datafile
Fail-over JDBC setting
MySQL and HA
MySQL 4G Limit?
DML
DDL
Locks
MysqlProxy
Check DB size
Partitions
Benchmarking
CharacterSet
Procedures or Functions
Good articles
MySQL slave lag behind
Encrypt MySQL connection with SSL
MySQL multi master hack
sum of size of all tables
mysql> select sum(DATA_LENGTH)/1024/1024/1024 from INFORMATION_SCHEMA.TABLES;
slow queries
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 sqlmysql -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 tablesTo 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.
# 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
