mysql 双机热备 实战

// 首先主从的mysql版本必须一致 , 否则可能引发 2003 问题,

master-master 就是两台服务器互为主从： 

服务器 A(master1) && 服务器 B(master2)

master1: 10.236.152.63
master2: 10.236.152.29


配置A 
mysql 版本最好一致
给从机赋予登陆权限 如果有多个从属的机子, 都要添加权限

创建给B登录用的用户名密码:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'10.236.152.29' IDENTIFIED BY '123456';
mysql> GRANT FILE ON *.* TO 'backup'@'10.236.152.29' IDENTIFIED BY '123456';
mysql> create database replication_test default charset utf8;

配置A的my.cnf

# vim /etc/my.cnf
[mysqld]
server-id=1    #主机id
#开启二进制日志，并且名称为 /var/lib/mysql/mysql-bin.***
# 如果是个路径则，保存到该路径下(log-bin=/var/log/mysql-bin.log  -> /var/log/mysql-bin.***)
log-bin=mysql-bin
read-only=0  #主机读写权限，读写都可以
binlog-do-db=replication_test   #记录日志的数据库：需要的备份数据，多个写多行
binlog-ignore-db=mysql #不记录日志的数据库：不需要备份的数据库，多个写多行
binlog-ignore-db=test
binlog-ignore-db=information_schema

# 自增字段奇数递增，防止冲突（1, 3, 5, ...,）
auto-increment-increment = 2  # 每次递增的步长
auto-increment-offset = 1  # 初始值
重启 mysql: serivce mysqld restart 或者 /etc/init.d/mysql restart

注释掉bind-address 127.0.0.1 这句, 这样3306 可以监听到来自非本机的请求

注释掉bind-address 127.0.0.1 这句, 这样3306 可以监听到来自非本机的请求

注释掉bind-address 127.0.0.1 这句, 这样3306 可以监听到来自非本机的请求

哈哈, 重要的事情说三遍,  我在配置的时候忘记了, 导致登陆不了,然后一直Slave_IO_Running  出connect状态,  报错2003

导出数据库

锁定数据库，不要退出 mysql shell

mysql>FLUSH TABLES WITH READ LOCK;
mysql># flush tables with read lock;

新开一个终端，导出数据库

mysqldump --master-data -uroot -p replication_test > replication_test.sql

查看主服务器的状态
mysql> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 98
    Binlog_Do_DB: replication_test
Binlog_Ignore_DB: mysql,test,information_schema
1 row in set (0.00 sec)


ERROR: 
No query specified
记下 Position 和 File 的值。 解锁
mysql> UNLOCK TABLES;
mysql> # unlock tables;

A的配置告一段落

这里配置 B 的相关属性
编辑 my.cnf
# vim /etc/my.cnf

[mysqld]
server-id=2
log-bin=mysql-bin
replicate-do-db =replication_test #只复制某个库，多个写多行
replicate-ignore-db=mysql #不复制某个库
replicate-ignore-db=test
replicate-ignore-db=information_schema
relay-log=mysqld-relay-bin  # 开启日志中继
log-slave-updates  # slave将复制事件写进自己的二进制日志
#5.5
#log-slave-updates = ON
#5.1
#log-slave-updates = 1
导入 master 导出的数据库：


mysql> create database replication_test default charset utf8;
mysql -uroot -p replication_test < replication_test.sql
在B上配置 master 信息：

mysql> slave stop;

mysql> CHANGE MASTER TO
-> MASTER_HOST='10.236.152.63', 
-> MASTER_USER='backup', 
-> MASTER_PASSWORD='123456',
-> MASTER_CONNECT_RETRY=60,  // 如果从服务器发现主服务器断掉，重新连接的时间差(秒)
-> MASTER_LOG_FILE='mysql-bin.000001', //主服务器二进制日志的文件名(前面要求记住的 File 参数)
-> MASTER_LOG_POS=98; //日志文件的开始位置(前面要求记住的 Position 参数)


mysql> CHANGE MASTER TO MASTER_HOST='10.236.152.63', MASTER_USER='backup', MASTER_PASSWORD='123456', MASTER_CONNECT_RETRY=60, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;


mysql > slave start;
重启 master, slave: service mysqld restart


