Mani's Blog

January 7, 2013

InnoDB: table %s/%s: unknown table type 33

Filed under: MySQL,Xtrabackup — mani @ 11:21 pm

Ran into issue while restoring and recovering using xtrabackup.  innobackupex-1.5.1 –apply-log ./

130107 20:32:49 InnoDB: table bac002/_user: unknown table type 33
130107 20:32:49 InnoDB: table bac002/_user: unknown table type 33
130107 20:32:49InnoDB: Assertion failure in thread 1230002496 in file ibuf0ibuf.c line 1309
InnoDB: Failing assertion: len % DATA_NEW_ORDER_NULL_TYPE_BUF_SIZE <= 1
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
innobackupex-1.5.1: Error:
innobackupex-1.5.1: ibbackup failed at /usr/bin/innobackupex-1.5.1 line 464.
root@@xnomdb912n2:/local/mysql/data#

Solution:

I was using incorrect my.cnf file,  in my case I had incorrect binlog-format, and few other parameter.  Once I fixed them then restore/recovery worked just fine.

Advertisements

March 30, 2012

xtrabackup: Fatal error: cannot find ./xtrabackup_logfile

Filed under: MySQL,Xtrabackup — mani @ 4:37 pm

We have a process to validate our backup every quarter.  I ran into following errors on one of the databases.  Did enough search to figure out the solution, initially thought it could be an xtrabackup bug.  Then turn out to be we were holding incomplete xtrabackup.  Backup failed quietly since there was another etl process running at the same time, due to heavy load and lock on the tables backup process died.

We are really lucky that we were able to catch it during the validation.   We have rescheduled the ETL job and added more checks to the backup monitoring.

Errors:

120228 19:25:55 innobackupex-1.5.1: Starting ibbackup with command: xtrabackup –prepare –target-dir=/local/mysql/data

xtrabackup Ver 1.0 Rev 113 for 5.0.84 unknown-linux-gnu (x86_64)
xtrabackup: cd to /local/mysql/data
xtrabackup: Error: cannot open ./xtrabackup_checkpoints
xtrabackup: error: xtrabackup_read_metadata()
xtrabackup: This target seems not to have correct metadata…
120228 19:25:55 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
xtrabackup: Warning: cannot open ./xtrabackup_logfile. will try to find.
120228 19:25:55 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
xtrabackup: Fatal error: cannot find ./xtrabackup_logfile.
xtrabackup: Error: xtrabackup_init_temp_log() failed.
innobackupex-1.5.1: Error:
innobackupex-1.5.1: ibbackup failed at /usr/bin/innobackupex-1.5.1 line 464.

InnoDB: Database page corruption on disk or a failed

Filed under: MySQL — mani @ 4:20 pm

I  ran into Database page corruption  error while I was restoring MySQL Xtrabackup.  Tried previous day backup then same errors.

Errors:

InnoDB: End of page dump
120329 17:59:38 InnoDB: Page checksum 1255360450, prior-to-4.0.14-form checksum 1044442903
InnoDB: stored checksum 1255360450, prior-to-4.0.14-form stored checksum 1429906423
InnoDB: Page lsn 512 461193189, low 4 bytes of lsn at page end 461874216
InnoDB: Page number (if stored to page already) 964,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 411
InnoDB: Page may be an index page where index id is 0 853
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 964.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
innobackupex-1.5.1: Error:
innobackupex-1.5.1: ibbackup failed at /usr/bin/innobackupex-1.5.1 line 464.

Solution:

Edit the file /etc/my.cnf and add parameter or variables  innodb_force_recovery=2 then apply the logs.  Everything went well.

Edit the file /etc/my.cnf and comment or remove innodb_force_recovery=2 then restart the mysql.

This seems to be fixed the issue.

 

November 15, 2011

Sample MySQL my.cnf file for OLTP

Filed under: Database,MySQL — mani @ 11:54 pm

This is sample my.cnf file for MySQL server running on 32G memory and 200G data for OLTP operations.   With Queries per second avg: 1321.714 and 35 threads.  Configured with Statement based replication.

[client]
socket = /tmp/mysql.sock
port = 3306

[mysql]
prompt = (\\u@\\h) [\\d]>\\_
default-character-set = utf8

