MySQL Backup Strategy.

Back up Strategies

If you are admin taking the backup is really an important task in light of MySQL you should do the following

1. Take the Backup regularly(weekly,regularly as you wish).Store the Back-up in some safe place.

2. Keep the binary logging enabled so that changes can be recorded in it.

3. While taking the backup flush the logs so that it generates the new binary log which record the changes after the backup is made.The Syntax is as

mysqldump -uuser -psecret --flush-logs --lock-all-tables --all-databases > dump.sql

use the other options with mysqldump if you need

--flush-logs : will generate new binary log before which the all dump will be generated.

--lock-all-tables : will lock all tables upto the time till the dump is being generated.

4. Keep binary logs in some safe place so in case of some failure you can use the most recent backup's and the binary logs generated after that to reach to the actual level of the database.

How to recover a currupted InnoDB table.

I was really in Problem when one of my InnoDB table get corrupted I posted My Question on dba.stackexchange.com.
The issue was as

mysql> check table City;
+-------------------+-------+----------+----------------------------------------------------------+
| Table             | Op    | Msg_type | Msg_text                                                 |
+-------------------+-------+----------+----------------------------------------------------------+
| world_innodb.City | check | Error    | Incorrect information in file: './world_innodb/City.frm' |
| world_innodb.City | check | error    | Corrupt                                                  |
+-------------------+-------+----------+----------------------------------------------------------+
2 rows in set (0.00 sec)

Then I Created a test scenario and found solution

I created a table called test2

