• MySql Replication over SSL

    mysql-replication

    Scenario :  Setup mysql master slave replication over ssl

    Master  Server  :  192.168.1.1   

    Slave  Server    :  192.168.1.2

    Confirm your mysql server is compiled/enabled to support ssl connections using the following command

    # mysql -u root -p

    mysql >   show variables like ‘%ssl%’ ;

    If you are getting an output some thing like as follows then you can confirm mysql is compiled to support ssl connections

    mysql> show variables like ‘%ssl%’;

    | have_openssl  | DISABLED |

    | have_ssl            | DISABLED |

    The above shows that mysql is compiled with ssl support but it not enabled in the configuration .

    Create Certificates

    # cd /var/lib/mysql

    # mkdir ssl

    >>> Create CA Certificate

    # openssl genrsa 2048 > ca-key.pem
    # openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

    >>> Create Server Certificate

    # openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
    # openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

    >>> Create Client Sertificate .

    # openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
    # openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

    Copy the keys to Slave server

    # scp ca-cert.pem client-cert.pem client-key.pem   root@192.168.1.2:/var/lib/mysql/ssl

    Master Side configuration

    #  vi /etc/my.cnf

    #bind-address = 127.0.0.1
    ssl
    ssl-ca=/var/opt/mysql/ssl/ca-cert.pem
    ssl-cert=/var/opt/mysql/ssl/server-cert.pem
    ssl-key=/var/opt/mysql/ssl/server-key.pem
    binlog-do-db=mydatabase
    server-id = 1
    log_bin = /var/lib/mysql/mysql-bin.log

    Note that server id should be unique here for master its 1.

    Restart mysql and confirm now ssl values are showing properly in ” mysql > show variables like ‘%ssl%’ ;  ”

    # mysql –u root –p

    GRANT all privileges ON *.* TO replication@'192.168.1.2' IDENTIFIED BY 'password' REQUIRE SSL;

    Slave Side Configuration

    # vi /etc/my.cnf

    bind-address = 0.0.0.0
    server-id=2
    master-host=192.168.1.1
    master-connect-retry=60
    replicate-do-db=mydatabase
    replicate_ignore_db=dataold
    replicate_ignore_db=data_duplicate
    slave-skip-errors=all
    relay-log=mysql-relay-bin.log

    check master status on the master node

    mysql > show master status ;

    | File | Position | Binlog_do_db | Binlog_ignore_db | +

    | mysql-bin.002 | 80600 | mydatabase | | +

     

    Update the log location and Position on Slave

    Msql > slave stop;

    Mysql > CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE=' mysql-bin.002', MASTER_LOG_POS=80600, MASTER_SSL=1, MASTER_SSL_CA = '/var/opt/mysql/ssl/ca-cert.pem', MASTER_SSL_CERT = '/var/opt/mysql/ssl/client-cert.pem', MASTER_SSL_KEY = '/var/opt/mysql/ssl/client-key.pem';

    Mysql > slave start ;

    Mysql > show slave status \G ;

    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 192.168.1.1
    Master_User: replication
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000003
    Read_Master_Log_Pos: 12345100
    Relay_Log_File: mysql-relay-bin.000002
    Relay_Log_Pos: 11381900
    Relay_Master_Log_File: mysql-bin.000003
    Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
    Replicate_Do_DB: mydatabase
    Replicate_Ignore_DB:

    The above lines which are marked in green shows that replication is working fine from master to slave.

  • Install mysql 5.5 from source

    I got a request from  one of my clients  to setup a mysql server with the latest version.  I decided to install mysql using source , because i always love compilation 🙂

    As usual I downloaded the latest source and fired the command ” ./configure ”  with options . But the result was not good 🙁

     

    =>> Download the latest MySql source

    wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.15.tar.gz/from/http://mysql.oss.eznetsols.org/
     
     
    =>> Configure ( Old Story )

    1)  tar -zxf mysql-5.5.15.tar.gz
    2)  cd mysql-5.5.15
    3)  ./configure
    ./configure: command not found

    ( After some goggling , i found  ”  In MySQL 5.5 onwards , CMake is used as the build framework on all platforms. ” )

     

    =>> Download and install cmake

    $  wget http://www.cmake.org/files/v2.8/cmake-2.8.5.tar.gz
    $  tar zxvf cmake-2.8.5.tar.gz
    $  cd cmake-2.8.5
    $  yum install gcc-c++
    $  ./configure
    $  make
    $  make install

     

    =>> Configure ( New Story )

    $ cd mysql-5.5.15

    $  Configure using cmake

    cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql5 -DMYSQL_TCP_PORT=3306  -DMYSQL_UNIX_ADDR=/tmp/mysql.sock 

    — Could NOT find Curses (missing:  CURSES_LIBRARY CURSES_INCLUDE_PATH)
    CMake Error at cmake/readline.cmake:83 (MESSAGE):
    Curses library not found.  Please install appropriate package,

    $ yum install ncurses-devel

    rm -f  CMakeCache.txt   ( Equivalent to ” make clean ”  )

    $  Again run the cmake command after fixing the curses error.

    cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql5  -DMYSQL_TCP_PORT=3306  -DMYSQL_UNIX_ADDR=/tmp/mysql.sock 

    more cmake configuration options here :  cmake options

    $  make

    $ make install

     

    =>> Post installation Steps

    $  cp support-files/my-medium.cnf   /etc/my.cnf

    $  cp support-files/mysql.server   /etc/init.d/mysql

    $  chown -R mysql:mysql .

    $  ./scripts/mysql_install_db  – -user=mysql  – -datadir=/var/lib/mysql

    $  /etc/init.d/mysql restart

    $  ./bin/mysqladmin -u root password ‘new-password’

    $  ./bin/mysql_secure_installation

    Thats its you installed mysql 5.5 successfully. You can configure / optimize the mysql using the my.cnf file .

     

    =>> Test the insatalation

    $ mysql -u root -p

    > create table new ;

    Hope that this will be helpful for someone worrying with ” ./configure command not found ” in mysql latest version.

    How to reset mysql root password : click here 🙂

  • mysqldump: Error : Table is marked as crashed and should be repaired when dumping table

    mysqldump: Error : Table is marked as crashed and should be repaired when dumping table

    Usually we will get this error while dumping database .

    Solution :-

    1) Repair the crashed table

    # mysql -u user -p pass

    >  use database ;

    > REPAIR TABLE `tablename`;

    2) Repair the complete database

    #  check for errors or which are tables are corrupted or not closed properly

    # cd /var/lib/mysql/DATABASE

    # myisamchk -s *.MYI

    will give the status

    # myisamchk -r  *.MYI

  • mysqldump: Error 2020: Got packet bigger than ‘max_allowed_packet’ bytes when dumping table

    If we are dumping big db’s we can see this error usually , because the default value of max_allowed_packet is 25M.

    Solution :-

    Two ways you can fix this

    1) Add the following in your /etc/my.cnf and restart mysql

    [mysqldump]
    max_allowed_packet = 500M

    2) Use mysqldump with the option –max_allowed_packet

    mysqldump -u root -p --max_allowed_packet=512M  -B  databaseName  -- tables  tablename > dbname.sql
    

     

  • Daily,Weekly and Monthly mysql backup

     

    Auto mysql backup is a fantastic script to create daily , weekly and monthly backup of mysql db’s .  I am using this in my most of the DB servers . The main advantage is , it will do a good level of compression ( backup file )

    Download the script :-

    http://sourceforge.net/projects/automysqlbackup/

    Configuration :-

    cp  automysqlbackup-2.5.1-01.sh  /etc/automysqlbackup/automysqlbackup.sh

    mkdir –p /etc/automysqlbackup/

    vi   /etc/automysqlbackup/automysqlbackup.conf

    To create a config file just copy the code between “### START CFG ###” and “### END CFG ###   to /etc/automysqlbackup/automysqlbackup.conf from automysqlbackup-2.5.1-01.sh

    In the configuration you need to change only the following

    # Username to access the MySQL server e.g. dbuser

    USERNAME=root

    # Password to access the MySQL server e.g. password

    PASSWORD=password

    # Host name (or IP address) of MySQL server e.g localhost

    DBHOST=localhost

    # List of DBNAMES for Daily/Weekly Backup e.g. “DB1 DB2 DB3”

    DBNAMES=”wordpress_db  forum_db”

    # Backup directory location e.g /backups

    BACKUPDIR=”/backup/MysqlAuto”

    # Email Address to send mail to? (user@domain.com)

    MAILADDR=admin@example.com

    # setup a cron to execute the mysql backup script.

    30 00 * * *  bash  /etc/automysqlbackup/automysqlbackup.sh

     

    Enjoy !! you done that..It’s a fantastic tool for mysql daily weekly and monthly backup . ..You can sleep with out worrying about your DB’s

  • MySql monitoring using nagios

    About :-

    Mysql Check is a very good plugin for nagios . We can use this to monitor mysql health status like mysql connection time , threads connected etc.

    Installation :-

    wget http://labs.consol.de/wp-content/uploads/2010/12/check_mysql_health-2.1.5.tar.gz

    tar -zxf check_mysql_health-2.1.5.tar.gz

    cd check_mysql_health-2.1.5

    ./configure

    make & make install

     

    In Mysql Server

    mysql> GRANT usage ON *.* TO ‘nagios’@’nagios-server-ip’ IDENTIFIED BY ‘pass’;

    Check the connection from nagios server

    $ ./check_mysql_health –hostname mysql-server  –username nagios –password pass  –mode connection-time

    Can’t locate Time/HiRes.pm in @INC (@INC contains: /usr/local/lib/perl5 /usr/local/share/perl5 /usr/lib/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib/perl5 /usr/share/perl5 .) at ./check_mysql_health line 940.

    BEGIN failed–compilation aborted at ./check_mysql_health line 940.

    I got the above error while trying to check this plugin locally and I have fixed this using the following

    $  perl -MCPAN -e shell

    Can’t locate CPAN.pm in @INC (@INC contains: /usr/local/lib/perl5 /usr/local/share/perl5 /usr/lib/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib/perl5 /usr/share/perl5 .).

    BEGIN failed–compilation aborted.

    ( cpan is not present in the server , so I have installed cpan first and installed all the other modules using cpan )

    $ yum install perl-CPAN

    $ perl -MCPAN -e shell

    $ cpan[1]> install Time::HiRes

    ./check_mysql_health –hostname mysql-server  –username nagios –password pass  –mode connection-time

    OK – 0.46 seconds to connect as nagios | connection_time=0.4560s;1;5

    ( This time its worked 🙂 )

    ./check_mysql_health –hostname mysql-server  –username nagios –password pass  –mode threads-connected

    CRITICAL – 34 client connection threads | threads_connected=34;10;20

    Nagios configuration

    # service ‘MySQL connection-time’

    define service{

    use                                   generic-service

    host_name                     MYSQL-Server

    service_description     MySQL connection-time

    check_command          check_mysql_health!nagios!pass!connection-time!2!4

    contact_groups            admins

    }

    # service ‘MySQL threads-connected’

    define service{

    use                                 generic-service

    host_name                   MYSQL-Server

    service_description    MySQL threads-connected

    check_command   check_mysql_health!nagios!pass!threads-connected!80!100

    contact_groups          admins

    }

    ( after editing the corresponding hosts cfg file verify the nagios configuration and restart the nagios serve r)

    $ nagios -v /etc/nagios/nagios.cfg

    $ /etc/init.d/nagios restart

    Hey you did that ..Its a fantastic nagios plugin  to monitor mysql health for high traffic DB server ‘s 😉

  • Wonder how to reset Mysql Password ?

    Forgetting? Isn’t that a Sign Of Old Age? 😉 . Okay , I’m here to help you!  You can reset the mysql password as follows :-

    First you need to stop the mysql service by running the following command :

    service mysql stop

    Now Run the following command and it will start the mysql in the background without any privileges .

    /usr/bin/mysqld_safe –skip-grant-tables &

    Now you will be able to  login into mysql without any password. Once logged in, set your new mysql password.

    #mysql
    mysql> use mysql;
    mysql> update user set password=PASSWORD(“new_password_here”) where User=’root’;
    mysql> flush privileges;
    mysql> quit;

    service mysql  restart

    Hey , you should now be able to login . Cheers!