MySQL 主从复制试验

一 机器角色

master 192.168.0.2 3306

slave    192.168.0.2 3307

 

二 主库

2.1 设置server-id的值并开启binlog日志

[mysqld]

server-id = 1

log-bin = /data/3306/mysql-bin

 

修改完mysql.cnf 需要重新启动数据库

 

# grep -E "server-id|log-bin" /data/3306/my.cnf

 

2.2 建立用于同步的账号

# mysql -uroot -p'3306' -S /data/3306/mysql.sock

mysql> grant replication slave on *.* to 'rep'@'192.168.0.%' indentified by 'rep123';

 

2.3 对数据库锁表只读

mysql > flush table with read lock;

mysql > show variables like '%timeout%'

interactive_timeout = 0;

wait_timeout = 60;

 

2.4 查看主表状态

mysql > show master status;

 

2.5 导出数据库数据

如果数据量很大，可以停库打包

# mkdir /server/backup -p

# mysqldump -uroot -p'3306' -S /data/3306/mysql.sock -A -B |gzip > /server/backup/mysql_bak.$(data +%F),sql.gz

mysql > unlock table;

 

2.6 把主库的数据迁移到从库

 

 

三 从库

3.1 设置server-id的值并关闭binlog日志

# grep -E "server-id|log-bin" /data/3307/my.cnf

 

3.2 还原主库备份数据

# gzip -d mysql_bak.2012-08-04.sql.gz

# mysql -uroot -p'password' -S  /data/3307/mysql.sock < mysql_bak.2012-08-04.sql

 

3.3 登录从库设置参数

# mysql -uroot -p'3307' -S /data/3307/mysql.sock -e "stop slave;"

# cat |mysql -uroot -p'3307' -S /data/3307/mysql.sock<< EOF
CHANGE MASTER TO  
MASTER_HOST='10.0.0.179', 
MASTER_PORT=3306,
MASTER_USER='rep', 
MASTER_PASSWORD='3306', 
MASTER_LOG_FILE='mysql-bin.000066',
MASTER_LOG_POS=257;
EOF

# mysql -uroot -p'3307' -S /data/3307/mysql.sock -e "start slave;"

# mysql -uroot -p'oldboy' -S /data/3307/mysql.sock -e "show slave status\G"

 

Error解决,怱略一个更新

Last_SQL_Error: Error 'Can't create database 'test1'; database exists' on query. Default database: 'test1'. Query: 'create database test1'

# mysql -uroot -p'oldboy' -S /data/3307/mysql.sock -e "set global sql_slave_skip_counter =1 ;"

 

# mysql -uroot -p'oldboy' -S /data/3307/mysql.sock -e "show slave status\G"|egrep "IO_Running|SQL_Running"

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

 

Replicate_Ignore_DB: mysql
Seconds_Behind_Master: 0

 

四 生产环境主从同步

4.1 申请停机时间

4.2 自动执行备份脚本

1 锁表备份全备一份

2 锁表前后取得show master status值记录日志里。

 

#!/bin/sh

#parameter defined
BAKDATE=`date +%F`
MYUSER=root
MYPASS="oldboy"
MYSOCK=/data/3306/mysql.sock
DBNAME=""
MAIN_PATH=/server/backup
DATA_PATH=/server/backup
LOG_FILE=${DATA_PATH}/mysql_logs_${BAKDATE}.log
DATA_FILE=${DATA_PATH}/mysql_backup_${BAKDATE}.sql.gz

#command defined
MYSQL_PATH=/usr/local/mysql/bin
#MYSQL_DUMP="$MYSQL_PATH/mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -A  -B -F --single-transaction -e"
MYSQL_DUMP="$MYSQL_PATH/mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F -B $DBNAME --default-character-set=gbk --single-transaction -e"
#backup command
${MYSQL_DUMP} | gzip > $DATA_FILE
#check backup result
du -sh $DATA_FILE >$LOG_FILE
mail -s "${BAKDATE} mysql bak log" nbcoder@163.com < $LOG_FILE

 

4.3 不停主库一键批量创建从库脚本

 

五 其它配置
1 配置MySQL怱略权限参数

2 主从库授权

 

http://sishuok.com/forum/blogPost/list/5329.html