Playing with count(*) optimizer work

Article about bug report #68814 related to testing count(*) explain plan.
Our sales table huge enough to play with.

mysql> select count(*) from sales; 
+----------+ 
| count(*) | 
+----------+ 
| 2500003 | 
+----------+ 
1 row in set (0.56 sec)

First with regular count(*) without where clause:

mysql> explain select count(*) from salesG 
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: sales 
type: index 
possible_keys: NULL 
key: sales_cust_idx 
key_len: 4 
ref: NULL 
rows: 2489938 
Extra: Using index 1 row in set (0.00 sec) 

Estimated rows -> rows: 2489938 Then with {where sales_id > 0}:

mysql> explain select count(*) from sales where sales_id > 0G
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: sales 
type: range 
possible_keys: PRIMARY 
key: PRIMARY 
key_len: 4 
ref: NULL 
rows: 1244969 
Extra: Using where; Using index 1 row in set (0.00 sec)

Estimated rows -> rows: 1244969 -> so there is difference between query with {sales_id > 0} and with no clause.

Another one with {where sales_id > 1800000}:

mysql> explain select count(*) from sales where sales_id > 1800000G
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: sales 
type: range 
possible_keys: PRIMARY 
key: PRIMARY 
key_len: 4 
ref: NULL 
rows: 1244969 
Extra: Using where; Using index 
1 row in set (0.00 sec) 

Estimated rows -> rows: 1244969 So there is no difference between {sales_id > 1800000} and {sales_id > 0} (by mean of explain plan and estimated rows)

Another interesting thing:

-- 1 
mysql> explain select count(*) from sales where sales_id >0 or sales_id <0G
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: sales 
type: range 
possible_keys: PRIMARY 
key: PRIMARY 
key_len: 4 
ref: NULL 
rows: 1244970 
Extra: Using where; Using index 
1 row in set (0.00 sec)

Estimated rows -> rows: 1244969 + 1

-- 2 
mysql> explain select count(*) from sales where sales_id >0 or sales_id <=0G
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: sales 
type: index 
possible_keys: PRIMARY 
key: sales_cust_idx 
key_len: 4 
ref: NULL 
rows: 2489938 
Extra: Using where; Using index 
1 row in set (0.00 sec) 

Estimated rows: 2489938

mysqldiskusage – source code examination

As you know there is a great toolset named “MySQL Utilities”, which you can use for solving various administrative tasks. mysqldiskusage utility is for calculating MySQL Server’s disk usage and generating informative reports. Of course this project is open source and everybody could review the source code. A few words about how mysqldiskusage calculates database disk usage will be crucial for understanding algorithm. The source tree is: mysql-utilities-1.5.4/scripts/mysqldiskusage.py If you open this Python file you will see (line 169-175) :

# We do database disk usage by default. 
try: 
    diskusage.show_database_usage(servers[0], datadir, args, options) 
except UtilError: _, e, _ = sys.exc_info() 
    print("ERROR: %s" % e.errmsg) 
    sys.exit(1) 

By default it shows database disk usage and calling another function named show_database_usage from mysql-utilities-1.5.4/mysql/utilities/command/diskusage.py file. Now if we open up this diskusage.py file and search for show_database_usage function, you should see that in turn this function uses another function named _build_db_list. From _build_db_list it gets back all necessary information as in code stated clearly(line 550-562):

# Get list of databases with sizes and formatted when necessary columns, rows     
db_total = _build_db_list(server, res, dblist, datadir, fmt == "grid", have_read, verbosity, include_empty or do_all, is_remote) 
if not quiet: 
    print "# Database totals:" print_list(sys.stdout, fmt, columns, rows, no_headers) 
if not quiet: _print_size("nTotal database disk usage = ", db_total) 
    print 

Now we know that all calculations are happened in _build_db_list function. If you search and find this function(begins from line 360) you can see that, in fact mysqldiskusage is calculating database disk usage as follows:

It finds (data_length + index_length) from information_schema.tables per database manner

Then it sum ups (data_length + index_length) with misc_files variable data which is in fact returned by _get_db_dir_size function. But what is this misc_files? Logically misc_files must be .opt and .frm files. So misc_files must not be an “.MYD” , “.MYI”, “.IBD”, “general_log”, “slow_log”.

