This is an old revision of the document!
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
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