秒杀MYSQL单双向同步-原创手记-慕课网

简介：

采用脚本直接配置，请事先安装好MYSQL，附件中脚本包里有4个文件分别为mysqlsa.py(主程序）、mysqldb(数据库模块）、master（参数）、slave（参数）

程序文件下载地址：http://down.51cto.com/data/229410

Sever info:

Master:192.168.1.3

Slave:192.168.1.4

脚本使用教程：

Master:192.168.1.3

1、解压TAR包

[root@SQL1 down]# tar -zxf mysql-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默认安装路经不是‘/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

[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行内容是否写入

# 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

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、进入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

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:

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数据库休闲原创文章