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

成都創(chuàng)新互聯(lián)是專業(yè)的迎澤網(wǎng)站建設(shè)公司，迎澤接單;提供成都網(wǎng)站設(shè)計、成都網(wǎng)站制作,網(wǎng)頁設(shè)計,網(wǎng)站設(shè)計,建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行迎澤網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊,希望更多企業(yè)前來合作!

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、打開master文件，設(shè)置你需要同步的庫

[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默認(rèn)安裝路經(jīng)不是‘/usr/local/mysql'在此腳本程序的10~11行把成“export LD_LIBRARY_PATH=your path '你自己路經(jīng)地址，否則有可能程序里面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服務(wù)器IP，這步是創(chuàng)建訪問帳號用的，我們輸入SLAVE的IP：192.168.1.4，注意：千萬別輸錯，不然SLAVE訪問就會出錯

                             Plase,Enter your option:1

Please,input your slave server ip:192.168.1.4

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

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、打開slave文件，設(shè)置你需要同步的庫和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默認(rèn)安裝路經(jīng)不是‘/usr/local/mysql'在此腳本程序的10~11行把成“export LD_LIBRARY_PATH=your path '你自己路經(jīng)地址，否則有可能程序里面MySQLdb模塊報錯

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

----------------------------------------------------------------------

                                1)Installation MySQL Master

                                2)Installation MySQL Slave

                                Q)Quit Installation

----------------------------------------------------------------------

                                Plase,Enter your option:

5、我們選擇‘2’安裝從庫

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

----------------------------------------------------------------------

                                1)Installation MySQL Master

                                2)Installation MySQL Slave

                                Q)Quit Installation

----------------------------------------------------------------------

                                Plase,Enter your option:2

6、檢查下‘/etc/my.cnf' 57行內(nèi)容是否寫入

# 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

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、進(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

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

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

答：只要把兩個操作反向做下就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***'

關(guān)于MYSQL中怎么實現(xiàn)單雙向同步問題的解答就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道了解更多相關(guān)知識。

新聞標(biāo)題：MYSQL中怎么實現(xiàn)單雙向同步-創(chuàng)新互聯(lián)
網(wǎng)站鏈接：http://www.aaarwkj.com/article44/ccoeee.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供動態(tài)網(wǎng)站、網(wǎng)頁設(shè)計公司、網(wǎng)站營銷、搜索引擎優(yōu)化、關(guān)鍵詞優(yōu)化、App開發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)