Category Archives: MySQL

Unable to export database in MySQL Workbench – delayed-insert error

In case you’re using MySQL Workbench for creating dumps please keep in mind that version 6.3.5 has one annoying bug.
After update, the first time I tried do create dump, the complete process failed with the next error:

...
mysqldump: [ERROR] unknown variable 'delayed-insert=FALSE'
...

As I can see, this error occurs on various systems (Windows / MAC / Linux) and it was fixed once (6.1.x was also affected – LINK)

For now, you can only apply temporarily fix:

Open file (as root) wb_admin_export_options.py (located in /usr/lib/mysql-workbench/modules for Ubuntu/Mint/Debian distros)

Find the line

"delayed-insert":["Write INSERT DELAYED statements rather than..."

and insert # at the begin of this line (to comment it out)
Save file and restart Workbench. Check the Advanced option and note that this option is not available

Note: Windows users
For 32bit systems, wb_admin_export_options.py is located in C:\Program Files\MySQL\MySQL Workbench XXX\modules
For x64 systems, wb_admin_export_options.py is located in C:\Program Files (x86)\MySQL\MySQL Workbench XXX\modules)

The bug fix is easy to apply but it was announced months ago we’re still waiting for update… So shame…

More info:
http://stackoverflow.com/questions/20128223/export-data-from-mysql-workbench-6-0
https://bugs.mysql.com/bug.php?id=72312

MyDumper – CentOS HowTo

Mydumper – MySQL backup tool created by Domas Mituzas and later supported by several other devs.

The main benefits are multi-threaded and fast backups with almost no locking (if not using non innodb tables), built-in compression, separate files for each table, making it easy to restore single tables or schema. It also has support to hard link files which can reduce the space needed for history of backups. Much faster than mysqldump. The main benefit for separate files is the ability to create backups in multiple threads (the same works for restoring process)

In short – Mydumper is how MySQL DBA and support engineer would imagine mysqldump.

To install mydumper follow the next steps

Install necessary devel libs and cmake

yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel cmake

Download mydumper – https://launchpad.net/mydumper (or directly here https://launchpad.net/mydumper/0.6/0.6.2/+download/mydumper-0.6.2.tar.gz)

Extract the tar.gz archive with

tar -xvzf mydumper-0.6.2.tar.gz
cd mydumper-0.6.2
cmake .
make

Creating backup

mydumper -u USER -p PASSWORD -o /home/DESTINATION_DIR/DATABASE/ -B DATABASE

Note: My advice is to create separate dir for every database.

Restore from backup

myloader -u USER -p PASSWORD -B DATABASE -d /home/SOURCE_DIR/DATABASE/

How to clone MySQL database

Here is a way to create a duplicate of one database, with all its tables and their data

Dump your source database into sql file

# mysqldump -uroot -p production -r production.sql

If you need only schema (database with empty tables)

# mysqldump -uroot -p production -r production.sql --no-data

Open up a MySQL shell and login as root

# mysql -uroot -p

Create a new database and populate it with the dumped data

CREATE DATABASE production_copy;
USE production_copy;
SOURCE production.sql;

Now if you like, you can create a new user and give it permissions to the new database

CREATE USER new_user IDENTIFIED BY 'some_password';
GRANT ALL ON production_copy.* TO 'new_user'@'localhost' IDENTIFIED BY 'some_password';
FLUSH PRIVILEGES;

Note: this procedure works on Windows and Linux

Slow InnoDB insert/update

If you’re migrating from MyISAM to InnoDB or you’re using MySQL 5.5.x or newer (InnoDB default engine) you’ll probably be disappointed with INSERT/UPDATE queries (with InnoDB tables). InnoDB is a transaction-safe, ACID compliant MySQL storage engine and with default settings, the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. This can be very slow (but very safe – every transaction is 100% written to the disk).

