mysql4u.com

To connect to the server, you will usually need to provide a MySQL user name when you invoke mysql and, most likely, a password. If the server runs on a machine other than the one where you log in, you will also need to specify a host name. Contact your administrator to find out what connection parameters you should use to connect (that is, what host, user name, and password to use). Once you know the proper parameters, you should be able to connect like this:

shell> mysql -h host -u user -p
Enter password: ********

host and user represent the host name where your MySQL server is running and the user name of your MySQL account. Substitute appropriate values for your setup. The ******** represents your password; enter it when mysql displays the Enter password: prompt.

If that works, you should see some introductory information followed by a mysql> prompt:

shell> mysql -h host -u user -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25338 to server version: 5.0.36-standard

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

The mysql> prompt tells you that mysql is ready for you to enter commands.

If you are logging in on the same machine that MySQL is running on, you can omit the host, and simply use the following:

shell> mysql -u user -p

If, when you attempt to log in, you get an error message such as ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2), it means that that MySQL server daemon (Unix) or service (Windows) is not running. Consult the administrator or see the section of Chapter 2, Installing and Upgrading MySQL that is appropriate to your operating system.

For help with other problems often encountered when trying to log in, see Section B.1.2, “Common Errors When Using MySQL Programs”.

Some MySQL installations allow users to connect as the anonymous (unnamed) user to the server running on the local host. If this is the case on your machine, you should be able to connect to that server by invoking mysql without any options:

shell> mysql

After you have connected successfully, you can disconnect any time by typing QUIT (or \q) at the mysql> prompt:

mysql> QUIT
Bye

On Unix, you can also disconnect by pressing Control-D.

Most examples in the following sections assume that you are connected to the server. They indicate this by the mysql> prompt.

I live in absolute and abject poverty. No, I don’t mean financially - I’ve been blessed beyond what I deserve and MySQL pays me very well. What I mean is that I am utterly time poor. Isn’t it amazing how you can be so free in terms of lifestyle and yet have so little time to enjoy and benefit from it all? There are so many options and choices in the world that many times you have a hard time knowing just what to say “Yes” to and what to turn down.

Trust me - you’ll face this dilemma at the upcoming MySQL User’s Conference. Our chairman, Jay Pipes, has outdone himself and put together the most engaging and power-packed agenda that we’ve ever had. The presenter’s list reads like a who’s who in the open source and modern database world, with sessions that address everything from deep dives down into database internals to the business side of implementing online database applications. You will find yourself hard-pressed to put together a personal attendee schedule because there will be so much you’ll want to see and hear.

And that’s where I’d like to help. If you’re a DBA or in a role where you’re in charge of managing one or hundreds of MySQL servers, let me give you a few pointers on sessions you really don’t want to miss and why. Of course, there are plenty other presentations that will grab your attention. But having slugged it out in the DBA trenches for years, and now being on the inside at MySQL, I can hopefully steer you into sessions that will feed you all the right stuff so you can go back to your job and immediately make big improvements in the way MySQL performs and is managed. Let’s start out with the area all DBA’s want to excel in - performance tuning.
Performance Management

When it comes to intelligently managing database performance, you have five core steps/areas to address:

1. Proactive Action - without question, the most important step. This is where you design your physical database (top contributor to performance), code, benchmark and test your SQL (second top contributor to performance), and stress test all parts of your system with realistic database volumes and user connections.
2. Monitoring - this includes real time and automated monitoring of all key database and server statistics.
3. Analysis - this is where performance decisions are made - where you evaluate performance patterns, do trend analysis and such, and move on to the next step.
4. Tuning - this includes making SQL tuning tweaks, database configuration massages, or adding additional memory and hardware.
5. Forecasting - probably the most neglected discipline in the database world. This is where you take your previously collected data used for trend analysis and smartly forecast future resource consumption.

The good news is there are sessions at the upcoming MySQL User’s conference devoted to helping you with all five steps above.

On the proactive action front, the best thing to do is learn from those who have gone before you and know the pitfalls to avoid and the right roads to run down. The MySQL Conference has a lot of these sessions to choose from, but you ought to make sure you catch Dathan Pattishalls presentation entitled, Federation at flickr: Doing Billions of Queries Per Day. If you’re looking for proactive help to put together heavy-duty replication and clustered systems, few people can tell you how to do it better than MySQL’s Brian Aker in his session, Replication and Clustering for Web Technologies. For you data warehousing enthusiasts, you’ll want to ensure you don’t miss one of MySQL’s data warehousing experts, Brian Miezejewski, and his session High Performance Data Warehousing with MySQL: Tricks and Tips from the Field. And if you want to write efficient and fast-running SQL queries, you’ll need to know how the MySQL optimizer works. To see what’s under the hood of the MySQL server, you’ll want to come and listen to one of MySQL’s optimizer gurus, Timour Katchaounov, speak on Query Optimizer Internals and What’s New in the MySQL 5.2 Optimizer.

For monitoring, you’ll be hard-pressed to find better information than that provided by Kristian Kohntopp (one of MySQL’s top consultants) in Monitoring MySQL and the session put on by Jay Pipes and Tobias Asplund, MySQL Performance Under a Microscope. For those of you wanting to see some of the new ways you can now use the tracing information from the general and slow query logs, be sure to attend Petr Chardin’s (Google) talk on MySQL 5.1’s Log Tables.