[mysqld]
core-file
socket = /tmp/mysql.sock
port = 3306
pid-file = /local/mysql/data/mysqld.pid
old-passwords = 0
skip-name-resolve
#basedir = /usr/bin
datadir = /local/mysql/data
back_log = 100
max_connections = 1000
max_connect_errors = 10000
key_buffer_size = 16M
myisam_sort_buffer_size = 32M
max_tmp_tables = 64
read_buffer_size = 8M
read_rnd_buffer_size = 8M
sort_buffer_size = 32M
table_cache = 3000
query_cache_limit = 16M
query_cache_size = 0M
query_cache_type = 0
thread_cache = 50
thread_stack = 512k
wait_timeout = 500
interactive_timeout = 500
net_read_timeout = 15
net_write_timeout = 30
tmp_table_size = 64M
max_heap_table_size = 512M
group_concat_max_len = 5120
max_allowed_packet = 256M
local_infile = 0
character_set_client = utf8
character_set_server = utf8
collation-server = utf8_general_ci
skip-character-set-client-handshake

performance_schema
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000

slow-query-log = 1
long_query_time = 1
log-queries-not-using-indexes
slow-query-log-file = /var/log/mysqld/mysqld.slow.log

default_storage_engine = INNODB
innodb_file_format = barracuda
innodb_file_per_table = 1
#innodb_use_native_aio = 0

innodb_log_group_home_dir = /local/mysql/data
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M

transaction_isolation = REPEATABLE-READ
innodb_data_home_dir = /local/mysql/data
innodb_data_file_path = ibdata1:512M:autoextend
innodb_buffer_pool_size = 24G
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_thread_concurrency = 8
innodb_additional_mem_pool_size = 20M
innodb_doublewrite = 1
innodb_checksums = 1
innodb_io_capacity = 400
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_locks_unsafe_for_binlog = 0
innodb_max_purge_lag = 0
innodb_fast_shutdown = 0

# replication
server-id = 1
sync_binlog = 1
binlog-format = STATEMENT
log-bin=/local/mysql/mojo/binary-logs
binlog_cache_size=262144
log_slave_updates
#skip-slave-start
relay-log=/local/mysql/mojo/mysqld-relay-bin
relay-log-index=/local/mysql/mojo/mysqld-relay-bin

[mysqld_safe]
log-error = /var/log/mysqld/mysqld.err
open-files-limit = 65535

November 10, 2011

Upgrade MySQL 5.5.16

Filed under: MySQL,Upgrade — mani @ 6:52 am
Tags:

Overview

