MySQL DBA - Tips And Techniques: November 2014

13 Nov 2014

mysqlcheck

Useful utility to check, repair, analyze and optimize mysql tables. Just be aware that it locks the table, so don't run on busy production tables if they are clustered as probe may time out.

mysqlcheck --auto-repair --all-databases –uroot

Extracting a database from any mysqldump file

Eg to extract the database OPALGATEWAY from the dump file all-06-11-11-17:00-mk-staging-1.sql.gz .

zcat all-06-11-11-17:00-mk-staging-1.sql.gz | awk '{ if ( $0 ~ /CREATE DATABASE.*OPALGATEWAY.*/ ) a=1; if ( $0 ~ /CREATE DATABASE / && $0 !~ /CREATE DATABASE.*OPALGATEWAY.*/ ) a=0; if (a==1) print $0 }' | gzip - | cat - > /tmp/OPALGATEWAY.sql.gz

Set up slow query log

slow_query_log = 1

slow_query_log_file = /ebill-slave-1/mysql-logs/mysqld-slow.log

long_query_time = 6000

log-queries-not-using-indexes

log-slow-admin-statements

max_connections=13000

..and

cd mk-myacct-dbslave-2/ebill-slave-2/mysql-logs >

touch mysqld-slow.log

chown mysql:mysql mysqld-slow.log

chmod 755 mysqld-slow.log

Got a packet bigger than 'max_allowed_packet' bytes

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: mk-mysqlcluster-2-miscdb-rw.uk.intranet

Master_User: replusr_miscdb

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000009

Read_Master_Log_Pos: 5506942

Relay_Log_File: mysql-relay.000516

Relay_Log_Pos: 72730

Relay_Master_Log_File: mysql-bin.000009

Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: sc3_test_database

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

Relay_Log_Space: 73121

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

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 1153

Last_IO_Error: Got a packet bigger than 'max_allowed_packet' bytes

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

mysql> exit

SOLUTION:

1) Increase max_allowed_packet to 16M, by default its 1MB

my.cnf

[mysqldump]

max_allowed_packet = 16M

2) bounce database.

3) had to rebuild slave as well, as slave wasn't replicating even afterwards.

MySQL Privilege management for Stored Procedures and Functions

Execute permission for a Procedure or Function can be granted to Individually as shown below:

GRANT EXECUTE ON PROCEDURE `eonline`.`sp_getmailjoblist` TO 'eonline_rw'@'%' ;

GRANT EXECUTE ON FUNCTION `eonline`.`fn_getmailjobsub` TO 'eonline_rw'@'%' ;

To Grant execute permission on all Procedures and Functions of a particular database say 'eonline' to a particular user say 'eonline_rw' in this case, use the following syntax.

GRANT EXECUTE ON `eonline`.* TO 'eonline_rw'@'%' ;

This will grant the permissions for all the procedures and functions of this database to be executed from any host. To restrict this to a particular host , hostname may be specified in the grant statement.

Securing MySQL Database – removal of anonymous accounts

Anonymous MySQL accounts allow clients to connect to the server without specifying a user name. To remove anonymous accounts, connect to the server as the MySQL root user to access the mysql database, then issue the following statements:

mysql> select user,host FROM user WHERE User = '';

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

| user | host |

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

| | localhost |

| | mk-myacct-dbmaster-1 |

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

2 rows in set (0.00 sec)

mysql> DELETE FROM user WHERE User = '';

Query OK, 2 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

The DELETE statement removes accounts that have an empty value in the User column of the user table that lists MySQL accounts, and FLUSH PRIVILEGES tells the server to reload the grant tables so the changes take effect.

mysql> select user,host FROM user WHERE User = '';

Empty set (0.00 sec)

Install InnoDB Plugin for mysql 5.1

You can read about it here. "

The InnoDB Plugin offers new features, improved performance and scalability, enhanced reliability and new capabilities for flexibility and ease of use. Among the features of the InnoDB Plugin are "Fast index creation," table and index compression, file format management, new INFORMATION_SCHEMA tables, capacity tuning, multiple background I/O threads, and group commit.

