multi master to one slave (Mysql replication)

[mysqld]

default-character-set=utf8
character-set-server=utf8
default-collation=utf8_unicode_ci


datadir=/stagedb/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
innodb_data_home_dir=/stagedb
default-table-type=INNODB
#Replication
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=oddsmatrixdb
server-id=1

#OS: tuning
table_cache = 1024
key_buffer = 256M
record_buffer = 16M
wait_timeout=800
connect_timeout=25
interactive_timeout=800
max_connections=500
sort_buffer = 128M
#innodb_log_file_size = 50M
innodb_log_buffer_size = 8M
innodb_buffer_pool_size = 1300MB
innodb_thread_concurrency=50
innodb_flush_method=O_DSYNC
innodb_status_file = 1
innodb_flush_log_at_trx_commit = 0
innodb_autoextend_increment = 25M
skip-external-locking
innodb_table_locks=0

max_allowed_packet=100M
tmp_table_size=96M
thread_concurrency=10
thread_cache_size=500

#result set and query cache
query_cache_type = 0
query_cache_size = 0

long_query_time = 1
log_slow_queries = /var/log/mysql/slow-queries.log


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

-----2009-09-14

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root
password =

[mysqld2]
socket = /var/lib/mysql2/mysql.sock2
port = 3307
pid-file = /var/lib/mysql2/hostname.pid2
datadir = /var/lib/mysql2
language = /usr/share/mysql/english
user = root
#Replication
server-id=2
master-host=192.168.10.191
master-user=slave
master-password=slavepass
#master_log_file=mysql-bin.000001
#master_log_pos=43029
master-connect-retry=60
replicate-do-db=oddsmatrixdb
relay-log = /var/log/mysqld/relay2.log
relay-log-info-file = /var/log/mysqld/relay2-log.info
relay-log-index = /var/log/mysqld/relay2-log.index
#Tunning
default-character-set=utf8
character-set-server=utf8
default-collation=utf8_unicode_ci
wait_timeout=800
connect_timeout=25
interactive_timeout=800
max_connections=50
sort_buffer = 228M
group_concat_max_len=65536
innodb_buffer_pool_size=4000MB
innodb_additional_mem_pool_size=20M
innodb_log_file_size=128M
innodb_log_buffer_size=8M
innodb_thread_concurrency=500
innodb_flush_method=O_DSYNC
table_cache=800
innodb_status_file = 1
innodb_flush_log_at_trx_commit = 0
innodb_autoextend_increment = 25M
skip-external-locking
innodb_table_locks=0
max_allowed_packet=100M
tmp_table_size=96M
thread_concurrency=10
thread_cache_size=500
log-error=/var/log/mysqld/mysql2.log

[mysqld3]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
socket = /var/lib/mysql3/mysql.sock3
port = 3308
pid-file = /var/lib/mysql3/hostname.pid3
datadir = /var/lib/mysql3
language = /usr/share/mysql/english
user = root
server-id=2
master-host=192.168.10.194
master-user=slave
master-password=slavepass
#master_log_file=mysql-bin.000001
#master_log_pos=4028
master-connect-retry=60
default-character-set=utf8
character-set-server=utf8
default-collation=utf8_unicode_ci
default-table-type=INNODB
wait_timeout=800
connect_timeout=25
interactive_timeout=800
max_connections=50
sort_buffer = 228M
group_concat_max_len=65536
innodb_buffer_pool_size=4000MB
innodb_additional_mem_pool_size=20M
innodb_log_file_size=128M
innodb_log_buffer_size=8M
innodb_thread_concurrency=500
innodb_flush_method=O_DSYNC
table_cache=800
innodb_status_file = 1
innodb_flush_log_at_trx_commit = 0
innodb_autoextend_increment = 25M
skip-external-locking
innodb_table_locks=0
max_allowed_packet=32M
tmp_table_size=96M
thread_concurrency=10
thread_cache_size=500
log-error=/var/log/mysqld/mysql3.log
slave-skip-errors=1061,1062
skip-slave-start

mysqld (script)

#!/bin/bash
#
# mysqld This shell script takes care of starting and stopping
# the MySQL subsystem (mysqld).
#
# chkconfig: - 64 36
# description: MySQL database server.
# processname: mysqld
# config: /etc/my.cnf
# pidfile: /var/run/mysqld/mysqld.pid

# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network


prog="MySQL"

# extract value of a MySQL option from config files
# Usage: get_mysql_option SECTION VARNAME DEFAULT
# result is returned in $result
# We use my_print_defaults which prints all options from multiple files,
# with the more specific ones later; hence take the last match.
get_mysql_option(){
result=`/usr/bin/my_print_defaults "$1" | sed -n "s/^--$2=//p" | tail -n 1`
if [ -z "$result" ]; then
# not found, use default
result="$3"
fi
}

