MySQL主从备份

前言

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件 [ 百度百科 ]

开始

1 .安装好Mysql

2 .在Master服务器上，编辑服务器配置

vi /etc/my.cnf

配置如下：

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = AFData
binlog-do-db = AFOrder_0
binlog-do-db = AFOrder_1
binlog-do-db = AFOrder_2

3 .重启服务器，并设置Slave 数据库服务器账号密码

service mysqld restart
mysql -u root -p  
mysql -> grant replication slave on *.* to 'slave86'@'172.16.2.86' identified by '123456';
mysql -> grant replication slave on *.* to 'slave87'@'172.16.2.87' identified by '123456';
mysql -> show master status;

4 .配置Slave 1数据库服务器

vi /etc/my.cnf 

配置如下：

[mysqld]
server-id=2
log-bin=mysql-bin
replicate-do-db=AFData
replicate-do-db=AFOrder_0
replicate-do-db=AFOrder_1
replicate-do-db=AFOrder_2

5 .重启Slave 1 数据库服务器，并设置master-slave

service mysqld restart
mysql -u root -p  
mysql -> stop slave; 
mysql -> change master to master_host='172.16.2.85',master_user='slave86',master_password='123456';
mysql -> start slave; 
mysql -> show slave status \G;

6 .配置Slave 2数据库服务器

vi /etc/my.cnf 

配置如下：

[mysqld]
server-id=2
log-bin=mysql-bin
replicate-do-db=AFData
replicate-do-db=AFOrder_0
replicate-do-db=AFOrder_1
replicate-do-db=AFOrder_2

7 .重启Slave 2 数据库服务器，并设置master-slave

service mysqld restart
mysql -u root -p  
mysql -> stop slave; 
mysql -> change master to master_host='172.16.2.85',master_user='slave87',master_password='123456';
mysql -> start slave; 
mysql -> show slave status \G;

错误 -1：
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
解决方法：先查看mysql路径，然后改mysql server 中的 uuid，不一致即可

 ps -ef|grep mysql
 cd /var/lib/mysql
 vi  auto.cnf
 service mysqld restart

---

结束