Shulou(Shulou.com)05/31 Report--

How to realize one-way and two-way synchronization in MYSQL? for this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Sever info:

Master:192.168.1.3

Slave:192.168.1.4

Script usage tutorial:

Master:192.168.1.3

1. Decompress the TAR package

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

2. Set permissions to enter the directory

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

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

3. Open the master file and set up the libraries you need to synchronize

[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. Run the script (note here that if your MYSQL default installation path is not'/ usr/local/mysql', change it to "export LD_LIBRARY_PATH=your path" your own path address on line 10 / 11 of this script, otherwise there may be an error in the MySQLdb module in the program.

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

1) Installation MySQL Master

2) Installation MySQL Slave

Q) Quit Installation

Plase,Enter your option:

1: configure and install the main MYSQL library

2: configure and install MYSQL slave library

Q: exit

5. We choose "1" to configure the MYSQL main library.

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

1) Installation MySQL Master

2) Installation MySQL Slave

Q) Quit Installation

Plase,Enter your option:1

6. Prompt to enter SLAVE server IP. This step is used to create an access account. We enter the IP:192.168.1.4 of SLAVE. Note: do not make a mistake, or SLAVE access will make an error.

Plase,Enter your option:1

Please,input your slave server ip:192.168.1.4

7, OK, the program will not give you any hints after the completion of the execution! Let's exit directly with an Q and check whether the following line 57 of / etc/my.cnf has been successfully written.

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. Restart MYSQL

[root@SQL1 down] # service mysqld restart

Slave:192.168.1.4

1. Decompress the TAR package

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

2. Set permissions to enter the directory

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

[root@SQL2 mysql-master-slave] #

3. Open the slave file and set the IP of the library and master that you need to synchronize.

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. Run the script (note here that if your MYSQL default installation path is not'/ usr/local/mysql', change it to "export LD_LIBRARY_PATH=your path" your own path address on line 10 / 11 of this script, otherwise there may be an error in the MySQLdb module in the program.

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

1) Installation MySQL Master

2) Installation MySQL Slave

Q) Quit Installation

Plase,Enter your option:

5. We choose'2' to install the slave library.

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

1) Installation MySQL Master

2) Installation MySQL Slave

Q) Quit Installation

Plase,Enter your option:2

6. Check whether the 57 lines of'/ etc/my.cnf' 'are written.

# 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. Restart MYSQL

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

Last step

1. Enter Master to adjust parameters

[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. Enter slave to adjust parameters

[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. Check whether it is successful

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

The above configuration has been completed, many friends may ask, is this just one-way synchronization?

Answer: as long as the two operations are done in reverse, OK

What is the problem with scripting?

A: http://down.51cto.com/data/229410

The following is the code for the main program

#! / 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,500500)

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 (57Jing 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 (57Jing 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 = = '1mm:

= 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\ nMy server-id 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 () = ='QQ:

Sys.exit ()

Elif info = = '1mm:

M.sql ()

M.mk ()

M.master ()

M.re ()

Elif info = = '2percent:

M.mk ()

M.slave ()

M.re ()

Else:

Err ='\ t\ t***You enter the option error***'

This is the answer to the question about how to achieve one-way and two-way synchronization in MYSQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.