Home > Database > Mysql Tutorial > centos6 mysql5.5配置masterslave数据同步备份_MySQL

centos6 mysql5.5配置masterslave数据同步备份_MySQL

WBOY
Release: 2016-06-01 13:01:21
Original
1220 people have browsed it

 

数据库A 做为主服务,数据库B做为同步库(注: 两边数据库库必须要一至,要不然会有不断的错误信息出来,改啊改,就是不成功)

1、配置 master 
vi /etc/my.cnf   
[mysqld]
server-id=1
log-bin=log path
binlog-do-db=xxxx //表示只备份xxxx这个库 ,可以配置多个库
binlog-ignore-db=yyy //表示忽略yyy库
注: 如果binlog-do-db,binlog-ignore-db不加会默认备份全部库

重启数据库    service mysqld restart

登陆到数据库  mysql -uroot -p123456

创建一个备份帐号,最好具有 slave, reload,super,权限
grant peplication slave,reload,super on *.* to 'backup'@'%'  identfiend by '1234' with grant option;
注:with grant option 这个必须有,表示backup 具有可操作权限
flush privileges;

show grant for 'backup'@'%'; //查看用户

show master status;

如果 ip 地址改变:
change master to master_host='xxx',master_user='backup',master_password='1234',master_port='3306',master_log_file='master-log.000001',master_log_pos=4, master_connect_retry=10;

2、配置 slave 
vi /etc/my.cnf 
server-id=2
change master to master_host='xxx',master_user='backup',master_password='1234',master_port='3306',master_log_file='master-log.000001',master_log_pos=4, master_connect_retry=10;
注:日志名称,用户名,密码必须与master 一至

show slave status\G;
查看信息是否正确

slave start;
用show slave status\G;

只要确定 : 
slave_io_running=yes
slave_sql_running=yes

就搞定了。

我遇到了2个问题
第一个:
slave_io_running=connecting
slave_sql_running=yes

last_io_error: error connecting master ......

找了老半天,原来是用户名密码整错了,真是浪费时间

第二个:(有点晕了)

slave_io_running=no
slave_sql_running=yes
last_io_error:   意思是说两边的库和表一至,同步没有找到相应的库和表

然后 :
slave stop;

reset slave; // 重新设置slave 

change master to master_host='xxx',master_user='backup',master_password='1234',master_port='3306',master_log_file='master-log.000001',master_log_pos=4, master_connect_retry=10;
再来一遍,没办法,最后终于看到 
slave_io_running=yes
slave_sql_running=yes
last_io_error: 为空
Copy after login
Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template