mysql主从同步配置

参考网址：http://blog.csdn.net/yangsongpan502/article/details/2861457

1.master上操作(web114)

GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO backup@172.16.4.188 IDENTIFIED BY '******';
flush privileges;


#vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin   //[必须]启用二进制日志
server-id=1       //[必须]服务器唯一ID，默认是1
binlog_format=mixed


2.slave上操作(web188)
#vi /etc/my.cnf
server-id=2
log-bin=mysql-bin
binlog_format=mixed
#event_scheduler=1 //注释掉事件任务

expire_logs_days=10

replicate_ignore_db=amon //过滤数据库
replicate_ignore_db=hmon
replicate_ignore_db=smon

replicate-ignore-table=bi_monitor.user_monitor //过滤单个表
replicate-wild-ignore-table=bhdp.merge% //过滤多个表

query_cache_type=0 //运行中出现invalidating query cache entries (table) 异常，然后配置此项解决了
query_cache_size=0 //运行中出现invalidating query cache entries (table) 异常，然后配置此项解决了

########后面的参数5.5版本测试不支持，需运行时配置同步##############
master-host=172.16.4.114
master-user=backup
master-password=***




3.重启两台服务器的mysql
service mysql restart


4.复制数据到slave


主服务操作：

mysql>flush tables with read lock;

mysql>show master status;

此处主要记录下file和position的值，slave端要使用到。
mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000002 | 446935067 |              |                  |
+------------------+-----------+--------------+------------------+




备份完后，主服务器操作：
mysql>unlock tables;


备份操作：
/*
myisam引擎的数据库可以直接cp文件，但注意得手动备份存储过程、函数和事件,bhdp有innodb引擎的表
tar -zcvf part.tar.gz kettle/ wap_data wap_data_temp/
tar -zcvf bhdp.tar.gz ./bhdp/bhdp_* ./bhdp/db.opt  ./bhdp/merge_*frm  ./bhdp/sys_logs*
tar -zcvf bi_monitor.tar.gz bi_monitor/db.opt  bi_monitor/rpt_* bi_monitor/t_partition_log.* bi_monitor/user_stat_*
scp *.tar.gz root@web188:/var/lib/mysql/
*/

(备份bhdp bi_monitor data hive kettle rtc_report wap_data wap_data_temp)



mysqldump -h172.16.4.114 -ubi_admin -p*** --default-character-set=utf8 --single-transaction --triggers -R -E  --databases bhdp --no-data >bhdp_1.sql
mysqldump -h172.16.4.114 -ubi_admin -p*** --default-character-set=utf8 --single-transaction --databases bhdp --tables bhdp_alarm_main_switch bhdp_datasource bhdp_dim_clt_type bhdp_hdelete bhdp_merge bhdp_report_datemap bhdp_report_event bhdp_report_event_export bhdp_table_lockstatus bhdp_table_read_info bhdp_table_read_info_test bhdp_web_user sys_logs >bhdp_2.sql

mysqldump -h172.16.4.114 -ubi_admin -p*** --default-character-set=utf8 --single-transaction --triggers -R -E  --databases bi_monitor --no-data >bi_monitor_1.sql
mysqldump -h172.16.4.114 -ubi_admin -p*** --default-character-set=utf8 --single-transaction --databases bi_monitor --tables t_partition_log  user_stat_url_config user_stat_url_report user_stat_url_sqltype user_stat_url_type user_stat_website_trend >bi_monitor_2.sql

mysqldump -h172.16.4.114 -ubi_admin -p*** --default-character-set=utf8 --single-transaction --triggers -R -E  --databases data >data.sql

mysqldump -h172.16.4.114 -ubi_admin -p*** --default-character-set=utf8 --single-transaction --triggers -R -E  --databases hive >hive.sql

mysqldump -h172.16.4.114 -ubi_admin -p*** --default-character-set=utf8 --single-transaction --triggers -R -E  --databases kettle >kettle.sql

mysqldump -h172.16.4.114 -ubi_admin -p*** --default-character-set=utf8 --single-transaction --triggers -R -E  --databases rtc_report >rtc_report.sql

mysqldump -h172.16.4.114 -ubi_admin -p*** --default-character-set=utf8 --single-transaction --triggers -R -E  --databases wap_data --no-data >wap_data_1.sql
mysqldump -h172.16.4.114 -ubi_admin -p*** --default-character-set=utf8 --single-transaction --databases wap_data --tables dim_user_reg_channel wap_hive_report wap_im_report wap_log_login wap_log_pay wap_log_photo wap_log_regist wap_log_sign >wap_data_2.sql

mysqldump -h172.16.4.114 -ubi_admin -p*** --default-character-set=utf8 --single-transaction --triggers -R -E  --databases wap_data_temp >wap_data_temp.sql


从服务器导入：
mysql -h172.16.4.188 -ubi_admin -p***

drop database bhdp;
drop database bi_monitor;
drop database data;
drop database hive;
drop database kettle;
drop database rtc_report;
drop database wap_data;
drop database wap_data_temp;

source bhdp_1.sql
source bhdp_2.sql
source bi_monitor_1.sql
source bi_monitor_2.sql
source data.sql
source hive.sql
source kettle.sql
source rtc_report.sql
source wap_data_1.sql
source wap_data_2.sql
source wap_data_temp.sql


5.配置从服务器Slave：
//注意不要断开，446935067无单引号。
   mysql>change master to master_host='172.16.4.114',master_user='backup',master_password='*******',master_log_file='mysql-bin.000002',master_log_pos=446935067;   

//启动从服务器复制功能
   Mysql>start slave;    
   
6.在从服务器进行show salve status验证
mysql> SHOW SLAVE STATUS\G


7.设置自动清理MySQL binlog日志，配置my.cnf：
expire_logs_days = 10
在运行时修改：

show binary logs; 
show variables like '%log%'; 
set global expire_logs_days = 10;

清除之前可以采用相应的备份策略。
手动删除10天前的MySQL binlog日志：
PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY); 
show master logs;

MASTER和BINARY是同义词。