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

Displaying posts with tag: mysql administration (reset)

How to get rid of orphaned tables in MySQL?
+1 Vote Up -0Vote Down

This post is about situation where comments in BUG reports are really valuable in term of learning and getting some internal view of going process.
Let’s take a look at -> http://bugs.mysql.com/bug.php?id=80183

The report is about how you can not drop general tablespace if it was a crash of MySQL instant while running alter statement.


mysql> create tablespace ts1 add datafile '/var/lib/mysql_tablespaces/ts1.ibd' engine=innodb;
Query OK, 0 rows affected (0,02 sec)

Kill -9 process while altering:






  [Read more...]
How to get confused about buffer pool size allocation?
+1 Vote Up -0Vote Down

How it feels when you realize that, something going to be scary in term of question – “Wait, why? how?”
Let’s see:

I am trying to allocate 9G buffer pool size with 64 instances:

innodb_buffer_pool_instances=64
innodb_buffer_pool_size=9G

From error log it said that:

InnoDB: Initializing buffer pool, total size = 16G, instances = 64, chunk size = 128M

“Wait, why? how?” N1

From select it is also allocated 16G:

mysql> select @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| …



  [Read more...]
The magical abandoned .ibd files after crash recovery with MySQL 5.7.10
+0 Vote Up -0Vote Down

If you are running ‘OPTIMIZE TABLE’ or altering ‘TABLESPACE’ just be careful not to kill MySQL service while running these commands
Here is a test case:
We have a table:

CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2427738 DEFAULT CHARSET=latin1 COMPRESSION='lz4'

After a while we want to ‘OPTIMIZE’ this table:










  [Read more...]
How to lose General Tablespace in MySQL 5.7.10
+1 Vote Up -0Vote Down

If you have not be careful with General Tablespaces in MySQL 5.7.10, you must be at least from now.
I have reported several bugs related to General Tablespaces for eg.:

* #77814 -> Altering table twice,with same tablespace will increase general tablespace size (verfied)

* #77556 -> Segmentation fault while creating general tablespace in disk full Linux (fixed/closed)

* #77830 -> …


  [Read more...]
Installing Percona Server with TokuDB and TokuBackup from source, in CentOS 7 minimal
+0 Vote Up -0Vote Down

Today i want to share experience with latest Percona Server where TokuDB and TokuBakcup plugins have been added in main source.
So there is no other steps needed here to get and started.
But in recent discussion in our local community, there was a question: “How about installing MySQL on CentOS minimal”.
The reason was clear, because somebody got too many errors in fresh CentOS 7 minimal.

I want to share here full steps.
Please follow numbers:

1. Getting Source package:

[root@testing-host1 ~]# wget …





  [Read more...]
Getting SIGABRT -> Assertion `is_binlog_empty()’ failed with MySQL 5.7.7-rc
+0 Vote Up -0Vote Down

What about inserting data to MySQL 5.7.7-rc-debug with GTID/binary log enabled. It is OK until you hit disk full condition while inserting data. It will crash on the way and likely you will lose MySQL. Error log indicates related problem:

2015-07-29T07:20:05.865708Z 2 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file ./sls2/sales.ibd, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at …
  [Read more...]
Altering tablespace of table – new in MySQL 5.7
+0 Vote Up -0Vote Down

Dear community, maybe you have already tested, there are new added features in MySQL 5.7. See previous post -> About 5.7.7-rc BUGs

“General Tablespace” feature -> From now you can place tables to separate tablespaces. Prior, on our hands there were only “System TableSpace” and “InnoDB file per table” options. Today’s topic is altering table to use this “General Tablespace”.

Our sample table structure:

mysql> show create table t1;

    | Table | …
  [Read more...]
Testing MySQL 5.7.7-rc with memory and disc full conditions
+0 Vote Up -0Vote Down

With MySQL 5.7 there will be many improvements and changes. The most valuable ones are:

1. “General Tablespace” feature -> From now you can place tables to separate tablespaces. Prior, on our hands there were only “System TableSpace” and “InnoDB file per table” options.

Check for further reading: General Tablespaces Create statement: Create TableSpace

2.

  [Read more...]
VALIDATE PASSWORD PLUGIN with mysql_secure_installation in 5.7.7-rc
+0 Vote Up -0Vote Down

While testing installation steps with MySQL 5.7.7-rc, surely you will find much more improvements, changes, deprecated functionality and etc.

The interesting one is activating VALIDATE PASSWORD PLUGIN via mysql_secure_installation script. Which we use by mean “securing” MySQL installations.

I will write a separate topic about MySQL 5.7.7-rc installation steps from source, with related BUG reports.

So after first run:

[root@centos7_vm mysql]# bin/mysql_secure_installation --socket=/opt/mysql/datadir/mysqld-new.sock
Securing the MySQL server deployment.
Connecting to MySQL server using password in …
  [Read more...]
Comprehensive guide to installing PXC on CentOS 7
+0 Vote Up -0Vote Down

Recently want to install Percona XtraDB Cluster + HAProxy + KeepAlived on CentOS 7, but could not find any all-in-one guide. So decided to write down all steps necessary for getting started and running HA solution using Open Source projects. Nowadays high availability is one of the main concerns faced by big and small companies.Minimum wasting of time (downtime per year), sustainably working of infrastructure and etc. is the main mission for all companies that they are trying to achieve.There are different approaches to this mission. One of them as large companies did, to buy expensive software and support from vendors. But small companies could not go through …

  [Read more...]
Showing entries 1 to 10 of 42 10 Older Entries

Planet MySQL © 1995, 2016, 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.