MYSQL中怎么實(shí)現(xiàn)單雙向同步

148次閱讀

共計(jì) 7293 個(gè)字符，預(yù)計(jì)需要花費(fèi) 19 分鐘才能閱讀完成。

MYSQL 中怎么實(shí)現(xiàn)單雙向同步，針對(duì)這個(gè)問(wèn)題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問(wèn)題的小伙伴找到更簡(jiǎn)單易行的方法。

Sever info:

Master:192.168.1.3

Slave:192.168.1.4

腳本使用教程：

Master:192.168.1.3

1、解壓 TAR 包

[root@SQL1 down]# tar -zxf -master-slave.tar.gz

2、進(jìn)入目錄設(shè)置權(quán)限

[root@SQL1 down]# cd mysql-master-slave

[root@SQL1 mysql-master-slave]# chmod 777 mysqlsa.py

3、打開(kāi) master 文件，設(shè)置你需要同步的庫(kù)

[root@SQL1 mysql-master-slave]# vi master

log-bin=/var/log/mysql/mysql-bin

binlog-ignore-db=mysql,test

binlog-do-db=wgdb

log-bin=/var/log/mysql/updatelog

4、運(yùn)行腳本程序（這里注意如果你的 MYSQL 默認(rèn)安裝路經(jīng)不是 lsquo;/usr/local/mysql 在此腳本程序的 10~11 行把成“export LD_LIBRARY_PATH=your path 你自己路經(jīng)地址，否則有可能程序里面 MySQLdb 模塊報(bào)錯(cuò)

[root@SQL1 mysql-master-slave]# ./mysqlsa.py

———————————————————————-

1)Installation MySQL Master

2)Installation MySQL Slave

Q)Quit Installation

———————————————————————-

Plase,Enter your option:

1：配置安裝 MYSQL 主庫(kù)

2：配置安裝 MYSQL 從庫(kù)

Q：退出

5、我們選擇“1”配置 MYSQL 主庫(kù)

[root@SQL1 mysql-master-slave]# ./mysqlsa.py

———————————————————————-

1)Installation MySQL Master

2)Installation MySQL Slave

Q)Quit Installation

———————————————————————-

Plase,Enter your option:1

6、提示要求輸入 SLAVE 服務(wù)器 IP，這步是創(chuàng)建訪問(wèn)帳號(hào)用的，我們輸入 SLAVE 的 IP：192.168.1.4，注意：千萬(wàn)別輸錯(cuò)，不然 SLAVE 訪問(wèn)就會(huì)出錯(cuò)

Plase,Enter your option:1

Please,input your slave server ip:192.168.1.4

7、OK, 執(zhí)行完成以后程序不會(huì)給你任何提示！我們直接安 Q 退出，查看 /etc/my.cnf 的 57 行下面內(nèi)容是否成功寫(xiě)入

54 # required unique id between 1 and 2^32 – 1

55 # defaults to 1 if master-host is not set

56 # but will not function as a master if omitted

57 server-id = 1

58 log-bin=/var/log/mysql/mysql-bin

59 binlog-ignore-db=mysql,test

60 binlog-do-db=wgdb

61 log-bin=/var/log/mysql/updatelog

8、重啟 MYSQL

[root@SQL1 down]# service mysqld restart

Slave:192.168.1.4

1、解壓 TAR 包

[root@SQL2 down]# tar -zxf mysql-master-slave.tar.gz

2、進(jìn)入目錄設(shè)置權(quán)限

[root@SQL2 down]# cd mysql-master-slave

[root@SQL2 mysql-master-slave]#

3、打開(kāi) slave 文件，設(shè)置你需要同步的庫(kù)和 master 的 IP

master-host = 192.168.1.3

master-user = replication

master-password = *.asDICf.1KD

master-port = 3306

master-connect-retry = 60

replicate-ignore-db = mysql,test

replicate-do-db = wgdb

relay-log=/var/log/mysql/slave-relay-bin

slave-skip-errors=all

4、運(yùn)行腳本程序（這里注意如果你的 MYSQL 默認(rèn)安裝路經(jīng)不是 lsquo;/usr/local/mysql 在此腳本程序的 10~11 行把成“export LD_LIBRARY_PATH=your path 你自己路經(jīng)地址，否則有可能程序里面 MySQLdb 模塊報(bào)錯(cuò)

[root@SQL2 mysql-master-slave]# ./mysqlsa.py

———————————————————————-

1)Installation MySQL Master

2)Installation MySQL Slave

Q)Quit Installation

———————————————————————-

Plase,Enter your option:

5、我們選擇 lsquo;2 rsquo; 安裝從庫(kù)

[root@SQL2 mysql-master-slave]# ./mysqlsa.py

———————————————————————-

1)Installation MySQL Master

2)Installation MySQL Slave

Q)Quit Installation

———————————————————————-

Plase,Enter your option:2

6、檢查下 lsquo;/etc/my.cnf 57 行內(nèi)容是否寫(xiě)入

# required unique id between 1 and 2^32 – 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id = 2