We upgraded our environment to Enterprise Edition.  Upgrading MySQL process pretty much simple process.   However there are some know issues, even there is bug in upgrading performance schema.

  1. Download binaries from http://www.oracle.com
    MySQL-*-5.5.16*
  2. copy them to local Filesystems.
    cd /usr/local/src
    cp /local/mysql/backups/MySQL_Software/5.5.16_ent/* .
  3. Stop the mysql
    /etc/init.d/mysql stop
  4. Make sure mysql is not running
    ps -ef |grep mysqld |grep -v grep
  5. Uninstall existing MySQL binaries
    for x in `rpm -qa |grep MySQL |grep "5.5"`;
    do
    echo "rpm -e ${x}";
    done
  6. Install new 5.5.16 binaries
    rpm -ivh --force --nodeps MySQL-client-advanced-5.5.16-1.rhel5.x86_64.rpm
    rpm -ivh --force --nodeps MySQL-shared-advanced-5.5.16-1.rhel5.x86_64.rpm
    rpm -ivh --force --nodeps MySQL-devel-advanced-5.5.16-1.rhel5.x86_64.rpm
    rpm -ivh --force --nodeps MySQL-server-advanced-5.5.16-1.rhel5.x86_64.rpm
  7. Start Mysql
    /etc/init.d/mysql start
  8. Run mysql_upgrade
    mysql_upgrade --skip-write-binlog -uroot -p
  9. Stop the mysql
    /etc/init.d/mysql stop
  10. Copy 5.5.16 performance_schema directory from one of the upgraded database.
    rm -rf /local/mysql/data/performance_schema
    cp -rp /local/mysql/backups/performance_schema /local/mysql/data
    chown -R mysql:mysql /local/mysql/data/performance_schema
  11. Start Mysql
    /etc/init.d/mysql start
  12. Check Performance_schema
    use performance_schema;
    select * from cond_instances;
  13. check the error log.
    tail -f /var/log/mysqld/mysql.err
  14. validate the replication.
    show slave status\G

Know issues and fixes

  1. Failed to uninstall
    Issue:
    root@@xnomdb912n1:/usr/local/src#rpm -e MySQL-shared-5.5.6_rc-1.rhel5
    error: Failed dependencies:
    libmysqlclient.so.16()(64bit) is needed by (installed) php-mysql-5.2.10-11.0.x86_64
    Fix: Just Ignore
  2. While running mysql_upgrade
    Issue: Error : Can't get stat of './mysql/general_log.CSV' (Errcode: 2)
    Error : Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
    error : Corrupt
    Cause: .frm file is there for the table, not data file
    Fix: touch the file and rerun the upgrade
    touch /local/mysql/data/mysql/general_log.CSV
  3. While running mysql_upgrade
    Issue:
    ERROR 1194 (HY000) at line 719: Table 'general_log' is marked as crashed and should be repaired
    ERROR 1194 (HY000) at line 730: Table 'slow_log' is marked as crashed and should be repaired
    FATAL ERROR: Upgrade failed
    Fix: Run repair on these tables.
    repair table general_log;
    repair table slow_log;
  4. During the install
    Issue:
    root@@xnomdb912n1:/usr/local/src#rpm -ivh --force --nodeps MySQL-server-advanced-5.5.16-1.rhel5.x86_64.rpm
    Preparing... ########################################### [100%]
    Some previous upgrade was not finished:
    -rw-r--r-- 1 root root 214 Oct 10 21:42 /var/lib/mysql/RPM_UPGRADE_MARKER
    Please check its status, then do
        rm /var/lib/mysql/RPM_UPGRADE_MARKER
    before repeating the MySQL upgrade.
    error: %pre(MySQL-server-advanced-5.5.16-1.rhel5.x86_64) scriptlet failed, exit status 1
    error: install: %pre scriptlet failed (2), skipping MySQL-server-advanced-5.5.16-1.rhel5
    Fix: Remove the upgrade marker and run the install again.
    rm /var/lib/mysql/RPM_UPGRADE_MARKER
    rpm -ivh --force --nodeps MySQL-server-advanced-5.5.16-1.rhel5.x86_64.rpm
  5. While start up mysql
    Issue: 
    root@@xnamdb01n2:/usr/local/src#/etc/init.d/mysql start
    Starting MySQL. ERROR! The server quit without updating PID file (/local/mysql/data/xnamdb01n2.ningops.com.pid).
    Error logs says:
    /usr/sbin/mysqld: File '/local/mysql/mojo/binary-logs.index' not found (Errcode: 13)
    111017 18:32:25 [ERROR] Aborting
    Fix: correct the privileges
    example:
    root@@xnamdb01n2:/local/mysql/mojo#chown mysql:mysql *
    /local/mysql/mojo#/etc/init.d/mysql start
  6. Replication error
    Issue: replication broken since we touched the file *.CSV
    Fix: skip the counter and start the slave.
    stop slave; set global sql_slave_skip_counter=1;start slave;
    show slave status\G
  7. Performance_schema issue
    Issue: wrong structure error when you read Performance_schema table.
    Fix: No fix yet.  Bug #13146778 is filed my Oracle support.
    Workaround: Copy upgraded performance-schema directory to the database and start MySQL.
    Installation steps are modified accordingly.
  8. Issue while MySQL startup on one or 2 databases.
    Issue: Error log shows.
    111024 23:16:04 InnoDB: Completed initialization of buffer pool
    InnoDB: Unable to lock /local/mysql/data/ibdata1, error: 11
    InnoDB: Check that you do not already have another mysqld process
    Solution: killall -9 mysqld_safe mysqld
    then /etc/init.d/mysql start
  9. PID file couldn’t be found
    Issue: I was trying to stop MySQL, but error reports as below.
    root@@xnomdbcmn011n1:/usr/local/src#/etc/init.d/mysql stop
     ERROR! MySQL server PID file could not be found!
    Fix: Only option worked was  
    root@@xnomdbcmn011n1:/usr/local/src#killall -9 mysqld_safe mysqld
    root@@xnomdbcmn011n1:/usr/local/src#/etc/init.d/mysql start
    Starting MySQL.......... SUCCESS!
    root@@xnomdbcmn011n1:/usr/local/src#/etc/init.d/mysql stop
    Shutting down MySQL......... SUCCESS!

MySQL: Point_In_Time Recovery using Xtrabackup

Filed under: Backup,MySQL,Xtrabackup — mani @ 6:11 am

Point_In_Time Recovery

This blog would be helpful If you are using XtraBackup to backup MySQL databases and try to restore and recover databases.  This recovery would be needed when user/admin commits errors like running any statements(truncate table, drop table, etc.) mistakenly. In this case you may need to take your DB to Point-in-Time before the incident.

  1. Restore the database from selected backup. Backup file location may be different for everyone. /local/mysql/data is datadir location.
    cd /local/mysql/data
    tar -ixvzf /local/mysql/backups/xnomdb001n2/2011-10-26_01-00-02_35569.tar.gz
  2. Apply the log
    cd /local/mysql/data
    innobackupex-1.5.1 --apply-log ./
  3. Fix the privileges
    chown -R mysql:mysql *
  4. Start MySQL
    /etc/init.d/mysql start
  5. Identify the binlog file and position
    root@@xnomdb912n2:/local/mysql/data#cat xtrabackup_binlog_info
    binary-logs.000138      36567332
  6. Capture binlog files info from current production server
    (root@localhost) [(none)]> show binary logs;
    +--------------------+------------+
    | Log_name           | File_size  |
    +--------------------+------------+
    | binary-logs.000131 |    9774400 |
    | binary-logs.000132 | 1073742520 |
    | binary-logs.000133 | 1073741997 |
    | binary-logs.000134 | 1073743592 |
    | binary-logs.000135 | 1073745994 |
    | binary-logs.000136 | 1073742312 |
    | binary-logs.000137 | 1073742367 |
    | binary-logs.000138 | 1073742549 |
    | binary-logs.000139 | 1073760891 |
    | binary-logs.000140 550608024 |
    +--------------------+------------+
    10 rows in set (0.00 sec)
  7. Identify the current binlog position from the production.
    (root@localhost) [(none)]> show master status;
    +--------------------+-----------+--------------+------------------+
    | File               | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
    +--------------------+-----------+--------------+------------------+
    | binary-logs.000140 | 550917898 |              |                  |
    +--------------------+-----------+--------------+------------------+
    1 row in set (0.00 sec)
    (root@localhost) [(none)]>
  8. scp the required binlogs files to recovery server
    Example:
    scp binary-logs.000138 root@xnomdb912n2:/local/mysql/mojo
    scp binary-logs.000139 root@xnomdb912n2:/local/mysql/mojo
  9. Generate SQL file off of binlog, which generates all transaction statements into a file. Look at the file and find position and/or time, so the DB can be recovered to that point-in-time.
    mysqlbinlog /local/mysql/mojo/binary-logs.000138   /local/mysql/mojo/binary-logs.000139 --start-position=36567332 > /local/mysql/mojo/mybinlog.sql
  10. Apply transactions to MySQL engine.
    mysqlbinlog /local/mysql/mojo/binary-logs.000138 /local/mysql/mojo/binary-logs.000139 --start-position=36567332 --stop-datetime="11-10-26 3:13:51" | mysql -u root -p

Keepalived Install, setup and usage

Filed under: MySQL — mani @ 1:13 am
Tags: , , ,

Overview

keepalived may help in failovers for a Multi-Master MySQL cluster.

What is Keepalived (http://www.keepalived.org/)

Keepalived is a utility that provides interface failover, it is virtual IP, and also can perform health checks. In the MySQL world and when using Multi-Master replication this is a very good mechanism to have. With a good implementation of Keepalived you will be able to fail over a virtual/floating IP address when the master (write) server becomes unavailable and switch that IP over the hot standby server.

Steps to set it up

If you have never installed keepalived please follow the steps below.

1. Get the tarball

# wget http://www.keepalived.org/software/keepalived-1.1.20.tar.gz
--2010-08-19 10:47:35--  http://www.keepalived.org/software/keepalived-1.1.20.tar.gz
Resolving www.keepalived.org... 188.165.36.82
Connecting to www.keepalived.org|188.165.36.82|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 233002 (228K) [application/x-gzip]
Saving to: `keepalived-1.1.20.tar.gz'

100%[======================================================================================>] 233,002      233K/s   in 1.0s

2010-08-19 10:47:37 (233 KB/s) - `keepalived-1.1.20.tar.gz' saved [233002/233002]

2. Untar and unzip the file

# /usr/local/src#tar xzvf keepalived-1.1.20.tar.gz

3. cd to the newly created directory

# /usr/local/src#cd keepalived-1.1.20

4. Make sure you have at a minium openssl-devel installed on your server

# /usr/local/src/keepalived-1.1.20#yum install openssl-devel.x86_64

5. If you want them all please run the following

yum -y install kernel-headers kernel-devel

6. Configure keepalived

# /usr/local/src/keepalived-1.1.20#./configure
checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... yes
checking whether we are cross compiling... no
checking for suffix of executables...
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for a BSD-compatible install... /usr/bin/install -c
checking for strip... strip
checking how to run the C preprocessor... gcc -E
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for ANSI C header files... yes
checking for sys/wait.h that is POSIX.1 compatible... yes
checking for uname... yes

…

configure: creating ./config.status
config.status: creating Makefile
config.status: creating genhash/Makefile
config.status: creating keepalived/core/Makefile
config.status: creating keepalived/include/config.h
config.status: creating keepalived.spec
config.status: creating keepalived/Makefile
config.status: creating lib/Makefile
config.status: creating keepalived/vrrp/Makefile

Keepalived configuration
------------------------
Keepalived version       : 1.1.20
Compiler                 : gcc
Compiler flags           : -g -O2
Extra Lib                : -lpopt -lssl -lcrypto
Use IPVS Framework       : No
IPVS sync daemon support : No
Use VRRP Framework       : Yes
Use Debug flags          : No

7. Run Make

# /usr/local/src/keepalived-1.1.20#make
make -C lib || exit 1;
make[1]: Entering directory `/usr/local/src/keepalived-1.1.20/lib'
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c memory.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c utils.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c notify.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c timer.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c scheduler.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c vector.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c list.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c html.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c parser.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c signals.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c logger.c

…

gcc -g -O2  -I/usr/src/linux/include -I../include -I../../lib -Wall -Wunused -Wstrict-prototypes -D_KRNL_2_6_ -D_WITHOUT_LVS_ -D_WITHOUT_IPVS_SYNCD_  -c vrrp_track.c

…

make[2]: Leaving directory `/usr/local/src/keepalived-1.1.20/keepalived/vrrp'
Building ../bin/keepalived
strip ../bin/keepalived

Make complete
make[1]: Leaving directory `/usr/local/src/keepalived-1.1.20/keepalived'
make -C genhash
make[1]: Entering directory `/usr/local/src/keepalived-1.1.20/genhash'
gcc -g -O2  -I/usr/src/linux/include -I../lib -Wall -Wunused -Wstrict-prototypes   -c -o main.o main.c

…

Building ../bin/genhash
strip ../bin/genhash

Make complete
make[1]: Leaving directory `/usr/local/src/keepalived-1.1.20/genhash'

Make complete
# /usr/local/src/keepalived-1.1.20#make install
make -C keepalived install
make[1]: Entering directory `/usr/local/src/keepalived-1.1.20/keepalived'
install -d /usr/local/sbin
install -m 700 ../bin/keepalived /usr/local/sbin/
install -d /usr/local/etc/rc.d/init.d
install -m 755 etc/init.d/keepalived.init /usr/local/etc/rc.d/init.d/keepalived
install -d /usr/local/etc/sysconfig
install -m 755 etc/init.d/keepalived.sysconfig /usr/local/etc/sysconfig/keepalived
install -d /usr/local/etc/keepalived/samples
install -m 644 etc/keepalived/keepalived.conf /usr/local/etc/keepalived/
install -m 644 ../doc/samples/* /usr/local/etc/keepalived/samples/
install -d /usr/local/share/man/man5
install -d /usr/local/share/man/man8
install -m 644 ../doc/man/man5/keepalived.conf.5 /usr/local/share/man/man5
install -m 644 ../doc/man/man8/keepalived.8 /usr/local/share/man/man8
make[1]: Leaving directory `/usr/local/src/keepalived-1.1.20/keepalived'
make -C genhash install
make[1]: Entering directory `/usr/local/src/keepalived-1.1.20/genhash'
install -d /usr/local/bin
install -m 755 ../bin/genhash /usr/local/bin/
install -d /usr/local/share/man/man1
install -m 644 ../doc/man/man1/genhash.1 /usr/local/share/man/man1
make[1]: Leaving directory `/usr/local/src/keepalived-1.1.20/genhash'

8. Type the following commands to create the service and run level:

cd /etc/sysconfig
ln -s /usr/local/etc/sysconfig/keepalived .
cd /etc/rc3.d/
ln -s /usr/local/etc/rc.d/init.d/keepalived S100keepalived
cd /etc/init.d/
ln -s /usr/local/etc/rc.d/init.d/keepalived

The Configuration Files

Locate the main keepalived configuration file in the following directory, /usr/local/etc/keepalived. The file name should be obvious but if not then look for keepalived.conf. I like to make a backup of this file so I have something to reference really quick if I need it but this is not necessary.

The configuration file on the master (write) server will be slightly different than the configuration file on the slave (read) server. Below are the two example files:

MASTER CONFIGUTATION FILE

global_defs {
   router_id MYTEST
}

vrrp_instance VI_1 {
    state BACKUP
    interface bond0
    virtual_router_id 40
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        xx.xx.xx.xxx
    }
}

SLAVE CONFIGURATION FILE

global_defs {
   router_id MYTEST
}

vrrp_instance VI_1 {
    state BACKUP
    interface bond0
    virtual_router_id 40
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        xx.xx.xx.xxx
    }
}

After you have implemented the configuration files you should symlink them over to the /etc/keepalived directory. This is where the /etc/init.d/keepalived script looks for the configuration file.

# mkdir /etc/keepalived
# cd /etc/keepalived
# ln -s /usr/local/etc/keepalived/keepalived.conf .

You are now ready to start keepalived on the master server but before we do that you should check the output of the following for reference:

# /sbin/ip addr show bond0 |grep inet
 inet 999.999.9.100/23 brd 999.999.9.1 scope global bond0

Start keepalived by running the following on both the master and slave server. Start the master server first then the slave server.

# /etc/init.d/keepalived start
Starting keepalived:                                       [  OK  ]

You can check to see if the IP has been added to bond0 by running the ip addr line again.

Example for the Master server:

# /sbin/ip addr show bond0 |grep inet
 inet 999.999.9.100/23 brd 999.999.9.1 scope global bond0
inet 999.999.9.102/32 scope global bond0

You can also check /var/log/messages for good information about keepalived.

Example for the Master server:

# tail/var/log/messages
19 20:27:38 sandbox Keepalived: Starting VRRP child process, pid=10521
19 20:27:38 sandbox Keepalived_vrrp: VRRP sockpool: [ifindex(7), proto(112), fd(9,10)]
19 20:27:39 sandbox Keepalived_vrrp: VRRP_Instance(VI_1) Transition to MASTER STATE
19 20:27:40 sandbox Keepalived_vrrp: VRRP_Instance(VI_1) Entering MASTER STATE
19 20:27:40 sandbox Keepalived_vrrp: VRRP_Instance(VI_1) setting protocol VIPs.
19 20:27:40 sandbox Keepalived_vrrp: VRRP_Instance(VI_1) Sending gratuitous ARPs on bond0 for 999.999.9.102
19 20:27:45 sandbox Keepalived_vrrp: VRRP_Instance(VI_1) Sending gratuitous ARPs on bond0 for 999.999.9.102

Example for the Slave server:

19 20:30:23 sandbox2 Keepalived_vrrp: Using LinkWatch kernel netlink reflector...
19 20:30:23 sandbox2 Keepalived_vrrp: VRRP sockpool: [ifindex(7), proto(112), fd(9,10)]
19 20:30:24 sandbox2 Keepalived_vrrp: VRRP_Instance(VI_1) Transition to MASTER STATE
19 20:30:24 sandbox2 Keepalived_vrrp: VRRP_Instance(VI_1) Received higher prio advert
19 20:30:24 sandbox2 Keepalived_vrrp: VRRP_Instance(VI_1) Entering BACKUP STATE

Testing

We can test our setup with a very simple ping. First you will need to have three shells open, one to the master server, one to the slave server and one running a ping to the virtual IP 999.999.9.102. While the ping is running you can simply stop keepalived and watch as the virtual IP flips over to the slave server. You should use the same techniques described above to check if the virtual IP has switched over to the slave server.

A ping test is the easiest way to test if the failover is going to work but it is NOT the only test you need to run. Running a simple BASH script that connects to MySQL on the Virtual IP (999.999.9.102) is a good way to test. Here is a VERY simple test for mysql:

# while true; do mysql –usomeuser --host=999.999.9.102 --port=3306 –e “select 1”; sleep .5; done

So instead of running a ping run the following script and see what the results are. I would expect that the Virtual IP would be flipped over to the slave server and the script above would not error out. Note that this is a very simple test and if you are implementing keepalived in production you should test with your production load.

MySQL Internal Check Script

#*/1 * * * * /bin/sh /var/mysql_support/mysqlping.sh >> /var/mysql_support/mysqlping.log 2>&1

