Live MySQL Replication/Backup

[v2k]

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.

[falko]

http://www.howtoforge.com/back_up_my..._interruptions

[v2k]

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?

[v2k]

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...

[v2k]

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

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

[v2k]

I setup the master with those additional innodb settings.

My data files now look something like this:

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:

[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

[v2k]

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:

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:

#2013 Lost connection to MySQL server at 'reading initial communication packet', system error: 111

I assume this is the problem:

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...

[v2k]

I can ssh from each box to the other. I'm not sure why mysql won't connect. Any ideas?

[v2k]

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

[v2k]

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

The filenames and position numbers look correct:

[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...