background preloader

Mysql Replication master-slave/master-master

Facebook Twitter

16.1.2.3 Replication Slave Options and Variables. 16.1.2 Replication and Binary Logging Options and Variables. TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE. January 19, 2007 by Peter Zaitsev19 Comments We all know disk based temporary tables are bad and you should try to have implicit temporary tables created in memory where possible, do to it you should increase tmp_table_size to appropriate value and avoid using blob/text columns which force table creation on the disk because MEMORY storage engine does not support them Right ?

TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE

Wrong. In fact setting tmp_table_size is not enough as MySQL also looks at max_heap_table_size variable and uses lower value as a limit to for in memory temporary table after which it will be converted to MyISAM. To make things more confusing this is not what you would read in MySQL manual as far as I understand it: From MEMORY table contents are stored in memory, which is a property that MEMORY tables share with internal tables that the server creates on the fly while processing queries.

Interesting enough there is 2.5 years old bug on this matter which just recently started to get attention. 13.2.3 InnoDB Startup Options and System Variables. Modifier le innodb_log_file_size. Je me suis déjà fait avoir 2 fois par cette option. Si vous avez des tables InnoDB avec un gros load d’écriture (insert, update), il est généralement recommandé d’avoir un innodb_log_file_size assez élevé. Mais soyez vigilant: plus la grosseur du log file est élévée, plus le temps de recovery est long dans le cas d’un crash.

Mais peu importe. Là ou je veux en venir, c’est sur la manière de modifier cette option. InnoDB est un engine capricieux et innodb_log_file_size est l’un de ses caprices. La manière de faire est relativement simple. On s’assure que la variable innodb_fast_shutdown ne vaut pas 2. Si la valeur de innodb_fast_shutdown est 2, il faut la changer pour 1, arrêter le serveur, le repartir, et l’arrêter de nouveau. On arrêtele serveur On modifie la configuration On renomme le fichier log lui-même On repart le serveur. 16.4 Replication Notes and Tips. 6 Réplication de MySQL. 6.4 Comment mettre en place la réplication. 12.5.2.1 CHANGE MASTER TO Syntax. 16.1.1.1 Setting the Replication Master Configuration.

16.1.1.2 Setting the Replication Slave Configuration. 16.1.1.3 Creating a User for Replication. 16.1.1.4 Obtaining the Replication Master Binary Log Coordinates. Master Master Howto. This tutorial describes how to set up MySQL master-master replication.

Master Master Howto

We need to replicate MySQL servers to achieve high-availability (HA). In my case I need two masters that are synchronized with each other so that if one of them drops down, other could take over and no data is lost. Similarly when the first one goes up again, it will still be used as slave for the live one. Here is a basic step by step tutorial, that will cover the mysql master and slave replication and also will describe the mysql master and master replication. Notions: we will call system 1 as master1 and slave2 and system2 as master2 and slave 1.

Step 1: Install mysql on master 1 and slave 1. configure network services on both system, like Master 1/Slave 2 ip: 192.168.16.4 Master 2/Slave 1 ip : 192.168.16.5 Step 2: On Master 1, make changes in my.cnf: [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockold_passwords=1 server-id=1 [mysql.server]user=mysqlbasedir=/var/lib Step 3: and restart the mysql master1. Step 4: