MySQL Master Master Replication | HowtoForge - Linux Howtos and. This tutorial describes how to set up MySQL master-master replication. 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: Load Balancing and MySQL Master and Slaves (Articles) | The Bake.
If you are currently using MySql master/slave replication for load balancing and wish to transport to cakePHP, it really couldn't be easier. 2 simple steps to master/slave replication in cake.. Firstly in app/config/database.php write the following:- class DATABASE_CONFIG { var $default = array( 'driver' => 'mysql', 'host' => 'slave.host.ip', 'login' => '... var $master = array( 'driver' => 'mysql', 'host' => 'master.host.ip', 'login' => ' } Then, in app/app_model.php, create 4 new methods: function beforeSave() { $this->useDbConfig = 'master'; return true; } function afterSave() { $this->useDbConfig = 'default'; return true; } function beforeDelete() { $this->useDbConfig = 'master'; return true; } function afterDelete() { $this->useDbConfig = 'default'; return true; } It really is as simple as that.
However, there are some instance where you may want to use a query in your controller where you update/insert information. With thanks to Nate and gwoo. How To Set Up A Load-Balanced MySQL Cluster | HowtoForge - Linux. Version 1.0 Author: Falko Timme This tutorial shows how to configure a MySQL 5 cluster with three nodes: two storage nodes and one management node. This cluster is load-balanced by a high-availability load balancer that in fact has two nodes that use the Ultra Monkey package which provides heartbeat (for checking if the other node is still alive) and ldirectord (to split up the requests to the nodes of the MySQL cluster). In this document I use Debian Sarge for all nodes. Therefore the setup might differ a bit for other distributions.
The MySQL version I use in this setup is 5.0.19. This howto is meant as a practical guide; it does not cover the theoretical backgrounds. This document comes without warranty of any kind! 1 My Servers I use the following Debian servers that are all in the same network (192.168.0.x in this example): In addition to that we need a virtual IP address : 192.168.0.105. So normally we would need five machines for our setup: loadb1.example.com: Ultra Monkey: If you get this in MySQL Lock wait timeout exceeded; try restart.
Error : Lock wait timeout exceeded; try restarting transaction - Bugs: #10641: 1205, 'Lock wait timeout exceeded; try restarting. Mysql Master Master Repliction « Nasser Heidari. I’m going to setup MySQL Multi-Master Replication, here are my systems information: DB-1 : 172.20.1.6 —> (Master 1 / Slave 2) DB-2 : 172.20.1.8 —> (Master 2 / Slave 1) # uname -a Linux DB-1.linax.wordpress.com 2.6.18-164.11.1.el5 #1 SMP Wed Jan 20 07:32:21 EST 2010 x86_64 x86_64 x86_64 GNU/Linux also I’m using MYSQL community Edition, you can find rpm’s here : # wget # wget # wget OK, lets start: I configured MySQL’s my.cnf file to use the tuning setup from the my-medium.cnf, which should give us good tuning parameters for our load at this point.
Then I added the pieces necessary for replication in both DB-1 and DB-2: ————————————————- 1. 2. ————————————————- 3. ————————————————- 4. Like this: Moving the MySQL's datadir directory. - lunatechian (lunatech-ia. By default, MySQL's datadir is placed in the /var/lib/mysql directory. However, if you are planning on using MySQL tables to store a lot of data and your /var partition is small, it might cause you problem at a later stage.
In such a scenario, it is better to move the MySQL's datadir to another partition (like /home. The steps are Stop your mysql server before starting this operation Create the directories that will be new datadir chown the directory to the mysql:mysql user copy the files from the old datadir to the new location. However, make sure that the files named ib_arch_log_0000000000, ib_logfile0 etc. are not copied to the newer location. Make sure that the files and directories are owned by mysql user Make changes in the my.cnf to point the new datadir. How to move MySQL datadir to another drive | Kaliphonia.com. Your MySQL database takes a huge space and your current drive is almost full? One of the available options is to move MySQL database to another drive with minimal downtime, here is how... On some machines this can also increase MySQL performance, especially one with fast drive -- such as Raptor or SCSI drive.
I'm assuming: - the second drive mounted as "/home2" - current MySQL datadir is "/var/lib/mysql" - the content of "/var/lib/mysql" is the following: [root@server ~]# ls -o /var/lib/mysql -rw-rw---- 1 mysql 10485760 Jul 9 19:04 ibdata1 -rw-rw---- 1 mysql 5242880 Jul 9 19:04 ib_logfile0 -rw-rw---- 1 mysql 5242880 Jul 9 07:13 ib_logfile1 drwx------ 2 mysql 4096 Jul 15 07:40 joomla drwx------ 2 mysql 4096 Jul 9 06:03 mysql srwxrwxrwx 1 mysql 0 Aug 13 09:42 mysql.sock drwx------ 2 mysql 4096 Jul 9 11:31 users drwx------ 2 mysql 4096 Jul 9 08:22 wordpress [root@server ~]# Steps: We need to shutdown MySQL to get all data copied properly.
/etc/init.d/mysql stop or /etc/rc.d/init.d/mysql stop Save. How to change the mysql database location - Spike Developer Zone. MySQL replikacja typu master-master | tech.punkt.pl. Top 20+ MySQL Best Practices. Database operations often tend to be the main bottleneck for most web applications today. It's not only the DBA's (database administrators) that have to worry about these performance issues. We as programmers need to do our part by structuring tables properly, writing optimized queries and better code. In this article, I'll list some MySQL optimization techniques for programmers. Before we start, be aware that you can find a ton of useful MySQL scripts and utilities on Envato Market. Most MySQL servers have query caching enabled. It's one of the most effective methods of improving performance, that is quietly handled by the database engine.
The main problem is, it is so easy and hidden from the programmer, most of us tend to ignore it. The reason query cache does not work in the first line is the usage of the CURDATE() function. Using the EXPLAIN keyword can give you insight on what MySQL is doing to execute your query. After adding the index to the group_id field: From MySQL docs: My MySQL Binary Log files are taking up all my disk space! – sys. …and other MySQL Oddities. If your MySQL binary log files are a “Growing” problem… there are a few simple steps you can use to curtail their growth and save your disk space from these huge files.
They probably look like: mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 … And around 1 gig in size each. These files are generated when you have log-bin=mysql-bin or other log-bin= line in your /etc/my.cnf configuration file. There are two ways to fix this problem: The first is to stop the MySQL server from creating these binary files. The second is to continue logging – this is necessary as stated above if you are performing SQL replication. You can add a line to your /etc/my.cnf file which will only keep these files around as long as necessary. Expire_logs_days = X Where X is the number of days you’d like to keep them around.
Just a side note: You know that you should do this anyway, but make sure you back up your mysql database.