Database server howto’s, for example MySQL

Installing Postgres 9.4.5 on Ubuntu Trusty

By default on Ubuntu Trusty there is only Postgres 9.3 available This shows how to get the latest version installed. Add the Postgres Repository: echo "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main" > /etc/apt/sources.list.d/postgres.list Import the repository key, and run an update: wget https://www.postgresql.org/media/keys/ACCC4CF8.asc apt-key add ACCC4CF8.asc apt-get update Install Postgres: apt-get install postgresql-9.4

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 http://repo.mysql.com/mysql-apt-config_0.2.1-1debian7_all.deb 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 http://repo.mysql.com/apt/debian/ wheezy mysql-5.6 deb-src http://repo.mysql.com/apt/debian/ 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 […]

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 = "PUT_YOUR_DATABASE_NAME_HERE";

mysql data files backup using mysqlhotcopy   Recently updated !

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 […]

Creating database backups using mysqldump   Recently updated !

In this article I'd like to show how one can create a backup of a mysql database. The most basic example, is to create a full backup of your database, including structure and data. I'm creating the backups in the folder /backup. You can use any other folder you want. The same for user root […]