When it comes to database analysis, MySQL has recently introduced a new way to analyze the performance and usage of one to hundreds of MySQL servers. To understand how you can benefit from this new service, don’t miss Sehmuz Bayhan (Director of database systems at CNET) and MySQL’s Andy Bang’s session entitled, A Real-world MySQL Watch Dog: Step-by-Step Usage of the MySQL Monitoring and Advisory Service.

On the tuning front, there are quite a few and specialized sessions to choose from. If you’re looking for general MySQL tuning, then check out Jacob Taylor’s Scaling MySQL for Mission Critical Applications presentation on how to scale and tune MySQL for critical applications. For tuning the InnoDB storage engine, you won’t get any closer to the source than attending Heikki Tuuri’s session, InnoDB Performance Optimization. And for those of you interested in MySQL Cluster, where better to go than to the architect of Cluster himself, Mikael Ronstrom, and his session Performance Tuning MySQL Cluster?

MySQL’s Replication continues to be one of the top (if not THE top) feature of the server, so if you want to know all the tricks needed to make your replication farm perform well, come listen to MySQL’s Replication lead, Lars Thalman, talk on the subject of Tips and Tricks for MySQL Replication.

Finally, on the topic of forecasting and capacity planning, you’ll want to make sure and attend Flickr’s presentation entitled, What Happens After You’re Scalable: Capacity Planning for LAMP. A properly set up capacity planning system will save you in more ways than you can count, so be sure you don’t exclude this practice in your data center.
Database Management

Smart MySQL database and system administrators concentrate on four things:

1. Knowing the internals of the database they’re tasked with
2. Protecting and securing the databases they run
3. Automating every aspect of their job that they can
4. Understanding what the future holds for the databases they’re talking care of

Again, the good news for you is that the MySQL User’s Conference has every one of these areas well covered. When it comes to understanding MySQL internals, it’s good to know how the various storage engines you’re using are architected so you can maximize their benefits.

There’s good news for those of you needing a transactional storage engine in MySQL - we’re bringing out a new engine called Falcon. To learn more about this exciting new storage engine, it doesn’t get any easier than to listen to the man who created it - Jim Starkey. Jim will be giving two sessions at the conference - Inside Falcon: MySQL’s Newest Storage Engine - and Falcon Concurrency Control.

Of course, Falcon isn’t the only transactional storage engine available in MySQL. First, there’s MySQL Cluster of course. You also have a number of third-party transactional storage engines available, including Solid, PBXT, and InnoDB. For information on the very feature-rich Solid engine, you can check out Ari Valtanen’s session, solidDB Storage Engines. An exciting up-and-coming transactional storage engine is PBXT, and its creator, Paul McCullagh, will be presenting a session entitled, PrimeBase XT: Design and Implementation of a Transactional Storage Engine. Of course, the most popular MySQL transactional storage engine in use is InnoDB and its creator, Heikki Tuuri, will be providing an update session at the conference entitled, InnoDB: Status, Architecture, and New Features.

When it comes to learning more about securing and protecting MySQL databases, you’ll have a number of options available at the Conference. First, Sheeri Kritzer will be presenting a don’t-miss session titled, Testing the Security of Your Site. You’ll also be able to learn about PHP and MySQL security in Laura Thomson’s session, PHP and MySQL Web App Security.

Automating routine MySQL administration processes can go a long way into freeing up time that you can use for lots of other things. Helping you with how-to’s on database administration automation are Guy Harrison (Oracle and MySQL guru) with his session, Using Stored Routines for MySQL Administration and Tobias Asplund’s session, Using Triggers and Events for MySQL Administration and Auditing.

The only way to really plan well for the future is to know what’s coming, so the MySQL Users Conference has a number of sessions to educate you on what lies ahead. If you want to know what new things are coming on the MySQL replication and backup front, don’t miss Lars Thalmann’s sessions: MySQL Replication Roadmap and Vision and MySQL Backup: Roadmap and Vision. If you’ve tried and like MySQL’s data and index partitioning (new in MySQL 5.1), then be sure to attend Mikael Ronstrom’s session, Partitioning in MySQL 5.1 and Onwards. And finally, if you want the overall picture of what to expect out of MySQL on every front (Server, MySQL Enterprise, Tools, etc.), be sure to visit yours truly’s session entitled, MySQL Server Roadmap.
Conclusion

Maybe one day time won’t be my enemy, but until that day comes, I have to make sure I maximize every minute of every day so I can get done what needs to get done. The upcoming MySQL User’s Conference is a can’t-miss opportunity to maximize your time and drink in the knowledge of the best-of-the-best MySQL experts so you can build high-performing and always-on MySQL database applications.

The MySQL User’s Conference (http://www.mysqlconf.com/) will be held April 23-26 in Santa Clara, California, and if you hurry, you can get a big discount on conference registration. Make sure you attend some or all of the recommended sessions above and don’t be shy about asking lots of questions and making your needs known to all of us who will represent MySQL at the conference. That’s the only way we can make things better.

Thanks, as always, for supporting MySQL!