Tech Notes:- Mysql Replication Error 'Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file''

I kept my slave mysql db down for a week and then when I switched on the slave db server and checked the replication status, I started seeing this error.



Last_IO_Errno: 1236

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'



The most probable reason for this error is that the binary log required for the replication by slave servers no longer exists on the master database server. This could have happened because someone might have manually deleted the binary files on the MySQL master either by removing the files from the file system or using the purge binary logs command.

Another reason could be that a variable has been set in the conf file to expire the logs. In my case, it was this one and the variable was set to 2 days

expire_logs_days=2

Let's have a detailed look at SHOW SLAVE STATUS and SHOW BINARY LOGS on master

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 172.25.1.20
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000023
          Read_Master_Log_Pos: 94955731
               Relay_Log_File: mysqld-relay-bin.000007
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000023
             Slave_IO_Running: No
            Slave_SQL_Running: Yes

mysql> SHOW BINARY LOGS;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000029 |      1315 |

+------------------+-----------+

1 row in set (0.21 sec)

So reading it all to gether, slave is looking for the master log file mysql-bin.000023 at position 94955731. But the master is at  mysql-bin.000029  and I dont have the file mysql-bin.000023. So this basically means that the mysql slave stopped replcation while at mysql-bin.000023.

How do we solve this?

The simplest way as well as the advisable approach is to dump the mysql from the master, restore it to slave and then restart the replication. But that may take time if the database is huge and there is no way to take the dump without impacting production performance.

If its a test system and you are ok with losing some data in replication, the best bet would be to set the position of the slave to the start of the new available master file.  

STOP SLAVE;

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000029', MASTER_LOG_POS=4;

START SLAVE;

You may need to skip some positions sometimes. If you have patience and time, the best option is to examine the events in the log and point to the position accordingly. You can do it using the command below

sudo mysqlbinlog /var/lib/mysql/mysql-bin.000029 | more

Tech Notes :- Ansible Ad hoc Command Examples

Ansible ad-hoc commands are useful when you want to run something quickly on multiple machines. Here I am providing some examples of different situations that might help. Let's assume that our inventory name is 'servers'  and  we the names of all servers in this file



Checking the status of all servers in our inventory file 'servers'
# ansible  'servers' -m ping

For checking the uptime of all servers
# ansible 'servers'  -a 'uptime'

Create a user sudeep and set the password on all servers in the inventory file servers
# ansible 'servers' -m user -a "name=sudeep password=redhat"

Updating  Linux server with latest updates
# ansible 'servers' -m package -a 'name=* state=latest'

Installing a specific package in all servers in the inventory
# ansible 'servers'  -m package -a "name=httpd state=present"

Reboot the servers and wait for it to come back
# ansible 'servers' -b -B 1 -P 0 -m shell -a "sleep 5 && reboot"

Remove a text from a file
# ansible 'servers'  -m lineinfile -a "dest=/etc/somefile.txt regexp='texttoberemoved' state=absent"

Checking entries in a file
# ansible 'servers' -m command -a 'grep listen  /etc/nginx/conf.d/nginx.conf'

Replacing strings in a file on multiple servers
# ansible 'servers' -m replace -a 'path=/etc/nginx.conf.d/nginx.conf regexp="192.168.1.20" replace=192.168.1.100'

Commenting a line in a file based on finding a pattern in that line.For example comment the line containing the word backup in a cron job

ansible 'servers' -m replace -a 'path=/etc/cron.d/backup_schedule regexp="(.*backup.*)" replace="#\1"'

Check the hypervisor on remote machines
# ansible 'servers' -m setup -a "filter=ansible_virtualization_*"

List remote files
# ansible 'servers' -m command -a 'ls -l  /var/www/html'

Copy files
# ansible 'servers' -m copy -a 'src=/home/sudeep/test.php dest=/var/www/html/test.php owner=sudeep group=sudeep'

List all variables a partiucular host can see
# ansible -m debug -a 'var=hostvars[inventory_hostname]' foo.com

Schedule a reboot
# ansible 'foo.com  -a  '/sbin/shutdown -r +5'

Checking the remote git version on multiple servers
# ansible 'servers' -m command -a "git status  args: chdir=/var/www/html/sudeep"

List all tasks in a playbook
# ansible-playbook --list-tasks playbooks/app-server.yml

Get an excerpt from a file
# ansible 'servers' -a 'grep -A 2 port /etc/supervisord.conf'

Remove a port from firewall where the firewall is configured using firewalld
# ansible 'server' -m command -a "firewall-cmd --remove-port='8080/tcp' --zone=internal --permanent "

Reload firewalld
#ansible 'server' -m command -a  'systemctl reload firewalld'

List all opened ports in public zone
#ansible 'server' -m command -a 'firewall-cmd --list-ports  --zone=public'

List all services in public zone
#ansible 'server' -m command -a 'firewall-cmd --list-services  --zone=public'

List all sources in public zone
#ansible 'server' -m command -a 'firewall-cmd --list-sources  --zone=public'

Adding a server to proxysql with a default_hostgroup of 80, writing the changes to disk and loading it to runtime.
ansible 'myserver.example.com' -m proxysql_backend_servers -a 'hostgroup_id=80 hostname=172.16.1.55 load_to_runtime=True login_host=proxysql.example.com login_password=Password123 login_port=6032 login_user=proxysql-admin save_to_disk=True state=present status=ONLINE comment="My new mysql database server"'


We can send the output of the ansible command(or any command) both to a file as well as stdout.See the example below
ansible 'servers' -m command -a "git status  args: chdir=/var/www/html/sudeep" 2>&1 | tee ~/outfile