How to configure MySQL master/slave replication with MHA automatic failover

This post is the first one of a package called "complete infrastructure". Following all posts of this package, you will be able to setup, from scratch, a complete "high available" web infrastructure with : 2 load balancer servers 2 web servers (Drupal in my case) with real-time files synchronization 2 database servers sharing a VIP in a master/slave replication, with MHA automatic failover To find all posts of this package, just click the tag "complete infrastructure" at the end of the page. Overview The purpose of this installation is to have two database servers, in a master/slave replication configuration. All requests from your web servers are always forwarded to the master server, which is replicated in real-time. Most of the time, the slave server will just act as a simple backup, and will not be used by the website. ...

Convert MySQL database from MyISAM to InnoDB

In MySQL, you have a command to convert a table from MyISAM engine to InnoDB engine. ALTER TABLE table_name ENGINE = InnoDB; However, this command needs to be done for EVERY table, so here’s a way to do it by scripting if you want to do the entire database :) 1) Backup your database 2) Create the script (change DATABASE_NAME by yours): mysql -p -e "show tables in <DATABASE_NAME>;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql 3) Check the file alter_table.sql to see if it looks correct 4) Run the script: mysql --database=<DATABASE_NAME> -p < alter_table.sql 5) Verify: mysql> show table status; ...

How to reduce size of ibdata1 file in MySQL

If you use Innodb engine for your MySQL database, you may have noticed that the /var/lib/mysql/ibdata1 file is the most important and heavy file of your MySQL server. Day by day, this file will increase and can quickly consume all your hard drive. Reducing its size can be a little bit tricky. When "innodb _ file _ per _ table" is set to "0" in your my.cnf file, the ibdata1 file normally stores many classes of information, such as table data, table indexes, MVCC (Multiversioning Concurrency Control) data, table metadata, and so on. You can check this if you want a well explained schema. Many people create multiple ibdata files hoping for better diskspace management and performance. It does not help. Unfortunately, OPTIMIZE TABLE against an InnoDB table stored in ibdata1 does two things: Makes the table's data and ...