共计 7293 个字符，预计需要花费 19 分钟才能阅读完成。
MYSQL 中怎么实现单双向同步，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。
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、进入目录设置权限
[root@SQL1 down]# cd mysql-master-slave
[root@SQL1 mysql-master-slave]# chmod 777 mysqlsa.py
3、打开 master 文件，设置你需要同步的库
[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、运行脚本程序（这里注意如果你的 MYSQL 默认安装路经不是 lsquo;/usr/local/mysql 在此脚本程序的 10~11 行把成“export LD_LIBRARY_PATH=your path 你自己路经地址，否则有可能程序里面 MySQLdb 模块报错
[root@SQL1 mysql-master-slave]# ./mysqlsa.py
———————————————————————-
1)Installation MySQL Master
2)Installation MySQL Slave
Q)Quit Installation
———————————————————————-
Plase,Enter your option:
1：配置安装 MYSQL 主库
2：配置安装 MYSQL 从库
Q：退出
5、我们选择“1”配置 MYSQL 主库
[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 服务器 IP，这步是创建访问帐号用的，我们输入 SLAVE 的 IP：192.168.1.4，注意：千万别输错，不然 SLAVE 访问就会出错
Plase,Enter your option:1
Please,input your slave server ip:192.168.1.4
7、OK, 执行完成以后程序不会给你任何提示！我们直接安 Q 退出，查看 /etc/my.cnf 的 57 行下面内容是否成功写入
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、进入目录设置权限
[root@SQL2 down]# cd mysql-master-slave
[root@SQL2 mysql-master-slave]#
3、打开 slave 文件，设置你需要同步的库和 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、运行脚本程序（这里注意如果你的 MYSQL 默认安装路经不是 lsquo;/usr/local/mysql 在此脚本程序的 10~11 行把成“export LD_LIBRARY_PATH=your path 你自己路经地址，否则有可能程序里面 MySQLdb 模块报错
[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; 安装从库
[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 行内容是否写入
# 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、进入 Master 调整参数
[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
Copyright (c) 2000, 2010, and/or its affiliates. All rights reserved.
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;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| updatelog.000001 | 106 | wgdb | mysql,test |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
2、进入 slave 调整参数
[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
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
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
以上配置已经全部完成，很多朋友可能会问，这只是单向同步吗？
答：只要把两个操作反向做下就 OK
脚本程序问题在哪里？
答：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:
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***
关于 MYSQL 中怎么实现单双向同步问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注丸趣 TV 行业资讯频道了解更多相关知识。
