So i ran into this problem today where i was unable to connect to my mysql server from a remote location.
The fix:
Add in one line to your my.ini under the [mysqld] section ‘skip-name-resolve’
restart mysql and your good to go.
Checking for corrupt MYSQL tables (myisam)
|
1 |
./mysqlcheck DATABASE-uUSERNAME -p --repair |
or
|
1 |
./mysqlcheck DATABASE -uUSERNAMEÂ -p |
to check check which tables have problems
MYSQL converting an UnixTime to a DateTime
Today i ran into an interesting problem, we had a field stored in the db that was a UnixTimestamp (Epoch) and we wanted to be able to return the results in a more human readable fashion, without using php. Â You can do this in MYSQL using FROM_UNIXTIME();
|
1 |
SELECT FROM_UNIXTIME(epoch_time_column) FROM table |
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime
Getting a Users Home Location in Java
|
1 |
String path = System.getProperty("user.home"); |
Will return something like “C:\Users\username”
Finding Bad Indexes in your MYSQL database
Using the information schema, you can find all of your indexes that have a Null Cardinality
|
1 |
SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'database' AND CARDINALITY IS NULL |
Mysql Dump From one Server to Another
This is a function i have used while transferring data from a production database down to a test/ staging server.
|
1 |
time mysqldump --quick --disable-keys --user=changeme--password=changeme database| ssh user@host mysql database |
The –disable-keys, works well for large MYISAM tables.
MYSQL Get table rows
Here is a nice little query, to get all the records from your database, and order them by when they where last updated, and how many rows they have.
|
1 2 3 4 |
SELECT TABLE_NAME,TABLE_ROWS,CREATE_TIME,UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '{your db name}'
ORDER BY UPDATE_TIME DESC, TABLE_ROWS DESC; |