get_mysql_option mysqld datadir "/var/lib/mysql"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql.sock"
socketfile="$result"
get_mysql_option mysqld_safe log-error "/var/log/mysqld.log"
errlogfile="$result"
get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld.pid"
mypidfile="$result"

start(){
touch "$errlogfile"
chown mysql:mysql "$errlogfile"
chmod 0640 "$errlogfile"
[ -x /sbin/restorecon ] && /sbin/restorecon "$errlogfile"
if [ ! -d "$datadir/mysql" ] ; then
action $"Initializing MySQL database: " /usr/bin/mysql_install_db
ret=$?
chown -R mysql:mysql "$datadir"
if [ $ret -ne 0 ] ; then
return $ret
fi
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
# Pass all the options determined above, to ensure consistent behavior.
# In many cases mysqld_safe would arrive at the same conclusions anyway
# but we need to be sure.
/usr/bin/mysqld_safe --datadir="$datadir" --socket="$socketfile"
--log-error="$errlogfile" --pid-file="$mypidfile"
>/dev/null 2>&1 &
ret=$?
# Spin for a maximum of N seconds waiting for the server to come up.
# Rather than assuming we know a valid username, accept an "access
# denied" response as meaning the server is functioning.
if [ $ret -eq 0 ]; then
STARTTIMEOUT=30
while [ $STARTTIMEOUT -gt 0 ]; do
RESPONSE=`/usr/bin/mysqladmin -uUNKNOWN_MYSQL_USER ping 2>&1` && break
echo "$RESPONSE" | grep -q "Access denied for user" && break
sleep 1
let STARTTIMEOUT=${STARTTIMEOUT}-1
done
if [ $STARTTIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to start MySQL Daemon."
action $"Starting $prog: " /bin/false
ret=1
else
action $"Starting $prog: " /bin/true
fi
else
action $"Starting $prog: " /bin/false
fi
[ $ret -eq 0 ] && touch /var/lock/subsys/mysqld
return $ret
}

stop(){
MYSQLPID=`cat "$mypidfile" 2>/dev/null `
if [ -n "$MYSQLPID" ]; then
/bin/kill "$MYSQLPID" >/dev/null 2>&1
ret=$?
if [ $ret -eq 0 ]; then
STOPTIMEOUT=60
while [ $STOPTIMEOUT -gt 0 ]; do
/bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break
sleep 1
let STOPTIMEOUT=${STOPTIMEOUT}-1
done
if [ $STOPTIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to stop MySQL Daemon."
ret=1
action $"Stopping $prog: " /bin/false
else
rm -f /var/lock/subsys/mysqld
rm -f "$socketfile"
action $"Stopping $prog: " /bin/true
fi
else
action $"Stopping $prog: " /bin/false
fi
else
ret=1
action $"Stopping $prog: " /bin/false
fi
return $ret
}

restart(){
stop
start
}

condrestart(){
[ -e /var/lock/subsys/mysqld ] && restart || :
}

# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
status)
status mysqld
;;
restart)
restart
;;
condrestart)
condrestart
;;
*)
echo $"Usage: $0 {start|stop|status|condrestart|restart}"
exit 1
esac

exit $?

begin
DECLARE no_more_gbmes int;
declare aid int;
declare auserLogId int;
declare aTime datetime;
declare aminid int;
declare num int;

declare gbmes_csr CURSOR FOR SELECT id,userLogId,accessTime,maxID FROM Ua2
where id>=11 and id<=50;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_gbmes=1;
SET no_more_gbmes=0;
OPEN gbmes_csr;
REPEAT
fetch gbmes_csr INTO aid,auserLogId,aTime,aminid;
delete from UserRequestLog where accessTime >'2009-07-20 00:00:00' and accessTime aminid;
until no_more_gbmes
END REPEAT;
CLOSE gbmes_csr;

end

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=root
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=bestbrain
log_bin = /var/log/mysql/log_bin
server-id=1
binlog-do-db = ccxyaccount
binlog-do-db = ccxydb
#sql-bin-update-same
#binlog-do-db = ccxyaccount,ccxydb
#binlog-ignore-db = mysql,test

master-host = 10.0.4.150
master-user = back
master-password = back
master-port = 3306
replicate-do-db=ccxyaccount
replicate-ignore-db=mysql,test

-----

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=root
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=

server-id = 2
master-host = 10.0.4.149
master-user = back
master-password = back
master-port = 3306
master-connect-retry = 120
read-only = 1
relay-log-purge = 1
replicate-do-db = ccxyaccount
replicate-ignore-db = mysql,test,ccxydb
report-host = back
slave-skip-errors = all
#master-log-file = ccxy_db.000008

log_bin=/var/log/mysql/log_bin
binlog-do-db=ccxyaccount
#binlog-ignore-db=mysql,test,ccxydb

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/91551/viewspace-1023309/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/91551/viewspace-1023309/