MySQL Performance Schema

The MySQL Performance Schema is an extremely valuable feature in MySQL 5.6 for finding queries which have a performance impact on the database. However, the performance schema is not enabled by default. The performance schema can be enabled through several different methods. Possible reasons why enabling the performance schema is a bad thing includes performance impact on the server. Percona has a good write up on the performance impact of enabling the Performance Schema. Therefore be careful when enabling the performance schema in a production environment as it can add a significant amount of overhead.

Setting the performance_schema directives in the mn.cnf file will ensure that the performance_schema is enabled at startup. One can also enable the performance_schema by running SQL queries after MySQL is up and running. This can be useful if you want to disable and enable the performance_schema to troubleshoot performance issues then turn them back off without having to restart MySQL.

vim /etc/my.cnf
performance_schema_consumer_events_statements_history_long=ON
performance_schema_consumer_events_statements_history=ON
performance_schema_consumer_events_stages_current=ON
performance_schema_consumer_events_stages_history=ON
performance_schema_consumer_events_stages_history_long=ON
performance_schema_consumer_events_waits_current=ON
performance_schema_consumer_events_waits_history=ON
performance_schema_consumer_events_waits_history_long=ON

performance_schema=ON

performance_schema_instrument='%=ON'

Consumers are setup in the setup_consumers table this specified where the data will go.

select * from performance_schema.setup_consumers;

MySQL Workbench_2015-08-11_07-12-25

View which objects have the performance schema setup on them.
The screen shot below shows that performance stats will be collected for all databases except for mysql, Performance_schema and information_schema.
perf_schema_setup_objects

The setup_actors table determines who to monitor, you can filter by host, user or role.
perf_schema_who_to_monitor

To view the current instruments which are enabled run the following query.

select * from setup_instruments

Instruments can be enabled by running an UPDATE statement on the table.

update setup_instruments
set enabled = 'yes', timed = 'yes'
where name = 'statement/sql/lock_tables'

MySQLinstruments

After the performance_schema has been configured you will be able to view the sys tables for statistics collected on queries.

select * 
from sys.`x$statements_with_full_table_scans`

Here it is important to note the difference between the average rows sent and the average rows examined. This means that the query optimizer is examining many rows but only sending a a small number. If there is a large difference between the two. This may indicate that their is a need for an index.

MySQL Workbench_2015-08-11_08-06-52

Additional performance tables can be viewed which present statistics on number of temp tables created.

select * 
from sys.`x$statements_with_temp_tables`
select * 
from sys.`x$statement_analysis`

Reference: For more in depth informaiton on the the performance schema and A Visual Guide to the MySQL Performance Schema

Profiling MySQL 5.6 InnoDB Fulltext index Performance

This test was performed on Centos 6.6 running MySQL 5.6.23.
I have a MySQL table which is populated by syslog messages via syslog-ng. I discussed how to configure syslog-ng to persist logs to a MySQL database in one of my earlier posts. I wanted to use this post to review some of the performance gains you will see by using a fulltext index on TEXT column in MySQL.

The syslog messages table layout looks like the following:

CREATE TABLE `logs` (
  `host` varchar(32) DEFAULT NULL,
  `facility` varchar(10) DEFAULT NULL,
  `priority` varchar(10) DEFAULT NULL,
  `level` varchar(10) DEFAULT NULL,
  `tag` varchar(10) DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  `program` varchar(15) DEFAULT NULL,
  `msg` text,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`seq`),
  KEY `date_host` (`datetime`,`host`),
  KEY `idx_msg` (`msg`(255)),
  FULLTEXT KEY `fulltext_msg` (`msg`)
) ENGINE=InnoDB AUTO_INCREMENT=56252 DEFAULT CHARSET=utf8

Total table record count: 56,274

Sample SYSLOG data in table
syslog_data

Test 1
For my first test a perform a query using the LIKE operator.
The query completes fairly quickly in 0.00057450 seconds. This is because the MySQL query optimizer is able to use the index.

SET profiling = 1;
select * from logs
where msg like '[e8725a%';
SHOW PROFILES;

I then performed the same test using the FULL TEX index.
This query finished slower in 0.00090200 seconds. Both the LIKE query and FULL TEXT index query examined 8 rows.

