Wednesday, January 25, 2017

How to clear old mysql bin log files in mysql 5.7

We have seen how to do mysql master slave replication in one of our previous post. Once we setup mysql server as master with traditional replication, it will start creating bin-logs. This will go on and on, depending on the transactions happening on you database it may eat all your storage space.

One day I checked the disk usage of one of our mysql instance and found that 82% of disk is utilized!

[root@mysql mysql]# df -hT
Filesystem           Type   Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                            ext4    50G   39G  8.6G  82% /
tmpfs                   tmpfs  3.0G     0  3.0G   0% /dev/shm
/dev/xvda1           ext4   477M   55M  398M  12% /boot
/dev/mapper/VolGroup-lv_home
                           ext4    94G  642M   89G   1% /home

You can check in you master mysql console which is the current bin-log file.
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000089 |    25682 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+

Then I checked the size of /var/lib/mysql and found a lot of bin-log files. When I checked the master status I found that the current bin file is mysql-bin.000089. So I can delete the files till that. Or the files till the last week.

And you can delete all the old bin-log files upto the latest one

mysql> PURGE BINARY LOGS TO 'mysql-bin.000075';
Query OK, 0 rows affected (0.91 sec)

You can actually automate this. You need to specify the expire log days variable in my.cnf of the master mysql server.

for example.
vim /etc/my.cnf
under [mysqld] add
expire_logs_days=7

Restart the mysql service and you are done.

How to set up Master Slave replication in mysql 5.7

We need to keep our data safe so we do the database replication. In mysql the most common replication is Master-Slave replication. In this post we will see how we can setup a mysql server as master and another one as slave. We are using Mysql version 5.7 in this example on Centos Linux 6.8.

We need two systems

1. Master
IP : 172.16.0.50
2. Slave
IP : 172.16.0.60

Configuring Master:
Install mysql community server version from mysql website and edit the my.cnf file as below
vim /etc/my.cnf
bind-address = 0.0.0.0
server-id = 1
log-bin = mysql-bin
binlog-ignore-db =mysql

innodb_flush_log_at_trx_commit=1
sync_binlog=1
expire_logs_days=7

Restart the mysql server.
/etc/init.d/mysqld restart

Once the mysql-server is running, you can check the status of the master with the following command.
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000089 |    35626 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Configuring slave:

Install mysql community server version from mysql website and edit the my.cnf file as below
vim /etc/my.cnf
bind-address = 0.0.0.0
server-id = 2
relay-log=mysqld-relay-bin

restart the mysql server and in the mysql console, set the details of the Master server from where client has to receive data.

mysql> CHANGE MASTER TO  MASTER_HOST='172.16.0.50',MASTER_USER='replication_user', MASTER_PASSWORD='abcdefgh', MASTER_LOG_FILE='mysql-bin.000089', MASTER_LOG_POS=23694;

If the slave service is already running then you will get the following error.
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.


Start the slave, using the --skip-slave-start option so that replication does not start.

Or you can use the command
mysql>STOP SLAVE;

Then once the master details are set, you can start slave service.
 mysql>START SLAVE;

Once the Slave is running and receiving the data from the master, you can see the slave status as follows.

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.0.50
                  Master_User: replication_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000089
          Read_Master_Log_Pos: 35626
               Relay_Log_File: mysqld-relay-bin.000168
                Relay_Log_Pos: 7186
        Relay_Master_Log_File: mysql-bin.000089
             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: 35626
              Relay_Log_Space: 9874
              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:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: a7e33c46-3463-11e5-a325-2e092b9265bc
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

mysql>

In the master server, you can see how many slaves are connected to that by issuing the following command.

mysql> SHOW SLAVE HOSTS;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | c18cf848-2efa-11e6-8534-ee8445cdbbe1 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)


References:
0. https://dev.mysql.com/doc/refman/5.7/en/replication.html
1. https://dev.mysql.com/doc/refman/5.7/en/replication-howto-masterbaseconfig.html
2. https://dev.mysql.com/doc/refman/5.7/en/replication-howto-masterstatus.html
3. https://dev.mysql.com/doc/refman/5.7/en/replication-setup-slaves.html
4. https://dev.mysql.com/doc/refman/5.7/en/replication-administration-status.html