查看 slave 状态：
进入 B

mysql> show slave status\G;
*************************** 1. row ***************************
            Slave_IO_State: Connecting to master
                Master_Host: 10.236.152.63
                Master_User: backup
                Master_Port: 3306
            Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 98
            Relay_Log_File: mysqld-relay-bin.000001
            Relay_Log_Pos: 98
    Relay_Master_Log_File: mysql-bin.000001
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
            Replicate_Do_DB: replication_test
        Replicate_Ignore_DB: mysql,test,information_schema
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 98
            Until_Log_Pos: 0
    Seconds_Behind_Master: NULL
1 row in set (0.00 sec)
注意一定要有下面两项，没有的话查看错误日志(less /var/log/mysqld.log)：


        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
测试


进入A服务器
mysql> use replication_test;
Database changed
mysql> create table test(id int);
mysql> insert int test set id=1;


mysql> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000002  # 注意这里
        Position: 276    # 注意这里
    Binlog_Do_DB: replication_test
Binlog_Ignore_DB: mysql,test,information_schema
1 row in set (0.00 sec)

B 服务器

mysql> use replication_test;
mysql> show tables;
+-----------------+
| Tables_in_replication_test |
+-----------------+
| test            | 
+-----------------+
1 row in set (0.00 sec)


mysql> select * from test;
+------+
| id   |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)


mysql> show slave status\G;
*************************** 1. row ***************************
            Slave_IO_State: Waiting for master to send event
                Master_Host: 10.236.152.63
                Master_User: backup
                Master_Port: 3306
            Connect_Retry: 60
            Master_Log_File: mysql-bin.000002  # 跟 master 一样
        Read_Master_Log_Pos: 276  # 跟 master 一样
            Relay_Log_File: mysqld-relay-bin.000003
            Relay_Log_Pos: 413
    Relay_Master_Log_File: mysql-bin.000002
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
            Replicate_Do_DB: replication_test
        Replicate_Ignore_DB: mysql,test,information_schema
                Last_Errno: 0
                Last_Error: 
            Skip_Counter: 0
        Exec_Master_Log_Pos: 276
            Relay_Log_Space: 413
    Seconds_Behind_Master: 0
1 row in set (0.00 sec)


进入B服务器
mysql> grant replication slave on *.* to 'backup'@'10.236.152.63' identified by '123456';
mysql> grant FILE on *.* to 'backup'@'10.236.152.63' identified by '123456';
mysql> flush tables with read lock;
mysql> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000002
        Position: 276
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
mysql> unlock tables;
vim /etc/my.cnf:


# as master
#开启二进制日志，并且名称为 /var/log/mysql/mysql-bin.***
log-bin=mysql-bin
read-only=0  #主机读写权限，读写都可以
binlog-do-db=replication_test   #记录日志的数据库：需要的备份数据，多个写多行
binlog-ignore-db=mysql #不记录日志的数据库：不需要备份的数据库，多个写多行
binlog-ignore-db=test
binlog-ignore-db=information_schema


# 自增字段偶数递增，防止冲突（2, 4, 6, ...,）
auto-increment-increment = 2  # 每次递增的步长
auto-increment-offset = 2  # 初始值
service mysqld restart


进入A服务器

mysql> slave stop;

mysql>  CHANGE MASTER TO MASTER_HOST='10.236.152.29', MASTER_USER='backup', MASTER_PASSWORD='123456', MASTER_CONNECT_RETRY=60, MASTER_LOG_FILE='mysql-bin.000002, MASTER_LOG_POS=276';

mysql> slave start;
vim /etc/my.cnf

# as slave
replicate-do-db =replication_test #只复制某个库，多个写多行
replicate-ignore-db=mysql #不复制某个库
replicate-ignore-db=test
replicate-ignore-db=information_schema
relay-log=mysqld-relay-bin  # 开启日志中继
log-slave-updates  # slave将复制事件写进自己的二进制日志
serivce mysqld restart

下面测试一下是否正常 
B服务器


mysql> use replication_test;    
mysql> insert into test set id=2;
mysql> select * from test;
+------+
| id   |
+------+
|    1 | 
|    2 | 
+------+
2 rows in set (0.00 sec)
mysql> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000003
        Position: 187
    Binlog_Do_DB: replication_test
Binlog_Ignore_DB: mysql,test,information_schema
1 row in set (0.00 sec)

A服务器

mysql> show slave status \G;
*************************** 1. row ***************************
            Slave_IO_State: Waiting for master to send event
                Master_Host: 10.236.152.29
                Master_User: backup
                Master_Port: 3306
            Connect_Retry: 60
            Master_Log_File: mysql-bin.000003
        Read_Master_Log_Pos: 187
            Relay_Log_File: mysqld-relay-bin.000003
            Relay_Log_Pos: 324
    Relay_Master_Log_File: mysql-bin.000003
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
            Replicate_Do_DB: replication_test
        Replicate_Ignore_DB: mysql,test,information_schema
                Last_Errno: 0
                Last_Error: 
            Skip_Counter: 0
        Exec_Master_Log_Pos: 187
            Relay_Log_Space: 324
            Until_Condition: None
            Until_Log_File: 
            Until_Log_Pos: 0
    Seconds_Behind_Master: 0


mysql> use replication_test;


mysql> select * from test;
+------+
| id   |
+------+
|    1 | 
|    2 | 
+------+
2 rows in set (0.00 sec)


最后的完整的配置文件


A服务器

# /etc/my.cnf
[mysqld]

# as master
server-id=1    #主机id，整数
#开启二进制日志，并且名称为 /var/lib/mysql/mysql-bin.***
log-bin=mysql-bin
read-only=0  #主机读写权限，读写都可以
binlog-do-db=replication_test   #记录日志的数据库：需要的备份数据，多个写多行
# binlog-do-db=replication_test2
binlog-ignore-db=mysql #不记录日志的数据库：不需要备份的数据库，多个写多行
binlog-ignore-db=test
binlog-ignore-db=information_schema


# as slave
replicate-do-db =replication_test #只复制某个库，多个写多行
replicate-ignore-db=mysql #不复制某个库
replicate-ignore-db=test
replicate-ignore-db=information_schema
relay-log=mysqld-relay-bin  # 开启日志中继
log-slave-updates  # slave将复制事件写进自己的二进制日志


# 自增字段奇数递增，防止冲突（1, 3, 5, ...,）
auto-increment-increment = 2  # 每次递增的步长
auto-increment-offset = 1  # 初始值


B服务器
# /etc/my.cnf
[mysqld]


# as slave
server-id=2
log-bin=mysql-bin
replicate-do-db =replication_test #只复制某个库，多个写多行
replicate-ignore-db=mysql #不复制某个库
replicate-ignore-db=test
replicate-ignore-db=information_schema
relay-log=mysqld-relay-bin  # 开启日志中继
log-slave-updates  # 示slave将复制事件写进自己的二进制日志


# as master
#开启二进制日志，并且名称为 /var/lib/mysql/mysql-bin.***
log-bin=mysql-bin
read-only=0  #主机读写权限，读写都可以
binlog-do-db=replication_test   #记录日志的数据库：需要的备份数据，多个写多行
binlog-ignore-db=mysql #不记录日志的数据库：不需要备份的数据库，多个写多行
binlog-ignore-db=test
binlog-ignore-db=information_schema


# 自增字段偶数递增，防止冲突（2, 4, 6, ...,）
auto-increment-increment = 2  # 每次递增的步长

auto-increment-offset = 2  # 初始值

总结一下,  开始的时候用了两个不同版本的机子作为从机, 结果报2003错误, slave_io_running 一直是 connecting 状态, 找了好久, 才发现主机的端口没开, 我这里没有防火墙, 但是要设置 my.cnf 里面的bind_address 这里默认是只监听 本机的,注释掉以后, 就可以监听到其他机子过来的请求了, 另外如果slave_sql_running 是no的, 那估计是你的同步的数据库可能不是一个, 他把其它数据库也同步了, 然后出现了各种写错误,  这个可以在主机里面添加ignore_db, 然后 重新change master to  .....  注意postion 的位置可能有改变, 所以要重新在master 上 show master status;   然后在slave 上重新执行一下   change master to ...... 问题应该不大,祝大家都能顺利完成配置.