簡介 ：
采用腳本直接配置，請事先安裝好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
4、運行腳本程序（這裡注意如果你的MYSQL默認安裝路經不是‘/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、我們選擇‘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
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, 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***'
本文出自 “Intelligence” 博客
