LinuxQuestions.org
Support LQ: Use code LQ3 and save $3 on Domain Registration
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 09-15-2010, 01:46 PM   #1
ajayan
Member
 
Registered: Dec 2007
Posts: 89

Rep: Reputation: 16
Purging Binary logs in MySQL Replication


I had Configured a MySQL Master-Slave replication.It seems that the binary logs steals so much space of My storage.

i.)My Master

show master status;

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000144 | 475823 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

ii.)My Slave.

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.277.55.141
Master_User: abcuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000144
Read_Master_Log_Pos: 479037
Relay_Log_File: server-relay-bin.000005
Relay_Log_Pos: 475925
Relay_Master_Log_File: mysql-bin.000144
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 479037
Relay_Log_Space: 479456
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

If i remove all binary files up tp mysql-bin.000144 using "PURGE",will it affect my existing database/any data loss..
 
Old 09-15-2010, 01:51 PM   #2
EricTRA
LQ Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295
Hello,

From the MySQL site:
Quote:
To safely purge binary log files, follow this procedure:

On each slave server, use SHOW SLAVE STATUS to check which log file it is reading.

Obtain a listing of the binary log files on the master server with SHOW BINARY LOGS.

Determine the earliest log file among all the slaves. This is the target file. If all the slaves are up to date, this is the last log file on the list.

Make a backup of all the log files you are about to delete. (This step is optional, but always advisable.)

Purge all log files up to but not including the target file.
These are points to consider before purging. Basically purging logs up to a certain log doesn't effect the database but it's always safe practice to make a dbdump before, just in case, you never know if Murphy is around. The purge command deletes the logs up to the one you indicate moving that one to the first position.

Kind regards,

Eric
 
1 members found this post helpful.
Old 09-16-2010, 07:21 AM   #3
ajayan
Member
 
Registered: Dec 2007
Posts: 89

Original Poster
Rep: Reputation: 16
Thanks Eric,

Here is my df -h

Filesystem Size Used Avail Use% Mounted on
/dev/sda1 15G 8.1G 6.0G 58% /

and

du -sh /var/log/mysql --->4.6G /var/log/mysql.

I had taken DB dump and its around 2.7 GB and also i moved it in to External Storage.

So i think if i remove all files up to the current BINARY LOG,Then the used space in System will be around 3.5 GB.(Consider even my DB size is around 2.7 GB.)..So i guess if i remove logs,it will lead to data loss.In mysql forums they haven't mentioned any thing detail.

Thanks,
Ajayan
 
Old 09-16-2010, 07:50 AM   #4
EricTRA
LQ Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295
Hi,

Purging the binary logs doesn't affect the data at all. Let me state that a bit more careful, it shouldn't affect data and/or result in data loss at all, if executed correctly. They are, as stated by the site referenced above, files that contain information about data modifications:
Quote:
The binary log is a set of files that contain information about data modifications made by the MySQL server
Of course it's always good practice to have a current backup. So just go ahead purging, following the steps indicated, and check your database(s) after that.

Kind regards,

Eric
 
Old 09-16-2010, 10:56 AM   #5
ajayan
Member
 
Registered: Dec 2007
Posts: 89

Original Poster
Rep: Reputation: 16
Smile

Hi Eric,

I had tried PURGE up to my last binary log and it sounds like hadn't affected DB.So its more clear for me "PURGE binary log Will not affect DB"

Thanks for your Comments.
By the way i am going through Mysql failover (Manual Mode)..i expect some more help from your side...

Thanks & regardas
Ajayan
 
Old 09-16-2010, 11:07 AM   #6
EricTRA
LQ Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295
Hi,

It's always great to get confirmation. Glad it worked out for you. If you consider this question/problem solved then please mark it as such using the Thread Tools.

I'll be looking forward to your next question, as will the whole community

Kind regards,

Eric
 
Old 09-18-2010, 06:56 AM   #7
ajayan
Member
 
Registered: Dec 2007
Posts: 89

Original Poster
Rep: Reputation: 16
Eric,
Hope you are doing well,

One more query.
If i am adding one more slave to the Existing master,is there any option to replicate existing(old) data rather than mysqldump.I mean if i have backup of all the binary logs on master,can i set Slave to use old MASTER_LOG_FILE and position.Does it make any sense..?

Thanks,
Ajayan
 
Old 09-18-2010, 07:48 AM   #8
quanta
Member
 
Registered: Aug 2007
Location: Vietnam
Distribution: RedHat based, Debian based, Slackware, Gentoo
Posts: 724

Rep: Reputation: 100Reputation: 100
Quote:
Originally Posted by ajayan View Post
One more query.
If i am adding one more slave to the Existing master,is there any option to replicate existing(old) data rather than mysqldump.I mean if i have backup of all the binary logs on master,can i set Slave to use old MASTER_LOG_FILE and position.Does it make any sense..?

Thanks,
Ajayan
http://dev.mysql.com/doc/refman/5.0/...nalslaves.html
 
