Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use MySQL periodic tasks to regularly clean up the list of online users, I believe most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's learn about it!

Scene setting

The client is an android mobile phone. After logging in, you want to save its online status (server), and there is a corresponding checksum value for each online user, which needs to be verified each time you perform the operation. In order to prevent hackers from intercepting the checksum value for a second attack, the checksum value changes dynamically after each login or interval. Therefore, it is necessary to save the user's online status on the server side, and delete the online record when the user is offline or when there is no operation for a certain period of time, so that a new checksum value will be generated each time a new login is made.

problem

It is necessary to regularly clean up expired online users, because some users prefer to save passwords rather than use the logout feature.

Solution method

If you regularly clean up expired online users, you can think of using some periodic scheduled tasks. Scheduled tasks can be set in the operating system or in web application, but what you want to do today is to use periodic tasks directly with MySQL.

Process

The periodic task in MySQL is Event Scheduler. See http://dev.mysql.com/doc/refman/5.6/en/events.html. On the right side of the page, you can select the version of your MySQL to view the corresponding Event Scheduler manual. We use MySQL 5.5.

Let's take a look at its overview:

"Conceptually, this is similar to the idea of the Unix crontab (also known as a" cron job ") or the Windows Task Scheduler."

As you can see, it is similar to cron job, so we can expect that it should also enable a list, and then add tasks to the list, and then mysql will start a thread dedicated to maintaining the list and execute qualified tasks on the list at the appropriate time.

With a general understanding of the concept of Event Scheduler, we move on to the next step, Event Scheduler Configuration.

The three states of Event Scheduler, ON/OFF/DISABLED, are introduced in Event Scheduler Configuration, and see the Event Scheduler Configuration page for what they mean and how to switch.

Now, let's use the show processlist command to see what the Event Scheduler is in the activity.

To execute or output:

We are going to enable Event Scheduler now, enter the command

Alternatively, you can set event_scheduler=1 in the server configuration file (my.cnf, or my.ini on Windows systems), and then restart the server.

Now let's look at the status of Event Scheduler:

Output:

You can see that it has been activated.

To test, let's first create an onlineUser table and insert data

The next step is to set our scheduled tasks:

CREATE EVENT cleanOvertimeOnlineUser ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 minute DO delete from test.onlineUser where lastVisit < date_sub (now (), interval 24 hour)

To execute or output:

We'll wait five minutes to see the results.

You can see that the record at 18:55:05 on 2013-04-14 has been deleted.

Other:

When Event Scheduler executes a statement that returns a value, such as select, it does not return content to console or to memory. In this way, the select statement does not seem to be useful in Event Scheduler, but it is not. It can be used as the raw material for insert statements. For example:

Delimiter | CREATE EVENT e_daily ON SCHEDULE EVERY 1 DAY COMMENT 'Saves total number of sessions then clears the table each day' DO BEGIN INSERT INTO site_activity.totals (time, total) SELECT CURRENT_TIMESTAMP, COUNT (*) FROM site_activity.sessions; DELETE FROM site_activity.sessions; END | delimiter

Using statements like this can automatically update some statistics of the website in the database layer without having to write them into web application. It's also very convenient.

The above is all the contents of the article "how to use MySQL cycle tasks to regularly clean up the list of online users". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.