Since MyISAM is not an option, we need to tune up our server so it can be used with InnoDB correctly. According to MySQL site, the next couple things should be considered:

  • Use OPTIMIZE TABLE statement to reorganize the table and compact any wasted space. Of course this operation won’t help if your database is empty
  • Use AUTO_INCREMENT column as the primary key
  • If you’re storing variable-length strings or if the column may contain NULL values, use the VARCHAR data type instead of CHAR (smaller tables fit better in the buffer pool and reduce disk I/O)
  • Since InnoDB must flush the log to disk at each transaction commit (if that transaction made modifications to the database), attach several queries into a single transaction to reduce the number of flush operations
  • In case you’re not building a finance application which can’t afford data loss if a crash occurs, you can set the parameter innodb_flush_log_at_trx_commit parameter to 0. In this case, InnoDB tries to flush the log once per second and not after every transaction (default setting is 1 which mean flush the log after every transaction).
  • To reduce the amount of disk I/O used by queries to access InnoDB tables, you can increase the innodb_buffer_pool_size.
  • Big disk-bound operations are always expensive. Use DROP TABLE and CREATE TABLE to empty a table, not DELETE FROM….Also TRUNCATE TABLE is much faster then DELETE * FROM…
  • innodb_flush_method parameter can also help but you must test yourself to see the right combination for your hardware and your database (possible values: fdatasync, O_DSYNC, O_DIRECT).
  • Make your log files big, even as big as the buffer pool and make the log buffer quite large as well
  • Disable autocommit during import operation (surround it with SET autocommit and COMMIT statements)
    SET autocommit=0;
     SQL queries
    COMMIT;
  • Temporarily turning off the uniqueness checks during the import session will help.
    SET unique_checks=0;
     SQL queries
    SET unique_checks=1;
  • Turn off foreign key checks during imports.
    SET foreign_key_checks=0;
     SQL queries
    SET foreign_key_checks=1;
  • If you often have repeating queries for tables that are not updated frequently, enable the query cache with
    query_cache_type = 1
    query_cache_size = 10M
  • Use the multiple-row INSERT syntax to reduce communication overhead between the client and the server if you need to insert many rows:
    INSERT INTO tbl VALUES (1,2), (5,5), ...;

The list above is not the final one. Please check the next link for more details about those parameters. Link
In my case, I won’t change a lot of parameters. The only parameter which I will change is the innodb_flush_log_at_trx_commit = 1 (default value is 1).

Before and after performance will be tested with Sysbench (Link). Since reading is not problem right now, I’ll stick with the write operations.

R/W test

sysbench --num-threads=16 --max-requests=10000 --test=oltp --oltp-table-size=500000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-test-mode=complex --mysql-user=TEST_USER --mysql-password=TEST_PASSWORD run

The result

OLTP test statistics:
    queries performed:
        read:                            146216
        write:                           52220
        other:                           20446
        total:                           218882
    transactions:                        10002  (181.90 per sec.)
    deadlocks:                           442    (8.04 per sec.)
    read/write requests:                 198436 (3608.90 per sec.)
    other operations:                    20446  (371.85 per sec.)
 
Test execution summary:
    total time:                          54.9852s
    total number of events:              10002
    total time taken by event execution: 879.1034
    per-request statistics:
         min:                                 33.38ms
         avg:                                 87.89ms
         max:                                480.77ms
         approx.  95 percentile:             135.31ms
 
Threads fairness:
    events (avg/stddev):           625.1250/2.29
    execution time (avg/stddev):   54.9440/0.03

Total time: 54.98s

