参考网址: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是同义词。