So in fact mysqldiskusage calculates database disk usage as -> (data_length + index_length)[size in bytes] + (.opt+.frm)[size in bytes]. First of all we must insist on not using information_schema for accurate disk usage calculation because of simple rule: “InnoDB preallocates pages(16Kib) for further table usage, but data_length column will not show these pages”

As proof of concept let’s create sample empty table:

mysql> create database test; 
Query OK, 
1 row affected (0,01 sec) 
mysql> use test; 
Database changed 
mysql> 
select data_length, index_length from information_schema.tables where table_schema='test' and table_name='t1'; 
+-------------+--------------+ 
| data_length | index_length | 
+-------------+--------------+ 
| 16384 | 0 | 
+-------------+--------------+ 
1 row in set (0,00 sec) 

If we consider that our exact table size is 16384 bytes, we are in wrong direction. In fact if we use OS commands we could see that the exact size of table is 98304 bytes:

[root@node1 ~]# ls -lt /var/lib/mysql/test/ 
total 208 
-rw-rw----. 1 mysql mysql 98304 Apr 18 11:44 t1.ibd 

So when we create an InnoDB table it is preallocates 6 pages(16Kib*6 = 98304) but only 1 page shown up from data_length column. Now let’s come back to our misc_files or exactly _get_db_dir_size() function. From source code we can see that there is no a check for “.IBD” files:

... 
for item in os.listdir(folder): 
    name, ext = os.path.splitext(item) 
    if ext.upper() not in (".MYD", ".MYI") and  name.upper() not in ('SLOW_LOG', 'GENERAL_LOG'): 
        itemfolder = os.path.join(folder, item) 
    ... 

Because of this calculation is wrong for databases as we see from output:

[root@node1 ~]# mysqldiskusage --server=root_pass -vvv 
# Source on localhost: ... connected. 
# Database totals: 
+---------------------+--------------+--------------+--------------+--------------+ | db_name | db_dir_size | data_size | misc_files | total | +---------------------+--------------+--------------+--------------+--------------+ | employees | 242.523.049 | 205.979.648 | 242.523.049 | 448.502.697 | 

As you see it sum ups data_size with misc_files and gets back total as 448.502.697 bytes. But in fact our employees database is exactly 242.523.049 bytes. And of course the: Total database disk usage = 450.940.391 bytes or 430,05 MB is wrong as well. For further exploration and how to patch source code see related BUG REPORT #76703.

MySQL-AutoXtrabackup command line tool for using Percona Xtrabackup

Want to introduce our MySQL-AutoXtraBackup command line tool, using Percona Xtrabackup in core. Looking for contributor 😉

Project Structure:

XtraBackup is powerfull and open-source hot online backup tool for MySQL from Percona.
This script is using XtraBackup for full and incremental backups, also for preparing and recovering taken backups.
Here is project tree:

* backup_dir — The main folder for storing backups.
* master_backup_script — Full and Incremental backup taker script.
* backup_prepare — Backup prepare and restore script.
* partial_recovery — Partial table recovery script.
* general_conf — All-in-one config file’s and config reader class folder.
* setup.py — Setup file.
* autoxtrabackup.py — Commandline Tool provider script.
* /etc/bck.conf — Config file will be created from general_conf/bck.conf

Now there is a config file located in ‘/etc/bck.conf’ where you should be able to change your settings and use.

Here you can watch Demo Usage Video.

Add Patch to MySQL source code in Linux

Due to reported BUG report(BUG 73365) i am testing full disk error conditions with MySQL 5.6.19-debug-log.

There were some interesting error messages in error log:

2014-07-23 08:09:59 7f44500f0700 InnoDB: Error: Write to file ./xxx/news.ibd failed at offset 218103808.
InnoDB: 1048576 bytes should have been written, only -1 were written.
InnoDB: Operating system error number 28.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 28 means 'No space left on device'.

InnoDB: 1048576 bytes should have been written, only -1 were written.
So what is “-1” here?

The answer from:

[24 Jul 16:02] David Bennett

The -1 value is an error return from the os_file_pwrite() function in storage/innobase/os/os0file.c. This only occurs on non-Windows platforms. On Windows the WriteFile functions _Out_opt_ lpNumberOfBytesWritten is used which will return 0 on error. A patch is attached which will correct the error message.