SET profiling = 1;
SELECT * FROM logs
WHERE MATCH (msg) AGAINST ('[e8725a');
SHOW PROFILES;

Test 2
In my second test I performed a another LIKE query using a wildcard on both ends of the string. This resulted in the query examining 56,274 rows and returning 25 in 0.15842475 seconds. When using a wildcard on both ends of the string the query optimizer is unable to use the index and must do a full index scan.

SET profiling = 1;
select * from logs
where msg like '%/PolicyKit1%';
SHOW PROFILES;

When doing a FULL TEXT query the optimizer examined 25 rows and returned 25 row in 0.00083450 seconds. Resulting in a significantly faster query.

SET profiling = 1;
SELECT * FROM logs
WHERE MATCH (msg) AGAINST ('/PolicyKit1');
SHOW PROFILES;

In conclusion, FULL TEXT search can significantly increase the performance for queries run against large TEXT fields.

Reference: https://dev.mysql.com/doc/refman/5.6/en/fulltext-natural-language.html

Rebuild MySQL Master Master Replication after ERROR: Got fatal error 1236 from master when reading data from binary log

Rebuild MySQL Master Master Replication after receiving the error below. This procedure can also be used to setup Mysql Master / Master replication.

Possible error in the mysql error log:

Got fatal error 1236 from master when reading data from binary log: 
'Found old binary log without GTIDs while looking for the oldest 
binary log that contains any GTID that is not in the given 
gtid set', Error_code: 1236

This post is about the following configuration. A pair of MySQL servers running CENTOS 6.5 and MySQL 5.6.
The MySQL servers are running salves to one another Multi-Master. The SQL clients write to a Virtual IP which is configured to float between the MASTER and SLAVE in the event of a failover.
Reference Architecture:
MYSQLSERVER01 – Primary Master
MYSQLSERVER02 – Backup Master
Keepalived – Virtual IP

STEP 1: ON MYSQLSERVER02
Log into MySQL Workbench and execute the following commands

STOP SLAVE;
RESET SLAVE;
RESET MASTER;

STEP 2: ON MYSQLSERVER01
Log into MySQL Workbench and execute the following commands

STOP SLAVE;
RESET SLAVE;
RESET MASTER;

STEP 3: ON MYSQLSERVER01 take a backup with mysqldump from the master:
SSH into the server and execute the following

mysqldump --all-databases --single-transaction --triggers --routines --events --user=root -p > /tmp/dump.sql

STEP 4: ON MYSQLSERVER01 transfer the mysqldump backup file form MYSQLSERVER01 to MYSQLSERVER02
SSH into the server and execute the following

scp /tmp/dump.sql root@MYSQLSERVER02:/tmp/dump.sql

STEP 5: ON MYSQLSERVER01 load the mysqldump file
SSH into the server and execute the following

mysql -u root -p  

STEP 6: ON MYSQLSERVER02

CHANGE MASTER TO MASTER_HOST='MYSQLSERVER01.lab.net', MASTER_USER='repl', MASTER_PASSWORD='replPassword', MASTER_AUTO_POSITION = 1;

STEP 7. ON MYSQLSERVER02
Verify the Slave_IO_Running and Slave_SQL_Running both have a status of YES

SHOW SLAVE STATUS;
start slave;
SHOW SLAVE STATUS;

STEP 8. ON MYSQLSERVER01
Log into MySQL Workbench and execute the following commands

CHANGE MASTER TO MASTER_HOST='MYSQLSERVER02.lab.net', MASTER_USER='repl', MASTER_PASSWORD='replPassword', MASTER_AUTO_POSITION = 1;

STEP 9. ON MYSQLSERVER01 start the slave and check the status of the slave operations
Log into MySQL Workbench and execute the following commands
Verify the Slave_IO_Running and Slave_SQL_Running both have a status of YES

SHOW SLAVE STATUS;
start slave;
SHOW SLAVE STATUS;

STEP 10. Additional testing can be done to confirm that the bi directional replication is working by inserting a record into the testdb.
Log into MySQL Workbench and execute the following commands
Perform insert test on MYSQLSERVER01, verify that the record has replicated to MYSQLSERVER02.