For information about these features, see InnoDB Plugin 1.0 for MySQL 5.1 User's Guide.

To install innodb plugin on 5.1

1. Check what current state is

mysql> show plugins;

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

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

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

You can see by default the builtin innoDB plugin is installed. To use new features need to install the innoDB plugin as follows.

2. change mycnf to disable the default builtin innodb and to activate the plugin

cp $MYCNF/my.cnf $MYCNF/my.cnf.20100629

vi $MYCNF/my.cnf

...insert after innodb_open_files = 1000

ignore-builtin-innodb

plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

3.restart the database

../mysql-scripts/

4. Check

mysql> show plugins;

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

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

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

Install MYSQL on SOLARIS

Download the correct version. There are two versions – a tar file and a package. The package version installs as part of the Solaris OS , but then means we can have no further mysql installs. So we tend to download the tar version e.g. mysql-advanced-gpl-5.1.47-solaris10-x86_64.tar

Put the download into the bin directory eg /ebilldb-master/mysql-bin

untar the download file, this creates a directory called mysql-advanced-gpl-5.1.47-solaris10-x86_64

change permissions i.e.

chown -R mysql:mysql mysql-advanced-gpl-5.1.47-solaris10-x86_64

create a softlink to the untarred directory called mysql

ln -s mysql-advanced-gpl-5.1.47-solaris10-x86_64 mysql

copy a my.cnf and put into the …/mysql-data dir
edit the my.cnf

-change the bind address to the same as that in /etc/hosts

- change the path names

- if it's a slave then make sure the server-id is different from the master

Populate the mysql Database Tables and check logs for any errors

cd …/mysql-bin/mysql

./scripts/mysql_install_db --user=mysql --basedir=/ebilldb-master /mysql-bin/mysql --datadir=/ ebilldb-master /mysql-data --defaults-file=/ ebilldb-master /mysql-data/my.cnf

Failed to initialize the master info structure

ERROR:

091104 11:12:49 [ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.9339843' (relay_log_pos 233)

091104 11:12:49 [ERROR] Could not find target log during relay log initialization

091104 11:12:49 [ERROR] Failed to initialize the master info structure

Due to the way in which the master and slaves where stopped the relay logs got out of sync, hence replication would not restart.

Found the solution was to

a) make a note of Master_Log_File and Exec_Master_Log_Pos

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: mk-tacacs-1.auth.as9105.net

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: log-bin.000008

Read_Master_Log_Pos: 9684

Relay_Log_File: mysqld-relay-bin.9339637

Relay_Log_Pos: 233

Relay_Master_Log_File: log-bin.000008

Slave_IO_Running: No

Slave_SQL_Running: No

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

Relay_Log_Space: 0

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

b) reset the slave and bypass the relay logs by configuring the replication again.

reset slave;

CHANGE MASTER TO MASTER_HOST='mk-tacacs-1.auth.as9105.net'

CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000008';

CHANGE MASTER TO MASTER_LOG_POS=9684;

start slave;

Reset Auto Increment for a Table - ERROR 1062 (23000): Duplicate entry '2147483647' for key 1

PROVSERVICE table on VMS ANYPROV has a column which is auto increment

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

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

| PROVSERVICEID | int(11) | NO | PRI | NULL | auto_increment |

Development were getting an error suggesting an issue with the auto increment column

ERROR 1062 (23000): Duplicate entry '2147483647' for key 1

When we checked the next value for the auto increment field it was as follows

mysql> SHOW TABLE STATUS LIKE 'PROVSERVICE';

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

| PROVSERVICE | MyISAM | 10 | Dynamic | 2025198 | 108 | 219489492 | 281474976710655 | 190609408 | 0 | 2147483648 | 2009-10-23 15:50:01 | 2009-10-23 16:12:06 | 2009-10-23 15:53:03 | utf8_general_ci | NULL | | |

1 row in set (0.00 sec)

