sync/replica mysql from 1server to another
 

how can we sync mysql data from one server machine to another machine in network...

i googled,and configure also but it not working.any one who had done this sync please guide...

if you've started somethign, as you clearly seem to have, you can't just go back to asking a question you've already presumably found the answer to. What guide did you follow? What's not working about it? log file entries etc...

http://www.debiantutorials.com/mysql.../#comment-1665

this link i follow...

if u know the concept of syn then first explain me ..

Hi



Mysql Database Replication

Master Server: 10.200.2.34
Slave Server: 10.200.2.42
MySQL Data path: /var/lib/mysql
MySQL slave user named slave_user
[Master]
First, edit the master server MySQL config file. Add/Replace the following lines
1
vim /etc/my.cnf
# [mysqld] section
# Start Modification
# First line is probably already there
datadir = /var/lib/mysql
server-id = 1
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/var/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin
# Stop Modification
Restart MySQL to load the changes
1
service mysqld restart
[Slave]
Now we’ll do about the same thing on the slave server
1
vim /etc/my.cnf
# [mysqld] section
# Start Modification
# First line is probably already there
datadir = /var/lib/mysql
server-id = 2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/var/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
# Stop Modification
Restart MySQL to load the changes
1
service mysqld restart
[Master]
Now we need to tell MySQL where we are replicating to and what user we will do it with.
1
2
3
4
mysql -u root -psecret
mysql> STOP SLAVE;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'secret';
mysql> FLUSH PRIVILEGES;
Now we will test that this side of the replication is working and get the location that we will start the replication from.
1
2
3
4
5
6
7
8
9
mysql> USE pommo;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | POSITION | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 451228 | | |
+------------------+----------+--------------+------------------+
1 ROW IN SET (0.00 sec)
Write down the File, Position number, as this is where we will start the replication from.
dump your master database(s) and pipe it into your slave server.
1
mysqldump -u root --all-databases --routines > /home/MasterSnapshot.sql
[Slave]
First grab your SQL dump file from the master server to slave server . Transfering using scp
1
scp /path/to/MasterSnapshot.sql root@10.2.200.34:/home/MasterSnapshot.sql
Import the SQL file into MySQL
1
mysql -u root -p < /home/MasterSnapshot.sql
Now we’ll set the slave to read from the master server, starting at the record position we wrote down earlier. Make sure you use the MASTER_LOG_FILE and MASTER_LOG_POS from a few steps back.
1
2
3
mysql> CHANGE MASTER TO MASTER_HOST='10.200.2.34', MASTER_USER='root', MASTER_PASSWORD='secret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=451228;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
Make sure that from the resulting output you have the following:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes


Mysql ssl certificate

# mkdir /etc/mysql/certs
# cd /etc/mysql/certs
# openssl genrsa 2048 > ca-key.pem
# openssl req -new -x509 -nodes -days 1095 -key ca-key.pem -out ca-cert.pem
# openssl req -newkey rsa:2048 -days 1095 -nodes -keyout server-key.pem -out server-req.pem
# openssl rsa -in server-key.pem -out server-key.pem
# openssl x509 -req -in server-req.pem -days 1095 -CA ca-cert.pem -CAkey ca-key.pem \ -set_serial 01 -out server-cert.pem



Configuring ssl certificate in master server
Vi /etc/my.cnf



[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
server-id = 4
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/var/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin
ssl_ca=/etc/mysql/newcerts/cacert.pem
ssl_cert=/etc/mysql/newcerts/server-cert.pem
ssl_key=/etc/mysql/newcerts/server-key.pem

[root@localhost ~]# mysql -uroot -psecret
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.95-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%ssl%';
+---------------+-------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/mysql/newcerts/cacert.pem |
| ssl_capath | |
| ssl_cert | /etc/mysql/newcerts/server-cert.pem |
| ssl_cipher | |
| ssl_key | /etc/mysql/newcerts/server-key.pem |
+---------------+-------------------------------------+
7 rows in set (0.00 sec)

thanks...
i tried this but not got success . so i tried rsync of mysql directory from mailserver to backup server.and it work .so for now iam closing this thread..