#!/bin/sh
PATH=/bin:/usr/bin:/sbin:/usr/sbin:/usr/local/bin:/usr/local/sbin; export PATH
uname="script_user"
pass=""  #no password given
/bin/date
for sock in `cat /etc/my.sockets`
do
#echo ;echo "Socket: $sock"; echo;
check=`/usr/local/mysql/bin/mysqladmin -u$uname -p$pass -S /tmp/${sock} ping`
echo; echo $check; echo;
  if [[ "$check" != "mysqld is alive" ]]; then
    ip addr show dev bond0 | grep xxx.xxx.xxx.xxx/xx >/dev/null 2>&1 ##  replace with your vip
    if [ $? -eq 0 ]; then
      echo "Killing Keepalived"
      /usr/bin/killall keepalived
    else
      echo "MySQL is down but no VIP present"
    fi
  elif [ "$check" == "mysqld is alive" ]; then
    echo "Socket: $sock = OK"
  fi
done

October 12, 2011

MySQL: ERROR! MySQL server PID file could not be found!

Filed under: MySQL — mani @ 3:24 am

I ran into error ERROR! MySQL server PID file could not be found!,  MyQL is complaining that PID file could not be found while stopping the MySQL.  I tried to shutdown mysqladmin, that didn’t work either.  Finally I had to kill the process ID as below.  Luckily the traffic was going through Slave and recovery didn’t cause any issue.

