MySQL

Reset Mysql Root Password

see http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

Basically, to reset the root password, restart mysql with the --skip-grant-tables option to start mysql without user accounts, then update the root password.

service mysql stop
mysqld_safe --skip-grant-tables
mysql
> update mysql.user set password=PASSWORD('newpassword') WHERE user='root';
> quit
service mysql restart

Binary Logs

see http://leo.steamr.com/2011/10/mysql-binary-logs/

InnoDB Sequential Autoincrement on Duplicate Key Insert

You may notice that the auto increment value increments even on a duplicate key insertion. To make InnoDB act similar to MyISAM, put the following line under the [mysqld] section in /etc/my.cnf

innodb_autoinc_lock_mode = 0

Restoring SQL Dump from cPanel Backups

To restore the .sql dump in a tarball such as a cpanel backup file do something like:

zcat account.tar.gz|tar -xvf - account/mysql/account_db.sql -O | mysql -u root -p account_db

Issue with Restoring Large Dumps

When restoring a large dump, you may see:

cat dump.sql | mysql -u root -p database
MySql server has gone away

If the import is dumping in a large blob of data, you will need to increase the max_allowed_packet in my.cnf.

Split Database Dumps to Individual Table Dumps

Use the csplit command to split database dumps at the '-- Table Structure' line.

csplit -s -ftable $1 "/-- Table structure for table/" {*}