replication newbie questions
Mysql newbie here, looking for some help configuring 5.0.45 master-slave replication. Here's my scenario... We have a heavily loaded 30gb 5.0.45 DB we need to replicate via master-slave configuration to a new, beefier server running same mysql 5.0.45, and then cutover to the new server. Due to extreme SAN congestion and a grossly overloaded master server, our DB dumps take 5.5 hours. But we cannot afford that much downtime or locking during the replication transition; we can manage 10-15 minutes, but more is very problematic. I understand that FLUSH TABLES WITH READ LOCK will lock the tables for the duration of the 5.5 hour dump. Is this true? If so, we'd like to dump/initialize/sync slave WITHOUT any locking anything the master for more than a few seconds if at all possible. Will this give us the dump we need? mysqldump --single-transaction --master-data --all-databases Thank you in advance for any help. Ed
Re: replication newbie questions
On 8/28/13 2:00 PM, Ananda Kumar wrote: Why don't u try snapshot backups, where the lock held for less duration. Or can't u take mysql dumps during Night time when there is less bd activity I neglected to mention these systems are both CentOS linux systems. Unfortunately, the 5.5 hour dumps are already done during the least busy times. Regarding snapshots, how long are snapshot locks held? These are ext4 filesystems. Assuming the lock is not held for long, what's the recommended way to do snapshots on ext4? Thanks, Ed On Thursday, August 29, 2013, Ed L. mysql@bluepolka.net mailto:mysql@bluepolka.net wrote: Mysql newbie here, looking for some help configuring 5.0.45 master-slave replication. Here's my scenario... We have a heavily loaded 30gb 5.0.45 DB we need to replicate via master-slave configuration to a new, beefier server running same mysql 5.0.45, and then cutover to the new server. Due to extreme SAN congestion and a grossly overloaded master server, our DB dumps take 5.5 hours. But we cannot afford that much downtime or locking during the replication transition; we can manage 10-15 minutes, but more is very problematic. I understand that FLUSH TABLES WITH READ LOCK will lock the tables for the duration of the 5.5 hour dump. Is this true? If so, we'd like to dump/initialize/sync slave WITHOUT any locking anything the master for more than a few seconds if at all possible. Will this give us the dump we need? mysqldump --single-transaction --master-data --all-databases Thank you in advance for any help. Ed
connection issue
This mysql newbie is having trouble connecting to a mysqld instance, hoping someone can offer a clue on troubleshooting. I have 2 mysql 5.0.45 installations on one RHEL server. One live mysqld is setup in what appears to be a relatively standard installation, port 3306, user 'mysql', etc. I've set up the other mysqld to run tests on a non-standard port 5045, user 'testsql', different data root, config, logs, etc. When I attempt to connect to the mysqld running on port 5045 from the command-line mysql client on the same host as follows ... # mysql -P 5045 ... it seems I'm actually connecting to the live server on 3306 because 'show databases' shows the live databases. How can I troubleshoot this best? Thanks.
