Friday, November 30, 2012

MySQL user authentication

I was looking around on the MySQL forums when I ran across this post and it got me thinking. First, thank you to Scott for helping in the community forum.  But also about how some still have issues with MySQL user authentication. So I thought I would put together a simple quick post about MySQL user authentication.

The forum post I mentioned above talks about user connection issues and how a user "is likely the record that is getting used by MySQL."

Well, lets review a simple example to ensure that we always will know what user is being authenticated by MySQL.

First we build out a few options for users.

CREATE USER 'foo'@'localhost' IDENTIFIED BY 'some_pass';
CREATE USER '%'@'localhost' IDENTIFIED BY 'some_pass';
CREATE USER 'foo'@'%' IDENTIFIED BY 'some_pass';

GRANT SELECT, INSERT, DELETE ON *.* TO 'foo'@'localhost';
GRANT SELECT, INSERT, DELETE ON *.* TO '%'@'localhost';
GRANT SELECT, INSERT, DELETE ON *.* TO 'foo'@'%';
FLUSH PRIVILEGES;

mysql> select User, Host,Password, Select_priv, Insert_priv, Delete_priv From user Where User = 'foo'\G
*************************** 1. row ***************************
       User: foo
       Host: localhost
   Password: *BF06A06D69EC935E85659FCDED1F6A80426ABD3B
Select_priv: Y
Insert_priv: Y
Delete_priv: Y
*************************** 2. row ***************************
       User: foo
       Host: %
   Password: *BF06A06D69EC935E85659FCDED1F6A80426ABD3B
Select_priv: Y
Insert_priv: Y
Delete_priv: Y


Some might look at this and say that we have a duplicate user and that localhost is not needed, so their 1st reaction is to remove it.


DROP USER 'foo'@'localhost';


Then they end up have user permission/connection issues and are confused as to why?

Always use "CURRENT_USER();" so you can see who your  being authenticated as. USER() -- is the name given by the client.



mysql> SELECT USER(),  CURRENT_USER();
+---------------+----------------+
| USER()        | CURRENT_USER() |
+---------------+----------------+
| foo@localhost | foo@%          |
+---------------+----------------+


So based on the CURRENT_USER result we realize that we need to look at other accounts.

mysql> select User, Host,Password, Select_priv, Insert_priv, Delete_priv From user Where Host = 'localhost'\G
*************************** 1. row ***************************
       User: %
       Host: localhost
   Password: *BF06A06D69EC935E85659FCDED1F6A80426ABD3B
Select_priv: Y
Insert_priv: Y
Delete_priv: Y
*************************** 2. row ***************************
       User: root
       Host: localhost
   Password: *2447D497B9A6A15F2776055CB2D1E9F86758182F
Select_priv: Y
Insert_priv: Y
Delete_priv: Y



The problem was not 'foo'@'localhost'  but instead the problem is '%'@'localhost'. MySQL will authenticate on Host 1st before it checks the User. So since it found a quick match to '%'@'localhost' we ended up with:


mysql -u foo -p

SELECT  CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| foo@%          |
+----------------+


Using '%'@'localhost' is never a good idea in my opinion. You always want to lock the database down to User and Host/ip. A wild card as part of an IP address I am ok with as well. ie: 'foo'@'192.168.0.%'

This is a very simple example but the point is still a valid one. Control the permission, users and hosts with a watchful eye.


Keep in mind the mysql_secure_installation options for a more secure server as well.


# mysql_secure_installation


NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] n
 ... skipping.

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...



All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!



Helpful urls :

Thursday, November 22, 2012

SHOW PROCESSLIST... still ?

So a couple of blogs posts have come out recently that got me curious to do a little more digging on how I might actually use "SHOW PROCESSLIST". Btw those blogs posts are:
 So we all know how this works:

Server version: 5.6.8-rc-log MySQL Community Server (GPL)

MySQL 5.6.8 RC> show processlist\G
*************************** 1. row ***************************
Id: 20007
User: root
Host: localhost
db: test
Command: Query
Time: 0
State: executing
Info: select 10000
*************************** 2. row ***************************
Id: 4
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
2 rows in set (0.00 sec)

That is quick and easy. It give you the ID, user , host , state and time running. It also shows the command so your able to take review any obvious issues or take it and execute an explain so you can check for indexes. In a normal database you will have a lot more rows and have to parse out information accordingly to find what your after. How many rows are acceptable is dependant on the application and database. A simple start by using"pager" is one way to at least be able to see the information available, when you might have 200+ rows of information for example.

pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
MySQL 5.6.8 RC> pager more
PAGER set to 'more' # Turns it on
MySQL 5.6.8 RC> show processlist\G

MySQL 5.6.8 RC> pager
Default pager wasn't set, using stdout. # turns it off

You can less as well but this is just an example and a starting point.

In Mark's blog he points how issues with SHOW PROCESSLIST like stealing connections, non-blocking and views. He talks about creating a view with information from the tables within the performance_schema and information_schema so we can gather more information in a non-blocking way. So if started to just dig around some might think that the events_statements_current would be a replacement ( seen below). But as you can see below it is not, lots of information but not a direct replacement.  
MySQL 5.6.8 RC> select * from events_statements_current\G

*************************** 2. row ***************************
THREAD_ID: 90033
EVENT_ID: 334676
END_EVENT_ID: NULL
EVENT_NAME: statement/com/Query
SOURCE: mysqld.cc:913
TIMER_START: 3807813684782000
TIMER_END: NULL
TIMER_WAIT: NULL
LOCK_TIME: 0
SQL_TEXT: select a+a+a+a+a+a+a+a+a+34673 from bench1
DIGEST: NULL
DIGEST_TEXT: NULL
CURRENT_SCHEMA: test
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
2 rows in set (0.00 sec)

So instead of digging across all the tables, while taking phone calls and etc... I will just check out the ps_helper information that Mark has available.

First per Mark's blog update the setup_consumers table; MySQL 5.6.8 RC> update 

setup_consumers set ENABLED = 'YES' WHERE NAME IN ('events_stages_current','events_stages_history','events_statements_history','events_waits_current','events_waits_history');
MySQL 5.6.8 RC> SELECT * FROM setup_consumers;
+--------------------------------+---------+
| NAME | ENABLED |
+--------------------------------+---------+
| events_stages_current | YES |
| events_stages_history | YES |
| events_stages_history_long | NO |
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | NO |
| events_waits_current | YES |
| events_waits_history | YES |
| events_waits_history_long | NO |
| global_instrumentation | YES |
| thread_instrumentation | YES |
| statements_digest | YES |
+--------------------------------+---------+
12 rows in set (0.00 sec)
mysql -p < /tmp/ps_helper_56.sql_.txt

