Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > Linux Forums > HOWTO-Related Questions

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 8th October 2009, 11:39
v2k v2k is offline
Member
 
Join Date: Sep 2007
Location: Vancouver, Canada
Posts: 93
Thanks: 3
Thanked 1 Time in 1 Post
Default Live MySQL Replication/Backup

I have a server running innodb. It's about 40G in size. It's getting hit with reads and writes pretty often. I'd like to minimize its downtime. I'm looking at ways to avoid taking it down and running mysqldump on a regular basis.

As discussed here:
mysql_database_replication

1. My basic plan was to sort out replication and back up the slave's database. From what I've read online, there seems to be some school of thought that replication is not reliable enough. i.e. the slave and master will drift and the data will not be restored properly in the case of losing the master. Is this a valid concern?

2. Is it feasible to setup this plan on a single server running two mysqlds and replicating itself on localhost? I would then copy the backups to a remote or external drive.

3. Perhaps I can take some shortcuts in my case. Let's say I'm not concened about the data being exact. If I have 1 billion rows, I want to backup everything, but I'm only really concerned about the last 100k rows say as they are currently most active. That is, the older rows are unlikely to change; and even if they did vary slightly, that would be okay. Is it feasible to have a script that would passively dump rows bit by bit in the background and rebuild the database slowly over time? Perhaps dumping 100 rows every 5 minutes say until the whole database was parsed for example.
Reply With Quote
Sponsored Links
  #2  
Old 9th October 2009, 14:19
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,749 Times in 2,579 Posts
Default

http://www.howtoforge.com/back_up_my..._interruptions
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
The Following User Says Thank You to falko For This Useful Post:
v2k (12th October 2009)
  #3  
Old 12th October 2009, 08:16
v2k v2k is offline
Member
 
Join Date: Sep 2007
Location: Vancouver, Canada
Posts: 93
Thanks: 3
Thanked 1 Time in 1 Post
Default

Do you think you could explain how to do this to the same host? Run two mysqlds and have the master and slave on the same machine?
Reply With Quote
  #4  
Old 12th October 2009, 09:11
v2k v2k is offline
Member
 
Join Date: Sep 2007
Location: Vancouver, Canada
Posts: 93
Thanks: 3
Thanked 1 Time in 1 Post
Default

Actually, nevermind. I'm just going to setup another machine as the slave.

The first problem I encountered was this:
/usr/libexec/mysqld: File '/var/log/mysql-bin.index' not found (Errcode: 13)
091011 22:59:21 [ERROR] Aborting

I changed:
log-bin to /var/log/mysql/mysql-bin.log (rather than /var/log/mysql/ which I misread the guide as)
and gave mysql permissions to /var/log/mysql

The alternative could be to use /var/lib/mysql...
Reply With Quote
  #5  
Old 12th October 2009, 09:23
v2k v2k is offline
Member
 
Join Date: Sep 2007
Location: Vancouver, Canada
Posts: 93
Thanks: 3
Thanked 1 Time in 1 Post
Default

I just noticed these comments on the howto; I'm running InnoDB, is this correct?

Quote:
You need to read the database replication documents a little more in depth; depending on your version of MySQL and wether or not you use InnoDB, you want to also include something like the following on the master server:

innodb_flush_log_at_trx_commit = 1
innodb_safe_binlog
sync-binlog = 1
log-bin = /path/to/mysql/data/master-bin
log-bin-index = /path/to/mysql/data/master-bin.index
Reply With Quote
  #6  
Old 12th October 2009, 09:56
v2k v2k is offline
Member
 
Join Date: Sep 2007
Location: Vancouver, Canada
Posts: 93
Thanks: 3
Thanked 1 Time in 1 Post
Default

I setup the master with those additional innodb settings.

My data files now look something like this:
Code:
4.0K mysql-bin.000001
4.0K mysql-bin.000002
4.0K mysql-bin.000003
4.0K mysql-bin.000004
 68K mysql-bin.000005
