Restoring Accidently Deleted Root User in MySQL

Posted by Dennis Liang

If you’ve ever been in the situation where you’ve accidentally deleted your root user in MySQL, you’ve probably noticed that GRANTS does not work when MySQL is started with skip-grant-tables. The way to get around this is to manually insert a record into the mysql.user’s table.

Continue reading

MySQL Master / Master Replication Sync

Posted by Dennis Liang

IMPORTANT: ALWAYS make a backup of your databases before attempting the following procedures. The following is based under the assumption that your MySQL servers are already setup to be Master / Master.

If you have have a MySQL Master / Master Replication, you know it is when you see a dreaded error that stops replication, such as:

Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
Error 'Duplicate entry 'value' for key 1' on query. Default database: 'break_me'. Query: 'insert into data_table (id, code, data) values ('', 'GYIQB', 'SHBK27637BJD')'

Though you can usually just run “SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;”, there is always worried about data consistency across the two master MySQL servers.

Continue reading

Changing the Default Text Editor in Linux

Posted by Dennis Liang

By default, CentOS / RHEL 5.x ships with VIM as the default text editor. However if you prefer a different text editor you can easily modify the default text editor by changing the VISUAL environment variable.

Continue reading

Calculate MySQL Database Sizes Using SQL Query

Posted by Dennis Liang

Here is a quick and easy way to get the sizes of your databases in MySQL, useful when you don’t have PHPMyAdmin and just want to compare two databases (live and development for example).

Continue reading

The Importance of RAID

Posted by Dennis Liang

Redundant Array of Independent Disks (RAID) is a key technology to mission critical / business servers which aim for the highest possible uptime on their servers. While S.M.A.R.T. (Self-Monitoring, Analysis, and Reporting Technology) hard disk technology can warn of an impending disk failure, there is no guarantee that it will give any sort of warning before a total failure.

With a single hard disk in your server, you run a high risk of being a victim of:

  1. Data Loss (Complete if No Backups)
  2. Certain Downtime

Having a RAID configuration setup on your servers however, can significantly lower the risk. With an RAID configuration you commonly have two or more hard disks in your system, where if one drive fails the system stays online with no data loss or downtime while your failed hard disk is replaced (with the exception of RAID 0).

Continue reading

Restore Single MySQL Database from Full Backup

Posted by Dennis Liang

We’ve recently ran into a situation where a client accidentally deleted her database and only had a 10 GB mysqldump created with –all-databases.

As you can imagine, trying to grab a single table out of a 10 GB mysqldump backup doesn’t work so well, so instead we figured out a pretty nifty solution to this problem without affecting other sites on the server or having to (somehow) go through the pains of manually grabbing the single database from the backup.

Continue reading

Blog is powered by WordPress and designed by Chico Web Design