Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 32 10 Older Entries

Displaying posts with tag: mysql administration (reset)

MySQL Optimizer Tracer usage case with count(*)
+0 Vote Up -0Vote Down

What is Optimizer Trace?
After reading topic about Optimizer Tracer by Morgan Tocker decided to test it.
From Optimizer Trace and EXPLAIN FORMAT=JSON in 5.7:
Optimizer trace is a new diagnostic tool introduced in MySQL 5.6 to show how the optimizer is working internally. It is similar to EXPLAIN, with a few notable differences:

It doesn’t just show the intended execution plan, it shows the alternative choices.
You enable the optimizer trace, then you …




  [Read more...]
MySQL LogRotate script
+0 Vote Up -0Vote Down

Did you ever try to use log rotate facility in Linux with MySQL?
There is no need to script one for this purpose, it is already installed.
From MySQL spec file, it looks for logrotate.d folder:

# Ensure that needed directories exists
install -d $RBR%{_sysconfdir}/{logrotate.d,init.d}

As well as there is dedicated mysql-log-rotate.sh script for installing logrotate script.
The script path is: /mysql-5.6.24/support-files/mysql-log-rotate.sh

Again from spec file:

# Install logrotate and autostart
install -m 644 $MBD/release/support-files/mysql-log-rotate $RBR%{_sysconfdir}/logrotate.d/mysql …



  [Read more...]
Partial table recovery from physical backup
+0 Vote Up -0Vote Down

In previous topic, we have covered “Transportable Tablespace” concept by copying and importing table’s tablespace to remote server. See -> Copying Tablespaces to Remote Server

The idea is copying tablespace file to remote server, in remote server you must create identical database names and table names manually, then you should discard new table’s tablespace file and import new copied one.

To achieve this you must have …

  [Read more...]
lower_case_table_names option to lose databases and tables
+0 Vote Up -0Vote Down

To lose your data or make it unavailable there is an excellent option in MySQL, rather than drop or delete

Option name is lower_case_table_names.
Default value of this setting is 0:

mysql> select @@lower_case_table_names;
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

Due to documentation value=0:

Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. You should not set …


  [Read more...]
Error reading GTIDs from binary log: -1
+0 Vote Up -0Vote Down

Wonder how MySQL Slave server will act, when disk full condition occurs?
Before in our articles we use only single MySQL server.
Now think about replication topology, where slave server has problem with full partition.
Firstly we will enable Binary Log/GTID on Slave side and will ensure that the changes also applied to binary log on Slave side:

# BINARY LOGGING #
#
server_id                      = 2
log_bin                        = /opt/mysql/datadir/mysql-bin
log_bin_index                  = /opt/mysql/datadir/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1
binlog_format                  = row
relay_log                      = …



  [Read more...]
Testing MySQL with “read-only” filesystem
+0 Vote Up -0Vote Down

From previous articles about “disk full” conditions, you have some taste of testing MySQL with such approach:
1. Testing Disk Full Conditions
2. Using GDB, investigating segmentation fault in MySQL

But there is still untouched topic, about read-only mounted file system and how MySQL will act in such condition.
In real life, i have encountered such situation that something happened with Linux server and file system suddenly goes to read-only …



  [Read more...]
Using GDB, investigating segmentation fault in MySQL
+0 Vote Up -0Vote Down

In previous article, we have covered some errors and issues with using MySQL in “disk full” environment. Where there was no space left on device.(See here: Testing Disk Full Conditions)
Today’s scenario is -> Starting MySQL with GTID/binary log enabled, in 0 space left Linux(CentOS 6.5) environment.

If you hit a bug or problem, general rule for helping community to fix it is to provide as much information as possible. Especially useful is to give gdb output from coredump. To get coredump you can read this wonderful article …


  [Read more...]
Testing “disk full” conditions in MySQL
+1 Vote Up -0Vote Down

How MySQL will act if there is no space left on hard disk?
To answer this question, let’s test it:

Our first test is with MySQL 5.6.24-debug with disabled binary log:
Trying to import huge dump, after while it says table is full:

Query OK, 12725 rows affected (2.46 sec)
Records: 12725  Duplicates: 0  Warnings: 0

Query OK, 12724 rows affected (2.40 sec)
Records: 12724  Duplicates: 0  Warnings: 0

Query OK, 12726 rows affected (2.53 sec)
Records: 12726  Duplicates: 0  Warnings: 0

ERROR 1114 (HY000): The table 'sales' is full
ERROR 1114 (HY000): The table 'sales' is full
ERROR 1114 (HY000): The table 'sales' is full

In error log you will see …



  [Read more...]
Playing with count(*) optimizer work
+2 Vote Up -0Vote Down

Article about bug report #68814 related to testing count(*) explain plan.

Or 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 sales\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: index
possible_keys: NULL
          key: sales_cust_idx
      key_len: 4
          ref: NULL
         rows: …
  [Read more...]
MySQL-AutoXtrabackup command line tool for using Percona Xtrabackup
+0 Vote Up -0Vote Down

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

Showing entries 1 to 10 of 32 10 Older Entries

Planet MySQL © 1995, 2015, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.