Although the PROVSERVICEID column is int(11) it would appear that 2147483647 is the maximum allowable value.

When we checked the data in the table we found that although this was the max value for PROVSERVICEID in the PROVSERVICE table, the next value below it was 2043538

select max(PROVSERVICEID) from PROVSERVICE where PROVSERVICEID < 2147483647;

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

| max(PROVSERVICEID) |

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

| 2043538 |

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

1 row in set (0.00 sec)

So somehow all values for PROVSERVICEID between 2043538 and 2147483647 had been missed.

To fix this, and to be able to use the missing values we had to

Update the row with PROVSERVICEID=2147483647 to have the next available value after 2043538

update PROVSERVICE set PROVSERVICEID=2043539 where PROVSERVICEID=2147483647;

Reset the auto increment value for the table

alter table PROVSERVICE auto_increment=1;

This takes the current max value and adds 1 to give the next auto increment value

Check that auto increment value had been reset correctly

SHOW TABLE STATUS LIKE 'PROVSERVICE';

| PROVSERVICE | MyISAM | 10 | Dynamic | 2025198 | 108 | 219489492 | 281474976710655 | 190608384 | 0 | 2043540 | 2009-10-23 16:34:42 | 2009-10-23 16:35:08 | 2009-10-23 16:37:45 | utf8_general_ci | NULL | | |

1 row in set (0.00 sec)

Resetting root password when locked out of mysql

http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html#resetting-permissions-unix

Uninstalling Mysql on FreeBSD

pkg_delete -x mysql

‘mysqld got signal 11’

It is not clear what caused this, but this is how it was fixed – eventually! The main issue that resulted from the failure was that the database would not restart and there were 1000's of relay log files in the $DATADIR

Killed all mysql processes and mysqld processes – had to use kill -9 for mysqld process

Removed all relay bin logs from $DATADIR

Restarted mysql using /usr/local/mysql/bin/mysqld_safe &

Stopped slave

Systems team stop mail

Recreated slave from master (oook)

Connect to database and issue 'flush logs'

Set master log file and llog file position

Restart slave

Everything should be working now!

Using mysqldump examples

backup_dbs_anyprov.sh

#!/bin/sh

#script to take consistent backup of ANYPROV database

date=`/bin/date +"%d-%m-%y-%H"`

umask 066

( /usr/local/bin/mysqldump -uroot –p** --databases ANYPROV --lock-all-tables | gzip) > /var/mysql-backups/anyprov-$date-`hostname -s`.sql.gz 2> /var/mysql-backups/anyprov-$date-`hostname -s`.sql.err.log

B. Take a no-data DDL dump of selected tables from a single database :

backup_dbs_anyprov_nodata.sh

#!/bin/sh

#script to take no-data backup of ANYPROV database selected tables

date=`/bin/date +"%d-%m-%y-%H"`

umask 066

( /usr/local/bin/mysqldump -uroot –p*** --no-data ANYPROV PROVSERVICEVW REPORTGEN | gzip) > /var/mysql-backups/anyprovnd-$date-`hostname -s`.sql.gz 2> /var/mysql-backups/anyprovnd-$date-`hostname -s`.sql.err.log

C. Take a data dump of selected tables from a single database :

backup_anyprov_tables.sh

#!/bin/sh

#script to take consistent backup of ANYPROV database

date=`/bin/date +"%d-%m-%y-%H"`

umask 066

( /usr/local/bin/mysqldump -uroot –p*** ANYPROV SERVICETEMPLATE SERVICEFEATURETEMPLATE RESPONSEDEFINITION | gzip) > /var/mysql-backups/anyprovtables-$date-`hostname -s`.sql.gz 2> /var/mysql-backups/anyprovtables-$date-`hostname -s`.sql.err.log

Setting Up mysql_profile

To make life easier, this profile needs to be created on every MySQL server we log on.

Connect as root and create the following file with correct locations.

cd /

touch /mysql_profile

chmod 600 /mysql_profile

