分类： LINUX
2010-05-28 16:36:07
| INSERT
INTO mysql.user VALUES
('%','monitoruser',password("monitoruserpasswd"),'Y','N','N','N','N','N','Y','N','N','Y','N','N','N','N','N','Y','N','Y','N','Y','N','N','N','N','N','N','','','','',0,0,0,0); INSERT INTO mysql.db VALUES ('%','monitor_db','monitoruser','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); |
| #include #include #include #include #include int main(int argc,char *argv[]) { char *server="127.0.0.1",*user="monitoruser",*password="monitoruserpasswd"; MYSQL *conn; MYSQL_RES *res; MYSQL_ROW row; conn = mysql_init(NULL); if (!mysql_real_connect(conn, server,user, password, "monitor_db", 0, NULL, 0)) { fprintf(stderr, "%s\n", mysql_error(conn)); exit(EXIT_FAILURE); } uuid_t uu; char myuuid[37]; uuid_generate(uu); uuid_unparse(uu,myuuid); char sqlstr[512]; if (argc == 2) { char *opt = "--install"; if (!strcmp(argv[1],opt)) { strcpy(sqlstr,"DROP DATABASE monitor_db"); mysql_query(conn,sqlstr); strcpy(sqlstr,"CREATE DATABASE monitor_db"); mysql_query(conn,sqlstr); strcpy(sqlstr,"CREATE TABLE monitor_db.monitor_uuid (`uuid` char(41) NOT NULL,`time` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8"); mysql_query(conn,sqlstr); strcpy(sqlstr,"INSERT INTO monitor_db.monitor_uuid VALUES ('UUID_00000000-0000-0000-0000-0000000',0000000000)"); mysql_query(conn,sqlstr); if(mysql_affected_rows(conn) != 1) printf("install monitor_db error.\n"); } } else { strcpy(sqlstr,"UPDATE monitor_db.monitor_uuid SET uuid=\"UUID_"); strcat(strcat(sqlstr,myuuid),"\",time=UNIX_TIMESTAMP(NOW())"); mysql_query(conn,sqlstr); if(mysql_affected_rows(conn) <= 0) printf("update uuid error."); } mysql_close(conn); exit(EXIT_SUCCESS); } |
| #!/usr/bin/python """Change the master from mysql slave""" import os,sys,re,string,time,struct,MySQLdb DBuser='monitoruser' DBpasswd='monitoruserpasswd' def main(masterip): try: slave_link = MySQLdb.connect(host='127.0.0.1',user=DBuser,passwd=DBpasswd,db='mysql') except Exception, e: print e sys.exit(1) slave_connect = slave_link.cursor() # slave stop sql = "slave stop" slave_connect.execute(sql) # get uuid sql = "select uuid from monitor_db.monitor_uuid" slave_connect.execute(sql) UUID=slave_connect.fetchall()[0][0] # get Exec_Master_Log_Pos sql = "show slave status" slave_connect.execute(sql) local_var = slave_connect.fetchall()[0] Exec_Master_Log_Pos=local_var[21] # get uuid form relaylog fp = open("/var/log/mysqllog/"+local_var[7],"rb") binlogstr=struct.unpack("4s",fp.read(4)) if binlogstr[0] != chr(0xfe) + chr(0x62) + chr(0x69) + chr(0x6e): print "binlogfile error !" sys.exit(1) relay_log_pos = None while relay_log_pos == None: try: binlogstr=struct.unpack("4s 5x 4s 4s 2x",fp.read(19)) except: print "Error: get uuid from relaylog failed" sys.exit(1) event_length = ord(binlogstr[1][0]) + ord(binlogstr[1][1])*256 + ord(binlogstr[1][2])*65536 + ord(binlogstr[1][3])*16777216 event = struct.unpack(str(event_length-19)+"s",fp.read(event_length-19)) if re.search(UUID,event[0]): relay_log_pos = ord(binlogstr[2][0]) + ord(binlogstr[2][1])*256 + ord(binlogstr[2][2])*65536 + ord(binlogstr[2][3])*16777216 break fp.close() # connect mysql master try: master_link = MySQLdb.connect(host=masterip,user=DBuser,passwd=DBpasswd,db='mysql') except Exception, e: print e sys.exit(1) master_connect = master_link.cursor() # get master binlog file and size sql = "show binary logs" master_connect.execute(sql) file_size = master_connect.fetchall() # get uuid from master binlog pos = 0 sql_cmd = None for fz in file_size[::-1]: sql = "show binlog events in '%s'" % fz[0] master_connect.execute(sql) for remote_var in master_connect.fetchall(): if re.search(UUID,remote_var[5]): pos = Exec_Master_Log_Pos - relay_log_pos + remote_var[4] for fixsize in file_size[list(file_size).index(fz)::]: if pos <= fixsize[1]: sql_cmd = "change master to MASTER_HOST='%s',\ MASTER_USER='%s',\ MASTER_PASSWORD='%s',\ MASTER_LOG_FILE='%s',\ MASTER_LOG_POS=%s"\ % (masterip,DBuser,DBpasswd,fixsize[0],pos) break else: pos = pos - fixsize[1] + 117 break if sql_cmd is not None: break master_connect.close() master_link.close() # change master for sql in ["flush tables","reset slave",sql_cmd,"slave start"]: slave_connect.execute(sql) time.sleep(1) event_name= ['Slave_IO_State:','Master_Host:','Master_User:','Master_Port:','Connect_Retry:','Master_Log_File:','Read_Master_Log_Pos:', 'Relay_Log_File:','Relay_Log_Pos:','Relay_Master_Log_File:','Slave_IO_Running:','Slave_SQL_Running:','Replicate_Do_DB:', 'Replicate_Ignore_DB:', 'Replicate_Do_Table:','Replicate_Ignore_Table:','Replicate_Wild_Do_Table:','Replicate_Wild_Ignore_Table:', 'Last_Errno:','Last_Error:','Skip_Counter:','Exec_Master_Log_Pos:','Relay_Log_Space:','Until_Condition:','Until_Log_File:', 'Until_Log_Pos:','Master_SSL_Allowed:','Master_SSL_CA_File:','Master_SSL_CA_Path:','Master_SSL_Cert:', 'Master_SSL_Cipher:', 'Master_SSL_Key:','Seconds_Behind_Master:'] sql = "show slave status" slave_connect.execute(sql) event_status = slave_connect.fetchall()[0] for i in range(0,33): print event_name[i].rjust(28),event_status[i] slave_connect.close() slave_link.close() if (len(sys.argv) == 3): if (sys.argv[1] == '--masterip'): iplist=string.split(sys.argv[2],'.') if len(iplist) == 4: if ( 0 <= int(iplist[0]) < 256 ) and ( 0 <= int(iplist[1]) < 256 ) and ( 0 <= int(iplist[2]) < 256 ) and ( 0 <= int(iplist[3]) < 256 ): main(sys.argv[2]) sys.exit(0) else: print "%s --masterip xx.xx.xx.xx" % sys.argv[0] sys.exit(1) |
| #!/bin/bash Slave_IP="192.168.1.100" PATH=${PATH}:/usr/local/bin /etc/init.d/mysqld stop rm -rf /var/log/mysqllog/relay* mysql -u'monitoruser' -p'monitoruserpasswd' -h${Slave_IP} -e "slave stop;flush tables;" Exec_pos=($(mysql -u'replication_user' -p'800HRreplication' -h${Slave_IP} -e "show slave status\G"|awk '$0~/Exec_Master_Log_Pos/{print $2}')) typeset -x RSYNC_PASSWORD='rsyncpassword'; rsync -av --delete mysql-data@${Slave_IP}::mysql-data /var/lib/mysql/ sed '3 c\'${Exec_pos} -i /var/lib/mysql/master.info mysql -u'monitoruser' -p'monitoruserpasswd' -h${Slave_IP} -e "slave start;" /etc/init.d/mysqld start sleep 3 mysql -u'monitoruser' -p'monitoruserpasswd' -h127.0.0.1 -e "show slave status\G" |
| [client] #password = your_password port = 3306 socket = /var/run/mysqld/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] bind = 0.0.0.0 port = 3306 socket = /var/run/mysqld/mysql.sock #---------------------------------# skip-locking skip-innodb skip-bdb skip-name-resolve interactive_timeout = 1800 wait_timeout = 1800 default-character-set = gbk #------------connect--------------# open_files_limit = 2048 max_connect_errors = 2048 back_log = 1024 max_connections = 1024 net_buffer_length = 256K thread_cache_size = 256 #-------------cache---------------# query_cache_wlock_invalidate = 1 query_cache_size= 8M query_cache_min_res_unit = 8192 #----------global-resource--------# key_buffer_size = 512M max_length_for_sort_data = 8192 myisam_sort_buffer_size = 512M myisam_max_sort_file_size = 512M #----------thread-resource--------# sort_buffer_size = 4M join_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 2M # low_priority_updates = 0 #--------------tmp-table----------# table_cache = 2048 max_heap_table_size = 256M tmp_table_size = 512M #---------------------------------# # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 #---------------------------------# # S2 可关闭 binlog log-bin = /var/log/mysqllog/bin-log/mysql-bin server-id = 10 long_query_time = 5000000 log-slow-queries = /var/log/mysqllog/db-slow.log log-error = /var/log/mysqllog/db.err master-info-file = /var/lib/mysql/master.info max_relay_log_size = 512M relay-log = /var/log/mysqllog/relay-log relay-log-index = /var/log/mysqllog/relay-log-index relay-log-info-file = /var/log/mysqllog/relay-log.info #sql-bin-update-same expire_logs_days = 7 max_binlog_size = 1024M max_binlog_cache_size = 64M binlog_ignore_db=mysql binlog_ignore_db=mysql_slave binlog_ignore_db=mysql_master binlog_ignore_db=bin-log binlog_ignore_db=information_schema #---------------------------------# log-slave-updates #---------------------------------# # Point the following paths to different dedicated disks tmpdir = /var/tmp [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer_size = 512M sort_buffer_size = 8M read_buffer = 8M write_buffer = 8M [myisamchk] key_buffer_size = 512M sort_buffer_size = 8M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout |
| pid_file=mysql.pid server_pid_file=/var/run/mysqld/mysql.pid |
| #!/bin/bash Slave_IP="192.168.1.100" PATH=${PATH}:/usr/local/bin /etc/init.d/mysqld stop rm -rf /var/log/mysqllog/relay* mysql -u'monitoruser' -p'monitoruserpasswd' -h${Slave_IP} -e "slave stop;flush tables;" Exec_pos=($(mysql -u'replication_user' -p'800HRreplication' -h${Slave_IP} -e "show slave status\G"|awk '$0~/Exec_Master_Log_Pos/{print $2}')) typeset -x RSYNC_PASSWORD='rsyncpassword'; rsync -av --delete mysql-data@${Slave_IP}::mysql-data /var/lib/mysql/ sed '3 c\'${Exec_pos} -i /var/lib/mysql/master.info mysql -u'monitoruser' -p'monitoruserpasswd' -h${Slave_IP} -e "slave start;" /etc/init.d/mysqld start sleep 3 mysql -u'monitoruser' -p'monitoruserpasswd' -h127.0.0.1 -e "show slave status\G" |
| address = 192.168.1.100 port = 873 use chroot = true max connections = 20 syslog facility = local5 pid file = /var/run/rsyncd.pid [mysql-data] path = /var/lib/mysql comment = Mysql Data repository (requires authentication) hosts allow = 192.168.1.0/24 read only = true list = true uid = 57 gid = 57 auth users = mysql-user secrets file = /etc/rsyncd.secrets |
| #!/bin/bash User="monitoruser" Passwd="monitoruserpasswd" Host="192.168.1.101" if [[ "$1" == "--install" ]];then CMD="CREATE DATABASE /*!32312 IF NOT EXISTS*/ \`monitor_db\` /*!40100 DEFAULT CHARACTER SET gbk */; USE \`monitor_db\`; CREATE TABLE \`monitor_uuid\` ( \`uuid\` char(36) NOT NULL, \`time\` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=gbk; INSERT INTO \`monitor_uuid\` VALUES ('UUID_00000000-0000-0000-0000-0000000',0000000000); " /usr/local/bin/mysql -u${User} -p${Passwd} -h${Host} -B monitor_db -e "${CMD}" fi CMD="UPDATE monitor_db.monitor_uuid SET uuid=\"UUID_$(uuidgen)\",time=UNIX_TIMESTAMP(NOW());" /usr/local/bin/mysql -u${User} -p${Passwd} -h${Host} -B monitor_db -e "${CMD}" |
| #!/bin/bash # # change mysql slave master # # function and variables definition # PATH=${PATH}:/usr/local/bin typeset M_cmd DBuser="monitoruser" DBpasswd="monitoruserpasswd" Slave_ip="127.0.0.1" typeset -x $1 >/dev/null 2<&1 function check_var() { V=0 which mysql >/dev/null 2<&1 V=$((V+$?)) which mysqlbinlog >/dev/null 2<&1 V=$((V+$?)) if (( V != 0 ));then echo "mysql mysqlbinlog command no found" return 1 fi if [[ x"$master_ip" == x ]];then echo "$0 master_ip=xx.xx.xx.xx" return 1 fi _ip=(${master_ip//./ }) if (( ${#_ip[@]} == 4 ));then if (( 0>=${_ip[0]}<256 )) && (( 0>=${_ip[1]}<256 )) &&(( 0>=${_ip[2]}<256 )) && (( 0>=${_ip[3]}<256 ));then : fi else echo "master_ip is error" return 1 fi C_cmd="select Select_priv,Reload_priv,File_priv,Super_priv,Lock_tables_priv,Repl_slave_priv from mysql.user where User=\"${DBuser}\"\G" if (( $( mysql -u${DBuser} -p${DBpasswd} -h${Slave_ip} -e "${C_cmd}"|grep -o Y|wc -l ) < 12 ));then echo "Select_priv,Reload_priv,File_priv,Super_priv,Lock_tables_priv,Repl_slave_priv must is Y" echo "please check $Slave_ip mysql user privileges" return 1 elif (( $( mysql -u${DBuser} -p${DBpasswd} -h${master_ip} -e "${C_cmd}"|grep -o Y|wc -l ) < 12 ));then echo "Select_priv,Reload_priv,File_priv,Super_priv,Lock_tables_priv,Repl_slave_priv must is Y" echo "please check $master_ip mysql user privileges" return 1 fi return 0 } function slave_stop() { M_cmd="slave stop;" mysql -u${DBuser} -p${DBpasswd} -h${Slave_ip} -e "${M_cmd}" } function get_Exec_master_log_pos() { M_cmd="show slave status\G" mysql -u${DBuser} -p${DBpasswd} -h${Slave_ip} -e "${M_cmd}"|awk '$0~/Relay_Log_File|Slave_IO_Running|Slave_SQL_Running|Exec_Master_Log_Pos/{print $2}' } function get_uuid() { M_cmd="select uuid from monitor_db.monitor_uuid" mysql -u${DBuser} -p${DBpasswd} -h${Slave_ip} -e "${M_cmd}"|grep "UUID" } function get_uuid_end_log_pos_from_relaylog() { mysqlbinlog /var/log/mysqllog/${local_var[0]} |awk '{if($4~/'${UUID}'/){print S;exit}if($6=="end_log_pos")S=$7}' } function get_uuid_from_master_binlog() { M_cmd="show binary logs" File_size=($(mysql -u${DBuser} -p${DBpasswd} -h${master_ip} -e "${M_cmd}"|tail -n +2|sort -r)) for((F=0;F<${#File_size[@]};F+=2));do M_cmd="show binlog events in '${File_size[$F]}'" Bpos=$(mysql -u${DBuser} -p${DBpasswd} -h${master_ip} -e "${M_cmd}"|awk '$0~/'${UUID}'/{print $5}') if [[ x${Bpos} != x ]];then pos=$[$Bpos+${local_var[3]}-${Rpos}] for((S=$[F+1];S>0;S-=2));do if (( "$pos" <= "${File_size[$S]}" ));then echo "${File_size[$[S-1]]} ${pos}" return 0 fi pos=$[pos-${File_size[$S]}+117] done fi done return 1 } # main () if ! check_var;then exit 1;fi slave_stop local_var=($(get_Exec_master_log_pos)) if [ ${local_var[1]} != "No" -o ${local_var[2]} != "No" ];then echo "Error: mysql slave stop failed" exit 1 fi UUID=$(get_uuid) if [[ x"$UUID" == x ]];then echo "Error: get uuid failed" exit 1 fi Rpos=$(get_uuid_end_log_pos_from_relaylog) if [[ x"$Rpos" == x ]];then echo "Error: get uuid from relaylog failed" exit 1 fi Remote_var=($(get_uuid_from_master_binlog 2>/dev/null)) if (( $? == 0 ));then M_cmd=" flush tables; reset slave; change master to MASTER_HOST='${master_ip}',MASTER_USER='${DBuser}',MASTER_PASSWORD='${DBpasswd}', MASTER_LOG_FILE='${Remote_var[0]}',MASTER_LOG_POS=${Remote_var[1]}; slave start;" mysql -u${DBuser} -p${DBpasswd} -h${Slave_ip} -e "${M_cmd}" M_cmd="show slave status\G" sleep 3 mysql -u${DBuser} -p${DBpasswd} -h${Slave_ip} -e "${M_cmd}" exit 0 else echo "Error: get master binlog pos failed" exit fi |
