I’ve decided to give people two more days for a chance to win a free MySQL Book — Sheeri Cabral’s MySQL Administrators Bible.
I have had five people so far provide recommendations for a simple MySQL configuration question as stated in For MySQL DBA fame and glory. Prize included. Shlomi Noach the current front runner.
Try your MySQL Performance Tuning skills. This is a good opportunity for new MySQL DBA’s and experienced DBA’s to provide basic input.
The reason for yesterday’s Basic OS/MySQL Security was a request to review a system and I was given the production server ‘root’ password in an email. Never email a ‘root’ password, especially including the hostname as well. Email is an insecure protocol that can be monitored by hackers. However, today’s basic security tip following a look at the system is:
Never store the MySQL ‘root’ user password in a ~root/.my.cnf file.
There is simply no reason to do so, and you expose your database to destruction or manipulation when a user has access to the ‘root’ OS user, for example via sudo.
I’ve heard excuses why the ‘root’ MySQL password has to be in a file, I’ve yet to be convinced.
Do you need to store a MySQL password in a file? Yes. Connection management for your application is an example, however that use should never be ‘root’ user to connect to your application.
You may also need to run scripts to backup your data with mysqldump. The solution is to create a dedicated user .e.g. ‘backup’, and then grant that user only the permissions necessary to do the specific task at hand. By default, a simple mysql command will never grant access without any authentication, the user will need to find the password.
As David Minor pointed out in comments, you can also with MySQL change the ‘root’ user name, which is not a bad idea for improved security. The follow steps perform this.
$ mysql -uroot -p[password] mysql> create user dba@localhost identified by '[newpassword]'; mysql> grant all on *.* to dba@localhost with grant option; mysql> exit # Check you can really login $ mysql -udba -pnewpassword mysql> select host,user from mysql.user; # Drop 'root' users as listed, generally mysql> drop user [email protected]; mysql> drop user root@localhost; mysql> drop user root@[hostname]; mysql> select host,user from mysql.user; mysql> exit
More information see the MySQL 5.1 Reference Manual at MySQL Privileges, Create User and Grant Syntax.
I came across the following configuration today on a Production MySQL system (5.0.67) running 30+ blogs using WordPress MU.
$ cat /etc/my.cnf [mysqld] set-variable = max_connections=500 safe-show-database
No I did not truncate the output. I could say I’ve seen worse, but that’s a stretch.
So the quiz and a prize for the best response, for the next 48 hours I’ll accept your comments as responses to this post for the top 5 settings you would add, and additionally what information you may need to add these settings. Bonus points for giving a reason why you would add the settings as well.
For example, I’ll give you the most obvious.
key_buffer_size = ????
To determine a key_buffer_size to start with I would look at the size of all Indexes via I_S, and combine with some estimate of growth, say 2x-5x.
For the best answer with the top 5 settings, I’ll send you a copy of Sheeri Cabral’s book MySQL Administrators Bible which even I as a resident MySQL Expert has enjoyed scanning and learning something new.
If you can do either of these on your MySQL production server, you need to correct immediately.
1. Login directly to your MySQL server as the ‘root’ Linux Operating System user. For example:
$ ssh root@server-name Password: ************
2. Connect to MySQL database as the ‘root’ MySQL user without a password.
$ mysql -uroot
Here are the 60 second fixes to address these major security flaws.
To disable direct root access to your server, first ensure you can login as a normal user, then su – or sudo su – appropriately. Then, disable ssh root access with the following configuration change.
$ vi /etc/ssh/sshd_config
# ensure this is commented out and set to no
PermitRootLogin no
$ /etc/init.d/sshd restart
This will stop any brute force attack on your server by automated bots and password generators.
Second, the default installation of MySQL *DOES NOT SET A PASSWORD*. Apart from being crazy, I’ve seen production systems without a MySQL ‘root’ user password. To set a password run:
$ mysqladmin -uroot password SOMEPASSWORD $ mysqladmin -uroot -pSOMEPASSWORD -hlocalhost password SOMEPASSWORD
I had the pleasure to sit on the Performance Panel at the recent Percona Performance Conference. While the panel contained a number of usual MySQL suspects, one person was not familiar, that being Cary Millsap from Method R.
An expert in optimizing Oracle performance, Cary also gave an session on Day 2 that I attended. While he opened professing not to be an expert in MySQL, his talk provided valuable foundation knowledge irrespective of whether you use MySQL or another database product.
Having come myself from 7 straight years in system architecture and performance tuning in Ingres, then a further 6 years in Oracle again heavily involved in system architecture and performance tuning, a lot of my experience in the 10 years of providing my own MySQL consulting is drawn from my past RDBMS experiences. In addition much of what I actually provide to clients today is common sense that I don’t see applied.
A summary of the excellent content provided by Cary.
The common technology agnostic problem we need to address is:
From a user’s perspective, their experience consist simply of two elements.
In general, business people simply don’t care about the “system” except thought the specific tasks that make up their pressing business needs. And for these users, performance is all about the time to complete this task.
Throughput can be stated as tasks per time.
Response time is the time taken per task.
Cary also quoted Donald Knuth — “The universal experience of programmers who have been using measurements tools has been that there intuitive guesses fail.”
Performance is easy if you stop guessing where your code is slow. A few best practice tips are:
In Summary the closing points were:
Performance is easy when code measures it’s own time and tasks. This closing statement on instrumentation I completely concur with.
Recently on a client site I had to fight the pain of having no way to confirm loss of data integrity when optimizing data types. Due to MySQL’s ability to perform silent conversion of data, when converting a number of columns we enabled sql_mode to catch any truncations as errors.
sql_mode=STRICT_ALL_TABLES
This ensured that should any data truncations occur, an error is thrown not a warning. The following shows an example case study for converting an INT to TINYINT UNSIGNED and shows that without sql_mode silent conversions occur.
mysql> drop schema if exists tmp; Query OK, 25 rows affected (0.40 sec) mysql> create schema tmp; Query OK, 1 row affected (0.01 sec) mysql> use tmp Database changed mysql> create table t1(i1 INT NULL); Query OK, 0 rows affected (0.15 sec) mysql> insert into t1 values(1),(2),(3),(256),(65536),(NULL); Query OK, 6 rows affected (0.06 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from t1; +-------+ | i1 | +-------+ | 1 | | 2 | | 3 | | 256 | | 65536 | | NULL | +-------+ 6 rows in set (0.03 sec) mysql> set session sql_mode=STRICT_ALL_TABLES; Query OK, 0 rows affected (0.05 sec) mysql> alter table t1 modify i1 TINYINT UNSIGNED NULL; ERROR 1264 (22003): Out of range value for column 'i1' at row 4 mysql> set session sql_mode=''; Query OK, 0 rows affected (0.03 sec) mysql> alter table t1 modify i1 TINYINT UNSIGNED NULL; Query OK, 6 rows affected, 2 warnings (0.07 sec) Records: 6 Duplicates: 0 Warnings: 2 mysql> show warnings; +---------+------+---------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------+ | Warning | 1264 | Out of range value for column 'i1' at row 4 | | Warning | 1264 | Out of range value for column 'i1' at row 5 | +---------+------+---------------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from t1; +------+ | i1 | +------+ | 1 | | 2 | | 3 | | 255 | | 255 | | NULL | +------+ 6 rows in set (0.00 sec)
The process however for the client included converting a number of DATETIME columns to DATE columns. The input data specification called for the input values to be dates only so storing and using DATETIME was simply a waste of space.
Unfortunately the use of sql_mode didn’t help here, so the combination of changing multiple columns in one time simply meant it was impossible to determine if any truncations occurred.
This combined with the sheer data volume, tables between 10GB – 500GB, with 10 – 100 million rows.
When you see 14+ millions rows modified, 35+million rows and of course SHOW WARNINGS by default only shows a very small number of actual warnings
It is of course impossible to run individual ALTER statements on columns. Of the top 10 tables, excluding the largest, alter times were between 3 and 15 hrs.
The only solution is to run pre SQL checks on the data to search for the boundary conditions manually. This is not the ideal solution for a customer.
As the recipient of the 2009 MySQL Community Member of the Year award I received a MySQL crystal ball. While it looks good in my bookcase, unfortunately the best advice I can offer during this time of uncertainty is “watch this space”.
A number of topics where information is still very much unknown and I’m either asked about, or am following includes:
Thanks to Julian Cash of the Human Creativity Project of his photograph at the 2009 MySQL Conference.
View Photo. View all 2009 MySQL Conference photos.
The announcement last month of Oracle to acquire Sun continues to warrant a lot of discussion over exactly what Oracle will do with MySQL. Only time will tell what will happen with the official product, however it is important to remember that MySQL is GPL, there will always be a free version of MySQL available for popular LAMP stack products such as WordPress and Drupal and new and existing startup’s will continue to use MySQL.
This announcement will see Oracle resources begin to better understand and evaluate MySQL. As a resident MySQL Expert, I also have a strong background in Oracle having also worked for Oracle Corporation. I have also delivered several successful one day and half day workshops on MySQL/Oracle related content including:
In addition I have proposed 2 MySQL related talks at Oracle Open World 2009
For more information check out my MySQL for the Oracle DBA Resources.
I recent visit with old Brisbane friend HiTCHO which I met at the Brisbane MySQL Users Group in 2005, has lead to this cool list of some hardware and software technologies he used that I am now considering or have already implemented or purchased.
Software
Hardware