Logging syslog messages to mysql using syslog-ng

The guide was built using CentOS 6.5, MySQL 5.6 Community Edition and Syslog-Ng. This guide assumes you already have MySQL up and running.

STEP 1. First we must download and install Extra Packages for Enterprise Linux (epel) repository for CentOS 6.

wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
sudo rpm -Uvh epel-release-6*.rpm

STEP 2. Install the following packages required for syslog-ng and mysql log destination.

yum install syslog-ng
yum install syslog-ng-libdbi
yum install libdbi-drivers
yum install libdbi-devel
yum install libdbi-dbd-mysql
yum install syslog-ng-libdbi

STEP 3. Create the following table in the syslog database in MySQL.

Create Table: CREATE TABLE `logs` (
  `host` varchar(32) DEFAULT NULL,
  `facility` varchar(10) DEFAULT NULL,
  `priority` varchar(10) DEFAULT NULL,
  `level` varchar(10) DEFAULT NULL,
  `tag` varchar(10) DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  `program` varchar(15) DEFAULT NULL,
  `msg` text,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`seq`),
  KEY `date_host` (`datetime`,`host`)
) ENGINE=InnoDB AUTO_INCREMENT=14120 DEFAULT CHARSET=utf8

STEP 4. Create a MySQL user which has DELETE, INSERT, SELECT, UPDATE on the syslog database / schema.
Create user and set password
MySQL user1
Give the user “syslog” access to write to the database.
MySQL user2

STEP 5. Begin editing the syslog-ng.conf file

vim /etc/syslog-ng/syslog-ng.conf

Sample syslog-ng configuration file to save logs to a MySQL destination.

options {
flush_lines (0);
time_reopen (10);
log_fifo_size (1000);
long_hostnames (off);
use_dns (yes);
use_fqdn (yes);
create_dirs (yes);
keep_hostname (yes);
};

# syslog-ng log source
source s_net { udp(ip("172.16.1.20") port(514)); };
perm(0644) dir_perm(0700) create_dirs(yes)); };


source s_sys {
file ("/proc/kmsg" program_override("kernel: "));
unix-stream ("/dev/log");
internal();
};

destination d_file { file("/var/log/messagestest"); };
perm(0644) dir_perm(0700) create_dirs(yes)); };
destination d_cons { file("/dev/console"); };
destination d_mesg { file("/var/log/messages"); };
destination d_auth { file("/var/log/secure"); };
destination d_mail { file("/var/log/maillog" flush_lines(10)); };
destination d_spol { file("/var/log/spooler"); };
destination d_boot { file("/var/log/boot.log"); };
destination d_cron { file("/var/log/cron"); };
destination d_kern { file("/var/log/kern"); };
destination d_mlal { usertty("*"); };

# MySQL define destination
destination d_mysql {
sql(
type(mysql)
username("syslog")
password("Pass123!")
database("syslog")
host("172.16.1.20")
table("logs")
columns("host", "facility", "priority", "level", "tag", "datetime", "program", "msg")
values("$HOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$TAG","$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC","$PROGRAM", "$MSG")
indexes("datetime", "host")
);
};


filter f_kernel { facility(kern); };
filter f_default { level(info..emerg) and
not (facility(mail)
or facility(authpriv)
or facility(cron)); };
filter f_auth { facility(authpriv); };
filter f_mail { facility(mail); };
filter f_emergency { level(emerg); };
filter f_news { facility(uucp) or
(facility(news)
and level(crit..emerg)); };
filter f_boot { facility(local7); };
filter f_cron { facility(cron); };

# MySQL log to destination
log {source(s_net); destination(d_file); destination(d_mysql);};

# map source to destination fields.
log { source(s_sys); filter(f_kernel); destination(d_kern); };
log { source(s_sys); filter(f_default); destination(d_mesg); };
log { source(s_sys); filter(f_auth); destination(d_auth); };
log { source(s_sys); filter(f_mail); destination(d_mail); };
log { source(s_sys); filter(f_emergency); destination(d_mlal); };
log { source(s_sys); filter(f_news); destination(d_spol); };
log { source(s_sys); filter(f_boot); destination(d_boot); };
log { source(s_sys); filter(f_cron); destination(d_cron); };

STEP 6. Restart syslog-ng

service syslog-ng start

STEP 7. Log into MySQL and verify that logs are being saved to MySQL.

SELECT * 
FROM syslog.logs
order by datetime desc;

After completing these steps you should see the logs being saved to MySQL.
mysql-syslog-ng

How to setup Active Directory Authentication in MySQL running on Linux

This document was written using Microsoft Windows Active Directory 2012, Mysql 5.6 and CentOS 6.5. MySQL is running on CentOS 6.5. This document describes how to map an Active Directory Group to a MySQL User and authenticate against Active Directory. The completion of these steps allows Active Directory groups to be utilized within MySQL.

In this demonstration the following groups will be referenced.

mySQL_AD – This is the MySQL user which will be mapped/associated to the Active Directory Group.

AD_mysql_users – This is the Active Directory user which will be mapped to mySQL_AD.

STEP 1. Prior to getting started you need the following pam module installed.

/usr/lib64/mysql/plugin/authentication_pam.so

STEP 2. After installing the module update the my.cnf file, my.cnf is normally located in /etc/my.cnf

[mysqld]
plugin-load=authentication_pam.so

STEP 3. Restart mysql and verify that the PAM modules is installed.

service mysql restart
mysql -u root
select * from information_schema.PLUGINS\G

The following output should be returned from the PLUGIN query

*************************** 47. row ***************************
           PLUGIN_NAME: authentication_pam
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: AUTHENTICATION
   PLUGIN_TYPE_VERSION: 1.0
        PLUGIN_LIBRARY: authentication_pam.so
PLUGIN_LIBRARY_VERSION: 1.4
         PLUGIN_AUTHOR: Georgi Kodinov
    PLUGIN_DESCRIPTION: PAM authentication plugin
        PLUGIN_LICENSE: PROPRIETARY
           LOAD_OPTION: ON
47 rows in set (0.00 sec)

STEP 4.
Create the following file: vim /etc/pam.d/mysql with the content below. This assumes you already have the MySQL Linux system joined to the Microsoft Active Directory Domain. The pam_winbind.so is a PAM module that can authenticate users against the local domain by talking to the Winbind daemon.

auth   required    pam_winbind.so
account required    pam_winbind.so

STEP 5. Create a MySQL user with a password. The password is not important because it will never be used.
mySQL_AD is the MySQL user which will be mapped to the Active Directory group.

CREATE USER 'mySQL_AD'@'%' IDENTIFIED BY 'SomePassw0rd';

STEP 6. Run the CREATE USER statement to add the MySQL user to Active Directory mapping. In this example the Active Directory group AD_mysql_users is being mapped to the MySQL user: mySQL_AD
The “mysql” in front of “AD_mysql_users=mySQL_AD” of should match the name of pam.d file e.g. (/etc/pamd./mysql)
AD_mysql_users=mySQL_AD

CREATE USER ''@''
IDENTIFIED WITH authentication_pam
AS 'mysql, AD_mysql_users=mySQL_AD';

STEP 7. Grant mySQL_AD PROXY access. This gives users in Active Directory the ability to impersonate the user: mySQL_AD. To run this command you must be logged in as MySQL root: (e.g. mysql -u root -p). Even if you are logged in with DBA access this step will not work, must be logged in under MySQL root user. I tired logging into MySQL with a user which had equivalent privileges to MySQL root and the GRANT failed. Only when I logged in with MySQL root did the GRANT succeed.

mysql -u root
GRANT PROXY ON 'mySQL_AD'@'%' TO ''@'';

STEP 8.
Do a SELECT on the table and see which MySQL users are mapped to Active Directory Groups

SELECT * FROM mysql.user;

Fig 1.
MySQL Workbench_2015-03-17_19-46-36

STEP 9.
Set the appropriate user access on the MySQL server for the MySQL user mySQL_AD. For example, add the appropriate permissions for the user to do a select on a MySQL database. Remember that mySQL_AD is the MySQL user which we created earlier and is mapped to the Active Directory Group AD_mysql_users.