root@@my-host001:/home/mani#/etc/init.d/mysql stop
ERROR! MySQL server PID file could not be found!

root@@my-host001:/home/mani#/etc/init.d/mysql stop
ERROR! MySQL server PID file could not be found!

root@@my-host001:/home/mani#ps -ef | grep mysql
root      1981     1  0 Jul26 ?        00:00:00 /local/mysql/support/ent/agent/libexec/mysql-monitor-agent –defaults-file=/local/mysql/support/ent/agent/etc/mysql-monitor-agent.ini –daemon –pid-file=/local/mysql/support/ent/agent/mysql-monitor-agent.pid
root      1982  1981  0 Jul26 ?        01:21:23 /local/mysql/support/ent/agent/libexec/mysql-monitor-agent –defaults-file=/local/mysql/support/ent/agent/etc/mysql-monitor-agent.ini –daemon –pid-file=/local/mysql/support/ent/agent/mysql-monitor-agent.pid
root     18464 18337  0 17:36 pts/7    00:00:00 mysql -uroot -p
root     18466 18339  0 17:36 pts/9    00:00:00 mysql -uroot -p
root     18467 18338  0 17:36 pts/8    00:00:00 mysql -uroot -p
root     18468 18342  0 17:36 pts/10   00:00:00 mysql -uroot -p
root     21016 18353  0 21:31 pts/11   00:00:00 grep mysql
root     30363     1  0 Jul26 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe –datadir=/local/mysql/data –pid-file=/local/mysql/data/mysqld.pid
mysql    31499 30363 17 Jul26 ?        10-22:08:25 /usr/sbin/mysqld –basedir=/usr –datadir=/local/mysql/data –plugin-dir=/usr/lib64/mysql/plugin –user=mysql –log-error=/var/log/mysqld/mysqld.err –open-files-limit=65535 –pid-file=/local/mysql/data/mysqld.pid –socket=/tmp/mysql.sock –port=3306

