mysql主从配置

在实际企业应用环境当中，单台mysql数据库是不足以满足日后业务需求的。譬如服务器发生故障，没有备份服务器来提供服务的话，业务就得停止。介于这种情况，我们来学习一下mysql主从复制。

使用mysql主从复制的好处有：

1、采用主从服务器这种架构，稳定性得以提升。如果主服务器发生故障，我们可以使用从服务器来提供服务。

2、在主从服务器上分开处理用户的请求，可以提升数据处理效率。

3、将主服务器上的数据复制到从服务器上，保护数据免受意外的损失。

环境描述：

新企业要搭建架构为主从复制的mysql数据库。

主服务器（mysql-master）：IP地址：192.168.124.128，mysql已安装，没有用户数据。

从服务器（mysql-slave）   ：IP地址：192.168.124.129，mysql已安装，没有用户数据。

主从服务器均可正常提供服务。

同步整个数据库

主从复制配置如下：

一。在主服务器上操作：

1)、确保/etc/my.cnf中有如下参数，没有的话需手工添加，并重启mysql服务。

[mysqld]

log-bin=mysql-bin 启动二进制文件

server-id=1 服务器ID

2)、登录mysql，在mysql中添加一个aaa的账号，并授权给从服务器。

[root@localhost ~]# mysql -uroot –p123456 登录mysql(默认没有密码)

mysql> grant replication slave on *.* to ‘aaa‘@‘192.168.124.129‘ identified by ‘1234‘; 创建aaa用户，并授权给192.168.124.129使用。

3)、查询主数据库状态，并记下File及Position的值，这个在后面配置从服务器的时候要用到。

mysql> show master status; 

二。在从服务器上操作：

1)、确保/etc/my.cnf中有log-bin=mysql-bin和server-id=1参数，并把server-id=1修改为server-id=2。修改之后如下所示：

[mysqld]

log-bin=mysql-bin 启动二进制文件

server-id=2 服务器ID

2）、重启mysql服务。

[root@localhost ~]#service mysqld restart

3)、登录mysql，执行如下语句

[root@localhost ~]# mysql -uroot –p123456

mysql> change master to master_host=‘192.168.124.128‘,master_user=‘aaa‘,master_password=‘1234‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=258;

4)、启动slave同步。

mysql> start slave;

5)、检查主从同步，如果您看到Slave_IO_Running和Slave_SQL_Running均为Yes，则主从复制连接正常。

mysql> show slave status\G

验证配置是否正常，mysql主从能否正常复制。

在主数据库上新建一个库，并且在库中写一个表和一些数据。

[root@localhost ~]# mysql -uroot –p123456

mysql> create database aaa;

mysql> use aaa;

mysql> create table user(id int(5),name char(10));

mysql> insert into user values (00001,‘zhangsan‘);

在从数据库中验证一下，是否正常复制到数据。

[root@localhost ~]# mysql -uroot –p123456

mysql> show databases;

mysql>use aaa;

mysql>select * from aaa.user;

从上图中的结果，我们可以看到mysql主从复制已经在起作用了，我们在主数据库中写入的数据已经复制到我们的从数据库中了。

同步单个库

主从复制配置如下：

在主数据库上操作：

1)、确保/etc/my.cnf中有如下参数，没有的话需手工添加，并重启mysql服务。

[mysqld]

log-bin=mysql-bin 启动二进制文件

server-id=1 服务器ID

binlog-do-db=test  #指定需要日志的数据库

[root@localhost ~]#service mysqld restart

2)、登录mysql，在mysql中添加一个用户同步的账号haha，并授权给从服务器即Slave机器有File权限，只赋予Slave机器有File权限还不行，还要给它REPLICATION SLAVE的权限才可以。。

[root@localhost ~]# mysql -uroot –p123456

mysql>grant file on *.* to ‘haha‘@‘192.168.124.129‘ identified by ‘1234‘;

mysql>grant replication slave on *.* to ‘haha‘@‘192.168.124.129‘ identified by ‘1234‘;

3）、用show master status/G命令看日志情况。正常为：

4)、进行锁表操作，不让数据进行写入动作，这么做事为了防止从数据库的原始数据和主数据库的原始数据不一致。

mysql> flush tables with read lock;

5)、使用mysqldump命令将刚才查询到的库导出来。

[root@localhost ~]#mysqldump –uroot –p123456 aaa > aaa.sql

6)、将导出来的库文件传送到从数据库的/tmp目录下。

[root@localhost ~]#scp -r aaa.sql [email protected]:/tmp

这中间需要输入一次验证口令。见下图

7)、进入mysql，进行表解锁操作。

mysql> unlock tables;

主数据库服务器上的操作告一段落。

在从数据库上操作：

1)、修改配置文件/etc/my.cnf

[mysqld]

log-bin=mysql-bin 启动二进制文件

server-id=2服务器ID
master-host=192.168.124.128
master-user=haha    #同步用户帐号
master-password=1234
master-port=3306
master-connect-retry=60 #预设重试间隔60秒
replicate-do-db=aaa    #告诉slave只做backup数据库的更新

2)、重启mysql服务。

[root@localhost ~]# service mysqld restart

3)、登录数据库，创建名为aaa的库。

mysql> create database aaa;

4)、将传过来的数据文件分别导入对应的数据库下。

[root@localhost ~]# mysql -uroot –p123456 aaa < aaa.sql

5)、检查主从同步，如果您看到Slave_IO_Running和Slave_SQL_Running均为Yes，则主从复制连接正常。

mysql> show slave status\G

验证主从是否正常配置：

在主数据库上插入数据：

mysql> show databases;

mysql> show tables;

mysql> use aaa;

mysql> select * from aaa.user;

mysql> insert into user values (4,‘ergou‘);

mysql> insert into user values (5,‘sanwazi‘);

在从数据库上查看是否正常同步：

mysql> show databases;

mysql> use aaa;

mysql> select * from aaa.user;

ps既做主又做从的my.cnf里一定写一行

log-slave-updates