Server Setup | Security | Performance Tuning | …

Run mysqlcheck to fix SQL index/.. errors and clean the caches once in a while. this can be used as a weekly cronjob, the output is sent by mail:

 mysqlcheck -A -r --host=${HOST}  --user=root --port=3308 \ 
 --password=${PASSWD} | mail -s '[SQL Server] mycheck complete' $EMAILADDI

If you receive an error like this while dumping: “Column count of mysql.proc is wrong. Expected 20, found 16.”

run:

mysql_upgrade -u root --password=PASSWD

If you receive this error: “Table './DB_NAME/TABLE_NAME' is marked as crashed and should be repaired”

run this in mysql console:

USE DB_NAME
REPAIR TABLE TABLE_NAME;

or repair all dbs - some say, the server should be stopped (it does not take long, if few tables are crashed):

mysqlcheck --repair --all-databases -u root --password=PASSWD

If the server crashs during import:

ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
creating  database xxx .:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)

this indicates crashed INNODB tables.

Various ways to repair include setting:

innodb_force_recovery=4

Then restart, dump and reimport..read the docs.. A good article about corrupted innodb: http://www.softwareprojects.com/resources/programming/t-how-to-fix-mysql-database-myisam-innodb-1634.html

If you get annoyed, because nothing helps: kill everything:

apt-get remove --purge mysql-server

remove /etc/mysql/ and /var/lib/mysql

apt-get install mysql-server

Then reset the passwd and retry

dump a database to a .sql file. there are many parameters. check 'man mysqldump'

–opt is important to fix encoding!

 mysqldump --opt --host=127.0.0.1 --default-character-set=utf8 --user=root --password=${PWD} $DB > $FILE

works by piping an sql file into the 'mysql' command:

 mysql --host=127.0.0.1  -u root --port=$PORT --password=${DBPWD} ${DBNAME} < ${MYSQLDUMP_FILE}

any sql statement can be executed in commandline and scripts by using '-e' param:

 mysql --host=127.0.0.1  -u $DBUSER --password=${DBPWD} --port=$PORT \ 
 -e "CREATE DATABASE ${DBNAME} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;"

there is a good chance to find the pass in root's commands history ;)

 cat /root/.mysql_history|more
 cat /root/.bash_history|more

if you are not lucky, you can put the mysql-server in recovery mode:

 /etc/init.d/mysql stop
 mysqld_safe --skip-grant-tables &   # start server wo/ permission checks..
 mysql -u root  # the SQL console is accessible without passwd..

 # change the pass in SQL:
 mysql> use mysql;
 mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
 mysql> flush privileges;
 mysql> quit

 # restart in normal mode - with the fresh pass:
 /etc/init.d/mysql stop
 /etc/init.d/mysql start

Thanx for the infos to Vivek! http://www.cyberciti.biz/tips/recover-mysql-root-password.html

# UTF-8 should be used instead of Latin1. Obviously.
# NOTE "utf8" in MySQL is NOT full UTF-8: http://mathiasbynens.be/notes/mysql-utf8mb4

[client]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
default-character-set = utf8mb4

utf84mb suggested (15), but utf8 for compatibility:

mysql> SHOW VARIABLES LIKE 'char%'; SHOW VARIABLES LIKE 'collation%';

* https://stackoverflow.com/questions/3513773/change-mysql-default-character-set-to-utf-8-in-my-cnf