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

Hello,

From the MySQL site:
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.

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

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

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

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

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

1 members found this post helpful.

Hi,

The best solution has already been pointed to by quanta.

Kind regards,

Eric

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

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

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

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

Thanks quanta,
I will try and will update you as soon as possible.

Thanks,
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