Home > Database > Mysql Tutorial > Mysql基于binlog主从复制配置_MySQL

Mysql基于binlog主从复制配置_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Release: 2016-06-01 13:01:21
Original
1372 people have browsed it

上次配置mysql的主从复制还是年初的时候,没想到现在又开始使用mysql了。话不多说,进入正题

Master-Slave 搭建环境:
Master:
Os: rhel-server-6.3-x86_64
Mysql: MySQL -5.5.35-1
虚拟机:Virtual BOX
Ip:192.168.56.12

Slave:
Os: rhel-server-6.3-x86_64
Mysql: MySQL -5.5.35-1
虚拟机:Virtual BOX
Ip:192.168.56.13
Copy after login

总体步骤:
1、 修改Master配置文件(my.cnf)
2、 在Master上创建一个有复制权限的用户
3、 复制Master上的数据库到Slave
4、 修改Slave配置文件(my.cnf)
5、 Slave初始化复制
6、 测试
7、 常见问题

1、 修改Master配置文件(my.cnf)

[mysqld] 
server-id=1  //必须是服务器唯一id,默认是0,  
log-bin=mysql-bin //必须启用二进制日志
replicate-do-db=zbdba
Copy after login



2、 在Master上创建一个有复制权限的用户

mysql>create user zbdba; 
mysql>grant replication slave on *.* to zbdba identified by '123456';
Copy after login


3、 复制Master上的数据库到Slave

mysql>flush tables with read lock; 
mysql> show master status
    -> ;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| ogg1-bin.000003 |      107 |              |                  |
+-----------------+----------+--------------+------------------+1 row in set (0.00 sec)
mysql>quit
[root@ogg /]# /usr/bin/mysqldump zbdba -uroot -pmysql --opt | mysql zbdba -uroot -pmysql -h 192.168.56.13
需要在slave中授权:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.56.13' IDENTIFIED BY 'mysql' WITH GRANT OPTION;

mysql>unlock tables;
Copy after login

4、 修改Slave配置文件(my.cnf)

[mysqld] 
server-id=2  //必须是服务器唯一id,默认是0,  
log-bin=mysql-bin //必须启用二进制日志
replicate-do-db=zbdba
Copy after login



5、 Slave初始化复制
mysql>slave stop; 
mysql>CHANGE MASTER TO MASTER_HOST='192.168.56.12',
MASTER_USER='zbdba',MASTER_PASSWORD='123456',MASTER_LOG_FILE='ogg1-bin.000003',MASTER_LOG_POS=107;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.12
                  Master_User: zbdba
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: ogg1-bin.000003
          Read_Master_Log_Pos: 107
               Relay_Log_File: ogg2-relay-bin.000002
                Relay_Log_Pos: 252
        Relay_Master_Log_File: ogg1-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: zbdba
Copy after login


6、 测试

master:

mysql> show tables;
+-----------------+
| Tables_in_zbdba |
+-----------------+
| guestbook       |
| test            |
| test1           |
+-----------------+
Copy after login

create table test2 select * from test1;
Copy after login

mysql> show tables;
+-----------------+
| Tables_in_zbdba |
+-----------------+
| guestbook       |
| test            |
| test1           |
| test2           |
+-----------------+
Copy after login
salve:

mysql> show tables;
+-----------------+
| Tables_in_zbdba |
+-----------------+
| guestbook       |
| test            |
| test1           |
| test2           |
+-----------------+
4 rows in set (0.00 sec)
Copy after login



如果需要配置相互主从的关系,只需要在slave数据库上面建立对应用户,原master上面做一下初始化就行了。

7、 常见问题
/etc下面没有对应的my.cnf文件
解决方法:
如果mysql使用rpm包安装的,/etc下没有对应的my.cnf文件,这时候只需要复制一个/usr/share/mysql目录下的.cnf文件到/etc目录,并改名为my.cnf即可。


Server-id参数无法修改
解决方法:
看my.cnf中是否存在相同的server-id=1


MYSQL主备出现这个错误: ERROR 1201 (HY000):
解决方法:
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> reset slave;


Query OK, 0 rows affected,(0.00 sec)




ERROR 1130 (HY000): Host '*.*.*.*' is not allowed to connect to this MySQL server
解决方法:
如果你想允许用户myuser从ip为192.168.1.3的主机连接到mysql服务器,并使用mypassword作为密码
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;



Related labels:
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