STEP 10. Flush security privileges, this causes the user settings to become active

flush privileges;

You can then perform a test connection.
You may receive the message below when performing a test connection.

[root@MYSQL01 log]# mysql -u testuser -p
Enter password:
ERROR 2059 (HY000): Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled

Set the flag to allow cleartext.

[root@MYSQL01 log]# export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1

If you are connecting from MySQL workbench edit your connection, select “Advanced” then “Enable Clear text Authentication Plugin”. Log into MySQL using your Active Directory Username / Password. You can verify which AD user and MySQL user you are logged in as. In Fig 2 I am authenticated as user testuser mapped to the MySQL user mySQL_AD. Note if you enable clear text authentication plugin it is recommended that you connect to MySQL using SSL via workbench.

SELECT USER(),CURRENT_USER();

Fig 2
MySQL Workbench_2015-03-18_19-57-35

The configuration below were used in support of the above configurations.

Additional configuration information in /etc/samba/smb.conf

[global]

        workgroup = LAB
        server string = Samba Server Version %v
        realm   =       LAB.NET
        netbios name = MYSQL01
        encrypt passwords = yes
        password server = dc01.lab.net
        #idmap config * : backend = rid
        #idmap config * : range = 10000-20000
        idmap config * : range = 16777216-33554431
        winbind use default domain = Yes
        winbind enum users = Yes
        winbind enum groups = Yes
        winbind nested groups = Yes
        winbind separator = +
        winbind refresh tickets = yes
        winbind offline logon = true
        template shell = /bin/bash
        template homedir = /home/%D/%U
        preferred master = no
        dns proxy = no
        wins server = dc01.lab.net
        wins proxy = no
        inherit acls = Yes
        map acl inherit = Yes
        acl group control = yes
        security = ads
        passdb backend = tdbsam

Additional configuration information in /etc/krb5.conf

[logging]
 default = FILE:/var/log/krb5libs.log
 kdc = FILE:/var/log/krb5kdc.log
 admin_server = FILE:/var/log/kadmind.log

[libdefaults]
 default_realm = LAB.NET
 dns_lookup_realm = true
 dns_lookup_kdc = true
 ticket_lifetime = 24h
 renew_lifetime = 7d
 forwardable = true
 proxiable = true

[realms]
 LAB.NET = {
  default_domain = LAB.NET
  kdc = DC01.LAB.NET
  admin_server = DC01.LAB.NET
 }

[domain_realm]
 .lab.net = LAB.NET
 lab.net = LAB.NET
 lab = LAB.NET

[appdefaults]
        pam = {
        ticket_lifetime         = 1d
        renew_lifetime          = 1d
        forwardable             = true
        proxiable               = true
        retain_after_close      = false
        minimum_uid             = 0
        debug                   = true
        }

Additional configuration information in /etc/nsswitch.conf

passwd:     files sss ldap winbind
shadow:     files sss ldap winbind
group:      files sss ldap winbind

Additional configuration information in /etc/pam.d/system-auth

auth        required      pam_env.so
auth        sufficient    pam_fprintd.so
auth        sufficient    pam_unix.so nullok try_first_pass
auth        sufficient    pam_krb5.so use_first_pass
auth        sufficient    pam_winbind.so cached_login use_first_pass
auth        requisite     pam_succeed_if.so uid >= 500 quiet
auth        required      pam_deny.so

account     required      pam_unix.so
account     sufficient    pam_localuser.so
account     sufficient    pam_succeed_if.so uid < 500 quiet
account     [default=bad success=ok user_unknown=ignore] pam_krb5.so
account     [default=bad success=ok user_unknown=ignore] pam_winbind.so cached_login
account     required      pam_permit.so

password    requisite     pam_cracklib.so try_first_pass retry=3 type=
password    sufficient    pam_unix.so sha512 shadow nullok try_first_pass use_authtok
password    sufficient    pam_krb5.so use_authtok
password    sufficient    pam_winbind.so cached_login use_authtok
password    required      pam_deny.so

