Configuring MySQL
Posted by Chief on Nov 12, 2009 in System Administration
No Comments
The following is how I set up MySQL on wharf.
- First, ensure mysql is installed:
ls /etc/init.d/my* - Now, make mysql start on boot:
sudo /sbin/chkconfig --level 35 mysqld on - Start the mysql server:
sudo /etc/init.d/mysqld start - Make MySQL more secure:
sudo /usr/bin/mysql_secure_installation - Log into mysql as root:
mysql -u root -p [to be entered by prompt] - Add another mysql user:
mysql> CREATE USER 'newuser'@'%' IDENTIFIED BY 'newuserpass';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON *.* TO 'newuser'@'%';
mysql> FLUSH PRIVILEGES; - Exit mysql:
mysql> exit - Test:
mysql -u <newuser> -p <newuserpass>
Things that Kill MySQL Performance
Posted by Chief on Nov 4, 2009 in Lessons
- Failing to plan for scaling out, scaling up
- Not using EXPLAIN (learn from your mistakes)
- Using the wrong data types (hint: use smallest fixed-size)
- Using persistent connections in PHP (leads to zombie processes)
- Using a heavy DB abstraction layer (hint: use PDO or custom)
- Using the wrong storage engine (MEMORY, ARCHIVE, InnoDB, etc.)
- Using indexes improperly (hint: select in the order of the index; hint: keep primary key small)
- Issuing SQL queries that can’t be cached easily; having too large of a query cache
- Using stored procedures when prepared statements are better
- Using functions on indexed columns in the WHERE clause
- Not indexing important columns; having redundant indexes
- Using sub-queries when joins would be better
- Issuing avoidable deep scans
- Doing SELECT COUNT(*) without WHERE on InnoDB table.
- Failing to profile/benchmark your SQL
- Not using AUTO_INCREMENT if applicable
- Not using ON DUPLICATE KEY UPDATE (1 query vs 2)
Reference:
MySQL DNS Caching
Posted by Chief on Dec 23, 2008 in Lessons
On December 12, 2008, SCCOOS metadata system was broken. The error was cryptic, but the message was clear: Connection to [IP] denied.
Resolution
Using the MySQL command-line tool:
mysql -u admin -p [-h
mysql> flush hosts;
That’s it!
Chain of Events
- UCSD’s DNS was polluted, identifying 132.239.123.144 as ridge2000.org for the past few weeks.
- Sandbar was restarted last week, freeing up both the OS and MySQL DNS caches, which meant all new connections will need to query a DNS server to identify whether it’s an allowed machine or not (do you see a security vulnerability?).
- Both the OS and MySQL cached the hostname, but MySQL further rejected all connections from alfredo because MySQL thought that alfredo was ridge2000 instead.
- The mysql database, user table identifies user sccoos can connect to sandbar from alfredo.ucsd.edu.
- The OS cache probably cleared, but since so few machines access sandbar, the MySQL cache hadn’t filled up yet.
- Just last night (2008-12-22), the UCSD DNS purged the ridge2000.org name from its list, so UCSD’s DNS is clean, but any caches may still be polluted.
- Windows machines cycle their caches regularly, so the problem isn’t more wide-spread or commonly understood.
- The long-lasting MySQL DNS cache is disturbing, but alternatives would cripple the imperative nature of a database.