CREATE TABLE `test2` (   `t` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I then inserted one record

insert into test2 values(1);

I stopped MySQL and then added a bunch of characters to the test2.frm file with vi.

I then started MySQL and saw the following

mysql> show table status \G;
*************************** 1. row ***************************
           Name: test2
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: Incorrect information in file: './test2/test2.frm'

I then created another table with the same structure

CREATE TABLE `test3` (   `t` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I then stopped mysql and copied test3.frm over test2.frm

cp /var/lib/mysql/testdb/test3.frm /var/lib/mysql/testdb/test2.frm

I restarted MySQL and had access to test2

mysql> show table status \G;
*************************** 1. row ***************************
           Name: test2
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 25675431936
 Auto_increment: NULL
    Create_time: 2012-02-07 16:24:20
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:

Here is the record that I inserted

mysql> select * from test2;
+------+
| t    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

If you know the structure of the table City , you could create an identical table and then copy the .frm file over the existing corrupted one

Step by step process for how to set the mk-heartbeat monitoring

Process For setting mk-heartbeat mechanism.

Take a case you have two servers(Master and Slave1 and Slave2)

replication is as Master->Slave

We want to set monitoring for slave.

Steps:

1.Download mk-heartbeat script

from terminal you can download as:

wget http://www.maatkit.org/get/mk-heartbeat

2.create a database Heartbeat and a table heartbeat on the master.It will automatically be replicated on the slave.

create database Heartbeat;

use Heartbeat;

CREATE TABLE heartbeat (

id int NOT NULL PRIMARY KEY,

ts datetime NOT NULL

)Engine = Memory;

Insert one row in the table heartbeat as

INSERT INTO heartbeat (id, ts) VALUES (1, NOW());

If the user has permissions on the Heartbeat we dont need to insert the row manually in the table,The mk-heartbeat will itself insert the row in it.

3.Now create a user heartbeat with some password as

Grant all on Heartbeat.* to heartbeat@'%' identified by 'maatkit';

the user heartbeat should have permissions on the Database Heartbeat, We have created the user heartbeat for our sake otherwise we have to start mk-heartbeat by any other or current user.mk-heartbeat takes the default values from the my.cnf it did not pass them at the script execution time.

4.Now we have to make mk-heartbeat to update the master and monitor the slave.

For master:

mk-heartbeat -D Heartbeat --update -h 10.167.29.58 -u heartbeat -p maatkit --daemonize

where 10.167.29.58 is IP for master

For Slave:

mk-heartbeat -D Heartbeat --monitor -h 10.166.162.231 -u heartbeat -p maatkit --daemonize --log /home/slave.log

where 10.166.162.231 is IP for slave.

It will generate the log in slave.log file generated by above command

Issue tail -f slave.log

It should be working fine.

MySQL replication set up

there are some basic steps to set mysql replication.

wee start from MASTER

1.see that binary logs are enabled.and also set server id in my.cnf file

If not then do as:

log-bin=mysql-bin ##THIS SHOULD BE THE SAME AS HERE

server-id=1

Restart the server

2.in the mysql shell issue a command

mysql > show binary logs;

If the binary logs are enabled it should not return the empty result set.If return empty set you are wrong somewhere pls check that step 1 is completed...??

otherwise you will face problem in replication

If done come to step 3.

3.In mysql shell write

mysql > FLUSH TABLES WITH READ LOCK;

Now just generate the dump of your master as or any other method u want

mysqldump -uroot -proot --all-databases --master-data > dbdump.db

4. mysql > show master status;

note down the binary log and position.

5.now In mysql shell

mysql > UNLOCK TABLES;

6.mysql > CREATE USER 'repl'@ '%' IDENTIFIED BY 'password';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@ '%' IDENTIFIED BY 'password';

NOW ON THE SLAVE DO SOME WORK

1.IN my.cnf

server-id=2

#and also try to write relay-log

restart the mysql server

2.

mysql > stop slave;

mysql > CHANGE MASTER TO

MASTER_HOST=’X.X.X.X’,

MASTER_USER=’user’,

MASTER_PASSWORD=’password’,

MASTER_PORT=3306,

MASTER_LOG_FILE=’mysql-bin.000001?,

MASTER_LOG_POS=98,

MASTER_CONNECT_RETRY=10;

mysql > start slave ;

mysql > show slave status\G

Now It should be working fine

MySQL startup issue

If you are facing problem in starting the mysql server try to start mysqld with the option –verbose it will tell what is the problem or you can also check the problem by your error log file.

Take a case here

abdul@system:~$ mysqld --verbose --user=mysql –datadir=/var/lib/mysql/ &

[1] 2975

abdul@system:~$ 111013 10:42:35 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.

111013 10:42:35 [Warning] Can't create test file /var/lib/mysql/OSS-240.lower-test

111013 10:42:35 [Warning] Can't create test file /var/lib/mysql/OSS-240.lower-test

mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13)

111013 10:42:35 [ERROR] Aborting

111013 10:42:35 [Note] mysqld: Shutdown complete

Above wee see that we are getting error code 13.means its permission issue login as root and now try.

root@system:~$mysqld --verbose --user=mysql --datadir=/var/lib/mysql/ &

now mysql is running & working properly.

But one point you should consider in mind is that we have written –user=mysql.

Always try to start your mysql server as mysql user if you start it as root its not a good practice.

Restore your data using mysqlbinlogs

Its a simple article on How you can restore your data using mysql binary logs.

mysql binary logs contain a record of each SQL query executed against the database that changes data since the logs where last flushed (new log file started).MySQL Binary Logs must be enabled by passing the –log-bin option to mysqld.

The log contains queries like UPDATE and DELETE. The log does not record SELECT or SHOW queries for example because they do not change the database.

MySQL Binary Logs are some times confused with the InnoDB binary transaction log. InnoDB uses a binary log to journal changes to the InnoDB table space file(s) as a protection from crashes to protect table space data integrity.

When --log-bin is enabled even InnoDB transactions are written to the binary transaction log used for replication or restores.

At this time take a scenario you has binary logs as follows:

see the location where binary logs are stored.

localhost-bin.000001

localhost-bin.000002

localhost-bin.000003

localhost-bin.index

firstly lock the tables as

LOCK TABLES

then flush the logs as

FLUSH LOGS

At this time take a scenario you has binary logs as follows:

see the location where binary logs are stored.

localhost-bin.000001

localhost-bin.000002

localhost-bin.000003

localhost-bin.000004

localhost-bin.index

after flushing the logs a new bianry log generated as localhost-bin.000004.

Now upto binary log localhost-bin.000003 you can take the complete backup.

take the backup using any method that you want.

This is you complete backup upto the log localhost-bin.000003

Then unlock the tables as

UNLOCK TABLES

Restoring with Binary Logs

To restore from a point in time using binary logs you would:

1. Restore the database from the last SQL Dump completed before the desired recovery point.

2. Use mysqlbinlog to restore to the desired point in timewhere N is the log entry number you want to restore up to.

mysqlbinlog –start-position=N    localhost-bin.000004   | mysql N is the position of binary log. 

How to reduce the ibdata space in Mysql

Dump the Whole Database

This is the easiest method How to reclaim the ibdata space

In fact, this is the method I used to solve the problem. It requires much more space and time but it’s maybe the easiest one. So here it is:

1. Dump all databases by using:

   mysqldump -uroot -proot --extended-insert --all-databases --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers > all-databases.sql

2. Stop the MySQL server;

/etc/init.d/mysql stop

3. Rename or remove the MySQL data directory and create an empty one with the same name and permissions;

i.e In case the datadir is /var/lib/mysql then remove the all content of that.

else remove /var/lib/mysql and create other one with the same permission and same name.

4. Make the appropriate changes in my.cnf.

5. Re-initialize the database with the following command.

   sudo -u root mysql_install_db

6. Start the MySQL server;

/etc/init.d/mysql start

7. Get into the ‘mysql‘ console and type:

   SET FOREIGN_KEY_CHECKS=0;
   SOURCE all-databases.sql;
   SET FOREIGN_KEY_CHECKS=1;

8. Restart the MySQL server.

At this point of time everything should be running fine.