vi /mysql_profile

export ERRORLOG=/local/db/mysql

export DATADIR=/local/db/mysql

export MYCNF=/etc

export SCRIPTDIR=

export BACKUPDIR=/var/mysql-backups

# some useful aliases

alias a='tail -500 $ERRORLOG/mk-stxx-1'

alias h='fc -l'

alias j=jobs

alias k='ls -lh'

alias g='egrep -i'

alias l='ls -alhrt'

alias df='df -h'

alias d='ls -lhrt | grep ^d'

alias p='ps -ef'

. /opt/tiscali/snmp/scripts/mysql/mySQL-conf.conf

alias m='mysql -u$user -p$pswd'

alias pass='cat $SCRIPTDIR/mySQL-conf.conf'

# Prompt

PS1=" `hostname` \${PWD} >"

export PS1

Then when logging onto a box, just run this profile and use the settings to get around easily.

sudo bash

. /mysql_profile

Check / Revoke access for a user

SINGLE USER:

To see which grants a user has do

show grants for sdas;

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

| Grants for sdas@% |

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

| GRANT USAGE ON *.* TO 'sdas'@'%' IDENTIFIED BY PASSWORD '*B79ACB61D4237708B6280EC0A5A1092E54A02849' |

| GRANT SELECT ON `ANYPROV`.* TO 'sdas'@'%' |

| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`RESPONSE` TO 'sdas'@'%' |

| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`FEATURE` TO 'sdas'@'%' |

| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`CUSTOMERVALUE` TO 'sdas'@'%' |

| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`SERVICE` TO 'sdas'@'%' |

| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`PRODUCT` TO 'sdas'@'%' |

| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`PROVPRODUCTATTR` TO 'sdas'@'%' |

| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`PROCESSENGINES` TO 'sdas'@'%' |

| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`PROVFEATURE` TO 'sdas'@'%' |

| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`REPORTGEN` TO 'sdas'@'%' |

| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`PROVPRODUCT` TO 'sdas'@'%' |

| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`FLOWEVENT` TO 'sdas'@'%' |

| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`ORDERS` TO 'sdas'@'%' |

| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`PROVSERVICEATTR` TO 'sdas'@'%' |

| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`CUSTOMER` TO 'sdas'@'%' |

| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`PROVSERVICE` TO 'sdas'@'%' |

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

17 rows in set (0.00 sec)

SHOW GRANTS FOR 'sdas'@'localhost';

To revoke the delete access do :

REVOKE DELETE ON `ANYPROV`.`RESPONSE` FROM sdas;

REVOKE DELETE ON `ANYPROV`.`SERVICE` FROM sdas;

REVOKE DELETE ON `ANYPROV`.`PRODUCT` FROM sdas;

REVOKE DELETE ON `ANYPROV`.`PROVPRODUCT` FROM sdas;

REVOKE DELETE ON `ANYPROV`.`ORDERS` FROM sdas;

REVOKE DELETE ON `ANYPROV`.`PROVSERVICE` FROM sdas;

MULTIPLE USER:

If requested to remove Delete privs from multiple users, you can use the following scripts to help

1) SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user WHERE user in ('sdas','sbhandari');

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

| CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') |

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

| SHOW GRANTS FOR 'sbhandari'@'%'; |

| SHOW GRANTS FOR 'sdas'@'%'; |

| SHOW GRANTS FOR 'sbhandari'@'localhost'; |

| SHOW GRANTS FOR 'sdas'@'localhost'; |

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

4 rows in set (0.00 sec)

Exit

2) cut and paste the SHOW GRANTS into a file /tmp/show_grants.sql

3) run it to generate the output statements, delete …

mysql -uroot –p*** -se "source /tmp/show_grants.sql" > /tmp/show_all_grants.sql

4) pull out all the delete statements only

grep -i DELETE /tmp/show_all_grants.sql > /tmp/revoke_deletes.sql

vi /tmp/revoke_deletes.sql

Then change the TO -> FROM