David Bennett attached an patch to my report.
There is no such terrible situation with patches.Patch is simply a text file which describe code difference and etc.
So content of attached patch file:

=== modified file 'storage/innobase/os/os0file.c'
--- storage/innobase/os/os0file.c       2014-02-06 10:25:06 +0000
+++ storage/innobase/os/os0file.c       2014-07-24 15:56:14 +0000
@@ -3045,6 +3045,8 @@

        if (!os_has_said_disk_full) {

+               if (ret == -1) ret++;
+
                ut_print_timestamp(stderr);

                fprintf(stderr,

It is clear that at line 3045 or nearby you must add if (ret == -1) ret++; line to os0file.c file which is in storage/innobase/os/.

Let’s try:

[root@localhost ~]# cd mysql-5.6.19/storage/innobase/os/
[root@localhost os]# ls
os0file.cc  os0proc.cc  os0sync.cc  os0thread.cc

Open this file with option to go to 3045’s line:

[root@localhost os]# nano +3045 os0file.cc
*
*
*
if (!os_has_said_disk_full) {
                // Added New Line 
                if (ret == -1) ret++;

                ut_print_timestamp(stderr);

                fprintf(stderr,
                        " InnoDB: Error: Write to file %s failed"
                        " at offset " UINT64PF ".n"
                        "InnoDB: %lu bytes should have been written,"
                        " only %ld were written.n"
                        "InnoDB: Operating system error number %lu.n"
                        "InnoDB: Check that your OS and file system"
                        " support files of this size.n"
                        "InnoDB: Check also that the disk is not full"
                        " or a disk quota exceeded.n",
                        name, offset, n, (lint) ret,
                        (ulint) errno);
                if (strerror(errno) != NULL) {
                        fprintf(stderr,
                                "InnoDB: Error number %d means '%s'.n",
                                errno, strerror(errno));
                }

                fprintf(stderr,
                        "InnoDB: Some operating system error numbers"
                        " are described atn"
                        "InnoDB: "
                        REFMAN "operating-system-error-codes.htmln");

                os_has_said_disk_full = TRUE;
        }

As you see, error messages is provided by this code lines and we added new line from patch.
Save this file and build/compile MySQL again. (See: Install MySQL from source with debugging option in Linux)

And again i create a full disk error condition but now the “-1” dissappears from error log, it is fixed:

InnoDB: 1048576 bytes should have been written, only 0 were written.
InnoDB: Operating system error number 28.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 28 means 'No space left on device'.
InnoDB: Some operating system error numbers are described at

Thank you.

Change binary log and relay log location to /home? SELinux issue

Recently in my slave server there was a full disk error because of the huge size of binary logs.Which is lead to critical error and BUG report which is still stays as open: http://bugs.mysql.com/bug.php?id=72437
So the issue was a partition problem which in partitioning stage haven’t been specified for “/” (root) filesystem. But in “/home” there were left enough space. The quick decision was to change binary and relay log location(path) to “/home”. But there was a great challenge to get real working slave again. Now we will explore all steps in our test virtual machine.
Test conditions for me: 1. CentOS 6.5 (x86_64) (both master and slave) 2. MySQL 5.6.19 GA (from official yum repo) (both master and slave)
Master’s my.cnf:

# BINARY LOGGING # 
server_id = 1 
log_bin = /var/lib/mysql/data/mysql-bin 
log_bin_index = /var/lib/mysql/data/mysql-bin 
expire_logs_days = 14 
sync_binlog = 1 
binlog_format = row 
gtid-mode = on 
enforce-gtid-consistency = true 
master-info-repository = TABLE 
relay-log-info-repository = TABLE 
slave-parallel-workers = 2 
binlog-checksum = CRC32 
master-verify-checksum = 1 
slave-sql-verify-checksum = 1 
binlog-rows-query-log_events = 1 
log_slave_updates = 1

Slave’s original my.cnf:

# BINARY LOGGING # 
server_id = 2 
log_bin = /var/lib/mysql/data/mysql-bin 
log_bin_index = /var/lib/mysql/data/mysql-bin 
expire_logs_days = 14 
sync_binlog = 1 
binlog_format = row 
relay_log = /var/lib/mysql/data/mysql-relay-bin 
log_slave_updates = 1 
read_only = 1 
gtid-mode = on 
enforce-gtid-consistency = true 
master-info-repository = TABLE 
relay-log-info-repository = TABLE 
#slave-parallel-workers = 2 
binlog-checksum = CRC32 
master-verify-checksum = 1 
slave-sql-verify-checksum = 1 
binlog-rows-query-log_events = 1

So we assume that we have already working master->slave topology. Let’s examine Slave Host for further explanation:

[root@linuxsrv2 home]# df -h 
Filesystem Size Used Avail Use% Mounted on 
/dev/mapper/vg_linuxsrv1-lv_root 48G 2,3G 43G 6% / 
tmpfs 246M 0 246M 0% /dev/shm 
/dev/sda1 485M 57M 403M 13% /boot 
/dev/mapper/new_lv_linuxsrv1-lv_home 7,4G 145M 6,9G 3% /home

So we have seperate free space on /home directory and now we want to change slave’s binary log and reloy log files into this directory. Step-by-step:

1. Create directory:

[root@linuxsrv2 ~]# mkdir /home/binlogs 
[root@linuxsrv2 ~]# ls -ld /home/binlogs/ 
drwxr-xr-x. 2 root root 4096 2014-06-13 15:39 /home/binlogs/

2. Change Slave’s my.cnf to reflect new location. So the new my.cnf of slave must be something like this:

# BINARY LOGGING # 
server_id = 2 
log_bin = /home/binlogs/mysql-bin 
log_bin_index = /home/binlogs/mysql-bin 
expire_logs_days = 14 
sync_binlog = 1 
binlog_format = row 
relay_log = /home/binlogs/mysql-relay-bin 
log_slave_updates = 1 
read_only = 1 
gtid-mode = on 
enforce-gtid-consistency = true 
master-info-repository = TABLE 
relay-log-info-repository = TABLE 
#slave-parallel-workers = 2 
binlog-checksum = CRC32 
master-verify-checksum = 1 
slave-sql-verify-checksum = 1 
binlog-rows-query-log_events = 1

3. Stop MySQL slave server:

[root@linuxsrv2 ~]# service mysqld stop 
Stopping mysqld: [ OK ]

4. Move all binary log and relay log files to new directory “/home/binlogs”:

[root@linuxsrv2 ~]# cd /var/lib/mysql/data/
[root@linuxsrv2 data]# ls 
mysql-bin.000001 mysql-bin.index mysql-relay-bin.000003 mysql-relay-bin.000004 mysql-relay-bin.index mysql-slow.log 
[root@linuxsrv2 data]# mv mysql-bin.* /home/binlogs/ 
[root@linuxsrv2 data]# ls mysql-relay-bin.000003 mysql-relay-bin.000004 mysql-relay-bin.index mysql-slow.log 
[root@linuxsrv2 data]# mv mysql-relay-bin.* /home/binlogs/ 
[root@linuxsrv2 data]# ls mysql-slow.log 
[root@linuxsrv2 data]# ls -l /home/binlogs/ 
total 20 
-rw-rw----. 1 mysql mysql 1320 2014-06-13 15:46 mysql-bin.000001 
-rw-rw----. 1 mysql mysql 37 2014-06-13 15:28 mysql-bin.index 
-rw-rw----. 1 mysql mysql 741 2014-06-13 15:31 mysql-relay-bin.000003 
-rw-rw----. 1 mysql mysql 471 2014-06-13 15:46 mysql-relay-bin.000004 
-rw-rw----. 1 mysql mysql 86 2014-06-13 15:31 mysql-relay-bin.index

5. Change owner of /home/binlogs to mysql:mysql:

[root@linuxsrv2 data]# cd /home 
[root@linuxsrv2 home]# ls binlogs lost+found my_changed.cnf my_original.cnf
[root@linuxsrv2 home]# chown -R mysql:mysql binlogs

6. Go to new directory edit *.index file of both binary logs and relay logs. Why? Open these files and you will see that, the old path is still there:

[root@linuxsrv2 binlogs]# cat mysql-bin.index
/var/lib/mysql/data/mysql-bin.000001 

[root@linuxsrv2 binlogs]# cat mysql-relay-bin.index
/var/lib/mysql/data/mysql-relay-bin.000003
/var/lib/mysql/data/mysql-relay-bin.000004 

So after editing it must be something like:

[root@linuxsrv2 binlogs]# cat mysql-bin.index 
/home/binlogs/mysql-bin.000001 

[root@linuxsrv2 binlogs]# cat mysql-relay-bin.index
/home/binlogs/mysql-relay-bin.000003 
/home/binlogs/mysql-relay-bin.000004

7. Now try to start MySQL:

[root@linuxsrv2 binlogs]# service mysqld start 
MySQL Daemon failed to start. 
Starting mysqld: [FAILED]

See error log:

140613 15:46:19 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 140613 16:01:50 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 
2014-06-13 16:02:15 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 
2014-06-13 16:02:16 12358 [Warning] Buffered warning: Performance schema disabled (reason: init failed). ^G/usr/sbin/mysqld: File '/home/binlogs/mysql-bin.index' not found (Errcode: 13 - Permission denied) 
2014-06-13 16:02:16 12358 [ERROR] Aborting 
2014-06-13 16:02:16 12358 [Note] Binlog end 
2014-06-13 16:02:16 12358 [Note] /usr/sbin/mysqld: Shutdown complete

But as you see we changed owner of this directory to mysql user, still error occured. SELinux come ups with it’s blocking functionalty here to prevent mysql to use other directories rather than, /var/lib/mysql. Simply you can disable SELinux at all, but it is not a best practice. First possible way is to run following SElinux related commands:

$ semanage fcontext -a -t mysqld_db_t “/home(/.*)?” 
$ restorecon -Rv /home

But if you are not comfortable with these commands, you can just do it automatically using audit2allow:

8. Install “policycoreutils-python” on Linux:

[root@linuxsrv2 ~]# yum install policycoreutils-python
[root@linuxsrv2 ~]# rpm -qa | grep policy*
selinux-policy-3.7.19-231.el6_5.3.noarch
policycoreutils-python-2.0.83-19.39.el6.x86_64 checkpolicy-2.0.22-1.el6.x86_64
policycoreutils-2.0.83-19.39.el6.x86_64
selinux-policy-targeted-3.7.19-231.el6_5.3.noarch 

9. Configuring SELinux:
##1##

[root@linuxsrv2 home]# audit2allow -w -a 
type=AVC msg=audit(1402659916.941:291): avc: denied { search } for pid=14356 comm="mysqld" name="/" dev=dm-2 ino=2 scontext=unconfined_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:file_t:s0 tclass=dir Was caused by: Missing type enforcement (TE) allow rule. You can use audit2allow to generate a loadable module to allow this access. 


[root@linuxsrv2 home]# audit2allow -a 
#============= mysqld_t ============== #
!!!! The source type 'mysqld_t' can write to a 'dir' of the following types: # var_log_t, mysqld_var_run_t, mysqld_db_t, tmp_t, mysqld_tmp_t, var_lib_t, var_run_t, cluster_var_lib_t, cluster_var_run_t, root_t, cluster_conf_t allow mysqld_t file_t:dir { write search read remove_name open add_name }; 
#!!!! The source type 'mysqld_t' can write to a 'file' of the following types: # mysqld_var_run_t, mysqld_db_t, mysqld_tmp_t, mysqld_log_t, cluster_var_lib_t, cluster_var_run_t, root_t, cluster_conf_t allow mysqld_t file_t:file { rename read create write getattr unlink open append };

##3##

[root@linuxsrv2 home]# audit2allow -a -M mymysqlallow 
******************** IMPORTANT *********************** 
To make this policy package active, execute: semodule -i mymysqlallow.pp 

##4##

[root@linuxsrv2 home]# ls 
binlogs lost+found my_changed.cnf mymysqlallow.pp mymysqlallow.te
my_original.cnf 
[root@linuxsrv2 home]# semodule -i mymysqlallow.pp 

10. Try again to start MySQL:

[root@linuxsrv2 ~]# service mysqld start Starting mysqld: [ OK ]

But actually Slave does not work now:
From slave status: ** Slave_IO_Running: No Slave_SQL_Running: No**
And from error log:

2014-06-13 17:03:36 15360 [ERROR] Failed to open the relay log '/var/lib/mysql/data/mysql-relay-bin.000004' (relay_log_pos 448). 
2014-06-13 17:03:36 15360 [ERROR] Could not find target log file mentioned in relay log info in the index file '/home/binlogs/mysql-relay-bin.index' during relay log initialization. 
2014-06-13 17:03:36 15360 [ERROR] Failed to initialize the master info structure 
2014-06-13 17:03:36 15360 [Note] Check error log for additional messages. You will not be able to start replication until the issue is resolved and the server restarted. 

Here is another issue, in fact we defined path correctly in mysql-relay-bin.index file but it shows the old path. So Why? As examining mysql database you will see some tables like:

mysql> show tables like 'slave%'; 
+--------------------------+ 
| Tables_in_mysql (slave%) |
+--------------------------+ 
| slave_master_info | 
| slave_relay_log_info | 
| slave_worker_info | 
+--------------------------+ 
3 rows in set (0,00 sec)

Select from slave_relay_log_info table:

mysql> select * from slave_relay_log_infoG 
*************************** 1. row *************************** 
Number_of_lines: 7 
Relay_log_name: /var/lib/mysql/data/mysql-relay-bin.000004 
Relay_log_pos: 448 
Master_log_name: mysql-bin.000002 
Master_log_pos: 478 
Sql_delay: 0 
Number_of_workers: 0 
Id: 1 
1 row in set (0,00 sec) 

Yes as you see , although, we have defined new path it is not updated in this table. So quite straight option is manually updating this table to new path:

mysql> update slave_relay_log_info set relay_log_name='/home/binlogs/mysql-relay-bin.000004' where id=1; 
Query OK, 1 row affected (0,05 sec) 
Rows matched: 1 Changed: 1 Warnings: 0 

11. Restart MySQL again:

[root@linuxsrv2 ~]# service mysqld restart 
Stopping mysqld: [ OK ] 
Starting mysqld: [ OK ]

Check Slave if it is working properly now? Slave_IO_Running: Yes Slave_SQL_Running: Yes Yes now the binary logs and relay logs of slave server is located in /home directory:

[root@linuxsrv2 ~]# ls -l /home/binlogs/ 
total 28 
-rw-rw----. 1 mysql mysql 1320 2014-06-13 15:46 mysql-bin.000001 
-rw-rw----. 1 mysql mysql 214 2014-06-13 17:21 mysql-bin.000002 
-rw-rw----. 1 mysql mysql 333 2014-06-13 17:26 mysql-bin.000003 
-rw-rw----. 1 mysql mysql 93 2014-06-13 17:22 mysql-bin.index 
-rw-rw----. 1 mysql mysql 173 2014-06-13 17:22 mysql-relay-bin.000006 
-rw-rw----. 1 mysql mysql 590 2014-06-13 17:26 mysql-relay-bin.000007 
-rw-rw----. 1 mysql mysql 74 2014-06-13 17:22 mysql-relay-bin.index

Thanks for reading 😉

Copying Tablespaces to Another Server (Transportable Tablespaces)

If you want take several actions on tables? It is really very exciting 😛 Start MySQL 5.6.17(as we have) with:

    [mysqld] 
    innodb_file_per_table = 1 
    lower_case_table_names = 1

Then create a huge table or find one from friend’s database to testing. First off all let’s to rename table which is nearly 2.4 gigs.

[root@linuxsrv3 xxx]# ls -lthr 
    total 2,4G 
    -rw-rw----. 1 mysql mysql 65 2014-05-07 11:05 db.opt
    -rw-rw----. 1 mysql mysql 20K 2014-05-12 12:07 test_table.frm
    -rw-rw----. 1 mysql mysql 2,4G 2014-05-12 14:05 test_table.ibd

We have renamed test_table to test_table2.:

    [root@linuxsrv3 xxx]# time mysql -u root -p -e 'rename table xxx.test_table to xxx.test_table2' 
    Enter password: 
    real 0m1.973s 
    user 0m0.002s 
    sys 0m0.004s 

It takes only 1.9 second. So what if we want to move table from one database to another. There is no need to backup this table and then import. Use Rename.

   [root@linuxsrv3 xxx]# time mysql -u root -p -e 'rename table xxx.test_table2 to test.test_table2' 
   Enter password: 
   real 0m1.876s 
   user 0m0.005s 
   sys 0m0.003s 

And very mysteriously our table is in test database now:

   [root@linuxsrv3 test]# ls -lthr 
   total 2,4G 
   -rw-rw----. 1 mysql mysql 8,4K 2014-05-12 11:32 ff.frm 
   -rw-rw----. 1 mysql mysql 96K 2014-05-12 11:33 ff.ibd 
   -rw-rw----. 1 mysql mysql 65 2014-05-12 11:34 db.opt 
   -rw-rw----. 1 mysql mysql 20K 2014-05-12 12:07 test_table2.frm 
   -rw-rw----. 1 mysql mysql 2,4G 2014-05-12 14:05 test_table2.ibd

So quite fast way to move tables. But what about moving one table from one server to another server? Here is a show of transportable tablespaces begin. If you want to use this features you must have a MySQL 5.6.x a good one the latest GA. and it must be started as innodb_file_per_table = 1. Ofcourse you must have identical database names and table names on both side. We have already test_table2 table in test database on linuxsrv3. Created on linuxsrv4 same database and table. Our source server is: linuxsrv3 Destination server is: linuxsrv4 On destination server discard existing tablespace:

    mysql> use test;  
    mysql> alter table test_table2 discard tablespace; 
    Query OK, 0 rows affected (0,05 sec)

If you check you will see no .ibd file on database directory:

    [root@linuxsrv4 test]# ls db.opt test_table2.frm

On source server:

    mysql> use test; 
    Database changed 
    mysql> FLUSH TABLES test_table2 FOR EXPORT; 
    Query OK, 0 rows affected (0,00 sec)

Copy .ibd file to destination server:

    [root@linuxsrv3 test]# scp test_table2.ibd root@192.168.1.88:/var/lib/mysql/test root@192.168.1.88's password: 
    test_table2.ibd 100% 2444MB 15.4MB/s 02:39 

On source server:

    mysql> use test;  
    Database changed 
    mysql> UNLOCK TABLES; 
    Query OK, 0 rows affected (0,00 sec)

On destination server:

   [root@linuxsrv4 test]# ls 
   db.opt 
   test_table2.frm 
   test_table2.ibd 

   mysql> use test; 
   Database changed 
   mysql> alter table test_table2 IMPORT TABLESPACE; 
   ERROR 1146 (42S02): Table 'test.test_table2' doesn't exist

If you see such error it indicates that there is an owner issue:

   [root@linuxsrv4 test]# ls -l 
   total 2502684 
   -rw-rw----. 1 mysql mysql 65 2014-05-12 15:03 db.opt 
   -rw-rw----. 1 mysql mysql 20166 2014-05-12 15:05 test_table2.frm 
   -rw-r-----. 1 root root 2562719744 2014-05-12 15:16 test_table2.ibd

Change file owner to mysql:

    [root@linuxsrv4 test]# chown mysql:mysql test_table2.ibd 
    [root@linuxsrv4 test]# ls -l total 2502684 
    -rw-rw----. 1 mysql mysql 65 2014-05-12 15:03 db.opt 
    -rw-rw----. 1 mysql mysql 20166 2014-05-12 15:05 test_table2.frm 
    -rw-r-----. 1 mysql mysql 2562719744 2014-05-12 15:16 test_table2.ibd

And now try again:

    mysql> use test;  
    Database changed 
    mysql> alter table test_table2 IMPORT TABLESPACE; 
    Query OK, 0 rows affected, 1 warning (2 min 29,38 sec) 

And checking :

    mysql> select idvisit from test_table2 order by idvisit desc limit 1; 
    +---------+ 
    | idvisit | 
    +---------+ 
    | 7016951 | 
    +---------+ 
    1 row in set (0,04 sec)

That’s all in such manner you can move one or more tables to another server for testing or just for what you need.

Install MySQL from source with debugging option in Linux

Dear all, our today’s topic is about finding out the cause of MySQL crash and in depth examining issue for BUG reporting. So what is exact mean of this article? While i reported BUGs for MySQL, developers(experts) every time ask for a core dump (with gdb) and full stack-trace. Yeap they are right, finding an issue is only one step. You must also provide all necessary information to MySQL developers if you really interested in fixing bugs in open source. You have to help to find the core of problem. For this purpose you must have a DEBUG MySQL edition on your hand to generate core dump and to see full stack-trace. Yes as you think, we will install MySQL from source with DEBUG option and will create core dump. But first of all let to tell a few theory: * There are several kinds of logging in MySQL:

1. General Log -> will log all kind of database activity. Surely you will not enable it in production because off heavy load, Maybe for a few minutes for auditing will be sufficient to your needs.

2. Slow Quer Log -> will log all queries which is default running more than 10 seconds. Ofcourse you can change default time to less value.

3. Error Log -> From start of MySQL it will log all warnings, errors and events to this log file. It is the first file where you must watch for any kind of problem. There will be somewhat stack-trace looking something like:

Thread pointer: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0 thread_stack 0x40000
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x35)[0xab6ead]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x404)[0x736ee0]
/lib64/libpthread.so.0[0x35df20f710] /usr/local/mysql/bin/mysqld[0xd27e2e]
/usr/local/mysql/bin/mysqld[0xd1cc5e] /usr/local/mysql/bin/mysqld[0xc5d5f6]
/usr/local/mysql/bin/mysqld[0xc5dca7] /usr/local/mysql/bin/mysqld[0xc5e152]
/lib64/libpthread.so.0[0x35df2079d1] /lib64/libc.so.6(clone+0x6d)[0x35deee8b5d]

Appending trace to BUG report will be valuable, but most of times developers want core dump. Now it is time to install MySQL from source with DEBUG option. Go through every step: ** Install Dependencies:**

[root@localhost ~]# yum groupinstall "Development Tools" 
[root@localhost ~]# yum install cmake 
[root@localhost ~]# yum install ncurses ncurses-devel 
[root@localhost ~]# yum install wget  

Download tar archive from: mysql-5.6.19.tar.gz Then:

1. [root@localhost ~]# wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.19.tar.gz 
2. [root@localhost ~]# tar -xvf mysql-5.6.19.tar.gz 
3. [root@localhost ~]# groupadd mysql 
4. [root@localhost ~]# useradd -r -g mysql mysql 
5. [root@localhost ~]# cd mysql-5.6.19 
6. [root@localhost mysql-5.6.19]# cmake -DWITH_DEBUG=1 
7. [root@localhost mysql-5.6.19]# make 
8. [root@localhost mysql-5.6.19]# make install

After install finished:

8. [root@localhost mysql-5.6.19]# cd /usr/local/mysql 
9. [root@localhost mysql]# chown -R mysql  
10. [root@localhost mysql]# chgrp -R mysql 
11. [root@localhost mysql]# scripts/mysql_install_db --user=mysql 2014-07-21 03:52:05 14280 [Note] InnoDB: Using atomics to ref count buffer pool pages 2014-07-21 03:52:05 7f6e8f793720 InnoDB: !!!!!!!! UNIV_DEBUG switched on !!!!!!!!! 2014-07-21 03:52:05 7f6e8f793720 InnoDB: !!!!!!!! UNIV_SYNC_DEBUG switched on !!!!!!!!!

You will see difference in error log too. After all:

12. [root@localhost mysql]# chown -R root
13. [root@localhost mysql]# chown -R mysql data 
14. [root@localhost mysql]# bin/mysqld_safe --user=mysql & 
15. [root@localhost mysql]# bin/mysqld_safe --user=mysql & [1] 14525 [root@localhost mysql]# 140721 03:55:41 mysqld_safe Logging to '/var/log/mysqld.log'. 140721 03:55:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 

AS you see MySQL is started and at this point you can begin to test whatever you want. When you connect you will see difference:

[root@localhost bin]# ./mysql -u root --socket=/usr/local/mysql/data/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 1 Server version: 5.6.19-debug-log Source distribution 

Now we have a DEBUG MySQL , we can try to create a core dump: Add this line under [mysqld] catalog: [mysqld] core-file And also append this to the end of my.cnf file: [mysqld_safe] core_file_size=unlimited And restart MySQL. If some crash happens you will see :

    bin/mysqld_safe: line 166: 27247 Segmentation fault (core dumped) 

Core Dumped indicates that, core file is created. Default core file will reside in MySQL datadir. Thank You!.