1 members found this post helpful.
Old 09-18-2010, 09:04 AM   #9
EricTRA
LQ Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295
Quote:
Originally Posted by ajayan View Post
Eric,
Hope you are doing well,

One more query.
If i am adding one more slave to the Existing master,is there any option to replicate existing(old) data rather than mysqldump.I mean if i have backup of all the binary logs on master,can i set Slave to use old MASTER_LOG_FILE and position.Does it make any sense..?

Thanks,
Ajayan
Hi,

The best solution has already been pointed to by quanta.

Kind regards,

Eric
 
Old 09-22-2010, 12:47 AM   #10
ajayan
Member
 
Registered: Dec 2007
Posts: 89

Original Poster
Rep: Reputation: 16
Hi friends,

I am getting stuck with one problem in MySQL fail-over.Currently my DB servers were set up on AWS (Amazone Web Service) and having a master (DB 1) and slave (DB 2) which is currently replicated.In case of master (DB 1) fail-over,the slave ((DB 2) will be promoted as master and new slave (DB 3) will be created from last snapshot of original slave (DB 2).Now the problem is,if master (DB 1) has 100 records and obviously the slave (DB 2) also have 100 records.But last snapshot which had taken 10 minutes before might have only 95 records.So when i am creating new slave from last snapshot then new master (DB 2) holds 100 and new slave (DB 3) hold only 95 records.As per Information from MySQL docs,i have to dump data from master to slave to fill missing data.since my data volume is high, it is not going be a practical solution..I had also tried with mysqlbinlog utility,so that i can read log position from mysql bin logs and compare it with new slave's postion,then i tried to point to that particular location.But that ends up in table duplication/corruption)..

Can any body suggest me, how to fill missing data on new slave...

Advance Thanks,
Ajayan
 
Old 09-22-2010, 01:03 AM   #11
EricTRA
LQ Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295
Hello,

Why would you only activate the second slave when the first one becomes master? In other words you can have more then one slave to a master. In that setup if the master fails, slave #1 becomes master and slave #2 stays slave and is always up to date. No need for snapshots and no risk loosing data because of the use of snapshots/dumps.

Have a look at this description, it explains it more in detail.

Kind regards,

Eric
 
Old 09-22-2010, 01:08 AM   #12
quanta
Member
 
Registered: Aug 2007
Location: Vietnam
Distribution: RedHat based, Debian based, Slackware, Gentoo
Posts: 724

Rep: Reputation: 100Reputation: 100
Did you "FLUSH TABLES WITH READ LOCK;" before dumping the data from Master?

Try again with following steps:

On the Master:
- flush tables with read lock;
- dump db with --master-data option
- unlock tables

On the Slave:
- stop slave
- reset slave
- import db to Slave
- start slave
- show slave status\G
 
Old 09-22-2010, 02:28 AM   #13
ajayan
Member
 
Registered: Dec 2007
Posts: 89

Original Poster
Rep: Reputation: 16
Eric,
The master in region 1(Us east) and slave in Region2(Us West).If regions 1 fails we have to ensure that database in up in region 2.Client can't afford setting up 2 slave DB's..o They just want to pay for an Instance at the time of master fail.Thats why i am looking for a Creating Slave 2 from a snapshot.My concern is,will it possible to update missing data from Master's Binary log (without duplication/corrupt).

Regards,
Ajayan
 
Old 09-22-2010, 02:29 AM   #14
ajayan
Member
 
Registered: Dec 2007
Posts: 89

Original Poster
Rep: Reputation: 16
Thanks quanta,
I will try and will update you as soon as possible.

Thanks,
Ajayan
 
Old 09-22-2010, 02:37 AM   #15
EricTRA
LQ Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295Reputation: 1295
Quote:
Originally Posted by ajayan View Post
Eric,
The master in region 1(Us east) and slave in Region2(Us West).If regions 1 fails we have to ensure that database in up in region 2.Client can't afford setting up 2 slave DB's..o They just want to pay for an Instance at the time of master fail.Thats why i am looking for a Creating Slave 2 from a snapshot.My concern is,will it possible to update missing data from Master's Binary log (without duplication/corrupt).

Regards,
Ajayan
Hi,

If that's the case then the only sure and certain option I see is what quanta pointed out. Locking the tables, make a dump and get that over to the other server. This will mean down time as I understand it since the data volume is rather on the high size.

That is if you want to stay with master-slave setup. Another option is to configure the two nodes in master-master configuration. I have that setup in a production environment here and I can have one node go down for 10 days max and it will synchronize when started within this timeframe.

Don't know if that might interest your customer since there's no extra hardware involved?

Kind regards,

Eric
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

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 Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Mysql replication failed after mysql reboot ajayan Linux - Newbie 8 09-08-2010 12:00 PM
binary logs parimi_777 Linux - General 3 08-25-2010 11:06 AM
mysql replication twistedpair Linux - General 7 06-23-2006 11:10 AM
how to view binary logs true_atlantis Linux - Newbie 4 04-05-2006 09:06 PM
mysql replication cheng Linux - Software 1 08-22-2002 03:11 PM


All times are GMT -5. The time now is 10:09 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration