Tag Archives: mysql

Mysql service shows stopped when mysql is running after upgrade

In this case, the upgrade changed a line in the service init script /etc/init.d/mysql from if ! mysqladmin ping &>/dev/null;then to if ! $(mysqladmin --no-defaults --socket="$socket" --user=UNKNOWN_MYSQL_USER ping &>/dev/null) ;then.

Printing out $socket revealed it was empty. That value was coming from /usr/sbin/mysqld --print-defaults, which prints out the the configuration defined in the config file. I had not defined socket in my config file (instead just using mysql default socket location), so adding the line socket=/var/run/mysqld/mysqld.sock to /etc/mysql/my.cnf solved the problem.

useful mysql commands

Few things I repeatedly look up on the interwebs, keeping here for reference:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON database1.* TO ‘yourusername’@’localhost’ IDENTIFIED BY ‘yourpassword’;

SHOW GRANTS FOR user@localhost;

SELECT * FROM mysql.user;

MySQL database dump & restore

Putting this here as I keep having to google it (paranoid that I’d do something wrong and mess up the db more than anything else) and keep finding crap results.

[sourcecode]
mysqldump -u [username] -p [database_name] > dump.sql
# it then prompts for the password

mysql -u [username] -p [database_name] < dump.sql
# it then prompts for the password
[/sourcecode]