root@@my-host001:/home/mani#ll /tmp
total 20
drwxrwxrwt  3 root  root  4096 Sep 27 21:28 .
drwxr-xr-x 25 root  root  4096 Dec  3  2010 ..
drwxrwxrwt  2 root  root  4096 Dec  3  2010 .ICE-unix
srwxrwxrwx  1 mysql mysql    0 Jul 26 06:46 mysql.sock
root@@my-host001:/home/mani#mysqladmin -uroot -p -S /tmp/mysql.sock shutdown
Enter password:
root@@my-host001:/home/mani#ps -ef | grep mysql
root      1981     1  0 Jul26 ?        00:00:00 /local/mysql/support/ent/agent/libexec/mysql-monitor-agent –defaults-file=/local/mysql/support/ent/agent/etc/mysql-monitor-agent.ini –daemon –pid-file=/local/mysql/support/ent/agent/mysql-monitor-agent.pid
root      1982  1981  0 Jul26 ?        01:21:23 /local/mysql/support/ent/agent/libexec/mysql-monitor-agent –defaults-file=/local/mysql/support/ent/agent/etc/mysql-monitor-agent.ini –daemon –pid-file=/local/mysql/support/ent/agent/mysql-monitor-agent.pid
root     18464 18337  0 17:36 pts/7    00:00:00 mysql -uroot -p
root     18466 18339  0 17:36 pts/9    00:00:00 mysql -uroot -p
root     18467 18338  0 17:36 pts/8    00:00:00 mysql -uroot -p
root     18468 18342  0 17:36 pts/10   00:00:00 mysql -uroot -p
root     21025 18353  0 21:32 pts/11   00:00:00 grep mysql
root     30363     1  0 Jul26 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe –datadir=/local/mysql/data –pid-file=/local/mysql/data/mysqld.pid
mysql    31499 30363 17 Jul26 ?        10-22:08:28 /usr/sbin/mysqld –basedir=/usr –datadir=/local/mysql/data –plugin-dir=/usr/lib64/mysql/plugin –user=mysql –log-error=/var/log/mysqld/mysqld.err –open-files-limit=65535 –pid-file=/local/mysql/data/mysqld.pid –socket=/tmp/mysql.sock –port=3306

