LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

Reply
 
LinkBack Search this Thread
Old 06-29-2012, 04:06 AM   #1
centos123
Member
 
Registered: Apr 2011
Posts: 390

Rep: Reputation: 14
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...
 
Old 06-29-2012, 04:33 AM   #2
acid_kewpie
Moderator
 
Registered: Jun 2001
Location: UK
Distribution: Gentoo, RHEL, Fedora, Centos
Posts: 43,344

Rep: Reputation: 1945Reputation: 1945Reputation: 1945Reputation: 1945Reputation: 1945Reputation: 1945Reputation: 1945Reputation: 1945Reputation: 1945Reputation: 1945Reputation: 1945
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...
 
Old 06-29-2012, 04:53 AM   #3
centos123
Member
 
Registered: Apr 2011
Posts: 390

Original Poster
Rep: Reputation: 14
http://www.debiantutorials.com/mysql.../#comment-1665

this link i follow...

if u know the concept of syn then first explain me ..
 
Old 06-30-2012, 01:16 AM   #4
arun5002
Member
 
Registered: Aug 2011
Location: Chennai,India
Distribution: Redhat,Centos,Ubuntu,Dedian
Posts: 548
Blog Entries: 5

Rep: Reputation: Disabled
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)
 
Old 08-01-2012, 02:47 AM   #5
centos123
Member
 
Registered: Apr 2011
Posts: 390

Original Poster
Rep: Reputation: 14
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..
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sync LDAP users with MySQL waelaltaqi Linux - Software 1 09-06-2008 05:51 AM
Sync LDAP users with MySQL waelaltaqi Linux - Software 1 09-05-2008 10:45 AM
Mysql master and replica over VPN Vitalka Linux - Networking 1 10-04-2007 07:40 PM
interesting question about mysql database sync shadow.blue Linux - Software 0 11-15-2003 08:24 AM


All times are GMT -5. The time now is 04:05 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration