mysql 主从,双主同步
1、创建用户并设置远程访问授权
1). A上添加: //ip地址为B的ip地址,用于B访问
# grant replication slave,replication client,reload,super on *.* to 'sync_user'@'192.168.2.220' identified by '123456' with grant option;
2). B上添加://ip地址为A的ip地址,用于A访问
# grant replication slave,replication client,reload,super on *.* to 'sync_user'@'192.168.2.67' identified by '123456' with grant option;
3). 执行命令更新数据库使用户生效。
# flush privileges;
2、确保mysql对外部ip开放
1). 确认一下3306是否对外开放,MySQL默认状态下是不开放对外访问功能的。查看的办法如下: ~# netstat -an | grep 3306 tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 从上面可以看出,mysql的3306端口只是监听本地的连接,这样就阻碍了外部IP对该数据库的访问,修改的办法其实很简单,进入到mysql的配置文件所在目录(/etc/mysql/my.cnf(默认),有的是在/etc/my.cnf)下,找到文件中的如下内容: # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1 将bind-address注释掉,或者改成你想要使用的客户端主机IP。 这样mysql的远程访问端口就算开启了
~# netstat -an | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
2). 关闭防火墙
# service iptables stop
3. 创建同步测试表
create database testsync; use testsync; create table t_user ( id int primary key auto_increment, username varchar(20), age int );
4. 配置A B的mysql(查看my.cnf位置 mysql --help | grep my.cnf)
# vi /etc/my.cnf
在[mysqld]下添加如下字段:
#两台机器的server-id不能相同 server-id = 1 log-bin=mysql-bin binlog_format=mixed replicate-do-db=testsync replicate-ignore-db=mysql,information_schema,performance_schema
5. 重启并进入mysql
0). 查看主机状态
# show master status;
+-------------------+----------+--------------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------------+------------------+-------------------+ | mysqld-bin.000005 | 68113 | happy,ipaddressmgr | | | +-------------------+----------+--------------------+------------------+-------------------+ 1 row in set (0.00 sec)
1). 设置主机信息
# change master to master_host='192.168.2.220',master_user='sync_user',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=68113;
2). 启动slave
# start slave;
3). 查看是否启动成功
# show processlist;
+----+-------------+-------------------------+------+-------------+------+-----------------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-------------------------+------+-------------+------+-----------------------------------------------------------------------------+------------------+ | 1 | system user | | NULL | Connect | 1264 | Waiting for master to send event | NULL | | 2 | system user | | NULL | Connect | 1262 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | | 38 | sync_user | pc.local:36102 | NULL | Binlog Dump | 1258 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 40 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+-------------+-------------------------+------+-------------+------+-----------------------------------------------------------------------------+------------------+
4). 查看slave上的master状态
#show master status;
5). 查看slave状态
# show slave status\G;
6. 以上便实现主从备份,如果想要双主,只需要在另一台mysql上,执行步骤4, 5即可。
错误处理:
101027 16:50:58 [Note] Slave I/O thread: connected to master 'xxx@xxxxxx:3306',replication started in log 'xxx-bin.000001' at position 1440046
101027 16:50:58 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236)
101027 16:50:58 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Error_code: 1236
101027 16:50:58 [Note] Slave I/O thread exiting, read up to log 'xxx-bin.000001', position 1440046
Slave_IO_Running: No
Slave_SQL_Running: Yes
原因:master_log_file或者master_log_pos配置错误 或者 权限错误
按照正常流程解决,应该是: stop slave; CHANGE MASTER TO MASTER_HOST='xxx',MASTER_USER='xx',MASTER_PASSWORD='xx',MASTER_PORT=3306,MASTER_LOG_FILE='xxx-bin.000001',MASTER_LOG_POS=1440046; start slave; 问题依然存在, 后来经过以下步骤解决,不过有点困惑: 1、重启主库
2、stop slave;
3、给从库重新授权
4、执行以下命令 reset slave; start slave;