Restoring Accidently Deleted Root User in MySQL
Posted by Dennis LiangIf 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.
MySQL Master / Master Replication Sync
Posted by Dennis LiangIMPORTANT: 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.
Changing the Default Text Editor in Linux
Posted by Dennis LiangBy 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.
Calculate MySQL Database Sizes Using SQL Query
Posted by Dennis LiangHere 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).
The Importance of RAID
Posted by Dennis LiangRedundant 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:
- Data Loss (Complete if No Backups)
- 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).
Restore Single MySQL Database from Full Backup
Posted by Dennis LiangWe’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.