Setting up mysql replication to Amazon RDS

If you want to set up a replication between an RDS and an existing mysql server environment, you might encounter privilege errors, when trying to execute commands like "change master to…" or "stop slave;" on the RDS. Amazon RDS comes with a bunch of stored procedures that you have to use instead: MySQL Command Stored Procedure […]

InnoDB Error Table mysql.innodb_table_stats not found

Follow these steps to recover from this innodb error "InnoDB: Error: Table mysql.innodb_table_stats not found.". First try to drop these tables from a mysql console: USE mysql; DROP TABLE innodb_index_stats; DROP TABLE innodb_table_stats; DROP TABLE slave_master_info; DROP TABLE slave_relay_log_info; DROP TABLE slave_worker_info; This may throw some errors. If your mysql data folder is somewhere different than the […]

Installing mysql 5.6 on Debian Wheezy

Add the Repo: wget dpkg -i mysql-apt-config_0.2.1-1debian7_all.deb The above package will create a file /etc/apt/sources.list.d/mysql.list which will contain lines like these or similar: deb wheezy mysql-5.6 deb-src wheezy mysql-5.6 Install mysql 5.6: apt-get update apt-get install mysql-server-5.6  

Using automysqlbackup to create regular backups of your database

This works on Debian and Ubuntu, but also on other distributions. The package can be installed via apt: apt-get install automysqlbackup For yum (Fedora, CentOS, RedHat, etc.) there's usually also a package provided. Next you need to edit the configuration file /etc/default/automysqlbackup. There are other options, the most important ones are: USERNAME=root PASSWORD=…….. DBHOST=localhost DBNAMES=`mysql –defaults-file=/etc/mysql/debian.cnf […]

Creating an encrypted Databag in Chef

Create a random encryption key: openssl rand -base64 512 | tr -d '\r\n' > secret_key  Use this to encrypt a data bag item named “passwords” located in a data bag named “production”: knife data bag create −−editor /usr/bin/vi −−secret-file ./secret_key production passwords This will open an text editor, example JSON data would be: {   "id": […]

MySQL Query to calculate DB and Table sizes

Calculate sizes of all your databases in MB: SELECT table_schema "DB Name", SUM( data_length + index_length) / 1024 / 1024  "DB Size" FROM information_schema.TABLES GROUP BY table_schema ; Calculate table sizes for a specific database: SELECT TABLE_NAME, table_rows, data_length, index_length,  round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = […]

Configure SMTP Auth in a Postfix/MySQL Configuration

To make E-mail delivery work with SMTP auth using SASL, you have to create the folder /etc/postfix/sasl (if it does not exist yet) and add the two files smtpd.conf and smtp.conf, having the same content each: pwcheck_method: auxprop auxprop_plugin: sql mech_list: plain login sql_engine: mysql sql_hostnames: localhost sql_user: postfix sql_passwd: post sql_database: postfix sql_verbose: yes […]

mysql data files backup using mysqlhotcopy

With mysqlhotcopy you can create a copy of your database data files. Compared to mysqldump, mysqlhotcopy does not create sql commands, but creates a copy of the files residing in /var/lib/mysql (may differ depending on your installation). The hotcopy can be created while the server is running, you don't need to stop it. Basically mysqlhotcopy […]