root@@my-host001:/home/mani#ll /tmp
total 20
drwxrwxrwt  3 root root 4096 Sep 27 21:32 .
drwxr-xr-x 25 root root 4096 Dec  3  2010 ..
drwxrwxrwt  2 root root 4096 Dec  3  2010 .ICE-unix

root@@my-host001:/home/mani#ps -ef | grep mysql
root      1981     1  0 Jul26 ?        00:00:00 /local/mysql/support/ent/agent/libexec/mysql-monitor-agent –defaults-file=/local/mysql/support/ent/agent/etc/mysql-monitor-agent.ini –daemon –pid-file=/local/mysql/support/ent/agent/mysql-monitor-agent.pid
root      1982  1981  0 Jul26 ?        01:21:23 /local/mysql/support/ent/agent/libexec/mysql-monitor-agent –defaults-file=/local/mysql/support/ent/agent/etc/mysql-monitor-agent.ini –daemon –pid-file=/local/mysql/support/ent/agent/mysql-monitor-agent.pid
root     18464 18337  0 17:36 pts/7    00:00:00 mysql -uroot -p
root     18466 18339  0 17:36 pts/9    00:00:00 mysql -uroot -p
root     18467 18338  0 17:36 pts/8    00:00:00 mysql -uroot -p
root     18468 18342  0 17:36 pts/10   00:00:00 mysql -uroot -p
root     21028 18353  0 21:33 pts/11   00:00:00 grep mysql
root     30363     1  0 Jul26 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe –datadir=/local/mysql/data –pid-file=/local/mysql/data/mysqld.pid
mysql    31499 30363 17 Jul26 ?        10-22:08:42 /usr/sbin/mysqld –basedir=/usr –datadir=/local/mysql/data –plugin-dir=/usr/lib64/mysql/plugin –user=mysql –log-error=/var/log/mysqld/mysqld.err –open-files-limit=65535 –pid-file=/local/mysql/data/mysqld.pid –socket=/tmp/mysql.sock –port=3306
root@@my-host001:/home/mani#  killall -9 mysqld_safe mysqld

