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.