Now, when I change innodb_flush_log_at_trx_commit to 0 (default value was 1), I get:

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (780.35 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (14826.69 per sec.)
    other operations:                    20000  (1560.70 per sec.)
 
Test execution summary:
    total time:                          12.8147s
    total number of events:              10000
    total time taken by event execution: 204.8297
    per-request statistics:
         min:                                  1.19ms
         avg:                                 20.48ms
         max:                               1669.69ms
         approx.  95 percentile:              44.50ms
 
Threads fairness:
    events (avg/stddev):           625.0000/19.56
    execution time (avg/stddev):   12.8019/0.00

Total time: 12.81s

As you can see, changing innodb_flush_log_at_trx_commit from 1 to 0 increases the write speed but we can lose data in some cases (hardware or power failures, etc). To avoid this problem, use battery backups, UPS, RAID, …

Extra Packages for Enterprise Linux – EPEL HowTo

EPEL (Extra Packages for Enterprise Linux) is a volunteer-based community effort from the Fedora project to create a repository of high-quality add-on packages that complement the Fedora-based Red Hat Enterprise Linux (RHEL) and its compatible spinoffs, such as CentOS and Scientific Linux.

Adding EPEL repo is very easy:

wget http://ftp.heanet.ie/pub/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpm
rpm -Uvh epel-release-6-8.noarch.rpm

From unknown reason for me, CentOS 6.x goes without php-mcrypt package and it is impossible to install this rpm from base repos. Some apps will complain about this and one of the solutions is to install this rpm from EPEL repo… After you added EPEL repo, type:

yum install php-mcrypt

How to disable foreign key checks – MySQL

From time to time you’ll need to delete something from your database and when you try to do it, the MySQL will complain on foreign keys (beside the fact that other table is empty)

This can be solved with disabled foreign keys

SET foreign_key_checks = 0;
DELETE FROM users WHERE id > 45;
SET foreign_key_checks = 1;

You probably noticed that after I deleted those records, I enabled foreign key checks again which you should do also.

MySQL DATE_FORMAT() letter representations

Here is a quick chart with MySQL DATE_FORMAT letter representation so you can format date how ever you want…

Specifier	Description
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week; used with %X
%v	Week (01..53), where Monday is the first day of the week; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal “%” character
%x	x, for any “x” not listed above

For example

SELECT DATE_FORMAT(NOW(),’%m-%d-%Y’);

FreeRadius install howto (4) – populating tables

In the last article about FreeRadius (Here), I wrote about basic settings and now I’ll write something about inserting users into database (MySQL).

The FreeRadius database schema contains several tables:

nas

This table contains data about NASes (radius clients) and it is a “replacement” for clients.conf file. It is much easier to maintain the clients in the database than inside config file. If you want to use database for NAS list, skip the step in the last howto (the part about clients.conf). Also, in case you want to keep your NASes in the nas table, you’ll need to uncomment the readclients = yes inside sql.conf.

        # Set to 'yes' to read radius clients from the database ('nas' table)
        # Clients will ONLY be read on server startup.  For performance
        # and security reasons, finding clients via SQL queries CANNOT
        # be done "live" while the server is running.
        #
        readclients = yes

As you can see from the comment, you will need to restart radiusd process to allow/disallow specific NAS.

nas table schema is located inside raddb/sql/mysql/nas.sql

To add IP 192.168.0.15 inside nas table, exec next query:

INSERT INTO  nas VALUES (NULL ,  '192.168.0.15',  'myNAS',  'other', NULL ,  'mysecret', NULL , NULL ,  'RADIUS Client'
);

and you will have

mysql> select * from nas;
+----+--------------+-----------+-------+-------+----------+--------+-----------+---------------+
| id | nasname      | shortname | type  | ports | secret   | server | community | description   |
+----+--------------+-----------+-------+-------+----------+--------+-----------+---------------+
|  1 | 192.168.0.15 | myNAS     | other |  NULL | mysecret | NULL   | NULL      | RADIUS Client |
+----+--------------+-----------+-------+-------+----------+--------+-----------+---------------+
1 row in set (0.00 sec)

radacct

This table is used for accounting data. In case you want to collect traffic stats, you will need to uncomment sql inside accounting {} section in /usr/local/etc/raddb/sites-available/default. The same table can be used for simultaneous use checking which is faster than radutmp. All you need to do is to uncomment sql inside session {} section inside /usr/local/etc/raddb/sites-available/default and uncomment simul_count_query inside /usr/local/etc/raddb/sql/mysql/dialup.conf

radcheck

This table keeps the check attributes for users (User-Password, Cleartext-Password, Expiration, Simultaneous-Use, Auth-Type, …)

radreply

Is used for reply attributes for specific user. For example Framed-IP-Address, upload and download speed, etc…

radgroupcheck

This table keeps the check attributes for groups (which means, all users inside specific group will be checked against this attributes).

radgroupreply

The same like radreply but for groups. (all users in specific group will get the same speed, etc). Also, Framed-Pool attribute goes here.

radpostauth

This table is used for logging failed login attempts. To use this, you’ll need to uncomment sql inside postauth section (/usr/local/etc/raddb/sites-available/default.). Think twice before you enable this option because it can overload your server with constant inserts. Your customers will probably spend their money on wireless or wired routers so the logging attempts will come over and over.

radusergroup

This table keeps relation between username and specific group and group priority. In Freeradius 1.x this table was named “usergroup” so in case you have your own billing which is made for old schema, rename this table to usergroup

        # Table to keep group info
        usergroup_table = "radusergroup"

Examples

We will create a sample service with the next attributes:
– 512kbps download speed
– 128kbps upload speed
– we will use PPPoE – Point to Point Protocol Over Ethernet
– we will assign dynamic IP addresses to our clients from “internet” IP pool

INSERT INTO `radgroupreply` (`id` ,`groupname` ,`attribute` ,`op` ,`value` )
VALUES (NULL , 'testservice', 'Ascend-Xmit-Rate', ':=', '524288'), 
(NULL , 'testservice', 'Ascend-Data-Rate', ':=', '131072'), 
(NULL , 'testservice', 'Framed-Pool', ':=', 'internet');

As you can see the speed is converted to bps.

After you created the service, lets create a sample user (assigned with created service).

As I noticed above, check attributes should be placed inside radcheck table.

INSERT INTO `radcheck` (`id` ,`username` ,`attribute` ,`op` ,`value` )
VALUES (NULL , 'testuser', 'User-Password', ':=', 'testpassword'), 
(NULL , 'testuser', 'Simultaneous-Use', ':=', '1');

In this sample, the password is in plain text format which is not reccommended. Insted User-Password (which is alternative to Cleartext-Password for Mikrotik) better option is to use MD5-Password but keep in mind that you won’t be able to use CHAP.

INSERT INTO `radcheck` (`id` ,`username` ,`attribute` ,`op` ,`value` )
VALUES (NULL , 'testuser', 'MD5-Password', ':=', MD5( 'testpassword' ) ), 
(NULL , 'testuser', 'Simultaneous-Use', ':=', '1');

Then we need to assign this user with created service (group)

INSERT INTO `radusergroup` (`username` ,`groupname` ,`priority` )
VALUES ('testuser', 'testservice', '1');

After those inserts, lets test

[root@ns2 raddb]# radtest testuser testpassword 127.0.0.1 0 testing123
Sending Access-Request of id 228 to 127.0.0.1 port 1812
        User-Name = "testuser"
        User-Password = "testpassword"
        NAS-IP-Address = 192.168.0.10
        NAS-Port = 0
        Message-Authenticator = 0x00000000000000000000000000000000
rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=228, length=54
        Ascend-Xmit-Rate = 524288
        Ascend-Data-Rate = 131072
        Framed-Pool = "internet"
[root@ns2 raddb]#

As you can see, the username/password combination is valid and RADIUS server returned all attributes assigned with user’s group.

To suspend user’s account you can insert Auth-Type := Reject for user.

INSERT INTO `radcheck` (`id` ,`username` ,`attribute` ,`op` ,`value` )
VALUES (NULL , 'testuser', 'Auth-Type', ':=', 'Reject');

and we have

[root@ns2 raddb]# radtest testuser testpassword 127.0.0.1 0 testing123
.....
rad_recv: Access-Reject packet from host 127.0.0.1 port 1812, id=145, length=20

Another option for disabling users is assigning with specific group which has Auth-Type := Reject inside radgroupcheck

INSERT INTO `radgroupcheck` (`id` ,`groupname` ,`attribute` ,`op` ,`value` )
VALUES (NULL , 'suspended', 'Auth-Type', ':=', 'Reject');

Assigning with suspended group can be done with

UPDATE `radusergroup` 
SET `groupname` = 'suspended' 
WHERE `username` = 'testuser' 
AND `priority` = 1;

and we have

rad_recv: Access-Reject packet from host 127.0.0.1 port 1812, id=198, length=20

Also, keep in mind that routers will try to connect again and again so you will have a big problems in case you have thousands of users. Another option is to assign users with specific group which doesn’t have Auth-Type attribute. Instead rejecting you can assign internal IPs and redirect them to suspended page.

Many questions on FreeRadius mailing list are about Simultaneus-Use. Solution to this problem is very simple and it is very rude to ask this question again and again…

All you need to do is to insert Simultaneous-Use := 1 for specific user (radcheck table) or inside radgroupcheck if you want to limit all users inside specific group.

INSERT INTO `radgroupcheck` (`id` ,`groupname` ,`attribute` ,`op` ,`value` )
VALUES (NULL , 'testservice', 'Simultaneous-Use', ':=', '1');

In case you want to set Expiration attribute you can insert the date and the time inside radcheck table.

INSERT INTO `radcheck` (`id` ,`username` ,`attribute` ,`op` ,`value` )
VALUES (NULL , 'testuser', 'Expiration', '==', 'November 30 2011 00:00:00');

then we have

[root@ns2 raddb]# radtest testuser testpassword 127.0.0.1 0 testing123
Sending Access-Request of id 28 to 127.0.0.1 port 1812
        User-Name = "testuser"
        User-Password = "testpassword"
        NAS-IP-Address = 192.168.0.10
        NAS-Port = 0
        Message-Authenticator = 0x00000000000000000000000000000000
rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=28, length=60
        Ascend-Xmit-Rate = 524288
        Ascend-Data-Rate = 131072
        Framed-Pool = "internet"
        Session-Timeout = 670889

You can note Session-Timeout attribute which contains the time in seconds between this moment and the date inside Expiration field. According to this value, the NAS will auto disconnect user when this time expire (in our case 670889 seconds). In case you set the time which already passed (for example yesterdays date) the user will be rejected.

Please keep in mind that this date format works for Mikrotik. I didn’t have chance to test it with other NASes.

If you want to reconnect users at regular intervals (for example every 24 hours – 86400 seconds) you can insert Session-Timeout inside radreply table (because it isn’t check attribute).
To recconect every user inside specific group, add this attribute inside radgroupreply table.

To assign a static IP for specific user insert Framed-IP-Address attribute inside radreply table where Value will be that IP address. Operator should be :=.

Please keep in mind that all inserts inside those tables are visible to radius server right after insert. Only inserts inside nas table won’t be until the restart (service radiusd restart)

I hope this post will help you to set up your own RADIUS server. Also, keep in mind that this is just an example and all this can done in many other ways.

In case you find a spelling errors please contact me so I can fix them.

MySQL Performance – Howto – part 2 (write cache dependance)

I already wrote about Sysbench and MySQL tunning scripts. Those articles can be found HERE and HERE.

Now lets do some tests and lets see the dependance from write cache enabled/disabled option on your disks. Keep in mind that RH based distros will probably disable write cache (I tried Ubuntu on this server and the same thing happen – write cache was disabled after installation).

Continue reading MySQL Performance – Howto – part 2 (write cache dependance)