Note: Beware that killall behaves differently on Solaris and other OS.

September 26, 2011

Incorrect table definition; there can be only one auto column and it must be defined as a key

Filed under: MySQL — mani @ 6:20 pm

I was dropping primary key which I don’t want.  I got the following error.

alter table invitation_migration drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Error can be skipped by:  alter table <table_name> drop primary key, ADD PRIMARY KEY (‘diff_column_from_table’);

September 6, 2011

MySQL: Defragmenting a Table

Filed under: MySQL — mani @ 11:28 pm

We have many sharded databases in MySQL.  The nature of the application updates, deletes records.  Realized that we may have lot of fragmentation on tables and wanted to remove it.  This is has been done on Master Slave environment.  We were able to reclaim good amount of storage from bigger fragmented tables.  It is performed by NULL alter table operation.  BE AWARE that this process would rebuild the objects, so cannot have DML running on the table during defragmentation.

First do it on Slave, then failover the service to slave  and then do it on Master.

— Set timeout

set wait_timeout = 28800;
set interactive_timeout = 28800;

select @@interactive_timeout;
select @@wait_timeout;
— stop the slave

stop slave;
show slave status\G

set sql_log_bin = 0;

ALTER TABLE _topic ENGINE=INNODB ;
Query OK, 882094 rows affected (11 min 48.53 sec)
Records: 882094  Duplicates: 0  Warnings: 0

Once you are done.  Start slave to catchup.

Failover the service to slave and do the same on Master.

We are able to reclaim 50GB storage per cluster(including master and slave).   Anyway it depends on environment and its characteristics.

Next Page »

Blog at WordPress.com.