mysql AB复制配置
使用俩台mysql服务器实现AB，主从复制。
一、在主ＭＡＳＴＥＲ服务器配置
MASTER 172.16.1.3
BACKUP 172.16.1.2
１、编辑my.cnf文件
#在原有基础上添加这俩行
[root@zhaoyun ~]# cat /etc/my.cnf
[mysqld]
log-bin=/mysql/bin #开启binlog
[mysqld]
log-bin=/mysql/bin #开启binlog
server-id=1 ＃配置不和另一台重复就行
２、重启服务
[root@zhaoyun ~]# service mysqld restart
停止 MySQL： [确定]
启动 MySQL： [确定]
停止 MySQL： [确定]
启动 MySQL： [确定]
３、授权用户
mysql> grant replication slave on *.* to
zhaoyun@172.16.1.2 identified by '123456'
[root@zhaoyun ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.77-log Source distribution
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.77-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
mysql> grant replication slave on *.* to zhaoyun@172.16.1.2 identified by '123456';
Query OK, 0 rows affected (0.15 sec)
mysql> grant replication slave on *.* to zhaoyun@172.16.1.2 identified by '123456';
Query OK, 0 rows affected (0.15 sec)
mysql>
4、在B服务器测试是否可以登录
[root@BACKUP ~]# mysql -uzhaoyun -p123456 -h172.16.1.3
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.77-log Source distribution
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.77-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show grants ;
+----------------------------------------------------------------------------------------------------+
| Grants for zhaoyun@172.16.1.2 |
+----------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'zhaoyun'@'172.16.1.2' IDENTIFIED BY PASSWORD '565491d704013245' |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
+----------------------------------------------------------------------------------------------------+
| Grants for zhaoyun@172.16.1.2 |
+----------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'zhaoyun'@'172.16.1.2' IDENTIFIED BY PASSWORD '565491d704013245' |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
5、查看master的状态
mysql> show master status ;
+------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| bin.000001 | 315 | | |
+------------+----------+--------------+------------------+
1 row in set (0.00 sec)
+------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| bin.000001 | 315 | | |
+------------+----------+--------------+------------------+
1 row in set (0.00 sec)
file字段是master的binlog文件名，position是binlog的节点。
二、配置BACKUP
1、编辑配置文件 my.cnf,添加4行。
[mysqld]
server-id=2
master-host=172.16.1.3 #MASTER服务器的ip地址
master-user=zhaoyun #连接MASTER服务器的用户名
master-password=123456 #密码
master-host=172.16.1.3 #MASTER服务器的ip地址
master-user=zhaoyun #连接MASTER服务器的用户名
master-password=123456 #密码
2、重启服务
[root@BACKUP ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
3、重启服务后会在数据库目录下生成几个文件
[root@BACKUP mysql]# ls
ib_logfile1 mysqld-relay-bin.000001 mysqld-relay-bin.index test
ibdata1 master.info mysql.sock
ib_logfile0 mysql relay-log.info
[root@BACKUP mysql]# pwd
/var/lib/mysql
ib_logfile1 mysqld-relay-bin.000001 mysqld-relay-bin.index test
ibdata1 master.info mysql.sock
ib_logfile0 mysql relay-log.info
[root@BACKUP mysql]# pwd
/var/lib/mysql
mysqld-relay-bin.000001 #binload文件，从master复制而来
mysqld-relay-bin.index #binload的信息
master.info #master信息
relay-log.info #中继日志信息
4、查看slave的状态
[root@BACKUP ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.77 Source distribution
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show slave status \G ;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.3
Master_User: zhaoyun
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000001
Read_Master_Log_Pos: 315
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 453
Relay_Master_Log_File: mysqld-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 315
Relay_Log_Space: 453
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.3
Master_User: zhaoyun
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000001
Read_Master_Log_Pos: 315
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 453
Relay_Master_Log_File: mysqld-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 315
Relay_Log_Space: 453
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
ERROR:
No query specified
No query specified
mysql>
#这个是主服务的binlog文件的状态，如果出现IO是NO的话，需检查这俩个文件的状态。
Master_Log_File: mysqld-bin.000001
Read_Master_Log_Pos: 315
Read_Master_Log_Pos: 315
#这俩条是slave的IO进程，和SQL进程的状态，AB复制的服务只有都为yes时才可用。
Slave_IO_Running: YES
Slave_SQL_Running: YES
#IO进程为NO可以将BACKUP的数据文件删除，重启服务重新同步就行了。
Slave_SQL_Running: YES
#IO进程为NO可以将BACKUP的数据文件删除，重启服务重新同步就行了。
5、到现在配置基本完成
三、创建一个表进行测试，是否同步成功。
1、在master上创建。
mysql> create database master ;
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql> use master
Database changed
mysql> create table master(id int,name char(5));
Query OK, 0 rows affected (0.04 se
2、在backup查看
Database changed
mysql> create table master(id int,name char(5));
Query OK, 0 rows affected (0.04 se
[root@BACKUP ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.77 Source distribution
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show database ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| master |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| master |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use master
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables ;
+------------------+
| Tables_in_master |
+------------------+
| master |
+------------------+
1 row in set (0.00 sec)
mysql> show tables ;
+------------------+
| Tables_in_master |
+------------------+
| master |
+------------------+
1 row in set (0.00 sec)
mysql>
可以看到数据已经同步过来了。到此实验成功。
故障排除：
IO 等于NO ： 需要检查节点和binlog文件名是否和在master看到的一致，如果不一致可以手动改写
命令
先停止slave服务
mysql>slave stop;
mysql>change master to master_log_file="在master看到的binlog文件名";
mysql>change master to master_log_pos=100; 这个数字是在master看到的。
mysql>slave start ;
change master to master_host='10.102.10.2',master_user='zhaoyun',master_password='monitor',master_log_file='mysql-bin.000003',master_log_pos='33221'
mysql> show master status ;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000001 | 507 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000001 | 507 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
SQL等于NO，可以试着删除几个文件重启服务重新同步
mysqld-relay-bin.000001 #binload文件，从master复制而来
mysqld-relay-bin.index #binload的信息
master.info #master信息
relay-log.info #中继日志信息
忽略mysql slave错误
mysql> show variables like '%skip%';
mysql> set global sql_slave_skip_counter=10;
本文转自zhaoyun00 51CTO博客，原文链接:http://blog.51cto.com/zhaoyun/733377