session     optional      pam_keyinit.so revoke
session     required      pam_limits.so
session     optional      pam_mkhomedir.so skel=/etc/skel umask=0027
session     [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid
session     required      pam_unix.so
session     optional      pam_krb5.so

Reference : http://dev.mysql.com/doc/refman/5.6/en/proxy-users.html

MySQL and NUMA

I recently discussed NUMA in my Couchbase NUMA post. All the same principles apply with MySQL and NUMA.
NUMA imbalances in MySQL can cause extreme swapping. This configuration was performed on Mysql 5.6 community edition and CentOS 6.5.

To begin install numactl if it is already not installed on your MySQL server.

yum install numactl

Issue the command below on your physical MySQL server.

numactl --hardware

As one can see the output of the “numactl –hardware” command shows the imbalance between NUMA nodes on this particular server. Node 0 has 11,565MB free and Node 1 has 26,192MB free.

available: 2 nodes (0-1)
node 0 cpus: 0 2 4 6 8 10 12 14 16 18 20 22
node 0 size: 32722 MB
node 0 free: 11565 MB
node 1 cpus: 1 3 5 7 9 11 13 15 17 19 21 23
node 1 size: 32768 MB
node 1 free: 26192 MB
node distances:
node   0   1
  0:  10  20
  1:  20  10

The solution is to add a script to the /etc/mysql.cnf file.
This causes memory allocation to be performed in a round robin fashion across NUMA nodes.

STEP 1. Create a script file

vim /usr/sbin/mysqld_numactl

STEP 2. Add the following content to the file

# work around to startup mysqld using numactl
numactl=/usr/bin/numactl        # Adjust the path as needed
mysqld=/usr/sbin/mysqld         # Adjust the path as needed
# use exec to avoid having an extra shell around.
exec $numactl --interleave all $mysqld "$@"

STEP 3. Make script executable

chmod 750 /usr/sbin/mysqld_numactl

STEP 4. update the /etc/my.cnf

[mysqld_safe]
mysqld=mysqld_numactl

STEP 5. restart mysql to verify configuration change

service mysql restart

UPDATE:9/24/2015
With most major Linux distributions switching over to Systemd for init. I have updated this post. With Systemd in CentOS 7 it is much easier to use numactl to start up MySQL. I have provided a sample mysql.service file below.

vim /etc/systemd/system/mysql.service

Content of mysql.service

[Unit]
Description=MySQL Server
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

# Use the nice program to set the server's scheduling priority to the given value
nice=0

PIDFile=/opt/mysql/data/mysqld.pid
LimitCore=unlimited

# Execute pre and post scripts as root
PermissionsStartOnly=true

# Needed to create system tables

# Start main service
#ExecStart=
ExecStart=/usr/bin/numactl --interleave all /opt/mysql/bin/mysqld --defaults-file=/opt/mysql/my.cnf --datadir=/mnt/cp_san/mysql/data/data --socket=/tmp/mysql.sock

Environment="TZ=America/Chicago"
Environment="LD_PRELOAD=/opt/mysql/lib/libtcmalloc_minimal.so"

WorkingDirectory=/opt/mysql
# Use this to switch malloc implementation
#EnvironmentFile=-/etc/sysconfig/mysql

#Restart=on-failure
LimitNOFILE=infinity
LimitMEMLOCK=infinity

RestartPreventExitStatus=1

PrivateTmp=false

Reference: http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/

Creating scheduled events in MySQL

Unknown to many, the MySQL instance has the ability to schedule events. This feature can be useful for scheduling activities like maintenance and batch processes. To get started the user in MySQL creating the event requires the privileges to create events.

1. The following will GRANT user jsmith the ability to create events on all DBs in MySQL.

GRANT event privileges on all DBs to user jsmith

2. GRANT event privileges to all tables in DB myschema.

GRANT EVENT ON myschema.* TO jsmith;

3. Enter the following code to create a scheduled event which runs every 1 hour starting at 2014-01-07 13:00:00.

CREATE EVENT evntTruncate -- event name
ON SCHEDULE EVERY '1' HOUR -- run every 1 hour
STARTS '2014-01-07 13:00:00' -- should be in the future
DO
TRUNCATE TABLE City; -- SQL statement to execute

4. Run the following query to verify that the event has been scheduled.

SELECT * FROM information_schema.events;