( Btw I had to take the processlist_full view from Mark's blog as was not in the ps_helper_56.sql_.txt file at the time I wrote this blog. )

MySQL 5.6.8 RC> use ps_helper
MySQL 5.6.8 RC> select * from processlist_full \G 

*************************** 2. row ***************************
thd_id: 180043
conn_id: 180024
user: root@localhost
db: test
command: Query
state: freeing items
time: 0
current_statement: select a+a+a+a+a+a+a+a+a+53833 from bench1
last_statement: NULL
last_statement_latency: NULL
lock_latency: 33.00 µs
rows_examined: 2
rows_sent: 2
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: YES
last_wait: wait/io/table/sql/handler
last_wait_latency: 618.26 ns
source: handler.cc:2715

I know have the information I was reviewing before with additional information. I like the "full_scan" field included into this so you can see if indexes are being used right away.

So the perks overall are, by taking the ps_helper code you should be able to easily install the views and not have to piece all of the different information together. All of that work has been done for you. Would I use the views all the time? No. SHOW PROCESSLIST will be just fine for me a lot of the time for now. I will take this and have it available, because when you do need it, it will be great information to have. I also think that once installed and available it will be a tool that will slowly be used more and more for debugging issues and concerns. Great work guys.

Some other show processlist references to note:

Monday, November 19, 2012

Thank you Davi and Twitter

Just a quick note to send a big thank you to Davi Arnaut from Twitter. He has continued to support and contribute bug fixes for MySQL and the following bugs have all been accepted.

Bug #65469 Infinite loop when opening a corrupted table
Bug #65692 Deadlock between START SLAVE and setting a system variable
Bug #65715 Wrong connection ID (thread ID) in the general and slow query logs
Bug #56240  please write proper dates to the error log
Bug #67156 Sporadic query cache related crash in pthread_rwlock_init()

A big thank you and we look forward to more contributions from Davi and the others from the MySQL Community.

Tuesday, November 13, 2012

Oracle’s MySQL OEM Tech Tour - Miami

Oracle’s MySQL OEM Tech Tour - Miami :

Tuesday, December 11, 2012
10:00 AM – 2:00 PM


Oracle Office
6505 Blue Lagoon Dr., Suite 400
Miami, FL 33126 Rm: DEMO-4004



Embedding MySQL:
Higher Application Performance and Streamlined Development with MySQL Embedded

Join us and get tips directly from the MySQL technical experts on how to better develop, integrate, secure, and tune MySQL for your application.

MySQL is not only the most popular open source database for Web applications, it's also a popular embedded database relied on by over 3,000 ISVs and technology providers around the world such as Adobe, Dell, Sage and Symantec. Whether for distributed on-premise applications or hosted SaaS offerings, MySQL is ideally suited for use as an embedded database.

Come and learn how to leverage MySQL's unique architecture, development tools and newly released features to speed your engineering efforts with MySQL and ensure that your application performs at its peak. Topics include how to:

  • Make the most of MySQL's Architecture
  • Streamline development and QA with MySQL Tools
  • Secure your customers' data with New Security Features
  • Improve performance with Optimization and Performance Tuning tips 

Register Now!

The server quit without updating PID file

With MySQL 5.6.8 RC available more and more people will start to test installs and upgrades.  This often comes up for some people so I hope this helps some.

If you seeing the error "The server quit without updating PID file" then obviously something recently changed on your environment.

So lets take it step by step:

# /etc/init.d/mysql start
Starting MySQL..The server quit without updating the PID file (/var/lib/mysql/localhost.localdomain.pid).

While yes people do say just move the my.cnf file and restart the database to by pass this error... You need to understand why. Why would moving the my.cnf allow mysql to start? Check the error logs that is how and why you will be able to fix it and get it restarted.

Issue 1:
"Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist."

So this is a result of a bad MySQL upgrade, most likely. You can start MySQL by avoiding the grants and keeping your my.cnf in place. 

# /etc/init.d/mysql start --skip-grant
Starting MySQL..                                           [  OK  ]

You also need to fix the missing grants tables.
# mysql_install_db
Installing MySQL system tables...
OK
Filling help tables...
OK

Issue 2:
InnoDB: Error: data file /var/lib/mysql/ibdata1 is of a different size

So your  my.cnf did change and innodb_data_file_path is now a different size than before. So you need to put back or remove the ibdata files so they can be rebuilt; the later of course will remove the data.


These are just two simple examples but if you take your time and look into the log file data you will find the reason why MySQL might not start. Moving the my.cnf is not the answer.

Additional References:
The Error Log
Monitoring MySQL – The error log
How to diagnose errors in the MySQL error log



Friday, November 9, 2012

MySQL monitoring, backups and recovery

Being a MySQL DBA is more than just being able to understand the explain command to optimize a query.  The best optimized queries in the world are worthless with the database is down.

Some of the big responsibilities with being a MySQL DBA include monitoring, backup and recovery. This is a high level overview of those topics.

Monitoring:

A single monitor is just not good enough. If you are running a Nagios server with the MySQL plugins that is a great start. This  should not be your only solution. The first thing that should be added is another remote Nagios server to monitor the 1st master Nagios  monitor. After all if the monitor goes down how do you know?

When running a revenue generating MySQL Server it is easy to suggest using the MySQL Enterprise Monitor.  I have used the MySQL Enterprise Monitor as an additional resource for monitoring. It helps you spot trends as well as offer suggestions for tuning.

Monitor your logs. In order to be able to recover, you need to understand what happened. Monitoring log is more than just the error log. You need to watch the slow_query_log, error_log as well as OS logs. Knowing how to gather everything from network stats to memory or raid status is key.

Monitor your reports. Put in place events || etls || checks to validate reporting numbers. Every application is different so this is a custom solution per application but be able to get valid notifications when key reporting numbers fall under thresholds. This could be an early warning sign to other issues.

Monitor your backup logs and sizes. If you fail to watch the sizes of your backups as well as the logged output then I wish you the best of luck. Something like an upgrade could change your backup procedure. While you consider it to still be running just fine, maybe they error out now? A simple cron job that executes a script to record file sizes and checks for errors can put the output into a the database. Then another script can be used for reporting on that file size output. You will then have all the trends of file sizes to make positive thresholds.

Take out the human factor.  If all the monitoring notifications go to one person, then that is a single point of failure itself. Make sure fine tune notifications to remove false positives and alert more than one person. Text messages to smart phones are not enough. People are allowed to sleep and it is very easy to sleep through a txt message. I enjoy the old school pager because it simply works. If you can take advantage of asterisks then use it. A phone call wakes people up more than just a txt message.

This quick overview highlights that monitoring is more than just a single entity. It is a vast process that should never be taken for granted.

Backups:
MySQL backups are the process that everyone knows is a good idea. The problem is how many people actually do it effectively. Meaning that they work, tested, and do not harm a live revenue generating site.

mysqldump  is an effective tool for backups when it is not a live system. Stopping a slave and doing a mysqldump then restarting a slave is a very safe method for your system. The problem is that to many applications or entities either take to long to do a dump and rarely tested when it is done.

mysqldbexport is another option over mysqldump because it allows different output options.

Replication is not a backup solution. It is a step in the right direction for higher availability and you can use replication for rolling historical data with time delayed replication but you still need to have the data as a backup to be safe. If using MySQL dump or the enterprise options, yes you can use them off a slave easier just replication is not a backup solution.

Take advantage of the mysqlbinlog and use it to backup your binlogs. The binlogs are the life of your MySQL server, take care of them.

The best option in my opinion is MySQL Enterprise Backup. Nothing against Percona XtraBackup but since I already encourage the use of enterprise monitor I typically would support and stay with enterprise backup since I have access to it.  Regardless of your choice, the point is simple, take advantage of on-line backups. What happens when you have a serious replication issue and your slaves are useless? Do you really want to do a mysqldump off the master?  They also offer incremental and compression to help with your backup needs.

Recovery:

This is often the piece of the process that tested in theory but less in reality. It is often not realistic for some companies to be able to restore their backups often. The hardware required to hold the database is not cheap and can be used for other things than just backup restores. You do need to test your backups though. Maybe it is just once a quarter and you can afford to test a slave with a restored backup.  Maybe you have a virtual environment and you can test a backup into a VM just to see if it works?  Execute some reporting on this database to verify numbers. Take advantage of the different database checksum options available in the community.

Whatever it is something is better than nothing.



This again is just a overview of the concepts about monitoring, backup and recovery. Please use the links provided to find out more information.

More community and MySQL resources and options:


Thursday, November 8, 2012

MySQL Community Server 5.6.8-rc has been released

In case you missed this......  
 
MySQL Server 5.6.8 RC was announced today.
MySQL Server 5.6.8 (Release Candidate) is a new version of the world's most popular open source database. 
Please download and test it out. Oracle is eager for any feedback that you might have on this release. 
 

 

Thursday, November 1, 2012

Debugging Stored Routines in MySQL

A new "Meet the MySQL Experts Podcasts" is available.

Debugging Stored Routines in MySQL with Fernando Gonzalez.

A big thanks to Fernando and the Podcast team at Oracle for helping with this podcast.

MySQL November 2012 Meetups

November is here and what do we have to be thankful for? A great community overall is the simple answer.

This month looks to have some great MySQL User Group Meetups:



A full list of user groups is available via the MySQL Wiki
Meetup.com also has a list of user groups available.
 
 
 
 

Wednesday, October 31, 2012

Binlogs 101

The mysqlbinlog are not new. We often reference it and all of the valuable information that it provides. I thought it might be useful to show some simple examples on how to get some of that information, for those unfamiliar with the mysqlbinlog tool.

For this example, I used the MySQL benchmark tools to populate data.

You can review binary log events a couple if different ways.
You can use the command line :

      mysqlbinlog mysql56rc-bin.000010
or within MySQL via a simple query.
      SHOW BINLOG EVENTS IN 'mysql56rc-bin.000010' ;

These will both dump out all of the data, and this demo currently has 2284725 rows of data, to much to review one by one.

Keep in mind that some of the differences between these two options. You will gather more information via the command line mysqlbinlog versus just the query "SHOW BINLOG EVENTS".

example: If we wanted to get an idea of how long a query took to run?

# mysqlbinlog mysql56rc-bin.000010
....
# at 915
#121031  7:52:49 server id 1  end_log_pos 1029 CRC32 0xd3201bdc         Query   thread_id=47    exec_time=0     error_code=0
...
insert into bench1 values ('C',2,9997,6)
....

I could also be more direct and check the logs for just that bin log position: 
# mysqlbinlog mysql56rc-bin.000010 --start-position=915 --stop-position=1029


The mysqlbinlog will also allow you to gather an idea of the execution time a query took on a master server. It is not perfect when checking the same query on a slave. Baron has a blog about this if you want to learn more.

We are unable to see execution time via the "SHOW BINLOG EVENTS".

MySQL 5.6 RC> SHOW BINLOG EVENTS IN 'mysql56rc-bin.000010' FROM  915 LIMIT 1\G
*************************** 1. row ***************************
   Log_name: mysql56rc-bin.000010
        Pos: 915
 Event_type: Query
  Server_id: 1
End_log_pos: 1029
       Info: use `test`; insert into bench1 values ('C',2,9997,6)
1 row in set (0.00 sec)


The mysqlbinlog also allows you to be able to search within a date range.

# mysqlbinlog mysql56rc-bin.000010 --start-datetime="2012-10-31 07:52:49" --stop-datetime="2012-10-31 07:52:50"

# at 915
#121031  7:52:49 server id 1  end_log_pos 1029 CRC32 0xd3201bdc         Query   thread_id=47    exec_time=0     error_code=0
SET TIMESTAMP=1351695169/*!*/;
insert into bench1 values ('C',2,9997,6)
/*!*/;


This is just a simple small sample intro into MySQL Binlog. Find more information via these great blogs and sites.

http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html
http://www.pythian.com/news/1174/mysqlbinlog-tips-and-tricks/
http://ronaldbradford.com/blog/tag/mysqlbinlog/
http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog-backup.html
http://flylib.com/books/en/2.304.1.130/1/
http://www.databasejournal.com/features/mysql/article.php/3903646/Inside-MySQL-Binary-Logs.htm
http://www.mysqlperformanceblog.com/2011/01/31/what-is-exec_time-in-binary-logs/




Friday, October 26, 2012

Keyser Soze in MySQL?

Who is Keyser Soze?

Some recall:
"...a rarely seen, nearly mythical kingpin... Most believe he is not real, but rather boogeyman story or name-drop to intimidate people."

Some people think of it as :
"Keyser Soze – DBA or developer who looks ordinary but has insanely good skills, hardly anybody knows about it."

What does any of this have to do with MySQL ?

Well for this blog post, "Keyser Soze" is the nearly mythical query that most believe is not real, but rather a "boogeyman sql query" that wastes resources in a MySQL Database. Typically, every application has at least one. Your developers blame the database for being slow and they have caught the ear of the CEO. It is time to resolve the boogeyman issue and find out. The difference is we want a better agent than Dave Kujan, so we have the MySQL Query Analyzer and Enterprise Monitor.


First, we get them on the case:
./mysqlmonitor-2.3.12.2175-linux-x86_64-installer.bin

FYI: Requires MySQL Server version 5.1.43 or later with partitioning and InnoDB storage engine enabled is required.


The install is very straight forward and just follow the prompts.
You will be asked:

Database Installation

Please select which database configuration you wish to use

[1] I wish to use the bundled MySQL database
[2] I wish to use an existing MySQL database

It is up to you which database you decide. In my opinion, a monitor should not alter the environment of what is is monitoring. I would choose option 1. It would not hurt to still backup this database, just in case.


Once this is installed we start to look over our graphs and advisor...









Very mellow server load here.
 ( granted this is a demo)














The MySQL Query Analyzer can shed more light on curious queries. 
It will take a couple edits to the ../enterprise/agent/etc/mysql-monitor-agent.ini file.  I prefer the server method because it is quick and simple and catches all default interactions with the database.







So now if an event was to hit your database you are aware whenever it happens. You will be able to scroll back overnight for example to see not only what ran, but how it ran.  If someone decided to put a small query into a infinite loop by mistake, you will find it here.





Once you do see something you are curious about... (Maybe it is "Keyser Soze" or maybe it is a planned query)

You can then highlight the graph and then pull up all queries related to that time frame.








 You will then be allowed to see the explain (as long as it meets time thresholds)  as well as graphs of how the selected query was executed via your server.








If you find yourself not getting the explains like you expected make sure to check your configuration and adjust the threshold.












While this is just a demo, this tool is very useful for exploring trends in your data, as well as find the critical events. The advisers can help you fine tune the server per the data that has been executed on it.  The Enterprise monitor will allow you to do a deep dive into the system and explore all options as you look for the "Keyser Soze" in your application.






Of course don't forget, the monitor also delivers replication information. So you can keep track of topology, server type, position, errors, and log space.




While all this  might not make an interesting movie like the Usual Suspects did, it does show that while a DBA might have a list of usual suspects when it comes to a problem, they also can lean on the tools available for a wealth of additional information.

MySQL Enterprise Edition




More Reference urls:








Friday, October 19, 2012

Amsterdam, MySQL User Group

A comment was recently posted on the MySQL User Group Wiki page about a new user group in the Netherlands available via Meetup.com.

I wanted to take a moment and say thank you and ask everyone to support this new user group.

If people are aware of any user groups not listed, please feel free to post on the wiki page this information.

Wednesday, October 17, 2012

MySQL Failover Utility

I was giving a replication talk at Ohio Linux Fest last week and we touched a little bit on the MySQL Failover Utility and how it works with MySQL 5.6 .

The first thing that this along with Innodb and other replication enhancements are big GPL offerings from Oracle to the community.

First the usage:

[root@mysql-master python]# ./mysqlfailover --help
Usage: mysqlfailover --master=roo@localhost --discover-slaves-login=root --candidates=root@host123:3306,root@host456:3306

mysqlfailover - automatic replication health monitoring and failover


....

  --candidates=CANDIDATES
                        connection information for candidate slave servers for
                        failover in the form:
                        <user>:<password>@<host>:<port>:<socket>. Valid only
                        with failover command. List multiple slaves in comma-
                        separated list.
  --discover-slaves-login=DISCOVER
                        at startup, query master for all registered slaves and
                        use the user name and password specified to connect.
                        Supply the user and password in the form
                        user:password. For example, --discover-slaves-
                        login=joe:secret will use 'joe' as the user and
                        'secret' as the password for each discovered slave.
  --exec-after=EXEC_AFTER
                        name of script to execute after failover or switchover


So I set up a test, I will monitor the master @ 192.168.0.17 and have a slave @ 192.168.0.7 all from a server @ 192.168.0.34.

Personally I would run this in screen, with a password on it, so I can have access to it easily from any location.

# screen
# ./mysqlfailover --master=root:<PASSWORD>@192.168.0.17 --discover-slaves-login=root --candidates=root:<PASSWORD>@192.168.0.7

Keep in mind that the password would be easily found in history and ps -ef views.

Now once it executes you realize that you never had GTID on in the first place..... 

ERROR: Topology must support global transaction ids and have GTID_MODE=ON

This is simple enough but 'gtid_mode' is a read only variable so it must be done via the my.cnf file.

vi /etc/my.cnf
[mysqld]
gtid_mode                                             =ON
disable-gtid-unsafe-statements          =1

show variables like '%gtid%';
+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| disable_gtid_unsafe_statements | ON        |
| gtid_done                      |           |
| gtid_lost                      |           |
| gtid_mode                      | ON        |
| gtid_next                      | AUTOMATIC |
| gtid_owned                     |           |
+--------------------------------+-----------+


 So now that those are all updated...

Again if your not in a screen this might be a good point to do so..
#screen
# ./mysqlfailover --master=root:<PASSWORD>@192.168.0.17 --discover-slaves-login=root --candidates=root:<PASSWORD>@192.168.0.7

# Discovering slaves for master at 192.168.0.17:3306
# Checking privileges.
# Checking privileges on candidates.

 We get a simple display to start

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Wed Oct  3 13:52:01 2012

Master Information
------------------
Binary Log File       Position  Binlog_Do_DB  Binlog_Ignore_DB 
mysql56rc-bin.000016  151                                      

Replication Health Status
+---------------+-------+---------+--------+------------+---------+
| host          | port  | role    | state  | gtid_mode  | health  |
+---------------+-------+---------+--------+------------+---------+
| 192.168.0.17  | 3306  | MASTER  | UP     | ON         | OK      |
+---------------+-------+---------+--------+------------+---------+
Q-quit R-refresh H-health G-GTID Lists U-UUIDs

Transactions executed on the servers:
+---------------+-------+---------+-----------------------------------------+
| host          | port  | role    | gtid                                    |
+---------------+-------+---------+-----------------------------------------+
| 192.168.0.17  | 3306  | MASTER  | 98CCBD8A-EB09-11E1-B4D5-08002701A7D7:1  |
+---------------+-------+---------+-----------------------------------------+

UUIDs
+---------------+-------+---------+---------------------------------------+
| host          | port  | role    | uuid                                  |
+---------------+-------+---------+---------------------------------------+
| 192.168.0.17  | 3306  | MASTER  | 98ccbd8a-eb09-11e1-b4d5-08002701a7d7  |
+---------------+-------+---------+---------------------------------------+


Since we are in screen we can control-A D and exit the screen and resume it later whenever we want to check on the status.

# screen -r

Now lets crash the master...   a quick power off of the virtual box should do it.

The output now refreshes and soon displays the new master.

# Discovering slaves for master at 192.168.0.7:3306

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Wed Oct 17 08:50:19 2012

Master Information
------------------
Binary Log File       Position  Binlog_Do_DB  Binlog_Ignore_DB 
mysql56rc-bin.000020  191                                      

Replication Health Status
+--------------+-------+---------+--------+------------+---------+
| host         | port  | role    | state  | gtid_mode  | health  |
+--------------+-------+---------+--------+------------+---------+
| 192.168.0.7  | 3306  | MASTER  | UP     | ON         | OK      |
+--------------+-------+---------+--------+------------+---------+


Now if you added the "--exec-after= " you will be able to execute a script that fits your environment. This script can be used to adjust the reference point for your application to the new master automatically.

Test it out with the new MySQL 5.6 RC... Oracle would love your feedback.



Tuesday, October 16, 2012

RMOUG Training Days 2013 & MySQL


Call for Abstracts Deadline Extended until October 22! 

MySQL Sessions are needed ! Submit an abstract for RMOUG Training Days 2013! They have extended the abstract submission deadline to Monday, October 22.

They are looking for presentations on the following topics:
  • Application Development
  • Business Intelligence
  • Database Administration
  • DBA Deep Dive
  • Database Tools of the Trade
  • Middleware
All primary authors of selected abstracts receive a complimentary conference registration.

Visit www.rmoug.org for topic descriptions and details on how to submit your abstract online. 

Sunday, October 14, 2012

MySQL Cluster - Web Scalability with Carrier Grade Availability

Just a quick note that the Colorado MySQL User Group will be have Craig Sylvester in town November 7th 2012 for a MySQL Cluster talk. Please feel free to RSVP and join us!

Thursday, October 11, 2012

SCREEN

Many of us know the joys of the "Screen" command. I did notice that this topic became a point of many questions at the recent Ohio LinuxFest, so I thought I could touch more on it here.

Being able to detach and reattach to a shell is a wonderful thing.  It is very useful when executing long query transactions like MySQL data exports or even running backups at times. It is also very useful to be able to change computers or locations and keep the same working shell environment. I often used Screen when I started a shell script that executed a full compressed backup, scp the files to a remote slave, and then would start to uncompress the backup onto the new slave. The entire process for this was around 24hrs easily so being able to reattached and monitor the progress was very helpful.

Being able to have a visual display that is quick and clear as to what host your connected on is of course critical. A fast an easy way to know MySQL Hosts is also very valuable.  At the very least you can change the mysql prompt in the my.cnf file
(   ie my.cnf --> mysql --prompt=" this is a mysql box>"  )

or just via your session

( mysql> prompt MySQL Master>
PROMPT set to 'MySQL Master> '
MySQL Master> show master status\G

). 


Screen becomes helpful keeping the shells organized. Personally, I do not like to have a different colors in my shell backgrounds.  A simple white or black is just fine for me.


Here are a few other things I do with Screen:

1st  create or edit a file for yourself vi ~/.screenrc

I like to enhance my status line at the bottom with the following:

hardstatus string '%{gk}[ %{G}%H %{g}][%= %{wk}%?%-Lw%?%{=b kR}(%{W}%n*%f %t%?(%u)%?%{=b kR})%{= kw}%?%+Lw%?%?%= %{g}][%{Y}%l%{g}]%{=b C}[ %m/%d %c ]%{W}'

You end up with something like the following on the bottom of all screen windows.

[ Remote Hostname ][            (0*$ bash)                             ][1.00 1.00 1.00][ 03/07  0:20 ]

if you have multiple screens you get this:

[ Remote Hostname ][          (0*$ bash) (1*$ bash)            ][1.00 1.00 1.00][ 03/07  0:20 ]

Yes you can have multiple screens just type screen when your inside a current screen to start another.

It is always best to name your screens as well.

Cntrl A then :
Then type "Title < title name>" of what you want to label the tab window. Your results are similar to below.

[ Remote Hostname ][    0-$ MySQL_Master  (1*$ MySQL_Slave)        ][1.00 1.00 1.00][ 03/07  0:25 ]

Then Cntrl A <0--9> to switch between tab windows.

You can access a screen session via any computer that is allowed to connect to the screen host. So always keep in mind to lock your screen sessions.

Cntrl A password  -- So you can lock with a password of your choice.

You might have several different Screen sessions so try to also name your screen sessions.  You might be executing a MySQL dump in one Screen session and working with a data export in another.

ie:
screen -S MySQL_dump
-S sockname   Name this session <pid>.sockname instead of <pid>.<tty>.<host>.

This naming comes in very handy when you have to reattach.
I often use the handy shell script found here: https://github.com/cep21/jackbash/blob/master/bin/screens


Another big advantage of Screen is being able to work with remote workers in training or just reviewing MySQL queries.  For example, you can have a few different developers on the same screen session and execute explain on their recent queries so you can all review the best index options together.
If you want to learn more about that here is a nice article.



Some good tips on screen are also available here
http://www.softpanorama.org/Utilities/Screen/tips.shtml
http://polishlinux.org/howtos/screen-tips-tricks/

Monday, October 8, 2012

Thanks for the support!

I wanted to post a big thanks to the Chinese MySQL Bloggers.  Almost a month ago we published the  Chinese version of Planet ( my previous blog post ). In the last month, we have collected 68 different blog posts about MySQL across 8 different MySQL Bloggers.

 Thanks for the support, this is an encouraging start.






Saturday, September 29, 2012

MySQL Connect 2012 Table of Contents

In case you have missed any of the recent blog posts about MySQL 5.6 and MySQL 7.3 from MySQL Connect.

MySQL Connect 2012 Table of Contents:
Keep in mind for next year: Conference Tips

Friday, September 14, 2012

MySQL Connect Schedule

So the MySQL Connect Conference is just around the corner. For those of you that do not get the newsletter:

The Oracle INFORMATION INDEPTH NEWSLETTER MySQL Edition was recently sent out. Take advantage of this newsletter because it highlights the schedule nicely with links into the abstracts. It also lists the receptions, demo pods.


http://www.oracle.com/us/corporate/newsletter/samples/mysql-1384701.html

Wednesday, September 12, 2012

MySQL Replication 101 Overview

Since MySQL Connect is just around the corner and several of the sessions are related to the replication features in MySQL 5.6, I figured I would put together a quick MySQL Replication 101 Overview for any of the new users.

Topology

First you must decide what type of Topology will be serve you and your applications needs.  Below are a list of typical topologies. For this blog post example we will set up a couple of versions.


Single Master and on slave:
Single Master and muliple slaves:
Circular:

Chain:


MySQL Configuration file edits
 
MySQL 5.6 will allow the us to take advantage of the database for our repository information. The  "master-info-repository=TABLE" and the "relay-log-info-repository=TABLE" reference allows this. A unique value is required for server id per server.


Master my.cnf

# vi /etc/my.cnf
        [mysqld]
        server-id=1
        log-bin = /var/lib/mysql/yoda-bin
        master-info-repository=TABLE

[mysql]
prompt=mysql_yoda>\\_


  
Start and Log into MySQL

    master_yoda>show master status\G
*************************** 1. row ***************************
            File: yoda-bin.000001
            Position: 114
            Binlog_Do_DB:
            Binlog_Ignore_DB:
            1 row in set (0.00 sec)



Master SQL



mysql_yoda> show variables like 'master_info_repository';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| master_info_repository | TABLE |
+------------------------+-------+
 


mysql_yoda>CREATE USER 'replication'@'192.168.0.%' IDENTIFIED BY 'slavepass';
mysql_yoda>GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
mysql_yoda>flush privileges;


Now lets create a copy of the database to start from.



# mysqldump -p  --all-databases  --master-data=2 > /tmp/replication_example.sql
THIS LOCKS THE DATABASE! 



or try  the MySQL utilities


mysqldbexport --server=root@server1:3306 --format=csv db1 --export=data

If you do not want to lock the database as you make a copy, Oracle offers MySQL Enterprise Backup  You can test this for 30 days as well for free.

Remember to adjust all firewall rules if required for MySQL Port. (3306)


SLAVE my.cnf
  vi /etc/my.cnf
        [mysqld]
        Server-id=2
        relay-log=/var/lib/mysql/luke-relay-bin
        relay-log-info-repository =TABLE

[mysql]
prompt=mysql_luke>\\_


# mysql --user=root  -p  <  /tmp/replication_example.sql

SLAVE SQL

mysql_luke> CHANGE MASTER TO
  MASTER_HOST='yoda',
  MASTER_USER='replication',
  MASTER_PASSWORD='slavepass',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='yoda-bin.000002',
  MASTER_LOG_POS=83415,
  MASTER_CONNECT_RETRY=10;


mysql_luke> start slave;

We gathered this info from the mysqldump file via the  “ --master-data=2 ” flag.

mysql_luke> show slave status\G

mysql_luke> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: yoda
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: yoda-bin.000003
          Read_Master_Log_Pos: 323
               Relay_Log_File: luke-relay-bin.000004
                Relay_Log_Pos: 475
        Relay_Master_Log_File: yoda-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                 Master_Server_Id: 1
    Master_UUID: 75d407df-2be4-11e1-9668-b4be9bce39b0
      Seconds_Behind_Master: 0
        SQL_Remaining_Delay: NULL 



Now we also have some alternative options to the above set up.

-- If you can afford to have both the master and slave database offline, MySQL Workbench comes with MySQL Utilties (also via launchpad) which has  mysqlreplicate

mysqlreplicate \ --master=root@master.example.com \ --slave=root@slave.example.com \ --rpl-user=repl:xyzzy


-- If you wanted to create a Chain or Circular topology, the slave ( or master 2 ) will also need a bin log which I have added below. Then you can set up another slave that is using "Luke", per the example, as the master.

SLAVE my.cnf
  vi /etc/my.cnf
        [mysqld]
        Server-id=2
        relay-log=/var/lib/mysql/luke-relay-bin
        relay-log-info-repository =TABLE 

        log-bin = /var/lib/mysql/luke-bin

To adjust the server examples above to a Circular Topology we will need to make a few more variable edits. The edits below will prevent index collisions.

SQL
   mysql_yoda> SET GLOBAL auto_increment_offset=2;
  
mysql_yoda> SET GLOBAL auto_increment_increment=1;  
 
   mysql_luke> SET GLOBAL auto_increment_offset=2;
  
mysql_luke> SET GLOBAL auto_increment_increment=2;


mysql_yoda>show global variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 2     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
mysql_luke>show global variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 2     |
| auto_increment_offset    | 2     |
+--------------------------+-------+


mysql_luke>SHOW MASTER STATUS\G
*************************** 1. row ***************************
        File: luke-bin.000005
        Position: 295


mysql_yoda>CHANGE MASTER TO
  MASTER_HOST='luke',
  MASTER_USER='replication2',
  MASTER_PASSWORD='slavepass',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='luke-bin.000005',
  MASTER_LOG_POS=295,
  MASTER_CONNECT_RETRY=10;
mysql_yoda> start slave;



This was a simple overview but it gets you started. If you would like to dig further, below is a list of related MySQL 5.6 and replication urls.