I installed owncloud, and set it to indexing a pile of files I wanted easier access to.
Well, /var filled, and the DB stopped. :o
I was on Debian Jessie (stable), and needed some updates to continue.
### Expand /var since I'm not ready to move /var/lib/mysql to its on filesystem
lvextend -L 16G /dev/rootvg/hd9
resize2fs /var
### Stop services using mysql
/etc/init.d/apache2 stop
### Dump all databases
mysqldump --all-databases --opt --routines --complete-insert -uroot -p | gzip -9 > /storage/test/mysqldump.2016-03-03.gz
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
### Drop all databases except mysql and information_schema
tar -czvf /storage/test/mysql_var_minus_innodb.tgz [dm-z]*
mysql -u root -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| owncloud |
| performance_schema |
| phpmyadmin |
| roundcube |
| test |
+--------------------+
7 rows in set (0.00 sec)
mysql> drop database owncloud;
mysql> drop database performance_schema;
mysql> drop database phpmyadmin;
mysql> drop database roundcube;
mysql> drop database test;
mysql> SET GLOBAL innodb_fast_shutdown = 0;
mysql> exit
### Or for the brave
mysql -e "SELECT DISTINCT CONCAT ('DROP DATABASE ',TABLE_SCHEMA,' ;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA <> 'mysql' AND TABLE_SCHEMA <> 'information_schema';" | tail -n+2 | mysql -u root -p
mysql -e "SELECT table_name, table_schema, engine FROM information_schema.tables WHERE engine = 'InnoDB';"
### Stop mysql
/etc/init.d/mysql stop
### Remove the InnoDB files
rm /var/lib/mysql/ib*
### changed from jessie to stretch to get MySQL 5.6
### Not quite ready for MariaDB 1x
vi /etc/apt/sources.list
# Standard repo
deb http://ftp.us.debian.org/debian stretch main contrib non-free
deb-src http://ftp.us.debian.org/debian stretch main contrib non-free
### Volatile
deb http://ftp.debian.org/debian/ stretch-updates main contrib non-free
deb-src http://ftp.debian.org/debian/ stretch-updates main contrib non-free
### Debian Backports
deb http://http.debian.net/debian stretch-backports main
### security updates
deb http://security.debian.org/ stretch/updates main contrib non-free
deb-src http://security.debian.org/ stretch/updates main contrib non-free
####################################
apt-get update
apt-get install mysql-server-5.6
apt-get install mysql-server-5.6 ## going from jessie to stretch, so it was a little tweaky
### Increased log and memory size for mysql from defaults (log 25% of buffer pool)
### Changed to barracuda (supports compressed tables)
### Changed to one file per table for various reasons.
vi /etc/mysql/my.conf
[mysqld]
# * InnoDB
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
innodb_file_per_table = ON
innodb_file_format = barracuda
innodb_flush_method=O_DIRECT
innodb_log_file_size=256M
innodb_buffer_pool_size=1G
#####################################
### it recreates the IB files on start
/etc/init.d/mysql start
### Make sure barracuda is set for real
mysql -u root -p
mysql> set global innodb_file_format = 'Barracuda';
mysql> exit
### Import the dump
gunzip < /storage/test/mysqldump.2016-03-03.gz | mysql -u root -p
###########################################################################
###########################################################################
### Repair a problem with MySQL installer / conversion / upgrade
### See http://bugs.mysql.com/bug.php?id=67179
/*
temporary fix for problem with windows installer for MySQL 5.6.10 on Windows 7 machines.
I did the procedure on a clean installed MySql, and it worked for me, at least it stopped
lines of innodb errors in the log and the use of transient innodb tables. So, do it at
your own risk..
1. drop these tables from "use mysql":
innodb_index_stats
innodb_table_stats
slave_master_info
slave_relay_log_info
slave_worker_info
2. delete all .frm & .ibd of the tables above.
3. run this file to recreate the tables above (source five-tables.sql).
4. restart mysqld.
Cheers,
CNL
*/
CREATE TABLE `innodb_index_stats` (
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`index_name` varchar(64) COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
`stat_value` bigint(20) unsigned NOT NULL,
`sample_size` bigint(20) unsigned DEFAULT NULL,
`stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE `innodb_table_stats` (
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`n_rows` bigint(20) unsigned NOT NULL,
`clustered_index_size` bigint(20) unsigned NOT NULL,
`sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE `slave_master_info` (
`Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
`Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
`User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
`User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
`Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
`Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
`Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
`Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
`Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
`Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
`Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
`Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
`Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
`Heartbeat` float NOT NULL,
`Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
`Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
`Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
`Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
`Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
`Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
`Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';
CREATE TABLE `slave_relay_log_info` (
`Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
`Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
`Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
`Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
`Number_of_workers` int(10) unsigned NOT NULL,
`Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
CREATE TABLE `slave_worker_info` (
`Id` int(10) unsigned NOT NULL,
`Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Relay_log_pos` bigint(20) unsigned NOT NULL,
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Master_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_seqno` int(10) unsigned NOT NULL,
`Checkpoint_group_size` int(10) unsigned NOT NULL,
`Checkpoint_group_bitmap` blob NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';
###########################################################################
###########################################################################
###########################################################################
### Regenerate performance_schema
mysql_upgrade --force -u root -p
### Make sure tables are okay
mysqlcheck -p
### Grow mysql temporary space to prevent:
#### ERROR 1034 (HY000): Incorrect key file for table 'oc_filecache'; try to repair it
lvextend -L 16G /dev/rootvg/hd1
resize2fs /dev/rootvg/hd1
### Set to compressed tables
# gzipped, the dump is 319MB, and deployed, the one table is 6GB, for read mostly data.
mysql -u root -p
mysql> alter table owncloud.oc_filecache ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
mysql> exit
### Clean up free space
mysql -u root -p
mysql> OPTIMIZE TABLE owncloud.oc_filecache;
mysql> exit
#####################################
### fix roundcube since it was unhappy with some of the updates
apt-get install roundcube;
### Cleanup some old stuff amplified by partial updates
apt-get autoremove
### Reboot since we had a new dbus installed, and apache2 is still down
shutdown -fr now
