Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.
Calculate age in MySQL
Answer:
If you have a datetime field in MySQL, e.g. dob and you want to calculate the age, you can try the following query.
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age FROM ...
Reference: http://ma.tt/2003/12/calculate-age-in-mysql/
Can't connect to local MySQL server through socket
Answer:
Sometimes when you type the mysql command, it shows:
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
The cause of this problem is the MySQL socket file is not in the default location, e.g. /tmp/mysql.sock
To fix, edit the MySQL configurations, e.g. /etc/my.cnf, add the following lines
[client]
socket=/tmp/mysql.sock
Replace string in MySQL
Answer:
Replace a string with the UPDATE statement is easy in MySQL
E.g.
mysql> UPDATE table1 SET name = REPLACE (name, 'foo', 'bar');
Set the auto increment start value in MySQL?
Answer:
In MySQL, you can change the table's auto increment start value by the following command.
mysql> ALTER TABLE `my_table` AUTO_INCREMENT = 2000;
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
Block a client from connecting to MySQL if too many errors
Answer:
You can block a client from connecting to MySQL if they introduced too many errors, e.g. sending incorrect password for authentication, insufficient privileges when connecting to a database etc.
To set the number of maximum allowed error, edit the MySQL configurations, e.g. /etc/my.cnf
..
max_connect_errors=100
..So if a client exceeded this value, they will be blocked from connecting to the server.
Don't forget to restart MySQL to take effect.
# /sbin/service mysqld restart