%s/TO/FROM

Also change @'%' -> ;

%s/@'%'/;

Also remove single quote

%s/'//g

Also change

%s/GRANT SELECT, INSERT, UPDATE,/REVOKE

SHOULD NOW LOOK LIKE:

REVOKE DELETE ON `ANYPROV`.`PROVSERVICE` FROM sdas;

5) then run it

Mysql> source /tmp/revoke_deletes.sql

Taking consistent mysql dumps

/usr/local/bin/mysqldump -uroot --p*** --databases prequal --lock-all-tables | gzip) > /home/backup/rebuild_prequal-`hostname -s`.sql.gz 2> /home/backup/rebuild_prequal-`hostname -s`.sql.err.log

I kept an eye on the database, and it queued all of the inserts until the backup finished, but allowed the selects to go through, an example of processlist is attached. Maybe we should consider this for some of the smaller databases and slaves

START /STOP MySQL daemon

Apart from the start/stop commands the below commands show the different commands that can also be used to start and stop mysql daemon especially if you have encountered the following errors when you were using the 'normal' start/stop commands:

ERROR! MySQL manager or server PID file could not be found!

Starting MySQL.................................. ERROR

1: On the server Search for mysqld_safe

cd /

find . -name mysqld_safe -print

(ie found : ./usr/local/mysql_5.0.24/mysql-standard-5.0.24a-freebsd6.0-i386/bin/mysqld_safe )

2: To Start mysql daemon:

cd /usr/local/mysql_5.0.24/mysql-standard-5.0.24a-freebsd6.0-i386/bin

./mysqld_safe --user=mysql &

3: To Stop mysql daemon:

cd /usr/local/mysql_5.0.24/mysql-standard-5.0.24a-freebsd6.0-i386/bin

./mysqladmin -uroot -pXXXX shutdown

MySQL On Demand Web Seminars

http://www.mysql.com/news-and-events/on-demand-webinars/

Error 134

090326 12:30:54 [ERROR] Got error 134 when reading table './ANYPROV/ORDERS'

Reference from Mysql Bug Database

http://lists.mysql.org/mysql/51228

perror 134

and get this result:

Unknown error 134

Record was already deleted (or record file crashed)

mysql> check table ORDERS;

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

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

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

2 rows in set (2.88 sec)

Mysql > repair table ORDERS;

Example of daily deletion of a table via crontab

00 08 * * * mysql -uroot -p*** -se 'delete from tacacs.accounting where date < date_sub(now(), interval 90 day);' > /tmp/delete_accounting.log 2>&1

Deleting master logs script ONLY after checking slaves dont need them

You can use this script to purge master logs older than the day of the current log. It checks the slaves are not using the older logs first.

Some prechecks:

1) create a repl_client user on each slave - i.e

GRANT REPLICATION CLIENT ON *.* TO 'repl_client'@'mk-dbxx-1' IDENTIFIED BY 'r3pl';

2) Put the master and slave user, pwd and hosts in the section below.

3) Set savedays to number of days of logs to keep if more than today+1.

4) Make sure perl DBD and CALC modules are installed.

perl –v ,

instmodsh (and enter l to list modules)

5) The purge statement will not work if the "show master logs;" doesn't match up with the actual log files on disk. This can happen if bin logs have been manually deleted, but the index file still thinks they are there. So to check it do

a) show master logs;

b) ls -l $DATADIR

c) if they are not in sync then remove all older ones

eg if 14 is the first one actually on the disk then run "purge master logs to "mysql-bin.000014";"

Crontab entry:

#HLakhan 25 Mar 09 - Purge master logs over a day old after checking slave

00 17 * * * /home/backup/purge.pl > /tmp/purge.log 2>&1

Script

vi purge.pl
cut and paste what is below & make necessary changes – pwds, server names

chmod 755 purge.pl

to run the script must do ./purge.pl - this tells it to use perl.

#!/usr/bin/perl

###########################################################################################################

# Script Name : purge.pl

# Usage : ./purge.pl