4.0K mysql-bin.index
mysql-bin.000005 seems to be the only file growing. The index file isnt changing size.

Here's my my.cnf:

Code:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
max_connections=500
query-cache-type = 1
query-cache-size = 128M
set-variable=long_query_time=2
log-slow-queries=/var/log/log-slow-mysql.log

# lines below setup logging for master
binlog-do-db=db4120
server-id=1
innodb_flush_log_at_trx_commit = 1
#innodb_safe_binlog
sync-binlog = 1
log-bin = /var/lib/mysql/mysql-bin
log-bin-index = /var/lib/mysql/mysql-bin.index

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Reply With Quote
  #7  
Old 15th October 2009, 11:13
v2k v2k is offline
Member
 
Join Date: Sep 2007
Location: Vancouver, Canada
Posts: 93
Thanks: 3
Thanked 1 Time in 1 Post
Default

I've followed the howto; have the db imported into the slave, but I'm having trouble getting the slave to connect to the master. I don't have iptables running on the master. I added:
ALL: 10.0.0.103

to the masters's hosts.allow.

Here's the error I'm seeing:

Quote:
091014 19:34:28 [ERROR] Slave I/O thread: error connecting to master 'slave_user @10.0.0.111:3306': Error: 'Lost connection to MySQL server at 'reading initial c ommunication packet', system error: 113' errno: 2013 retry-time: 60 retries: 86400
It also won't let me log into phpMyAdmin on the slave, reporting:

Quote:
#2013 Lost connection to MySQL server at 'reading initial communication packet', system error: 111
I assume this is the problem:

Quote:
telnet: Unable to connect to remote host (10.0.0.111): No route to host
Is there something I need to do to tell 10.0.0.111 to listen on 3306? I don't have skip-networking set...
Reply With Quote
  #8  
Old 16th October 2009, 01:14
v2k v2k is offline
Member
 
Join Date: Sep 2007
Location: Vancouver, Canada
Posts: 93
Thanks: 3
Thanked 1 Time in 1 Post
Default

I can ssh from each box to the other. I'm not sure why mysql won't connect. Any ideas?
Reply With Quote
  #9  
Old 16th October 2009, 01:23
v2k v2k is offline
Member
 
Join Date: Sep 2007
Location: Vancouver, Canada
Posts: 93
Thanks: 3
Thanked 1 Time in 1 Post
Default

Edit: nevermind, I'm an idiot... apparently iptables WAS running

Last edited by v2k; 16th October 2009 at 01:31.
Reply With Quote
  #10  
Old 16th October 2009, 01:37
v2k v2k is offline
Member
 
Join Date: Sep 2007
Location: Vancouver, Canada
Posts: 93
Thanks: 3
Thanked 1 Time in 1 Post
 
Default

Okay, so it now connects fine, but I get some errors.

The filenames and position numbers look correct:

Quote:
[Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000006' at position 102556477, relay log './slave-relay-bin.000005' position: 98

[Note] Slave I/O thread: connected to master 'slave_user@10.0.0.111:3306', replication started in log 'mysql-bin.000006' at position 102556477

091015 15:29:36 [ERROR] /usr/local/mysql/libexec/mysqld: Incorrect information in file: './db4120/table1.frm'
091015 15:29:36 [ERROR] /usr/local/mysql/libexec/mysqld: Incorrect information in file: './db4120/table1.frm'
This sounds like an innodb issue... more digging...
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
BIG Problem Postfix issue admins Installation/Configuration 11 13th November 2009 11:05
Problem installing MySQL on suse 10.3 Txchaser Installation/Configuration 20 18th April 2008 02:05
Management/system config/settings & /server/settings not working!! dactor Installation/Configuration 9 6th February 2008 10:11
Messed up ISPConfig-2.2.8 Upgrade Morons Installation/Configuration 4 29th November 2006 13:17
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 17:04


All times are GMT +2. The time now is 08:56.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.