master-host = 192.168.1.3

master-user = replication

master-password = *.asDICf.1KD

master-port = 3307

master-connect-retry = 60

replicate-ignore-db = mysql,test

replicate-do-db = wgdb

relay-log=/var/log/mysql/slave-relay-bin

slave-skip-errors=all

7、重起 MYSQL

[root@SQL2 mysql-master-slave]# service mysqld restart

最后一步

1、進(jìn)入 Master 調(diào)整參數(shù)

[root@SQL1 down]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.1.57-log Source distribution

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql slave stop;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql show master status;

+——————+———-+————–+——————+

+——————+———-+————–+——————+

+——————+———-+————–+——————+

1 row in set (0.00 sec)

2、進(jìn)入 slave 調(diào)整參數(shù)

[root@SQL2 mysql-master-slave]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.1.57-log Source distribution

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql slave stop;

Query OK, 0 rows affected (0.00 sec)

mysql CHANGE MASTER TO MASTER_LOG_FILE= updatelog.000001 ,MASTER_LOG_POS=106;

Query OK, 0 rows affected (0.02 sec)

mysql slave start;

Query OK, 0 rows affected (0.01 sec)

3、檢查是否成功

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

以上配置已經(jīng)全部完成，很多朋友可能會(huì)問(wèn)，這只是單向同步嗎？

答：只要把兩個(gè)操作反向做下就 OK

腳本程序問(wèn)題在哪里？

答：http://down.51cto.com/data/229410

以下是主程序的代碼

#!/usr/bin/python

#

#This is a mysql reversed sync and data pool script.

import os,MySQLdb

import sys

import re

#Check that Mysql exists

if os.path.isfile(/etc/my.cnf):

b=os.system(python mysqldb.py)

profile = open(/etc/profile , r+)

if re.search(export LD_LIBRARY_PATH=/usr/local/mysql/lib/mysql/ ,profile.read()) is None:

profile.write(export LD_LIBRARY_PATH=/usr/local/mysql/lib/mysql/)

profile.close

else:

print Your mysql not installiation

sys.exit(1)

class msa:

def sql(self):

try:

conn = MySQLdb.connect(host = localhost ,user= root ,passwd=)

except:

pw = raw_input(Please,input your amdin of mysql passwd:)

conn = MySQLdb.connect(host = localhost ,user= root ,passwd= %s)%pw

IP = raw_input(Please,input your slave server ip:)

cursor = conn.cursor()

sql = GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* TO replication @ %s identified by *.asDICf.1KD Flush privileges; %(IP)

cursor.execute(sql)

except Exception,e:

print e

sys.exit()

def mk(self):

var= /var/log/mysql

if not os.path.isdir(var):

os.makedirs(var)

os.chmod(var,448|56)

os.chown(var,500,500)

def master(self):

my_conf = open(/etc/my.cnf , r)

my_conf_read = my_conf.readlines()

my_conf.close

if re.search(binlog-do-db ,str(my_conf_read)) is None:

master_file = open(master , r)

read = master_file.read()

my_conf_read.insert(57,read)

my_conf = open(/etc/my.cnf , w)

my_conf.writelines(my_conf_read)

master_file.close

my_conf.close

def slave(self):

my_conf = open(/etc/my.cnf , r)

my_conf_read = my_conf.readlines()

my_conf.close

if re.search(replicate-do-db ,str(my_conf_read)) is None:

master_file = open(slave , r)

read = master_file.read()

my_conf_read.insert(57,read)

my_conf = open(/etc/my.cnf , w)

my_conf.writelines(my_conf_read)

master_file.close

def re(self):

my_conf = open(/etc/my.cnf , r)

#my_conf_read = my_conf.readlines()

my_conf.close

if info == 1 :

= re.sub() = re.sub( log-bin=mysql-bin , #log-bin=mysql-bin ,my_conf.read())

else:

if re.search(binlog-do-db ,my_conf.read()) is None:

my_conf.seek(0)

= re.sub() = re.sub( server-id\t= 1\n , server-id\t= 2\n ,my_conf.read())

else:

my_conf.seek(0)

= my_conf.read() = my_conf.read()

my_conf = open(/etc/my.cnf , w)

my_conf.writelines(sub)

my_conf.close

m = msa()

while 1 b:

#os.system(clear)

print — * 35

print \t\t1)Installation MySQL Master\n

print \t\t2)Installation MySQL Slave\n

print \t\tQ)Quit Installation\n

print — * 35

if (err in dir()) is True:

print err

del err

info = raw_input(\t\tPlase,Enter your option:)

if info.lower() == q :

sys.exit()

elif info == 1 :

m.sql()

m.mk()

m.master()

m.re()

elif info == 2 :

m.mk()

m.slave()

m.re()

else:

err = \t\t***You enter the option error***

關(guān)于 MYSQL 中怎么實(shí)現(xiàn)單雙向同步問(wèn)題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒(méi)有解開(kāi)，可以關(guān)注丸趣 TV 行業(yè)資訊頻道了解更多相關(guān)知識(shí)。

正文完