# Description :

# A Perl script for a Master with one or more Slaves. It allows different user/passwords for each slave.

# This one finds the oldest log in use by a slave.

# It purges the logs up to, but not including, X days before the date of that log, according to $savedays.

# That is, if the oldest log in use is dated 2008-11-14 and you set $savedays = 1,

# it will purge all logs dated up to and including the last log from 2008-11-12, not purging those from 2008-11-13.

# It uses the Perl module Date::Calc, available at cpan.org, to find the previous day's date. Also DBD mysql.

# I have commented the lines to leave out if you do not want install that module to save the previous day's logs.

# It will not purge logs from the same date as the oldest log in use.

# If run as a cron job, the print statements should cause an email to be sent to the cron job user.

# You need the SUPER privilege for this operation.

# The script does two sanity checks, exiting if either fails:

# 1. Check that all logs in use by slave(s) exist on the master. If not, it prints the log file name(s), and slave host using it.

# 2. Check that the oldest log on the server is older than the date you are purging to.

# Also, the purge statement will not work if the "show master logs;" doesn't match up with the actual log files on disk.

# This can happen if bin logs have been manually deleted, but the index file still thinks they are there.

# So to check it do

# a) show master logs;

# b) ls -l $DATADIR

# c) if they are not in sync then remove all older ones

# eg if 14 is the first one actually on the disk then run "purge master logs to "mysql-bin.000014";"

#############################################################################################################

# Script Name : purge.pl

# Usage : ./purge.pl

# Prerequisites : 1) create a repl_client user on each slave - i.e

# GRANT REPLICATION CLIENT ON *.* TO 'repl_client'@'mk-dbmaster-1.portal.uk.intranet' IDENTIFIED BY 'r3pl_cl13nt';

# 2) Put the master and slave user, pwd and hosts in the section below.

# 3) Set savedays to number of days of logs to keep if more than today+1.

# 4) Make sure perl DBD and CALC modules are installed. I.e perl –v , instmodsh (and enter l to list modules)

# Author Version Date Comment

# -----------------------------------------------------------------------------------------------------------

# Fred McIntyre 1 15 Nov 08 Initial version from the web

# Harjit Lakhan 2 23 Mar 10 Made some changes to get it working

use strict;

use DBI;

# For saving previous day's logs

use Date::Calc qw(Add_Delta_Days);

# Master connection

# master user requires Super_priv

my $mstr_dbuser = 'root';

my $mstr_dbpassword = '**';

my $mstr_dbdsn = ('dbi:mysql:host=localhost');

#to specify the socket if its non-standard use following syntax

#my $mstr_dbdsn = ('dbi:mysql:host=localhost;mysql_socket=/tmp/mysql-master.sock');

# Change if appropriate.

my $log_dir = '/var/lib/mysql';

# Name for binary logs, from my.cnf: log-bin=

my $log_bin = 'mysql-bin';

# For saving previous day's logs

# Days prior to currently used log file to not purge

my $savedays = 1;

# Slave connection(s)

# slave user requires Repl_client_priv

#for more than one slave just comma separate eg ('repl_client','repl_client')

my @slv_dbusers = ('repl_client');

#for more than one slave just comma separate eg ('pwd','pwd2')

my @slv_dbpasswords = ('r3pl_cl13nt');

#for more than one slave just comma separate eg ('dbi..1','dbi...2')

my @slv_dbdsns = ('dbi:mysql:host=mk-dbslave-1.portal.uk.intranet');

#to specify the socket if its non-standard use following syntax

#my @slv_dbdsns = ('dbi:mysql:host=;mysql_socket=/tmp/mysql-slave.sock');

### No changes needed below here. ##

$log_dir .= '/' unless $log_dir =~ m|/$|;

# Find the date of the oldest log in use by a slave. Use file mtime.

my $time = time;

# Add an hour to make sure $time is later than newest possible log mtime.

$time += 3600;

my ($no_connect,$not_exist);

for (my $i = 0; $i < @slv_dbdsns; $i++) {

my $slv_dbdsn = $slv_dbdsns[$i];

my $slv_dbuser = $slv_dbusers[$i];

my $slv_dbpassword = $slv_dbpasswords[$i];

my $slv_dbh = DBI->connect($slv_dbdsn,$slv_dbuser,$slv_dbpassword);

print 'MySQL slave connect '.$slv_dbdsns[$i].$slv_dbusers[$i]."\n";

#print 'MySQL slave connect '.$slv_dbdsns[$i].$slv_dbusers[$i].$slv_dbpasswords[$i]."\n";

unless ($slv_dbh) {

$no_connect .= 'MySQL binary log purge: error connecting to slave database '.

$slv_dbdsns[$i].' - '.$DBI::errstr."\n";

next;

}

# Use field name to get current log name.

my $sth = $slv_dbh->prepare('show slave status');

$sth->execute;

my $rows = $sth->fetchall_arrayref({});

$sth->finish;

$slv_dbh->disconnect;

if (-f $log_dir.$rows->[0]->{Master_Log_File}) {

# Get mtime of this log file.

my $this_time = (stat($log_dir.$rows->[0]->{Master_Log_File}))[9];

$time = $this_time if $this_time < $time;

} else {

$not_exist .= 'MySQL binary log purge: major error - oldest log in use, by slave host '.

$slv_dbdsns[$i].', does not exist on master: '.

$log_dir.$rows->[0]->{Master_Log_File}."\n";

}

if ($no_connect) {

print $no_connect."\n";

exit;

}

if ($not_exist) {

print $not_exist."\n";

exit;

}

my ($day,$mon,$year) = (localtime($time))[3..5];

$year += 1900;

$mon++;

# For saving previous day's logs

$savedays *= -1 unless $savedays < 0;

($year,$mon,$day) = Add_Delta_Days($year,$mon,$day,$savedays);

$mon = '0'.$mon if $mon < 10;

$day = '0'.$day if $day < 10;

# Check if oldest existing log is on or before purge date

# Purge works okay if not, but best to warn in case it indicates problems

my $firstlog = `head -n 1 $log_dir$log_bin.index`;

chomp $firstlog;

my $logtime = (stat($log_dir.$firstlog))[9];

my ($Lsec,$Lmin,$Lhour,$Lday,$Lmon,$Lyear) = (localtime($logtime))[0..5];

$Lyear += 1900;

$Lmon++;

$Lmon = '0'.$Lmon if $Lmon < 10;

$Lday = '0'.$Lday if $Lday < 10;

$Lhour = '0'.$Lhour if $Lhour < 10;

$Lmin = '0'.$Lmin if $Lmin < 10;

$Lsec = '0'.$Lsec if $Lsec < 10;

if ($Lyear.$Lmon.$Lday.$Lhour.$Lmin.$Lsec >= $year.$mon.$day.'000000') {

print 'MySQL binary log purge: oldest log on master is later than, or equal to, '.

$year.'-'.$mon.'-'.$day.' 00:00:00'."\n";

exit;

}

my $mstr_dbh = DBI->connect($mstr_dbdsn,$mstr_dbuser,$mstr_dbpassword);

#my $mstr_dbh = DBI->connect('dbi:mysql::'.$mstr_dbhost,$mstr_dbuser,$mstr_dbpassword);

#print 'MySQL master connect '.$mstr_dbdsn.$mstr_dbuser.$mstr_dbpassword."\n";

unless ($mstr_dbh) {

print 'MySQL binary log purge: error connecting to master database - '.$DBI::errstr."\n";

exit;

}

###comment out while testing

$mstr_dbh->do('purge master logs before "'.$year.'-'.$mon.'-'.$day.' 00:00:00"');

$mstr_dbh->disconnect;

print 'MySQL binary log purge: purged before '.$year.'-'.$mon.'-'.$day."\n";

## end ##

MySQL DBA - Tips and Techniques Magazine

13 Nov 2014

Subscribe To